Identify & understanding wait event in Oracle during performance analysis

Main component in performance issue is response time. In what time oracle database return response to the user. example user fire select query in which response time user get the result of select query.

Response time = Processing time + wait time

Two main type of Contention in Oracle:

1. Contention of Transaction lock on a table row.
2. Contention for simultaneous requests for area of the shared pool. (latch contentions)

Find percentage of wait time / processing time

col metric_name for a30
select metric_name, value from v$sysmetric where metric_name in ('Database CPU Time Ratio','Database Wait Time Ratio')
and intsize_csec =(select max(INTSIZE_CSEC) from V$SYSMETRIC);

METRIC_NAME VALUE
--------------------------- ----------
Database Wait Time Ratio 0
Database CPU Time Ratio 100.265895

On another Server:

METRIC_NAME VALUE
------------------------------ ----------
Database Wait Time Ratio 56.1708528
Database CPU Time Ratio 43.8291472

Note: on First Server, its seems fine result zero wait time, but on anther server waiting time is more than processing time, query shows a very high value for the Database Wait time then Ratio is much greater than the Database CPU Time Ratio, the database is spending more time waiting than processing and you must dig deeper into the Oracle wait events to identify the specific wait events causing this.

Oracle uses time model statistics

DB time is CPU time plus wait time for all sessions in AWR/statspack

Note: In time model statistic DB CPU consumes most of the DB time for the instance, it shows the database was actively processing most of the time.

Identify the SQL statements responsible for the most waits in Database:

Query rank queries that run in past 30 minutes, according to total time waited by each query:

-- for last 30 minutes
select ash.user_id, u.username, s.sql_text,sum(ash.wait_time + ash.time_waited) ttl_wait_time
from v$active_session_history ash, v$sqlarea s, dba_users u
where ash.sample_time between sysdate - 60/2880 and sysdate and ash.sql_id = s.sql_id and ash.user_id = u.user_id
group by ash.user_id,s.sql_text, u.username
order by ttl_wait_time

-- For last 24 hours
select ash.user_id, u.username, s.sql_text,sum(ash.wait_time + ash.time_waited) ttl_wait_time
from v$active_session_history ash, v$sqlarea s, dba_users u
where ash.sample_time between sysdate - 1 and sysdate and ash.sql_id = s.sql_id and ash.user_id = u.user_id
group by ash.user_id,s.sql_text, u.username
order by ttl_wait_time

Find out the wait event in Session:

select event, count(*) from v$session_wait group by event;

select event, state, seconds_in_wait from v$session_wait where sid = 10;

Check wait class time:

select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits) sum_waits
from v$system_wait_class
group by wait_class
order by 3 desc;

WAIT_CLASS SUM(TIME_WAITED) SUM_WAITS
-------------------- ---------------- ----------
Idle 1.5858E+10 78.9074156
Administrative 6729 55.6115702
Configuration 5896 17.6526946
Scheduler 87 2.63636364

Note: if Idle wait time is sufficient then database is healthy
If database has accumulated a very large wait time for the Application wait class, or the User I/O wait class

Further investigate the WAIT CLASS categories:

select a.event, a.total_waits, a.time_waited, a.average_wait
from v$system_event a, v$event_name b, v$system_wait_class c
where a.event_id=b.event_id
and b.wait_class#=c.wait_class#
and c.wait_class in ('Application','Concurrency')
order by average_wait desc;

Note:
db file sequential read event (indicates indexed reads) at the top of the wait event list. If high values for the db file sequential read wait event are due to a very large number of small indexed reads, it’s not really a problem—this is natural in a database.
db file scattered read wait event is due to full table scans of large tables. If you experience this wait event, investigate the possibility of adding indexes to the table or tables

Drill down the contention wait & log file sync wait event

select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait
from v$session_event a, v$session b
where time_waited > 0
and a.sid=b.sid
and b.username is not NULL
and a.event='enq: TX - row lock contention';

Workaround:
log file sync wait event can also be caused by too large a setting for the LOG_BUFFER initialization parameter.
Too large a value for the LOG_BUFFER parameter will lead the LGWR process to write data less frequently to the redo log files.
The database automatically calculates the value of the log_io_size parameter. By default, this parameter is set to one third of the log buffer size.

alter system set "_log_io_size"=1024000 scope=spfile;

Size of Redo check with LOG_BUFFER parameter.
show parameter log_buffer

Resolving Buffer Busy Waits

One User is simultaneously access the same data block caused high number of buffer busy waits i.e. an inefficient query is reading
too many data blocks into the buffer cache, thus keeping in wait other sessions that want to access one or more of those same blocks.
Not only that, a query that reads too much data into the buffer cache may lead to the aging out of necessary blocks from the cache.
You must investigate queries that involve the segment causing the buffer busy waits with a view to reducing the number of data blocks they’re reading into the buffer cache.

Workaround:
1. Get the object id which causing buffer busy wait

select row_wait_obj# from v$session where event = 'buffer busy waits';

2. Get the object name and type:

select owner, object_name, subobject_name, object_type from dba_objects where data_object_id = &row_wait_obj;

3. Find the query related to objects and tune them.
— If have enough storage then you can use the KEEP POOL in the buffer cache
— Tried to avoid indexes of low cardinality means low number of unique values. It caused too many block reads
— Tried to reduce low cardinality indexes on database.

Reduce read by other session Wait Events

Read by other session wait event is responsible for the highest number of waits in AWR/SP report.
Ready by other session wait means same block is needed to read by multiples sessions i.e. may be table or index

1. you can find data blocks a session is waiting for by following query:

select p1 "file#", p2 "block#", p3 "class#" from v$session_wait where event = 'read by other session';

2. Find the exact segment (table or index)

select relative_fno, owner, segment_name, segment_type from dba_extents where file_id = &file and &block between block_id and block_id + blocks - 1;

3. Once you find the segment name then
— identified the query using the object and tune them
— try to create a new tablespace with a smaller block size and move the segment to that tablespace.
— check if any low cardinality indexes are being used, because this type of an index will make the database read a large number of data blocks into the buffer cache.
— Try to replace low cardinality indexes with an index on a column with a high cardinality.

For more information regarding waits events and solution: Wait Events and Suggestions