AWR TOP 5 Timed Event Analysis

enq: TX - row lock contention
Enqueues are locks that coordinate access to database resources. This event indicates that the session is waiting for a lock that is held by another session.

The name of the enqueue is included as part of the wait event name, in the form enq: enqueue_type - related_details. In some cases, the same enqueue type can be held for different purposes, such as the following related TX types:
enq: TX - allocate ITL entry
enq: TX - contention
enq: TX - index contention
enq: TX - row lock contention

These are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.

• Waits for TX in mode 6: occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

•Waits for TX in mode 4 can also occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

Problem: The performance of overall database will be effected with this kind of Wait Event and DB Time in AWR will be very high.

Solution:The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK. In our situation we have seen extensive usage of select for update statements. Reducing these we have reduced a lot of wait events. Also we have found couple of procedures where commit is being kept at the end rather immediately after the update statement. After placing the commits closure to the update statement the wait event has drastically reduced.

AWR Timed Event Analysis

Top 5 Timed Event --- direct path read




direct path read and direct path read temp
When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event. If the I/O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.
If the I/O subsystem supports asynchronous I/O, then the process is able to overlap issuing read requests with processing the blocks already existing in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it then issues a wait call and updates the statistics for this event. Hence, the number of waits is not necessarily the same as the number of read requests (unlike db file scattered read and db file sequential read).

Causes

This happens in the following situations:
•The sorts are too large to fit in memory and some of the sort data is written out directly to disk. This data is later read back in, using direct reads.

•Parallel slaves are used for scanning data.

•The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.

Actions
This wait event can be because of large sorts to disk or full table scans by parallel slaves. This is the biggest wait for large data warehouse sites. However, if the workload is not a DSS workload, then examine why this is happening.
In the above scenario we identified a query using parallel slaves performing multiple full scans on couple of tables having 20 million records each. If tables are defined with a high degree of parallelism, then this could skew the optimizer to use full table scans with parallel slaves and result into direct path read wait event on the top.

AWR / TOP 5 Timed Event

Top 5 Timed Event ---SGA: allocation forcing component growth

Problem: Database Hangs/ Some of the jobs like export fails.




Solution : The waits for "SGA: allocation forcing component growth" indicates that SGA is inadequate. It looks like there is no enough memory available in SGA for the growth of components. SGA has to be increased or disable the SGA automation by setting SGA_TARGET=0. I will write more about the problems of SGA Resizing as there are many bugs related to the functionility.One has to query the view V$Sga_Resize_Ops to know how many times Oracle Resizes are happening.