Page History

Perform the following options:

• Examine buffer wait over time, in the Activity Tab.

• Examine buffer wait substate events in the Statistics tab.

Perform one of the following options:

• If Buffer Busy wait is the more dominant Oracle event, consider doing the following:

• • Increase the free lists for the table to reduce the chances of contention.3

• • Increase the PCTFREE parameter or decrease the block size for the table, to distribute data among many blocks and reduce the chances for contention.
• If Free Buffer wait is the more dominant Oracle event, consider doing the following:

• • Tune access to the object, to reduce the number of blocks fetched.

• • If the problem is a global instance problem, increase the number of DBWR processes or I/O

• • slaves.

To reduce the lock wait for the object, perform one of the following:

• Check to see if the lock appears in the Current tab. If so, examine the lock chain to identify which statement is holding the lock.

• Try to identify the locking statement in the Activity tab using smaller time frames that match the lock periods. Focus on the locked table and associated statements. The DML statements (and update queries) that are NOT waiting for locks should be the immediate suspects.

The object is suffering because it is accessing an object that suffers from chained rows. Chained rows are typically caused by the Insert operation. To solve the problem, perform one of the following:

• Increase the PCTFREE parameter (can be by alter or move table).

• Reorganize table (export/import or manually reorganize table).

• Move table to tablespace with higher block size.