| |
 |
|
Oracle Tips by Burleson |
Monitoring Events
Oracle is an event-driven system. This means that sessions wait for
calls, locks and latches spin, and processes slumber and wake at the
behest of events. The v$session_event view tracks all current events
by session.
The following script, events.sql, will generate a report on current
Oracle events. The script, sys_events_pct.sql, which then follows,
provides a more detailed view of the event profile by adding the CPU
contribution and calculating the overall percentages each wait is
contributing.
* events.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
COLUMN sid HEADING Sid
COLUMN event HEADING Event FORMAT a40
COLUMN total_waits HEADING Total|Waits
COLUMN total_timeouts HEADING Total|Timeouts
COLUMN time_waited HEADING Time|Waited
COLUMN average_wait HEADING Average|Wait
COLUMN username HEADING User
BREAK ON username
ttitle "Session Events By User"
SPOOL events
SET LINES 132 PAGES 59 VERIFY OFF FEEDBACK OFF
SELECT
username,
event,
total_waits,total_timeouts,
time_waited,average_wait
FROM
sys.v_$session_event a,
sys.v_$session b
WHERE
a.sid= b.sid
ORDER BY 1;
SPOOL OFF
PAUSE Press Enter to continue
CLEAR COLUMNS
CLEAR BREAKS
SET LINES 80 PAGES 22 VERIFY ON FEEDBACK ON
TTITLE OFF
The following is an example of an event report:
Total Total Time Average
User Event Waits Timeouts Waited Wait
------ ------------------------------ --------- --------- ---------
---------
SYSTEM enqueue 149 149 44425 298
control file sequential read 214 0 14 0
log file sync 61 0 65 1
SQL*Net message to client 462 0 1 0
single-task message 2 0 5 2
SQL*Net break/reset to client 4 0 0 0
SQL*Net message from client 800 0 735778 920
SQL*Net message to client 19 0 0 0
db file sequential read 24987 0 10986 0
db file sequential read 16 0 10 1
pmon timer 167133 167130 49031711 293
rdbms ipc message 163764 163681 49050112 300
control file sequential read 20 0 2 0
control file parallel write 163663 0 75411 0
direct path read 14 0 0 0
db file parallel write 3300 3300 8 0
db file scattered read 596 0 750 1
db file sequential read 248 0 232 1
log file parallel write 3760 0 1168 0
log file single write 4 0 0 0
log file sequential read 4 0 2 1
library cache load lock 1 1 299 299
smon timer 1640 1634 47313312 28850
direct path write 12 0 0 0
direct path read 14 0 0 0
control file parallel write 8 0 4 0
control file sequential read 49110 0 1069 0
async disk IO 1 0 0 0
rdbms ipc message 166085 163858 49051891 295
Notice that the report is by username. This can help isolate which
database users are generating the most wait events. However, it can
be more useful to capture the events as a percentage of all wait
time, including that for the CPU usage time. Note that on multi-CPU
systems the CPU usage may be skewed high and the need may exist to
divide by the number of CPUs to get a useful number for the current
CPU used by a session. Next, a different cut of this report that
uses a percentage calculation to show the relative weight of each
event by percent will be reviewed.
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
