Skip to content

SYS_OP_C2C full table scan

SYS_OP_C2C full table scan published on Комментариев к записи SYS_OP_C2C full table scan нет

on prod system we have query that use FTS instead of IRS

SQL_ID 960mmmrtwxbpf, child number 2
-------------------------------------
SELECT "SV_CPP$SEARCH_BY_CONTACT"."ID",
"SV_CPP$SEARCH_BY_CONTACT"."ID_OBJ", "SV_CPP$SEARCH_BY_CONTACT"."VAL"
FROM "SV_CPP$SEARCH_BY_CONTACT" WHERE "SV_CPP$SEARCH_BY_CONTACT"."VAL"
= :arg0

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1577350318

---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 186K(100)| |
|* 1 | TABLE ACCESS FULL| CTT | 264K| 10M| 186K (1)| 00:37:15 |
---------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter(("VRF_ON" IS NOT NULL AND SYS_OP_C2C("VAL")=:ARG0 AND
INTERNAL_FUNCTION("CTT_TYP")))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "ID"[NUMBER,22], "VAL"[VARCHAR2,4000], "R_OBJ"[NUMBER,22]

this is because developers put in bind variable nvarchar2 ( you may find bind type in v$sql_bind_capture ) instead of varchar2, they say that they can’t change code

solved by

create index i_ctt_nvar_var on ctt (SYS_OP_C2C(VAL)) online;

select from v$restore_point without catalog role

select from v$restore_point without catalog role published on Комментариев к записи select from v$restore_point without catalog role нет
CREATE TYPE t_rp_row AS OBJECT (
  scn           NUMBER,
  DATABASE_INCARNATION#  number,
  GUARANTEE_FLASHBACK_DATABASE varchar(3),
  STORAGE_SIZE  number,
  time    timestamp(9),
  restore_point_time timestamp(9),
  PRESERVED varchar2(3),
  name varchar2(128)
);
CREATE TYPE t_rp_tab IS TABLE OF t_rp_row;

create or replace FUNCTION get_rp_info  
          return t_rp_tab pipelined as
begin
  for i in ( select * from v_$restore_point   )
 loop
   pipe row (t_rp_row(i.scn,i.DATABASE_INCARNATION#,i.GUARANTEE_FLASHBACK_DATABASE,i.STORAGE_SIZE,i.time,i.restore_point_time,i.PRESERVED,i.name));    
  end loop;
 return;
end;

and you can grant select on this funtion to every one, without FLASHBACK ANY TABLE\SELECT_CATALOG_ROLE\SELECT ANY grants:

TEST_RP@u10pgp1> select * from table(SYS.get_rp_info)
  2  ;

       SCN DATABASE_INCARNATION# GUARANTEE STORAGE_SIZE TIME				RESTORE_POINT_TIME	       PRESERVED NAME
---------- --------------------- --------- ------------ ------------------------------- ------------------------------ --------- ----------
3.3816E+11		       2 NO		      0 14-DEC-15 04.58.27.000000000 PM 			       NO	 T1

Oracle Memory Architecture

Oracle Memory Architecture published on Комментариев к записи Oracle Memory Architecture нет

http://www.datadisk.co.uk/html_docs/oracle/memory.htm

copy to my blog
 

 

Oracle Memory Architecture

Oracle uses three kinds of memory structures

SGA

(System Global Area)

is a large part of memory that all the oracle background processes access.

PGA

(Process Global Area)

This is memory that is private to a single process or thread and is not accessible by any other process or thread

UGA

(User Global Area)

This is memory that is assoicated with your session, it can be found in the PGA or SGA depending on whether you are connected to the database via shared server

Shared Server – the UGA will be in the SGA

Dedicated Server – the UGA will be in the PGA

SGA

There are five memory structures that make up the System Global Area (SGA). The SGA will store many internal data structures that all processes need access to, cache data from disk, cache redo data before writing to disk, hold parsed SQL plans and so on.image00

Shared Pool

The shared pool consists of the following areas:

Library cache includes the shared SQL area, private SQL areas, PL/SQL procedures and packages the control structures such as locks and library cache handles. Oracle code is first parsed, then executed , this parsed code is stored in the library cache, oracle first checks the library cache to see if there is an already parsed and ready to execute form of the statement in there, if there is this will reduce CPU time considerably, this is called a soft parse, If Oracle has to parse it then this is called a hard parse. If there is not enough room in the cache oracle will remove older parsed code, obviously it is better to keep as much parsed code in the library cache as possible. Keep an eye on missed cache hits which is an indication that a lot of hard parsing is going on.

Dictionary cache is a collection of database tables and views containing information about the database, its structures, privileges and users. When statements are issued oracle will check permissions, access, etc and will obtain this information from its dictionary cache, if the information is not in the cache then it has to be read in from the disk and placed in to the cache. The more information held in the cache the less oracle has to access the slow disks.

The parameter SHARED_POOL_SIZE is used to determine the size of the shared pool, there is no way to adjust the caches independently, you can only adjust the shared pool size.

The shared pool uses a LRU (least recently used) list to maintain what is held in the buffer, see buffer cache for more details on the LRU.

You can clear down the shared pool area by using the following command

    alter system flush shared_pool;

Buffer cache

This area holds copies of read data blocks from the datafiles. The buffers in the cache contain two lists, the write list and the least used list (LRU). The write list holds dirty buffers which contain modified data not yet written to disk.

The LRU list has the following

  • free buffers hold no useful data and can be reused
  • pinned buffers actively being used by user sessions
  • dirty buffers contain data that has been read from disk and modified but hasn’t been written to disk

It’s the database writers job to make sure that they are enough free buffers available to users session, if not then it will write out dirty buffers to disk to free up the cache.

There are 3 buffer caches

  • Default buffer cache, which is everything not assigned to the keep or recycle buffer pools, DB_CACHE_SIZE
  • Keep buffer cache which keeps the data in memory (goal is to keep warm/hot blocks in the pool for as long as possible), DB_KEEP_CACHE_SIZE.
  • Recycle buffer cache which removes data immediately from the cache after use (goal here is to age out a blocks as soon as it is no longer needed), DB_RECYCLE_CACHE_SIZE.

The standard block size is determined by the DB_CACHE_SIZE, if tablespaces are created with a different block sizes then you must also create an entry to match that block size.

DB_2K_CACHE_SIZE (used with tablespace block size of 2k)

DB_4K_CACHE_SIZE (used with tablespace block size of 4k)

DB_8K_CACHE_SIZE (used with tablespace block size of 8k)

DB_16K_CACHE_SIZE (used with tablespace block size of 16k)

DB_32K_CACHE_SIZE (used with tablespace block size of 32k)

buffer cache hit ratio is used to determine if the buffer cache is sized correctly, the higher the value the more is being read from the cache.

     hit rate = (1 – (physical reads / logical reads)) * 100

You can clear down the buffer pool area by using the following command

    alter system flush buffer_cache;

Redo buffer

The redo buffer is where data that needs to be written to the online redo logs will be cached temporarily before it is written to disk, this area is normally less than a couple of megabytes in size. These entries contain necessary information to reconstruct/redo changes by the INSERT, UPDATE, DELETE, CREATE, ALTER and DROP commands.

The contents of this buffer are flushed:

  • Every three seconds
  • Whenever someone commits a transaction
  • When its gets one third full or contains 1MB of cached redo log data.
  • When LGWR is asked to switch logs

Use the parameter LOG_BUFFER parameter to adjust but be-careful increasing it too large as it will reduce your I/O but commits will take longer.

Large Pool

This is an optional memory area that provide large areas of memory for:

  • Shared Server – to allocate the UGA region in the SGA
  • Parallel execution of statements – to allow for the allocation of inter-processing message buffers, used to coordinate the parallel query servers.
  • Backup – for RMAN disk I/O buffers

The large pool is basically a non-cached version of the shared pool.

Use the parameter LARGE_POOL_SIZE parameter to adjust

Java Pool

Used to execute java code within the database.

Use the parameter JAVA_POOL_SIZE parameter to adjust (default is 20MB)

Streams Pool

Streams are used for enabling data sharing between databases or application environment.

Use the parameter STREAMS_POOL_SIZE parameter to adjust

The fixed SGA contains a set of variables that point to the other components of the SGA, and variables that contain the values of various parameters., the area is a kind of bootstrap section of the SGA, something that Oracle uses to find other bits and pieces of the SGA

For more information regarding setting up the SGA click here.

PGA and UGA

The PGA (Process Global Area) is a specific piece of memory that is associated with a single process or thread, it is not accessible by any other process or thread, note that each of Oracles background processes have a PGA area. The UGA (User Global Area) is your state information, this area of memory will be accessed by your current session, depending on the connection type (shared server) the UGA can be located in the SGA which is accessible by any one of the shared server processes, because a dedicated connection does not use shared servers the memory will be located in the PGA

  • Shared server – UGA will be part of the SGA
  • Dedicated server – UGA will be the PGA

Memory Area

Dedicated Server

Shared Server

Nature of session memory

Private

Shared

Location of the persistent area

PGA

SGA

Location of part of the runtim area for select statements

PGA

PGA

Location of the runtime area for DML/DDL statements

PGA

PGA

Oracle creates a PGA area for each users session, this area holds data and control information, the PGA is exclusively used by the users session. Users cursors, sort operations are all stored in the PGA. The PGA is split in to two areas image01

Session Information

(runtime area)

PGA in an instance running with a shared server requires additional memory for the user’s session, such as private SQL areas and other information.

Stack space

(private sql area)

The memory allocated to hold a sessions variables, arrays, etc and other information relating to the session.

Automatic PGA Management

To reduce response times sorts should be performed in the PGA cache area (optimal mode operation), otherwise the sort will spill on to the disk (single-pass / multiple-pass operation) this will reduce performance, so there is a direct relationship between the size of the PGA and query performance. You can manually tune the below to increase performance

  • sort_area_size – total memory that will be used to sort information before swapping to disk
  • sort_area_retained_size – memory that is used to retained data after a sort
  • hash_area_size – memory that will would be used to store hash tables
  • bitmap_merge_area_size – memory Oracle uses to merge bitmaps retrieved from a range scan of the index.

Staring with Oracle 9i there is a new to manage the above settings that is to let oracle manage the PGA area automatically by setting the parameter following parameters Oracle will automatically adjust the PGA area basic on users demand.

  • workarea_size_policy – you can set this option to manual or auto (default)
  • pga_aggregate_target – controls how much to allocate the PGA in total

Oracle will try and keep the PGA under the target value, but if you exceed this value Oracle will perform multi-pass operations (disk operations).

System Parameters

workarea_size_policy

manual or auto (default)

pga_aggregate_target

total amount of memory allocated to the PGA

 

PGA/UGA amount used

select a.name, to_char(b.value, ‘999,999,999’) value

from v$statname a, v$mystat b

where a.statistic# = b.statistic#

and a.name like ‘%ga memory%’;

Display if using memory or disk sorts

set autotrace traceonly statistics;

set autotrace off;

Display background process PGA memory usage

select program, pga_used_mem, pga_alloc_mem, pga_max_mem from v$process;

 

script to slice awr\ash report by time

script to slice awr\ash report by time published on Комментариев к записи script to slice awr\ash report by time нет
select tt.*,s.sql_text from (
select t.time#,sum(t.pct_time) pct_db_time,t.tot_act_sess,t.pl_sql_obj,t.sql_id,t.event,t.username from 
(
select TRUNC( ash.SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM  ash.SAMPLE_TIME), &&sample_interval_in_minutes) /(24 * 60) time# ,
            100* round (sum( ash.TM_DELTA_DB_TIME) / (sum(sum( ash.TM_DELTA_DB_TIME)) over ( partition by TRUNC(SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM  ash.SAMPLE_TIME), &&sample_interval_in_minutes) /(24 * 60) )) ,2) pct_time,
            count (distinct  ash.session_id) over ( partition by TRUNC( ash.SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM  ash.SAMPLE_TIME), &&sample_interval_in_minutes) /(24 * 60) )  as tot_act_sess,
            ash.session_id, ash.session_serial#,
            dp.owner||nvl2(dp.object_name,'.'||dp.object_name,null) ||nvl2(dp.procedure_name,'.'||dp.procedure_name,null) as pl_sql_obj,           
            ash.sql_id ,ash.event,du.username
            from dba_hist_active_sess_history ash
  left join dba_procedures dp on dp.object_id=ash.PLSQL_ENTRY_OBJECT_ID and dp.subprogram_id=ash.PLSQL_ENTRY_SUBPROGRAM_ID
  join dba_users du on du.user_id=ash.user_id
  where  ash.sample_time between trunc (sysdate -2) +14/24 +30/(24*60) and trunc(sysdate-2) +16/24  +30/(24*60)
group by dp.owner||nvl2(dp.object_name,'.'||dp.object_name,null) ||nvl2(dp.procedure_name,'.'||dp.procedure_name,null),
sql_id ,ash.event,
TRUNC(SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM SAMPLE_TIME), &&sample_interval_in_minutes) /(24 * 60) ,
session_id,session_serial#,du.username
) t
  group by t.time#,t.tot_act_sess,t.pl_sql_obj,t.sql_id,t.username,t.event ) tt
  left join dba_hist_sqltext s on tt.sql_id=s.sql_id
  where tt.pct_db_time >0
order by 1 asc,2 desc;

replace dba_hist_active_sess_history with v$active_session_history to get ash info

oracle 12c new features index:

oracle 12c new features index: published on Комментариев к записи oracle 12c new features index: нет

CDB and PDB
View
• CDB_xxx: All of the objects in the CDB across all PDBs
• DBA_xxx: All of the objects in a container or PDB
• CDB_pdbs: All PDBs within CDB
• CDB_tablespaces: All tablespaces within CDB
• CDB_users: All users within CDB (common and local)
• V$PDBS: Displays information about PDBs associated with the current instance
• V$CONTAINERS: Displays information about PDBs and the root associated with the current instance
• PDB_PLUG_IN_VIOLATIONS: Displays information about PDB violations after compatibility check with CDB
• RC_PDBS: Recovery catalog view about PDB backups

CDB and PDB
• ENABLE_PLUGGABLE_DATABASE: Required to create a CDB at CDB instance startup
• PDB_FILE_NAME_CONVERT: Maps names of existing files to new file names when processing a CREATE PLUGGABLE DATABASE statement
• CDB_COMPATIBLE: Enables you to get behavior similar to a non- CDB
• PDB_OS_CREDENTIAL 12.1.0.2 : Enables another OS user than oracle to connect to PDBs
Package
• DBMS_PDB.DESCRIBE
• DBMS_PDB.CHECK_PLUG_COMPATIBILITY

Heat Map and ADO
• DBA_HEAT_MAP_SEG_HISTOGRAM
• DBA_HEAT_MAP_SEGMENT
• V$HEAT_MAP_SEGMENT
• DBA_ILMOBJECTS
• DBA_ILMPOLICIES, DBA_ILMDATAMOVEMENTPOLICIES
• DBA_ILMTASKS, DBA_ILMEVALUATIONDETAILS
• DBA_ILMRESULTS
Parameter
• HEAT_MAP: Activates activity tracking and statistics collection

Heat Map and ADO
Package
• DBMS_HEAT_MAP
– BLOCK_HEAT_MAP – EXTENT_HEAT_MAP
• DBMS_ILM
– EXECUTE_ILM,STOP_ILM
– PREVIEW_ILM,ADD_TO_ILM,REMOVE_FROM_ILM – EXECUTE_ILM_TASK
• DBMS_ILM_ADMIN – CUSTOMIZE
– DISABLE_ILM, ENABLE_ILM
– CLEAR_HEAT_MAP_ALL,CLEAR_HEAT_MAP_TABLE – SET_HEAT_MAP_START
– SET_HEAT_MAP_ALL,SET_HEAT_MAP_TABLE

In-Database Archiving and Temporal Validity
New column
• ORA_ARCHIVE_STATE in application tables
Package
• DBMS_FLASHBACK_ARCHIVE – ENABLE_AT_VALID_TIME – SET_CONTEXT_LEVEL

Security: Auditing
View
• UNIFIED_AUDIT_TRAIL
• AUDIT_UNIFIED_POLICIES
• AUDIT_UNIFIED_ENABLED_POLICIES
New columns in UNIFIED_AUDIT_TRAIL
• FGA_POLICY_NAME
• DP_xxx (Data Pump operations)
• RMAN_xxx
• OLS_xxx
• DV_xxx (Database Vault operations)
• XS_xxx (Real Application Security operations)
Package
• DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL

Security: Privilege Analysis
view:
• DBA_USED_PRIVS
• DBA_USED_SYSPRIVS, DBA_USED_OBJPRIVS
• DBA_USED_PUBPRIVS
• DBA_USED_OBJPRIVS_PATH, DBA_USED_SYSPRIVS_PATH
• DBA_UNUSED_PRIVS
• DBA_UNUSED_OBJPRIVS, DBA_UNUSED_SYSPRIVS
• DBA_UNUSED_OBJPRIVS_PATH
• DBA_UNUSED_SYSPRIVS_PATH
• DBA_PRIV_CAPTURES

Security: Privilege Analysis and New Privileges
Package
• DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE • DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE • DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE • DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT • DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE
Privilege
• SYSBACKUP
• SYSDG
• SYSKM
• PURGE DBA_RECYCLEBIN

Security: Oracle Data Redaction
View
• REDACTION_POLICIES
• REDACTION_COLUMNS
• REDACTION_VALUES_FOR_TYPE_FULL
Package
• DBMS_REDACT.ADD_POLICY
• DBMS_REDACT.ALTER_POLICY
• DBMS_REDACT.DROP_POLICY
• DBMS_REDACT.ENABLE_POLICY
• DBMS_REDACT.DISABLE_POLICY
• DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES
Privilege
• EXEMPT REDACTION POLICY
• EXEMPT DDL REDACTION POLICY
• EXEMPT DML REDACTION POLICY

HA: Flashback Data Archive
• DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL
• DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT

Manageability: Database Operations
New Column
• DBOP_NAME
• DBOP_EXEC_ID in
– V$SQL_MONITOR
– V$ACTIVE_SESSION_HISTORY
– CDB_HIST_ACTIVE_SESS_HISTORY
– DBA_HIST_ACTIVE_SESS_HISTORY
Parameter
• STATISTICS_LEVEL=TYPICAL
• CONTROL_MANAGEMENT_PACK_ACCESS= DIAGNOSTIC+TUNING

Manageability: Database Operations
Package
• DBMS_SQL_MONITOR.BEGIN_OPERATION
• DBMS_SQL_MONITOR.END_OPERATION
• DBMS_SQL_MONITOR.REPORT_SQL_MONITOR
• DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_LIST
• DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_XML
• DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_LIST_XML

Manageability: ADDM
Package DBMS_ADDM:
New functions
• REAL_TIME_ADDM_REPORT
• COMPARE_INSTANCES
• COMPARE_DATABASES
• COMPARE_CAPTURE_REPLAY_REPORT • COMPARE_REPLAY_REPLAY_REPORT

Performance: In-Memory Column Store
Package
• DBMS_INMEMORY.REPOPULATE Parameter
• INMEMORY_SIZE
• INMEMORY_QUERY
• INMEMORY_CLAUSE_DEFAULT
• INMEMORY_FORCE
New Columns
• INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION in DBA_TABLES, DBA_TAB_PARTITIONS
• DEF_INMEMORY_PRIORITY, DEF_INMEMORY_DISTRIBUTE, DEF_INMEMORY_COMPRESSION in DBA_TABLESPACES

Performance: In-Memory Column Store
View
• V$IM_COLUMN_LEVEL • V$IM_COL_CU
• V$IM_HEADER
• V$IM_SEGMENTS
• V$IM_USER_SEGMENTS
• V$IM_SEGMENTS_DETAIL • V$IM_SEG_EXT_MAP
• V$IM_TBS_EXT_MAP
• V$IM_SMU_HEAD
• V$IM_SMU_CHUNK

Performance: Full Database In-Memory Caching
New Columns
• FORCE_FULL_DB_CACHING in V$DATABASE

Performance: Automatic Big Table Caching
Parameter
• DB_BIG_TABLE_CACHE_PERCENT_TARGET
View
• V$BT_SCAN_CACHE
• V$BT_SCAN_OBJ_TEMPS

Performance: SQL Tuning
Package
• DBMS_SPM.REPORT_AUTO_EVOLVE_TASK • DBMS_SPM.CREATE_EVOLVE_TASK
• DBMS_SPM.EXECUTE_EVOLVE_TASK
• DBMS_SPM.REPORT_EVOLVE_TASK
• DBMS_SPM.IMPLEMENT_EVOLVE_TASK • DBMS_STATS.SEED_COL_USAGE
• DBMS_STATS.REPORT_COL_USAGE
Parameter
• OPTIMIZER_ADAPTIVE_REPORTING_ONLY New Column
• IS_RESOLVED_ADAPTIVE_PLAN, IS_REOPTIMIZABLE in V$SQL
View
• DBA_SQL_PLAN_DIR_OBJECTS

Performance: Resource Manager
view
• DBA_CDB_RSRC_PLAN_DIRECTIVES
Package DBMS_RESOURCE_MANAGER: New procedures
• CREATE_CDB_PLAN / UPDATE_CDB_PLAN / DELETE_CDB_PLAN
• CREATE_CDB_PLAN_DIRECTIVE / UPDATE_CDB_PLAN_DIRECTIVE
/ DELETE_CDB_PLAN_DIRECTIVE
• UPDATE_CDB_DEFAULT_DIRECTIVE
• UPDATE_CDB_AUTOTASK_DIRECTIVE
Package DBMS_RESOURCE_MANAGER: Updated procedure
• SET_CONSUMER_GROUP_MAPPING: New values for the ORACLE_FUNCTION attribute
– INMEMORY_PREPOPULATE
– INMEMORY_POPULATE
– INMEMORY_REPOPULATE
– INMEMORY_TRICKLE

Performance: Multi-Process Multi-Threaded
Parameter
• THREADED_EXECUTION=TRUE

Performance: Database Smart Flash Cache
Parameter change
• DB_FLASH_CACHE_FILE=file1, file1
• DB_FLASH_CACHE_SIZE=size1, size2

Performance: Temporary UNDO
veiew:
• V$TEMPUNDOSTAT Parameter
• TEMP_UNDO_ENABLED=TRUE

Performance: Online Operations
package
• DBMS_REDEFINITION.START_REDEF_TABLE ( …, copy_vpd_opt => DBMS_REDEFINITION.CONS_VPD_AUTO)
• EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE (…, dml_lock_timeout => 100);

Miscellaneous: Partitioning
New Column
• DEF_INDEXING in
– DBA_PART_TABLES
• INDEXING in
– DBA_TAB_PARTITIONS
– DBA_TAB_SUBPARTITIONS
– DBA_INDEXES
• ORPHANED_ENTRIES in
– DBA_INDEXES
– DBA_IND_PARTITIONS
Package
• DBMS_PART.CLEANUP_GIDX

Miscellaneous: SQL
Parameter
• MAX_STRING_SIZE=standard|extended
• DB_SECUREFILE=preferred

Data Comparison
Package
• DBMS_COMPARISON

Oracle Learning Library OBEs and Demos
– http://www.oracle.com/technology/obe/demos/admin/demos.html
– http://www.oracle.com/technology/obe/start/index.html
https://apexapps.oracle.com/pls/apex/f?p=44785:141:::::P141_PAGE_ID,P141_SECTION_ID:6,84

Real-Time Database Operation Monitoring

Real-Time Database Operation Monitoring published on Комментариев к записи Real-Time Database Operation Monitoring нет

A database operation is:
• One or more SQL or PL/SQL statements
• A series of statements done from on or more sessions
A database operation can:
• Be monitored
• Produce active reports

A DB operation is an operation that the database server runs to accomplish tasks. It can be either simple or composite.
• Simple database operations: Consist of a single SQL statement or a single PL/SQL function or procedure
• Composite database operations: Consist of the activity of one or more sessions between two points in time. SQL statements or PL/SQL procedures running in these sessions are part of the composite operations:
– Single sessions: Single-session operations fall into two cases.
– In one case, exactly one session exists for the duration of the database
operation.
– In the other case, multiple sessions exist in the database operation, but no more than one session runs at any given time. The application jumps from session to session.
– Multiple concurrent sessions: In ETL, it is common for a job to involve multiple sessions running at the same time. You can define the entire ETL job as a single database operation.


In this illustration, there are four sessions. The first (upper) session shows that a session can belong to at the most one database operation at a time. It belongs to database operation A at first, then belongs to database operation B. The END_OPERATION and then BEGIN_OPERATION commands cause the session to change database operations.
Sessions 1, 2, and 3 show that the name and execution ID are taken together to uniquely identify the database operation B.
The time that a session belongs to a database operation but is not executing a SQL or PL/SQL statement is idle time.

Enabling Monitoring of Database Operations
•At system level:
– Set STATISTICS_LEVEL to TYPICAL
– Set CONTROL_MANAGEMENT_PACK_ACCESS to DIAGNOSTIC+TUNING
•At database operation level:
– Use FORCED_TRACKING attribute in
DBMS_SQL_MONITOR.BEGIN_OPERATION function
• At statement level:
– Use the MONITOR hint

Additionally, the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC+TUNING

Identifying, Starting and Completing a Database Operation
1. Identify the database operation.
– Operation Name
– Execution ID
2. Start the database operation with
DBMS_SQL_MONITOR.BEGIN_OPERATION.
3. Complete the database operation with
DBMS_SQL_MONITOR.END_OPERATION.

SQL> VAR dbop_eid NUMBER;
SQL> EXEC :dbop_eid := DBMS_SQL_MONITOR.BEGIN_OPERATION (‘ORA.MV.refresh’, FORCED_TRACKING => ‘Y’)
SQL> SELECT …
SQL> SELECT …
SQL> EXEC DBMS_SQL_MONITOR.END_OPERATION (‘ORA.MV.refresh’, :dbop_eid)

Completed database operations and reports are stored in AWR and can be reported in the following views:
– DBA_HIST_REPORTS, DBA_HIST_REPORTS_DETAILS
– DBA_HIST_ACTIVE_SESS_HISTORY

Oracle Data Redaction

Oracle Data Redaction published on Комментариев к записи Oracle Data Redaction нет

Available Redaction Methods

Type Description
None No redaction is performed.
Full Columns are redacted to constant values based on the column data type.
Partial User-specified positions are replaced by a user-specified character.
Random Data type is preserved and different values are output each time.
Regular Expression A “match and replace” based on parameters is performed.

What Is a Redaction Policy?
The redaction policy dictates:
• What to redact, as specified by:
– Schema name (OBJECT_SCHEMA)
– Object name (OBJECT_NAME)
– Column name (COLUMN_NAME)
• How to redact, as specified by:
– Function type (FUNCTION_TYPE)
– Function parameters (FUNCTION_PARAMETERS) or regular expression parameters (REGEXP_*)
• When to redact, as specified in a policy expression (EXPRESSION)
When you create the policy, you can provide only one “how to redact” specification.

Restrictions:
• You cannot redact SYS or SYSTEM schema objects.
• You cannot redact virtual columns.
• You cannot redact columns of specific data types.
• You can apply VPD policies only to columns that have not been redacted.

Managing Redaction Policies
• You use the procedures in the DBMS_REDACT package to manage redaction policies:
– ADD_POLICY: Add a redaction policy to a table.
– DROP_POLICY: Remove a redaction policy from a table.
– ALTER_POLICY: Change a redaction policy.
– ENABLE_POLICY: Enable a redaction policy after it is disabled.
– DISABLE_POLICY: Disable a redaction policy.
• EXECUTE privilege on DBMS_REDACT is required to
execute the procedures.
• Enterprise Manager Cloud Control 12c supports Oracle Data Redaction.

Use the DBMS_REDACT.ALTER_POLICY procedure to alter an existing redaction policy as follows:
• Modify the policy expression.
• Modify the type of redaction for a specified column.
• Modify the function parameters for a specified column.
• Add a column to the redaction policy.
• Remove a column from the redaction policy.

Exempting Users from Redaction Policies
• Conditions included in policy expressions may allow users to see actual data.
• SYS is exempt from all redaction policies.
• Grant the EXEMPT REDACTION POLICY system privilege to exempt other users from all redaction policies.
• Best Practices:
– Use default deny (white list) conditions in policy expressions.
– Grant the EXEMPT REDACTION POLICY privilege judiciously to ensure that the redaction policies are enforced appropriately.

The SYS user is exempt from redaction policies and is able to view actual values for data.
If other users need access to the actual values, you must grant them the EXEMPT REDACTION POLICY system privilege. This privilege exempts the users from all redaction policies.
Users who are granted the DBA role are also exempt from redaction policies because the DBA role contains the EXP_FULL_DATABASE role, which is granted the EXEMPT REDACTION POLICY system privilege.
Because applications may need to perform CREATE TABLE AS SELECT operations that involve redacted source columns, you can grant the application the EXEMPT DDL REDACTION POLICY system privilege.

usefull links:
Data Redaction Demo for Oracle Advanced Security (Oracle Database 12c) – Part 2
Protecting Data with Data Redaction

Privilege Analysis

Privilege Analysis published on Комментариев к записи Privilege Analysis нет

Oracle Database 12c offers a new package to analyze used privileges. Privilege Analysis requires Oracle Database Vault, which is a licensed option.
good tutorial:

saved copy

Determining Least Privilege Access Using Privilege Analysis

This tutorial covers the steps required to capture privileges used by users during a short period of time to help you decide which privileges need to be revoked or kept.

Time to Complete

Approximately 25 minutes.

Introduction

The Privilege Analysis feature allows you to:
Define a privilege capture
Start a privilege analysis during a period of time
Generate results about privileges and roles used and unused during the analysis period
Compare used and unused privileges using views to decide which privileges and or roles need to be revoked or kept
Delete capture analysis
Scenario

In this tutorial, you will perform three types of analysis:

Capture privileges used by all users
Capture privileges used through roles
Capture privileges used through contexts
Prerequisites

Before starting this tutorial, you should:
Oracle Database 12c should be installed.
You need a started database.
The environment used in the development of this tutorial is as follows:

ORACLE_HOME: /u01/app/oracle/product/12.1.0
TNS Listener port: 1521
Container databases:
SID: cdb1
SID: cdb2
Pluggable databases (in cdb1):
pdb1
pdb2
Non-CDB SID: noncdb

Create Users, Roles, Tables and Grant Privileges and Roles
Use SQL*Plus to connect as system to create the users JIM and TOM , roles, and grant privileges and or roles.

Connect as SYSTEM:

. oraenv
[enter cdb1 at the prompt]

sqlplus system/oracle@localhost:1521/pdb1

Create users.
create user jim identified by jim;
create user tom identified by tom;
create user ann identified by ann;

Create roles.
create role HR_MGR;
create role SALES_CLERK; 
create role ANALYST;

Grant privileges. 
grant create session to jim, tom, ann;

grant select, update, delete, insert on hr.employees to HR_MGR;
grant HR_MGR to JIM;

grant select on sh.sales to SALES_CLERK;
grant SALES_CLERK to TOM; 

grant select any table to ANALYST;
grant ANALYST to ann;

Define the Captures
In this section, you will prepare the three types of captures.

Define the capture of privileges used by all users.
Define the capture of privileges used by all users.
Use the DBMS_PRIVILEGE_CAPTURE package and the CREATE_CAPTURE procedure with the appropriate type of capture.

exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( - 
        name           =>  'All_privs', - 
        description    =>  'All privs used', -
        type           =>   dbms_privilege_capture.g_database)

Define the capture of privileges used through roles.
Define a capture of privileges used by roles HR_MGR, ANALYST, and SALES_CLERK. Use the DBMS_PRIVILEGE_CAPTURE package and the CREATE_CAPTURE procedure with the appropriate type of capture and the list of roles analyzed.

exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( - 
        name           => 'Role_privs', -
        description    => 'Privs used by HR_MGR, SALES_CLERK', -
        type           =>  dbms_privilege_capture.g_role, -
        roles          =>  role_name_list('HR_MGR', 'SALES_CLERK','ANALYST'))

Define the capture of privileges used through contexts.
Define a capture of privileges used by the user TOM or by the specific role SALES_CLERK.
Use the DBMS_PRIVILEGE_CAPTURE package and the CREATE_CAPTURE procedure with the appropriate type of capture, the list of roles analyzed and the context in which the analysis would take place once started.

exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( - 
        name           => 'Special_capt', - 
        description    => 'Special', -
        type           =>  dbms_privilege_capture.g_role_and_context, -
        roles          =>  role_name_list('SALES_CLERK'), -
        condition      => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')=''TOM''')

List the existing captures.
Use the DBA_PRIV_CAPTURES view.
Note: You may see different role ID results.

 
COL name     FORMAT A12
COL type     FORMAT A16
COL enabled  FORMAT A2
COL roles    FORMAT A24 COL context  FORMAT A43

select name, type, enabled, roles, context
from   dba_priv_captures;

Start Privilege Captures and Analyze

Start and analyze the capture of privileges used by all users.
Start capturing the privileges while users are performing their daily work using privileges. Use the ENABLE_CAPTURE procedure.

exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'All_privs')

exit

[Note that you must exit SQL*Plus and then relaunch SQL*Plus in the next step, otherwise you may see unintended results in the privilege analysis views in later steps of this tutorial.]
The users JIM, TOM, and ANN run SQL statements using privileges. JIM, and ANN who select rows from HR.EMPLOYEES table and TOM who selects rows from SH.SALES table.

sqlplus jim/jim@localhost:1521/pdb1
select * from hr.employees where salary < 3000;

connect tom/tom@localhost:1521/pdb1
select * from sh.sales where amount_sold < 6.42 and cust_id = 6452;

connect ann/ann@localhost:1521/pdb1
select * from hr.employees where salary > 3000 and rownum <=5 order by salary desc;

exit

[Note you must exit SQL*Plus and then relaunch SQL*Plus in the next step.]

Stop capturing. Use the DISABLE_CAPTURE procedure.

sqlplus system/oracle@localhost:1521/pdb1
exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => 'All_privs')

Generate the capture results. It may take a few minutes. Use the GENERATE_RESULT procedure.

exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'All_privs')

Display the object privileges used during the capture period. Use the DBA_USED_OBJPRIVS view.

COL username FORMAT A10
COL object_owner FORMAT A12
COL object_name FORMAT A30
COL obj_priv FORMAT A25

select username, object_owner, object_name, obj_priv
from   dba_used_objprivs
where  username in ('JIM', 'TOM')
and    object_name in ('SALES' , 'EMPLOYEES');

Display the privileges used by ANN. Use the DBA_USED_PRIVS view. Does Ann, or the ANALYST role, really need the SELECT ANY TABLE privilege?

select username, used_role, sys_priv, object_name FROM dba_used_privs
where  username ='ANN' and object_name = 'EMPLOYEES';

Display the system privileges used. Use the DBA_USED_SYSPRIVS view.

select username, sys_priv FROM dba_used_sysprivs
where  username IN ('JIM', 'TOM', 'ANN');

Display the path of the privileges used if the privileges were granted to roles, and roles to users. Use the DBA_USED_OBJPRIVS_PATH view.

COL object_name FORMAT A12
COL path FORMAT A32
COL obj_priv FORMAT A10

select username, obj_priv, object_name, path
from   dba_used_objprivs_path 
where  username IN ('TOM','JIM','ANN') 
and    object_name IN ('SALES','EMPLOYEES');

JIM is granted select, update, delete, insert privileges on HR.EMPLOYEES table through HR_MGR role. He used only the SELECT privilege thus far.
Use the DBA_UNUSED_PRIVS view to list the unused privileges. You can decide which of the unused privileges can be revoked if necessary.

select username, sys_priv, obj_priv, object_name, path
from   dba_unused_privs
where  username='JIM'
and object_name = 'EMPLOYEES';

Start and analyze the capture of privileges used through roles.
Delete the previous capture so as to remove all previous captured information from the views. Use the DROP_CAPTURE procedure.

exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'All_privs')

Verify that there is no data left from the All_privs capture. Use the DBA_UNUSED_PRIVS view. There should be no rows returned by the query.

select username, sys_priv, obj_priv, object_name, path
from   dba_unused_privs
where  username='JIM';

Start capturing the privileges while users are performing their daily work using roles.

exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'Role_privs')

The users JIM and TOM run SQL statements using privileges. JIM who selects rows from HR.EMPLOYEES table and TOM who selects rows from SH.SALES table.

connect jim/jim@localhost:1521/pdb1
select * from hr.employees where salary < 3000;

connect tom/tom@localhost:1521/pdb1
select * from sh.sales where amount_sold < 6.42 and cust_id = 6452;

Stop capturing.

connect system/oracle@localhost:1521/pdb1
exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => 'Role_privs')

Generate the capture results.

exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name =>'Role_privs')

Display the object privileges used by the roles HR_MGR and SALES_CLERK during the capture period.

COL used_role FORMAT A14

select  username, object_owner, object_name, obj_priv, used_role
from    dba_used_objprivs
where   used_role in ('HR_MGR', 'SALES_CLERK');

Display the system privileges used by the roles HR_MGR and SALES_CLERK during the capture period.

select username, sys_priv, used_role 
from dba_used_sysprivs
where used_role in ('HR_MGR', 'SALES_CLERK');

No rows are returned because no system privileges were used.

HR_MGR is granted select, update, delete on HR.EMPLOYEES table. The role used by JIM during the capture period used the SELECT privilege.
The unused privileges are visible in DBA_UNUSED_PRIVS view. You can decide which of the unused privileges or role can be revoked if necessary.

COL username FORMAT A12
COL path FORMAT A32
COL object FORMAT A10
COL sys_priv FORMAT A10
COL obj_priv FORMAT A10

select sys_priv, obj_priv, object_name, path
from   dba_unused_privs
where  rolename IN ('HR_MGR', 'SALES_CLERK');

Define the capture of privileges used through contexts.
Delete the previous capture so as to remove all captured information from the views.

exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => ‘Role_privs’)

Verify that there is no data left from the Role_privs capture. There should be no rows returned by the query.

select sys_priv, obj_priv, object_name, path
from   dba_unused_privs
where  rolename IN ('HR_MGR', 'SALES_CLERK');

Start capturing the privileges while users are performing their daily work using privileges.

exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'Special_capt')
The users JIM and TOM run SQL statements using privileges. JIM selects rows from HR.EMPLOYEES table and TOM selects rows from SH.SALES table.

connect jim/jim@localhost:1521/pdb1
select * from hr.employees where salary < 3000;

connect tom/tom@localhost:1521/pdb1
select * from sh.sales where amount_sold < 6.42 and cust_id = 6452;

Stop capturing.

connect system/oracle@localhost:1521/pdb1
exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name =>'Special_capt')

Generate the capture results.

exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'Special_capt')

Display the object privileges used during the capture period.

COL username FORMAT A10
COL owner FORMAT A8
COL object FORMAT A16
COL obj_priv FORMAT A10
COL used_role FORMAT A14

select  username, object_owner, object_name, obj_priv, used_role
from    dba_used_objprivs
where   username ='TOM' OR used_role='SALES_CLERK';

Check whether any system privileges were used. There should be no rows returned, because no system privileges were used.

select username, sys_priv from dba_used_sysprivs;

Display the path of the privileges used if the privileges were granted to roles, and roles to users. Use the DBA_USED_OBJPRIVS_PATH view.

COL object FORMAT A12
COL path FORMAT A32
COL obj_priv FORMAT A10

select username, obj_priv, object_name, path
from   dba_used_objprivs_path 
where  username IN ('TOM','JIM') 
and    object_name IN ('SALES','EMPLOYEES');

TOM is granted the select privilege on the SH.SALES table through SALES_CLERK role. He used the privilege.
The unused privs are visible in DBA_UNUSED_PRIVS view.
The query returns no rows, because there are no unused privileges. Therefore, there is no privilege that has been unnecessarily granted.

select username, sys_priv, obj_priv, object_name, path
from   dba_unused_privs
where  username='TOM' OR rolename='SALES_CLERK';

Delete Captures
List all captures to delete:

COL name     FORMAT A12
COL type     FORMAT A12
COL enabled  FORMAT A2
COL roles    FORMAT A26

select name, type, enabled, roles, context
from   dba_priv_captures;

Delete all captures.

exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'Special_capt')

Resetting Your Environment

Delete users and roles.

drop user jim cascade;
drop user tom cascade;
drop user ann cascade;

drop role hr_mgr;
drop role sales_clerk;
drop role analyst;

Summary
In this tutorial, you have learned how to:

Define a privilege capture for:
Privileges used by all users
Privileges used through roles
Privileges used through contexts
Start a privilege analysis during a period of time
Generate results about privileges and roles used and unused during the analysis period
Compare used and unused privileges using views to decide which privileges and / or roles need to be revoked or kept
Delete capture analysis
Resources

To learn more about Oracle Database 12c refer to additional OBEs in the Oracle Learning Library
Credits

Lead Curriculum Developer: Dominique Jeunot
Other Contributors: Jean-Francois Verrier
Help OLL About Oracle Contact Us Terms of Use

[свернуть]

Screen Shot 2015-12-02 at 16.35.48

Screen Shot 2015-12-02 at 16.34.56

Privilege Analysis Types and Conditions
When creating an analysis, you first define the targeted objects to be analyzed in used privileges. You do that by setting the type of analysis:
• Database analysis: If no condition is given, it analyzes the used privileges (except privileges used by administrative users) within the whole database.
• Role analysis: If roles are defined, it analyzes the privileges exercised through any given role. For example, if you create a privilege analysis policy to analyze on PUBLIC, the privileges that are directly and indirectly granted to PUBLIC are analyzed when they are used.
• Context-specific analysis: If the contexts are defined, it analyzes the privileges that are used through a given application module or specified contexts.

Reporting Used Privileges
Your third step is to generate a report. Reporting includes two types of results:
• Used privileges visible in DBA_USED_xxx and DBA_USED_xxx_PATH views
• Unused privileges visible in DBA_UNUSED_xxx and DBA_UNUSED_xxx_PATH views

Primary Sidebar

Яндекс.Метрика