Database Reference
In-Depth Information
Oracle provides visibility and instrumentation to these resource related matrix via the Oracle wait interface (OWI).
OWI is driven by the three primary views—
GV$SYSTEM_EVENT
,
GV$SESSION_EVENT
, and
GV$SESSION_WAIT
. These views
provide wait times at the system level or at the individual session level. To isolate waits, a specific set of modules or
sessions grouped into services within application wait times could be queried using
GV$SERVICE_EVENT
.
Wait events are classified into wait classes; this helps in grouping wait events and helps directing tuning efforts to
the various areas of the environment. The following query (Table
17-2
) illustrates the number of wait events grouped
under the various wait classes.
SELECT WAIT_CLASS,
COUNT(*)
FROM V$SYSTEM_EVENT
GROUP BY WAIT_CLASS;
Table 17-2.
Wait Count by Wait Class
WAIT_CLASS
12cR1 Count
11gR2 Count
12cR1 Count
Concurrency
20
12
14
User I/O
17
12
14
System I/O
14
11
10
Administrative
11
1
1
Configuration
12
6
8
Scheduler
2
1
1
Other
203
152
160
Application
8
9
7
Cluster
24
24
21
Idle
43
33
40
Commit
1
1
1
Network
3
3
1
Almost all wait events have direct or indirect impact in a RAC environment; however, for our purposes, we
discuss few of the wait events
7
that belong to the Cluster class.
Oracle RAC events have some intelligence built into their structure, meaning there is a built-in format to the
structure. Figure
17-3
illustrates the format of the wait event.
buffer type
message type
performance hint
identifier
hops
busy
gc
current
block
non gc
cr
grant
congested
Figure 17-3.
RAC wait event structure
7
For single-instance-related wait events, please refer to the book
Oracle Wait Interface: A Practical Guide To Performance
Diagnostics & Tuning
(2004, Oracle Press) by Richmond Shee, Kirtikumar Deshpande, and K. Gopalakrishnan.
Search WWH ::
Custom Search