Wait Events Parameter

The parameters (p1, p2, p3) for some of the most common wait events are given below.

Thanks

Controlfile Sequential Read/ Parallel Write

'controlfile sequential read' occurs while reading control file (backup, share information from controlfile between instances etc). The parameters in V$session_wait are as follows,

P1 - The file# of control file from which the session is reading.
P2 – The block# from which the session starts reading.
P3 – The no. of blocks the session is trying to read.

'controlfile parallel write' occurs while writing to all the control files. The parameters in V$session_wait are as follows,

P1 – No. of control files being updated.
P2 – No. of blocks that are being updated.
P3 – No. of IO requests.

Tuning Options: Use Asynchronous IO if possible. Move the controlfile to a different disk or use faster disk.

Thanks

db file parallel read/write

'db file parallel read' occurs during recovery. The datablocks that need to be changed are read from various datafiles and are placed in non-contiguous buffer blocks. The server process waits till all the blocks are read in to the buffer.

Tuning options - same as db file sequential read.

'db file parallel write' occurs when database writer (DBWr) is performing parallel write to files and blocks. Check the average_wait in V$SYSTEM_EVENT, if it is greater than 10 milliseconds then it signals a slow IO throughput.

Tuning options - The main blocker for this wait event is the OS I/O sub systems. Hence use OS monitoring tools (sar -d, iostat) to check the write performance. To improve the average_wait time you can consider the following,

If the data files reside on raw devices use asynchronous writes. However if the data files reside on cooked file systems use synchronous writes with direct IO.

Note: If the average_wait time for db file parallel write is high then you may see that the system waits on free buffer waits event.

Thanks

Objects and Blocks in waits

When you find a session waiting for either sequential read or scattered read, it might be useful to find which object is being accessed for further tuning.

To find the object and the block number the session is accessing,

SQL> Select SID, Event, P1 File#, p2 Block#, p3 “Blocks Fetched”,
wait_time, seconds_in_wait, state
From V$Session_Wait
Where Sid in (Select Sid From V$Session where osuser != ‘oracle’
and status = ‘ACTIVE’);

From the above query get the file# and the block#.

To find the name of the file, issue the following query.

SQL> SELECT tablespace_name, file_name FROM dba_data_files
WHERE file_id = &File#;

To find the object, issue the following query.

SQL> SELECT owner , segment_name , segment_type, partition_name
FROM dba_extents
WHERE file_id = &File#
AND &Block# BETWEEN block_id AND block_id + blocks -1 ;

Thanks

db file scattered read

'db file scattered read' occurs during multiblock read (Full table Scan, Index Fast Full Scans).

Tuning Options

1. Check for SQL that performs Full scans. Tune for optimal plans.
2. If the multiblock scans are due to optimal plans then increase the init parameter DB_FILE_MULTIBLOCK_READ_COUNT (up to 9i). Set this parameter to 0 (automatic tuning) in 10g.
3. Use Partitions if possible.

Thanks

db file sequential read

'db file sequential read' occurs during single block read (Reading index blocks, row fetch by row id).

Tuning Options

1. Find the top SQL with high physical reads (AWR or Statspack).
Analyze the objects for better Execution plans.
Use more selective index.
Rebuild the indexes if it is fragmented.
Use Partition if possible.

2. Find the I/O Statistics
Check hot disks using V$filestat.
Move datafiles to avoid contention to a single disk.

3. Try to increase the Buffer Cache
In 9i, use buffer cache advisory and in 10g use ASSM to determine the optimal size for buffer cache.
Check for hot segments and place it in the Keep Pool.

Thanks

Optimizer Statistics - Views

To Check when the object's statistics was last gathered

SQL> Select last_analyzed, table_name, owner, num_rows, sample_size
From dba_tables
Order By last_analyzed;

To check the statistics gathered on the tables

SQL> Select * From DBA_TAB_STATISTICS;

To check the statistics gathered on Columns

SQL> Select * From DBA_TAB_COL_STATISTICS;

To check the amount of DML operations on tables

SQL> Select * From SYS.DBA_TAB_MODIFICATIONS;

Thanks

Restore old statistics

When you collect new statistics, oracle stores the old statistics in the data dictionary. If the new statistics lead to sub optimal plans then you can restore the old statistics.

To view the history of statistics gathering

SQL> Select * From DBA_OPTSTAT_OPERATIONS;
SQL> Select * From DBA_TAB_STATS_HISTORY;

The old statistics are purged automatically after the retention period (default 31 days). Hence you can restore the statistics to anytime in last 31 days.

To check the current retention period

SQL> Select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

To change the retention period

SQL> Exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45)

To manually purge the old statistics

SQL> Exec dbms_stats.purge_stats(Timestamp);

To Restore the statistics

SQL> Exec dbms_stats.restore_database_stats(Sysdate -1);
SQL> Exec dbms_stats.restore_dictionary_stats(Sysdate -1);
SQL> Exec dbms_stats.restore_system_stats(‘Sysdate -1’);
SQL> Exec dbms_stats.restore_schema_stats(‘SCOTT’,’Sysdate -1’);
SQL> Exec dbms_stats.restore_table_stats(‘SCOTT’,’EMP’’Sysdate -1’);

Thanks

Gathering System Statistics

The System Statistics enable optimizer to generate better execution plan. The Automatic statistic gathering feature of 10g does not gather the system statistics. You have to manually collect System Statistics. There are two options for collecting System Statistics, they are

1. Workload Statistics
2. No Workload Statistics

Workload Statistics: This type of statistics are taken during normal working period. It will not generate any workload. The execution plans are generated based on the workload at the time of submitting the query. For eg. if the system is I/O intensive then the CBO choses an execution plan that is less intensive on I/O.

To gather Workload Statistics

SQL> Exec dbms_stats.gather_system_stats('start')
SQL> Exec dbms_stats.gather_system_stats('stop')
Or
SQL> Exec dbms_stats.gather_system_stats('interval', interval=>N)

where N is the number of minutes when statistics gathering will be stopped automatically.

To delete system statistics and reset to the default noworkload statistics

SQL> Exec dbms_stats.delete_system_stats().

NoWorkload Statistics: Noworkload statistics gather data by submitting random reads against all data files. It gathers I/O transfer speed, I/O seek time and CPU speed. There will be some overhead while gathering statistics. By default oracle uses noworkload statistics. If workload statistics are available oracle ignores noworkload statistics.

To gather no workload statistics

SQL> Exec dbms_stats.gather_system_stats();

Thanks

Sampling in Statistics Gathering

Gathering statistics on any object involves a full table scan and sorts which uses more resources. To reduce the resource spent for gathering statistics use sampling.

The ESTIMATE_PERCENT attribute of DBMS_STATS package is used to set the sample size. Oracle recommends to set the value to be AUTO_SAMPLE_SIZE. This value lets oracle to decide the best sample size.

SQL> DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', DBMS_STATS.AUTO_SAMPLE_SIZE);

Thanks

Manual Statistics Gathering

1. To Gather Statistics on System Schemas (SYS, SYSTEM, etc)

SQL> Exec DBMS_STATS.GATHER_DICTIONARY_STATS
SQL> Exec DBMS_STATS.DELETE_DICTIONARY_STATS

2. To Gather Statistics on all database objects including System schemas.

SQL> Exec DBMS_STATS.GATHER_DATABASE_STATS
SQL> Exec DBMS_STATS.DELETE_ DATABASE _STATS

3. To Gather Statistics on schemas other than System schemas

SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS(‘Ownername’);
SQL> Exec DBMS_STATS.DELETE_SCHEMA_STATS(‘Ownername’);

4. To Gather Statistics on individual tables

SQL> Exec DBMS_STATS.GATHER_TABLE_STATS(‘Ownername’,’Tablename’);
SQL> Exec DBMS_STATS.DELETE_TABLE_STATS(‘Ownername’,’Tablename’);

5. To Gather Statistics on indexes

SQL> Exec DBMS_STATS.GATHER_INDEX_STATS(‘Ownername’,’Indexname’);
SQL> Exec DBMS_STATS.DELETE_INDEX_STATS(‘Ownername’, ’Indexname’);

6. To Gather Statistics on individual columns of tables

SQL> Exec DBMS_STATS.GET_COLUMN_STATS(‘Ownername’,’Tablename’,’Column’);
SQL> Exec DBMS_STATS.DELETE_ COLUMN _STATS(‘Owner’,’Tablename’,’column’);

Note: Oracle invalidates the parsed SQL statements when new statistics are updated.

Thanks

Automatic Statistic Gathering

From Oracle 10g, any DML operations performed on any objects are monitored by oracle. The information is stored in the view SYS.DBA_TAB_MODIFICATIONS. Based on the amount of changes, oracle decides whether to gather new statistics for an object.

When you create the database using DBCA, oracle automatically creates a Job, GATHER_STATS_JOB, automatically and is scheduled to run during the maintenance window.

The following query is used to find whether the job is running,

SQL> Select Job_name from dba_scheduler_jobs where job_name like 'GATHER_STAT%';

Maintenance Windows: Oracle Scheduler have two Maintenance windows,

Weeknight window : Between 10 pm and 6 am (monday - friday)
Weekend window: Between 12 am saturday and 12 am sunday

The job is closed when the maintenance window closes even if the job has not finished.

To enable statistics collection to continue even after the maintenance window closes, set the stop_on_window_close attribute to false.

SQL> Exec DBMS_SCHEDULER.SET_ATTRIBUTE (‘GATHER_STATS_JOB’, ‘STOP_ON_WINDOW_CLOSE’, ‘FALSE’);

Candidates for Statistics Gathering: The job gathers statistics for objects with

Missing Statistics : Objects without statistics
Stale Statistics : If more than 10% of the rows in an object is modified, its statistics are considered stale.

Note: System Statistics and the Fixed table (X$ tables) statistics are not gathered automatically.

To Disable Automatic Statistic Gathering

SQL> Exec DBMS_SCHEDULER.DISABLE(‘GATHER_STATS_JOB’);

To get the details about the maintenance window

SQL> Select window_name, repeat_interval, duration, next_start_date
From dba_scheduler_windows;

To change the period of Maintenance Windows

SQL> Exec DBMS_SCHEDULER.SET_ATTRIBUTE(‘WEEKNIGHT_WINDOW’, ‘repeat_interval’, 'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0; byminute=0; bysecond=0');

Lock Statistics: If you dont want to gather statistics for certain object or schema then you can lock it as follows,

SQL> Exec DBMS_STATS.LOCK_SCHEMA_STATS(‘SCOTT’);
SQL> Exec DBMS_STATS.LOCK_TABLE_STATS(‘SCOTT’, ‘EMP’);

To unlock

SQL> Exec DBMS_STATS.UNLOCK_SCHEMA_STATS(‘SCOTT’);
SQL> Exec DBMS_STATS.UNLOCK_TABLE_STATS(‘SCOTT’, ‘EMP’);

Note: When you perform bulk loads on tables gather statistics on those tables immediately.

Thanks

Recompile Invalid Objects

Script to find the invalid objects in the database

SQL> Select * From dba_objects where status = 'INVALID';

To Recompile Invalid Objects

Method 1: With access to SYS user

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

Method 2: Without access to SYS user (Cannot validate SYS objects)

SQL> Exec DBMS_UTILITY.compile_schema('SYSTEM');

Method 3: Manual Script

SQL> select 'ALTER ' OBJECT_TYPE ' ' OWNER '.' OBJECT_NAME
' COMPILE;'from dba_objects where status = 'INVALID'and object_type in ('PACKAGE','FUNCTION','PROCEDURE', 'VIEW', 'TRIGGER', 'SYNONYM');

SQL> Select 'ALTER PACKAGE' ' ' OWNER '.' OBJECT_NAME ' COMPILE BODY;' From dba_objects where status = 'INVALID'
And object_type in ('PACKAGE BODY');

SQL> Select 'ALTER MATERIALIZED VIEW' ' ' OWNER '.' OBJECT_NAME ' COMPILE;' From dba_objects where status = 'INVALID'
and object_type in ('UNDEFINED');

Thanks