Oracle Response Time Components

Oracle wait event
Oracle wait


@response.sql

SET LINESIZE 132 PAGESIZE 100 FEEDBACK OFF
COLUMN time_sum NOPRINT
COLUMN total_time NOPRINT
COLUMN wait_class FORMAT A15 HEADING 'Class'
COLUMN event FORMAT A40 HEADING 'Event'
COLUMN wait_seconds FORMAT 999,999,999,999.99 HEADING 'Wait Seconds'
COLUMN wait_pct FORMAT 99.999999 HEADING '% of T' NOPRINT
COLUMN nonidle_pct FORMAT 99.999999 HEADING '% of R'

BREAK ON wait_class SKIP PAGE
COMPUTE SUM OF wait_seconds nonidle_pct ON wait_class

TTITLE LEFT "*** Response Time Components by Class ***"

SELECT vswc.time_waited time_sum,
ven.wait_class,
vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
instime.time_waited total_time,
(ROUND((vse.time_waited_micro/1000000),6)/(instime.time_waited/100))*100 wait_pct,
TO_NUMBER(DECODE(vswc.wait_class, 'Idle', NULL, 'Network', NULL,
(ROUND((vse.time_waited_micro/1000000),6)/
((instime.time_waited - idletime.time_waited)/100))*100)) nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
UNION
SELECT vss.value time_sum,
'CPU Utilization' wait_class,
NULL,
vss.value/100 wait_seconds,
instime.time_waited total_time,
(ROUND((vss.value/100),6)/(instime.time_waited/100))*100 wait_pct,
((ROUND((vss.value/100),6)/((instime.time_waited - idletime.time_waited)/100))*100) nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE vss.name = 'CPU used when call started'
ORDER BY time_sum, wait_class, wait_seconds DESC
/

CLEAR BREAK
CLEAR COMPUTE

TTITLE LEFT "*** Response Time Components by Time ***"

SELECT vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
(ROUND((vse.time_waited_micro/1000000),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
AND vswc.wait_class NOT IN ('Idle', 'Network')
UNION
SELECT 'CPU Utilization' wait_class,
vss.value/100 wait_seconds,
(ROUND((vss.value/100),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE vss.name = 'CPU used when call started'
ORDER BY wait_seconds DESC
/












SQL> @responce

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Network SQL*Net more data from client .03
SQL*Net more data to client .01
SQL*Net message to client .01
*************** ------------------- ----------
sum .05

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Configuration checkpoint completed .43 .013184
log buffer space .10 .003156
undo segment extension .02 .000676
enq: SQ - contention .00 .000024
latch: redo writing .00 .000004
*************** ------------------- ----------
sum .56 .017044

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Administrative JS coord start wait .86 .026148
JS kgl get object wait .10 .003051
*************** ------------------- ----------
sum .96 .029199

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Commit log file sync 39.11 1.192503
*************** ------------------- ----------
sum 39.11 1.192503

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Other job scheduler coordinator slave wait 14.21 .433321
enq: CR - block range reuse ckpt 4.26 .129956
control file heartbeat 4.00 .121970
enq: CF - contention 2.95 .090048
ADR block file read 2.47 .075283
rdbms ipc reply 2.24 .068444
ARCH wait for process start 3 2.00 .061011
ADR block file write 1.84 .056206
enq: JS - queue lock .34 .010249
reliable message .17 .005181
latch: enqueue hash chains .17 .005119
kksfbc child completion .10 .003079
asynch descriptor resize .09 .002878
LGWR wait for redo copy .08 .002580
ADR file lock .08 .002286
SGA: allocation forcing component growth .07 .002012
enq: PR - contention .05 .001598
CRS call completion .04 .001141
latch: call allocation .02 .000728
Streams AQ: qmn coordinator waiting for .01 .000354
slave to start

latch free .01 .000204
latch: object queue header operation .01 .000200
instance state change .00 .000098
latch: cache buffers lru chain .00 .000030
latch: redo allocation .00 .000005
ARCH wait for archivelog lock .00 .000004
*************** ------------------- ----------
sum 35.22 1.073983

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Concurrency os thread startup 91.94 2.803294
library cache load lock 9.20 .280573
latch: shared pool 3.48 .106173
cursor: pin S wait on X 1.18 .035971
row cache lock .33 .009924
resmgr:internal state change .10 .003065
buffer busy waits .01 .000277
latch: row cache objects .00 .000131
latch: cache buffers chains .00 .000042
library cache: mutex X .00 .000036
*************** ------------------- ----------
sum 106.24 3.239486

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Scheduler resmgr:cpu quantum 121.26 3.697244
*************** ------------------- ----------
sum 121.26 3.697244

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
CPU Utilization 145.04 4.422423
*************** ------------------- ----------
sum 145.04 4.422423

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
User I/O db file sequential read 316.38 9.646716
db file scattered read 61.88 1.886868
Data file init write 31.37 .956459
local write wait 4.91 .149644
db file parallel read 3.21 .097827
read by other session 2.78 .084886
utl_file I/O 2.10 .064176
Disk file operations I/O 1.86 .056811
Parameter File I/O 1.58 .048154
direct path sync .86 .026246
db file single write .60 .018347
direct path read .34 .010384
Disk file Mirror/Media Repair Write .15 .004546
direct path write .05 .001602
direct path write temp .01 .000394
*************** ------------------- ----------
sum 428.09 13.053059

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Application enq: TX - row lock contention 490.69 14.961517
SQL*Net break/reset to client .73 .022267
enq: RO - fast object reuse .10 .002963
*************** ------------------- ----------
sum 491.51 14.986747

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
System I/O control file parallel write 1,144.43 34.894774
db file async I/O submit 427.99 13.049917
log file parallel write 254.10 7.747749
log file sequential read 58.29 1.777309
control file sequential read 6.41 .195364
Log archive I/O 1.26 .038356
recovery read .34 .010387
RMAN backup & recovery I/O .16 .004989
log file single write .12 .003611
control file single write .02 .000723
*************** ------------------- ----------
sum 1,893.12 57.723180

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Idle rdbms ipc message 1,665,247.86
DIAG idle wait 208,746.17
SQL*Net message from client 104,462.03
Streams AQ: qmn slave idle wait 104,419.46
pmon timer 104,385.85
shared server idle wait 104,382.54
Streams AQ: qmn coordinator idle wait 104,371.62
dispatcher timer 104,356.51
Streams AQ: waiting for time management 104,325.21
or cleanup tasks

smon timer 104,168.34
Space Manager: slave idle wait 103,983.65
jobq slave wait 35,947.71
VKRM Idle 14,399.80
class slave wait 299.96
PL/SQL lock timer 2.00
*************** ------------------- ----------
sum 2,863,498.73

*** Response Time Components by Time ***
Event Wait Seconds % of R
---------------------------------------- ------------------- ----------
control file parallel write 1,144.43 34.894242
enq: TX - row lock contention 490.69 14.961289
db file async I/O submit 427.99 13.049718
db file sequential read 316.38 9.646569
log file parallel write 254.10 7.747631
CPU Utilization 145.09 4.423880
resmgr:cpu quantum 121.26 3.697187
os thread startup 91.94 2.803251
db file scattered read 61.88 1.886839
log file sequential read 58.29 1.777282
log file sync 39.11 1.192485
Data file init write 31.37 .956444
job scheduler coordinator slave wait 14.21 .433315
library cache load lock 9.20 .280569
control file sequential read 6.41 .195361
local write wait 4.91 .149641
enq: CR - block range reuse ckpt 4.26 .129954
control file heartbeat 4.00 .121968
latch: shared pool 3.48 .106171
db file parallel read 3.21 .097825
enq: CF - contention 2.95 .090046
read by other session 2.78 .084885
ADR block file read 2.47 .075282
rdbms ipc reply 2.24 .068443
utl_file I/O 2.10 .064175
ARCH wait for process start 3 2.00 .061010
Disk file operations I/O 1.86 .056810
ADR block file write 1.84 .056205
Parameter File I/O 1.58 .048153
Log archive I/O 1.26 .038356
cursor: pin S wait on X 1.18 .035971
direct path sync .86 .026246
JS coord start wait .86 .026147
SQL*Net break/reset to client .73 .022267
db file single write .60 .018347
checkpoint completed .43 .013183
recovery read .34 .010387
direct path read .34 .010383
enq: JS - queue lock .34 .010249
row cache lock .33 .009923
reliable message .17 .005181
latch: enqueue hash chains .17 .005119
RMAN backup & recovery I/O .16 .004989
Disk file Mirror/Media Repair Write .15 .004546
log file single write .12 .003611
log buffer space .10 .003156
kksfbc child completion .10 .003079
resmgr:internal state change .10 .003065
JS kgl get object wait .10 .003051
enq: RO - fast object reuse .10 .002963
asynch descriptor resize .09 .002878
LGWR wait for redo copy .08 .002580
ADR file lock .08 .002286
SGA: allocation forcing component growth .07 .002011
direct path write .05 .001602
enq: PR - contention .05 .001598
CRS call completion .04 .001141
latch: call allocation .02 .000728
control file single write .02 .000723
undo segment extension .02 .000676
direct path write temp .01 .000394
Streams AQ: qmn coordinator waiting for .01 .000354
slave to start

buffer busy waits .01 .000277
latch free .01 .000204
latch: object queue header operation .01 .000200
latch: row cache objects .00 .000131
instance state change .00 .000098
latch: cache buffers chains .00 .000042
library cache: mutex X .00 .000036
latch: cache buffers lru chain .00 .000030
enq: SQ - contention .00 .000024
latch: redo allocation .00 .000005
ARCH wait for archivelog lock .00 .000004
latch: redo writing .00 .000004