| General Information |
| Related Dynamic Performance Views |
| View Name |
View Description |
| GV$SESSION_EVENT |
One row for each session-specific wait event since session startup (gv$session logon_time) |
| GV$SESSION_WAIT |
One row for each session listed in v$session (gv$session.sid) |
| V$EVENT_NAME |
One row for each named Oracle kernel wait event |
| V$SYSTEM_EVENT |
One row for each instance-wide wait event since instance startup (v$instance.startup_time) |
|
| |
| Buffer Cache Busy Waits |
| Description |
Buffer busy waits happens when a session tries to access a block in the buffer cache but it cannot because the buffer is busy,
i.e. another session is modifying the block and the contents of the block are in flux. To guarantee that the reader has a coherent image of the block with either
all of the changes or none of the changes, the session modifying the block marks the block header with a flag letting other users know a change is taking place
and to wait until the complete change is applied.
The two main cases where this wait occurs are:
- Another session is reading the block into the buffer - this specific case has been split out into a "read by other session" wait event in 10g and higher
- Another session holds the buffer in an incompatible mode to our request
While the block is being changed, the block is marked as unreadable by others. The changes that are being made should last under a few hundredths of a second,
e.g. a disk read should be under 20 milliseconds and a block modification should be under one millisecond. Therefore it will take a lot of buffer busy waits to
cause a problem, but some examples of this are:
- Hot block issue, such as the first block on the free list of a table, with high concurrent inserts. All users will insert into that block at the same time,
until it fills up, then users start inserting into the next free block on the list, and so on
- Multiple users running an inefficient SQL statement performing a full table scan on the same large table at the same time. One user will read the block off disk,
and the other users will wait on buffer busy waits (or read by other session in 10g and higher) for the physical I/O to complete
|
| Gather information |
SELECT s.sql_hash_value, sw.p1 file#, sw.p2 block#, sw.p3 reason
FROM gv$session_wait sw, gv$session s
WHERE sw.event = 'buffer busy waits'
AND sw.sid = s.sid; |
| Identify the object of a wait event |
SELECT owner , segment_name , segment_type
FROM dba_extents
WHERE file_id = &FileNo
AND &BlockNo BETWEEN block_id AND (block_id + blocks-1); |
| Top 10 buffer busy wait events |
col owner format a10
col object_name format a20
col tsname format a10
col value format 99999
SELECT *
FROM (
SELECT owner, object_name, subobject_name, object_type, tablespace_name TSNAME, value
FROM gv$segment_statistics
WHERE statistic_name='buffer busy waits'
ORDER BY value DESC)
WHERE ROWNUM < 11; |
| Issue Resolution Considerations |
- Increase extent size (are extents added too frequently?)
- Reduce rows per block (is there hot block contention?)
- Increase undo retention (by altering size or retention time)
- Tune queries
|
| TKPROF Output Parameters |
| Parameter Number |
Description |
| P1 |
File number of the data file containing the block |
| P2 |
Block number within the datafile |
| P3 |
Reason code |
|
| |
| Control File Waits |
| Description |
The three different wait events of 'control file sequential read', 'control file single write', and 'control file
parallel write' all contribute to the amount of time Oracle takes to keep the control file current.
Oracle maintains a record of the consistency of the database's physical structures and operational state through a set of control files. The Oracle
control file is essential to the database operation and ability to recover from an outage. In fact, if you lose the control file(s) associated with an
instance you may not be able to recover completely. It is the Oracle control file(s) that records information about the consistency of a database's
physical structures and operational statuses. The database state changes through activities such as adding data files, altering the size or location
of datafiles, redo being generated, archive logs being created, backups being taken, SCN numbers changing, or checkpoints being taken.
Through normal operation the control file is continuously hammered with reads and writes as it is being updated.
Why Control File Waits Occur
The performance around reads and writes against control files is often an indication of misplaced control files that share the same I/O access path or
are on devices that are heavily used. It is interesting to note that Oracle has always defaulted the creation of control files in a single directory.
You can check where your control files reside on disk with this simple query. |
| View wait events |
col event format a30
col wait_class format a20
SELECT inst_id, event, total_waits, total_timeouts, time_waited, average_wait, wait_class
FROM gv$system_event
WHERE event LIKE '%control%'; |
| View sessions impacted by control file wait events |
SELECT event, wait_time, p1, p2, p3
FROM v$session_wait WHERE event LIKE '%control%'; |
| Issue Resolution Considerations |
- Relocate files for less contention
- Reduce the frequency of commits and log switches
|
| TKPROF Output Parameters (Control File Parallel Write) |
| Parameter Number |
Description |
| P1 |
Number of control files being written to |
| P2 |
Number of blocks written |
| P3 |
Number of I/O requests |
|
| TKPROF Output Parameters (Control File Sequential Read) |
| Parameter Number |
Description |
| P1 |
Control containing the block |
| P2 |
Bock number within the control file |
| P3 |
Number of blocks read |
|
| |
| DB File Waits |
| TKPROF Output Parameters (db file parallel read) |
| Parameter Number |
Description |
| P1 |
Number of files the session is reading |
| P2 |
Total number of blocks read |
| P3 |
Total number of I/O requests |
|
| TKPROF Output Parameters (db file parallel write) |
| Parameter Number |
Description |
| P1 |
Total number of I/O requests |
| P2 |
|
| P3 |
Timeout value in hundredths of a second to wait for the I/O completion. |
|
| TKPROF Output Parameters (db file scattered read) |
| Parameter Number |
Description |
| P1 |
File number |
| P2 |
Block number |
| P3 |
Number of blocks that the session is trying to read from the file# starting at block# |
|
| TKPROF Output Parameters (db file sequential read) |
| Parameter Number |
Description |
| P1 |
File number |
| P2 |
Block number |
| P3 |
Number of blocks that the session is trying to read (should be 1) |
|
| TKPROF Output Parameters (db file single write) |
| Parameter Number |
Description |
| P1 |
File number |
| P2 |
Block number |
| P3 |
The number of blocks that the session is trying to write in file# starting at block# |
|
| |
| Idle Events |
| The table to the right is not complete but is a listing of the events most commonly seen idle events |
| Event Name |
Description |
| client message |
|
| dispatcher time |
|
| gcs for action |
|
| gcs remote message |
|
| ges remote message |
|
| i/o slave wait |
|
| jobq slave wait |
|
| lock manager wait for remote message |
|
| null event |
|
| parallel queyr dequeue |
|
| pipe get |
|
| PL/SQL lock timer |
|
| pmon timer |
|
| PX Deq Credit: need buffer |
|
| PX Deq Credit: send blkd |
|
| PX Deq: Execute Reply |
|
| PX Deq: Execution Msg |
|
| PX Deq: Signal ACK |
|
| PX Deq: Table Q Normal |
|
| PX Deque wait |
|
| PX Idle Wait |
|
| queue messages |
|
| rdbms ipc message |
|
| slave wait |
|
| smon timer |
|
| SQL*Net message from cllient |
|
| SQL*Net message to client |
|
| SQL*Net more data from client |
|
| virtual circuit status |
|
| wakeup time manager |
|
|
| |
| Log Buffer Space |
| Wait for space in the SGA redo buffer |
| Parameter Number |
Description |
| P1 |
Not used |
| P2 |
Not used |
| P3 |
Not used |
|
| |
| Log File Switch |
| Database switches redo logs.
The previously current log file becomes active and is archived (or) waiting for checkpoint to complete because all redo logs are full (or) waiting for the switch to complete |
| Parameter Number |
Description |
| P1 |
Not used |
| P2 |
Not used |
| P3 |
Not used |
|
| |
| Log File Sync |
| Wait for redo flush upon commit or rollback |
| Parameter Number |
Description |
| P1 |
buffer# in log buffer that needs to be flushed |
| P2 |
Not used |
| P3 |
Not used |
|
| |
| Session Wait Events |
| DFS Lock Handle |
SELECT CHR(TO_CHAR(BITAND(p1,-16777216))/16777215) ||
CHR(TO_CHAR(BITAND(p1, 16711680))/65535) "Lock",
TO_CHAR(BITAND(p1, 65535)) "Mode"
FROM v$session_wait
WHERE event = 'DFS lock handle' |
| |
| Unspecified Wait Events |
| This query is one way to identify so-called unspecified wait events by object |
SELECT current_obj#, SUM(time_waited)/1000000, COUNT(*)
FROM gv$active_session_history
WHERE event = 'unspecified wait event'
GROUP BY current_obj#
ORDER BY 3 DESC; |
| |
| Wait Event Tracing |
| Active tracing in the current session |
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'; |
| Active tracing in a different session |
SELECT schemaname, sid, serial#
FROM gv$session;
exec dbms_monitor.session_trace_enable(144, 253, TRUE, FALSE); |
