| |
 |
|
Oracle Tips by Burleson |
Foreground Wait Event
The next section covers the foreground wait event
section. This section shows all of the
instance wait
events and the Top 5 Wait Events
section is a subset of these values. The report in the following
listing has been truncated to only show the first 20 readings. The
normal report runs to nearly two and one half pages of data. Oracle
tries to push the idle waitevents to the bottom of the listing.
Wait Events
DB/Inst: SSD/ssd2 Snaps: 3-4
-> s - second
-> cs -
centisecond - 100th of a second
-> ms -
millisecond - 1000th of a second
-> us -
microsecond - 1000000th of a second
-> ordered by
wait time desc, waits desc (idle events last)
Avg
Total Wait
wait Waits
Event Waits Timeouts Time (s) (ms)
/txn
--------------------------- ----------- ---------- ---------- -------
--------
enq: TX - row
lock contenti 596 324 208 349 0.1
gc cr block
2-way 49,722 0 77 2 6.6
gc current block
2-way 36,791 0 52 1 4.9
gc buffer
busy 1,726 2 29 17 0.2
SQL*Net more
data from clie 830 0 26 32 0.1
latch: library
cache lock 96 0 26 271 0.0
latch: cache
buffers chains 1,043 1,018 13 12 0.1
gc current block
busy 275 0 10 37 0.0
class slave
wait 2 2 10 4995 0.0
gc cr block
busy 2,599 0 6 2 0.3
gc cr multi
block request 3,096 0 3 1 0.4
enq: TX -
allocate ITL entr 8 5 3 362 0.0
gc current grant
busy 7,525 0 3 0 1.0
log file
parallel write 10,410 0 2 0 1.4
log file
sync 6,756 0 2 0 0.9
db file
sequential read 5,500 0 2 0 0.7
buffer busy
waits
110
0 2
16 0.0
gcs log flush
sync 2,674 104 2 1 0.4
control file
parallel write 1,498 0 1 1 0.2
gc current grant
2-way 2,530 0 1 1 0.3
-------------------------------------------------------------
The events in the
Foreground Event
report are those that occurred in
user or application processes. If excessive GCStype events
dominate the report, the interconnect is causing most of the
wait events in the database.
Likewise, if I/O related events such as
db file scattered read
or db file sequential readdominate, the system is becoming I/O bound. Both
interconnect issues and I/O bound conditions can be fixed quickly
using SSD. The next section of the report deals with the background
wait events. These are
wait events from the Oracle
background processes such as SMON, PMON or DBWR. The
following listing shows an example of this section of the AWRRPTreport.
Background Wait
Events DB/Inst: SSD/ssd2 Snaps: 3-4
-> ordered by
wait time desc, waits desc (idle events last)
Avg
Total Wait
wait Waits
Event Waits Timeouts Time (s)
(ms) /txn
--------------------------- ----------- ---------- ---------- -------
--------
log file
parallel write 10,411 0 2 0 1.4
gcs log flush
sync 2,674 104 2 1 0.4
control file
parallel write 1,496 0 1 1 0.2
control file
sequential rea 4,507 0 1 0 0.6
process
startup 14 0 1 39 0.0
DFS lock
handle 2,172 0 0 0 0.3
CGS wait for IPC
msg 31,913 31,913 0 0 4.2
latch: cache
buffers chains 23 23 0 5 0.0
enq: CF -
contention 163 0 0 1 0.0
gc cr block
2-way 66 0 0 1 0.0
gc current block
2-way 55 0 0 1 0.0
enq: HW -
contention 161 0 0 0 0.0
latch: KCL gc
element paren 13 11 0 3 0.0
db file parallel
write 26,742 0 0 0 3.5
control file
single write 100 0 0 0 0.0
row cache
lock 44 0 0 0 0.0
ksxr poll remote
instances 2,843 2,650 0 0 0.4
IPC send
completion sync 15 15 0 1 0.0
reliable
message 27 0 0 0 0.0
PX Deq: Signal
ACK 35 12 0 0 0.0
-------------------------------------------------------------
Again, this report was cut off at 20 events. The
full report can be a page or more in length. Usually, the background
wait event report is not very
useful in tuning unless there are background process issues to
diagnose.
The
above book excerpt is from:
Oracle
Solid State Disk Tuning
High Performance Oracle
tuning with RAM disk
ISBN
0-9744486-5-6
Donald K. Burleson & Mike Ault
http://www.rampant-books.com/book_2005_1_ssd.htm
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
