Database Reference
In-Depth Information
Rows Row Source Operation
------- ---------------------------------------------------
106 NESTED LOOPS (cr=223 pr=0 pw=0 time=166 us)
107 TABLE ACCESS FULL EMPLOYEES (cr=11 pr=0 pw=0 time=1568 us)
106 TABLE ACCESS BY INDEX ROWID DEPARTMENTS (cr=212 pr=0 pw=0 time=4747 us)
106 INDEX UNIQUE SCAN DEPT_ID_PK (cr=106 pr=0 pw=0 time=2151 us)(object id
51906)
This trace file excerpt shows the more verbose format of Oracle11
g
:
STAT #4 id=1 cnt=106 pid=0 pos=1 obj=0 op='MERGE JOIN (cr=19 pr=0 pw=0 time=0 us cos
t=6 size=3604 card=106)'
STAT #4 id=2 cnt=27 pid=1 pos=1 obj=16967 op='TABLE ACCESS BY INDEX ROWID DEPARTMENT
S (cr=12 pr=0 pw=0 time=26 us cost=2 size=432 card=27)'
STAT #4 id=3 cnt=27 pid=2 pos=1 obj=16968 op='INDEX FULL SCAN DEPT_ID_PK (cr=6 pr=0
pw=0 time=11 us cost=1 size=0 card=27)'
STAT #4 id=4 cnt=106 pid=1 pos=2 obj=0 op='SORT JOIN (cr=7 pr=0 pw=0 time=24 us cost
=4 size=1926 card=107)'
STAT #4 id=5 cnt=108 pid=4 pos=1 obj=16970 op='TABLE ACCESS FULL EMPLOYEES (cr=7 pr=
0 pw=0 time=4 us cost=3 size=1926 card=107)'
Wait Events
Capturing wait events is essential for solving performance problems due to waiting for resources
such as disk access, latches, locks, or inter-instance communication in RAC. Oracle10
g
is the
first release that has a documented interface for tracing wait events—the PL/SQL package
DBMS_MONITOR
.
WAIT Entry Format
The minimum SQL trace level for enabling
WAIT
entries is 8. Never use a SQL trace level below
8 when investigating performance problems. Otherwise the contribution of wait events to
response time will be unknown, thus preventing diagnosis of performance problems where
waiting and not CPU consumption is the most important contribution to response time.
Each wait event is associated with up to three parameters that provide more detail on the
event. Many wait events are documented in the
Oracle Database Reference
(e.g.,
Appendix C
of
Oracle Database Reference 10g Release 2
). The full listing of wait events and their parameters is
available by querying the view
V$EVENT_NAME
like this:
SQL> SELECT name, parameter1, parameter2, parameter3 FROM v$event_name ORDER BY 1;
Some example rows from the result of the query are shown here:
NAME PARAMETER1 PARAMETER2 PARAMETER3
----------------------------- --------------- ------------------ ----------
SQL*Net message from client driver id #bytes
SQL*Net message from dblink driver id #bytes
SQL*Net message to client driver id #bytes
SQL*Net message to dblink driver id #bytes
db file parallel write requests interrupt timeout
