Wait Events Table

Wait Events and Potential Causes

Wait Event	General Area	Possible Causes
buffer busy waits	Buffer cache, DBWR	Depends on buffer type. For example, waits for an index block may be caused by a primary key that is based on an ascending sequence. Examine V$SESSION while the problem is occurring to determine the type of block in contention.
free buffer waits	Buffer cache, DBWR, I/O	Slow DBWR (possibly due to I/O?) Cache too small Examine write time using operating system statistics. Check buffer cache statistics for evidence of too small cache
db file scattered read	I/O, SQL statement tuning	Poorly tuned SQL Slow I/O system Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.
db file sequential read	I/O, SQL statement tuning	Poorly tuned SQL Slow I/O system Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.
enqueue waits (waits starting with enq:)	Locks	Depends on type of enqueue Look at V$ENQUEUE_STAT.
library cache latch waits: librarycache, librarycache pin, andlibrary cache lock	Latch contention	SQL parsing or sharing Check V$SQLAREA to see whether there are SQL statements with a relatively high number of parse calls or a high number of child cursors (column VERSION_COUNT). Check parse statistics in V$SYSSTAT and their corresponding rate for each second
log buffer space	Log buffer, I/O	Log buffer small Slow I/O system Check the statistic redo buffer allocation retries inV$SYSSTAT. Check configuring log buffer section in configuring memory chapter. Check the disks that house the online redo logs for resource contention.
log file sync	I/O, over- committing	Slow disks that store the online logs Un-batched commits Check the disks that house the online redo logs for resource contention. Check the number of transactions (commits + rollbacks) each second, from V$SYSSTAT.

 

To view the EXPLAIN PLAN for this query, use the command:

EXPLAIN PLAN FOR 
select /*+ result_cache */ deptno, avg(sal) 
from emp 
group by deptno;

select PLAN_TABLE_OUTPUT from TABLE (DBMS_XPLAN.DISPLAY());

The EXPLAIN PLAN output for this query will be similar to:

----------------------------------------------------------------------------------
| Id| Operation           | Name                |Rows |Bytes |Cost(%CPU)|Time     |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |                     | 11  | 77   |  4 (25)  | 00:00:01|
| 1 |  RESULT CACHE       |b06ppfz9pxzstbttmy   |     |      |          |         |
| 2 |   HASH GROUP BY     |                     | 11  | 77   |  4 (25)  | 00:00:01|
| 3 |    TABLE ACCESS FULL| EMP                 |107  | 749  |  3 (0)   | 00:00:01|
----------------------------------------------------------------------------------

In this EXPLAIN PLAN, the ResultCache operator is identified by its CacheId, which is b06ppfz9pxzstbttpbqyqnfbmy. You can now run a query on the V$RESULT_CACHE_OBJECTS view by using this CacheId.


EXPLAIN PLAN FOR SELECT SUM(salary) FROM emp2 GROUP BY department_id;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-----------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU) |    TQ  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |   107 |  2782 |     3 (34)  |        |
|   1 |  PX COORDINATOR          |          |       |       |             |        |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |   107 |  2782 |     3 (34)  |  Q1,01 |
|   3 |    HASH GROUP BY         |          |   107 |  2782 |     3 (34)  |  Q1,01 |
|   4 |     PX RECEIVE           |          |   107 |  2782 |     3 (34)  |  Q1,01 |
|   5 |      PX SEND HASH        | :TQ10000 |   107 |  2782 |     3 (34)  |  Q1,00 |
|   6 |       HASH GROUP BY      |          |   107 |  2782 |     3 (34)  |  Q1,00 |
|   7 |        PX BLOCK ITERATOR |          |   107 |  2782 |     2 (0)   |  Q1,00 |
|   8 |         TABLE ACCESS FULL| EMP2     |   107 |  2782 |     2 (0)   |  Q1,00 |
------------------------------------------------------------------------------------