Wait Events
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)
%Timeouts: value of 0 indicates value was <
.5%. Value of null is truly 0
Event
Waits
%Time -outs
Total Wait Time (s)
Avg wait (ms)
Waits /txn
control file parallel write
185,090
4,545
25
0.70
log file parallel write
131,759
2,452
19
0.50
log file sync
11,578
1
312
27
0.04
rdbms ipc reply
2,804
78
28
0.01
control file sequential read
13,119,070
75
0
49.70
direct path sync
7,346
67
9
0.03
os thread startup
3,254
58
18
0.01
SQL*Net more data from client
443,465
41
0
1.68
Streams AQ: qmn coordinator waiting for slave to start
8
100
39
4884
0.00
enq: RO - fast object reuse
2,284
37
16
0.01
enq: KO - fast object checkpoint
835
17
20
0.00
latch free
1,237
12
10
0.00
SQL*Net break/reset to client
78,572
12
0
0.30
db file single write
924
10
10
0.00
log file switch completion
64
7
113
0.00
library cache lock
16
13
6
367
0.00
RMAN backup & recovery I/O
9,681
6
1
0.04
enq: UL - contention
14
4
307
0.00
Datapump dump file I/O
21,593
3
0
0.08
utl_file I/O
192,555
3
0
0.73
SQL*Net message to client
3,594,107
2
0
13.61
SQL*Net more data to client
179,520
2
0
0.68
Log archive I/O
1,107
2
2
0.00
reliable message
3,131
2
0
0.01
log buffer space
6
1
245
0.00
undo segment extension
18
78
1
77
0.00
log file single write
112
1
8
0.00
db file sequential read
106,815
1
0
0.40
switch logfile command
6
1
117
0.00
kupp process wait
7
100
1
98
0.00
log file sequential read
1,337
0
0
0.01
enq: CF - contention
8
0
31
0.00
cursor: pin S wait on X
20
95
0
10
0.00
recovery area: computing obsolete files
3
0
39
0.00
enq: TC - contention
1
0
79
0.00
db file scattered read
262
0
0
0.00
direct path read
44,371
0
0
0.17
latch: shared pool
25
0
2
0.00
latch: library cache
38
0
1
0.00
direct path write
7,289
0
0
0.03
control file single write
55
0
0
0.00
LGWR wait for redo copy
771
0
0
0.00
enq: TQ - DDL contention
2
0
5
0.00
latch: session allocation
237
0
0
0.00
direct path write temp
488
0
0
0.00
latch: redo writing
16
0
0
0.00
buffer busy waits
52
0
0
0.00
latch: cache buffers chains
57
0
0
0.00
direct path read temp
314
0
0
0.00
latch: row cache objects
5
0
0
0.00
enq: TX - contention
1
0
0
0.00
recovery area: computing dropped files
3
0
0
0.00
recovery area: computing backed up files
3
0
0
0.00
latch: library cache lock
3
0
0
0.00
latch: object queue header operation
3
0
0
0.00
cursor: pin S
3
0
0
0.00
latch: messages
3
0
0
0.00
latch: object queue header heap
2
0
0
0.00
enq: TQ - TM contention
1
0
0
0.00
SQL*Net message from client
3,767,826
25,499,204
6768
14.27
Streams AQ: qmn slave idle wait
59,923
0
1,079,521
18015
0.23
jobq slave wait
185,790
96
543,077
2923
0.70
virtual circuit status
18,481
100
539,599
29197
0.07
Streams AQ: qmn coordinator idle wait
55,630
36
539,301
9694
0.21
Streams AQ: waiting for time management or cleanup tasks
143
94
260,762
1823512
0.00
wait for unread message on broadcast channel
10,363
9
1,267
122
0.04
PL/SQL lock timer
77
99
12
154
0.00
class slave wait
1,265
0
0
0.00
针对control file parallel
write等待事件，在很多情况下我们会发现由两个进程，LGWR和CKPT引起，日志切换和检查点是导致control
file parallel
write等待事件最主要的原因，相应的解决方法了就是增加日志组和日志大小，对controlfile分开存储到磁盘等。
在一个繁忙的生产系统中我发现引起control file paralle
write等待事件的都是后台进程ckpt和lgwr，出现此等待事件很正常，但是如果影响了db性能就需要酌情调整。
针对log file parallel
write等待事件，该事件的等待表示重做日志所处的磁盘设备缓慢或存在争用。
SELECT s.event, s.time_waited, s.average_wait FROM
v$system_event s WHERE s.event IN('log file parallel write','log
file sync');注：'log file parallel
write'事件的平均等待时间大于10ms(1cs)，这通常表示存在缓慢的IO吞吐量。
SQL Statistics
SQL ordered by Elapsed Time
Resources reported for PL/SQL code includes the resources
used by all SQL statements called by the code.
% Total DB Time is the Elapsed Time of the SQL statement
divided into the Total Database Time multiplied by 100
Total DB Time (s): 2,889
Captured SQL account for 112.1% of Total
Elapsed Time (s)
CPU Time (s)
Executions
Elap per Exec (s)
% Total DB Time
SQL Id
SQL Module
SQL Text
642
575
7
91.73
22.23
BEGIN SYS.KUPW$WORKER.MAIN('S...
257
138
38
6.76
8.89
OGG-EXTLC-GLOPEN_DATA_SOURCE
declare extract_name varchar2(...
252
137
76
3.32
8.72
OGG-EXTLC-GLOPEN_DATA_SOURCE
DECLARE opstring_in VARCHAR2(3...
211
212
22,431
0.01
7.29
Ips.GateWay.exe
Begin PKG_UTIL.SP_TASK_GETTASK...
136
139
1,921
0.07
4.69
Ips.GateWay.exe
with orderby as (select rowid ...
107
104
552,487
0.00
3.70
select privilege#, level from ...
77
16
258,250
0.00
2.68
OGG-EXTLC-OCI_META_THREAD
SELECT 1 FROM V$LOGFILE WHERE(...
76
78
147
0.52
2.63
insert into wrh$_sga_target_ad...
70
71
1,017
0.07
2.43
OGG-EXTLC-GLOPEN_DATA_SOURCE
DECLARE sql_text ora_name_list...
69
66
183,678
0.00
2.39
select privilege# from sysauth...
69
15
258,251
0.00
2.38
OGG-EXTLC-OCI_META_THREAD
SELECT MAX(sequence#) FROM v$l...
69
65
5
13.77
2.38
DBMS_SCHEDULER
call dbms_stats.gather_databas...
62
64
7
8.91
2.16
Data Pump Worker
SELECT SYS_XMLGEN(VA...
56
57
8,817
0.01
1.95
DECLARE job BINARY_INTEGER := ...
51
52
2,810
0.02
1.75
OGG-EXTLC-GLOPEN_DATA_SOURCE
SELECT T.BIGFILE FROM DBA_TABL...
50
0
258,251
0.00
1.72
OGG-EXTLC-OCI_META_THREAD
SELECT DECODE(archived, 'YES',...
46
44
173,759
0.00
1.58
select SYS_CONTEXT('USERENV', ...
45
38
63,756
0.00
1.56
Ips.GateWay.exe
Begin sp_exceptionhandlingtime...
37
38
126
0.29
1.27
Data Pump Worker
BEGIN "SYS"."DBMS_SCHED_EXPORT...
36
37
152,271
0.00
1.26
Ips.GateWay.exe
begin dbms_output.get_lines(:o...
34
35
7
4.87
1.18
Data Pump Worker
SELECT SYS_XMLGEN(VA...
33
39
258,251
0.00
1.14
OGG-EXTLC-OCI_META_THREAD
SELECT DECODE(status, 'STALE',...
32
31
63,756
0.00
1.10
Ips.GateWay.exe
Begin pkg_util.sp_exceptionhan...
31
32
319
0.10
1.09
OGG-EXTLC-GLOPEN_DATA_SOURCE
SELECT TS.BIGFILE FROM DBA_TAB...
29
28
173,759
0.00
1.02
select value$ from props$ wher...
SQL ordered by CPU Time
Resources reported for PL/SQL code includes the resources
used by all SQL statements called by the code.
% Total is the CPU Time divided into the Total CPU Time
times 100
Total CPU Time (s): 5,736
Captured SQL account for 48.0% of Total
CPU Time (s)
Elapsed Time (s)
Executions
CPU per Exec (s)
% Total
% Total DB Time
SQL Id
SQL Module
SQL Text
575
642
7
82.09
10.02
22.23
BEGIN SYS.KUPW$WORKER.MAIN('S...
212
211
22,431
0.01
3.69
7.29
Ips.GateWay.exe
Begin PKG_UTIL.SP_TASK_GETTASK...
139
136
1,921
0.07
2.42
4.69
Ips.GateWay.exe
with orderby as (select rowid ...
138
257
38
3.63
2.40
8.89
OGG-EXTLC-GLOPEN_DATA_SOURCE
declare extract_name varchar2(...
137
252
76
1.80
2.38
8.72
OGG-EXTLC-GLOPEN_DATA_SOURCE
DECLARE opstring_in VARCHAR2(3...
104
107
552,487
0.00
1.82
3.70
select privilege#, level from ...
78
76
147
0.53
1.36
2.63
insert into wrh$_sga_target_ad...
71
70
1,017
0.07
1.24
2.43
OGG-EXTLC-GLOPEN_DATA_SOURCE
DECLARE sql_text ora_name_list...
66
69
183,678
0.00
1.14
2.39
select privilege# from sysauth...
65
69
5
12.98
1.13
2.38
DBMS_SCHEDULER
call dbms_stats.gather_databas...
64
62
7
9.13
1.11
2.16
Data Pump Worker
SELECT SYS_XMLGEN(VA...
57
56
8,817
0.01
1.00
1.95
DECLARE job BINARY_INTEGER := ...
SQL ordered by Gets
Resources reported for PL/SQL code includes the resources
used by all SQL statements called by the code.
Total Buffer Gets: 112,462,772
Captured SQL account for 82.4% of Total
Buffer Gets
Executions
Gets per Exec
%Total
CPU Time (s)
Elapsed Time (s)
SQL Id
SQL Module
SQL Text
54,135,650
7
7,733,664.29
48.14
574.66
642.12
BEGIN SYS.KUPW$WORKER.MAIN('S...
26,741,315
22,431
1,192.16
23.78
211.69
210.66
Ips.GateWay.exe
Begin PKG_UTIL.SP_TASK_GETTASK...
24,750,966
1,921
12,884.42
22.01
138.88
135.61
Ips.GateWay.exe
with orderby as (select rowid ...
22,834,440
1,017
22,452.74
20.30
71.32
70.06
OGG-EXTLC-GLOPEN_DATA_SOURCE
DECLARE sql_text ora_name_list...
21,264,254
126
168,763.92
18.91
37.52
36.66
Data Pump Worker
BEGIN "SYS"."DBMS_SCHED_EXPORT...
21,187,592
319
66,418.78
18.84
32.20
31.45
OGG-EXTLC-GLOPEN_DATA_SOURCE
SELECT TS.BIGFILE FROM DBA_TAB...
7,810,646
38
205,543.32
6.95
137.77
256.97
OGG-EXTLC-GLOPEN_DATA_SOURCE
declare extract_name varchar2(...
7,785,809
76
102,444.86
6.92
136.58
251.99
OGG-EXTLC-GLOPEN_DATA_SOURCE
DECLARE opstring_in VARCHAR2(3...
6,071,579
552,487
10.99
5.40
104.39
106.84
select privilege#, level from ...
5,386,317
2,810
1,916.84
4.79
51.76
50.55
OGG-EXTLC-GLOPEN_DATA_SOURCE
SELECT T.BIGFILE FROM DBA_TABL...
5,115,803
7
730,829.00
4.55
63.91
62.40
Data Pump Worker
SELECT SYS_XMLGEN(VA...
3,114,570
5
622,914.00
2.77
64.89
68.84
DBMS_SCHEDULER
call dbms_stats.gather_databas...
2,465,535
7
352,219.29
2.19
21.35
20.87
Data Pump Worker
SELECT SYS_XMLGE...
2,089,493
681,534
3.07
1.86
11.54
11.38
Data Pump Worker
SELECT C.DEFAULT$, C.PROPERTY ...
1,545,645
7
220,806.43
1.37
20.32
19.86
Data Pump Worker
SELECT SYS_XMLGE...
1,287,202
183,678
7.01
1.14
65.58
69.15
select privilege# from sysauth...
1,279,736
7
182,819.43
1.14
34.93
34.07
Data Pump Worker
SELECT SYS_XMLGEN(VA...
1,209,676
8,817
137.20
1.08
57.38
56.41
DECLARE job BINARY_INTEGER := ...
SQL ordered by Reads
Total Disk Reads: 477,142
Captured SQL account for 23.3% of Total
Physical Reads
Executions
Reads per Exec
%Total
CPU Time (s)
Elapsed Time (s)
SQL Id
SQL Module
SQL Text
434,476
7
62,068.00
91.06
574.66
642.12
BEGIN SYS.KUPW$WORKER.MAIN('S...
36,381
5
7,276.20
7.62
5.84
5.95
DBMS_SCHEDULER
call dbms_space.auto_space_adv...
36,375
62
586.69
7.62
1.64
1.80
DBMS_SCHEDULER
insert into wri$_adv_objspace_...
35,692
7
5,098.86
7.48
21.35
20.87
Data Pump Worker
SELECT SYS_XMLGE...
2,243
38
59.03
0.47
137.77
256.97
OGG-EXTLC-GLOPEN_DATA_SOURCE
declare extract_name varchar2(...
2,243
76
29.51
0.47
136.58
251.99
OGG-EXTLC-GLOPEN_DATA_SOURCE
DECLARE opstring_in VARCHAR2(3...
2,223
5
444.60
0.47
64.89
68.84
DBMS_SCHEDULER
call dbms_stats.gather_databas...
168
1
168.00
0.04
2.29
2.97
BEGIN SYS.KUPM$MCP.MAIN('SYS_...
168
1
168.00
0.04
2.27
3.01
BEGIN SYS.KUPM$MCP.MAIN('SYS_...
168
1
168.00
0.04
2.53
3.13
BEGIN SYS.KUPM$MCP.MAIN('SYS_...
SQL ordered by Reads: 记录了执行占总磁盘物理读(物理IO)的TOP
SQL(请注意是监控范围内该SQL的执行占磁盘物理读总和，而不是单次SQL执行所占的磁盘物理读)。
磁盘物理读对SQL性能的影响非常大，
所以当对SQL进行调优时，第一步就是找磁盘物理读比较高的SQL语句。原因很简单，因为通常磁盘物理读通常比BUFFER读慢10000倍。
此数据库中，此部分占前几位的都是数据库内部自带的SQL语句，这也说明当前系统loading比较低。
SQL ordered by Executions
Total Executions: 6,058,865
Captured SQL account for 73.6% of Total
Executions
Rows Processed
Rows per Exec
CPU per Exec (s)
Elap per Exec (s)
SQL Id
SQL Module
SQL Text
681,534
681,534
1.00
0.00
0.00
Data Pump Worker
SELECT C.DEFAULT$, C.PROPERTY ...
552,487
4,774,328
8.64
0.00
0.00
select privilege#, level from ...
258,251
258,251
1.00
0.00
0.00
OGG-EXTLC-OCI_META_THREAD
SELECT MAX(sequence#) FROM v$l...
258,251
258,251
1.00
0.00
0.00
OGG-EXTLC-OCI_META_THREAD
SELECT DECODE(status, 'STALE',...
258,251
258,251
1.00
0.00
0.00
OGG-EXTLC-OCI_META_THREAD
SELECT DECODE(archived, 'YES',...
258,250
258,250
1.00
0.00
0.00
OGG-EXTLC-OCI_META_THREAD
SELECT 1 FROM V$LOGFILE WHERE(...
183,678
552,489
3.01
0.00
0.00
select privilege# from sysauth...
173,759
173,758
1.00
0.00
0.00
select SYS_CONTEXT('USERENV', ...
173,759
173,759
1.00
0.00
0.00
select value$ from props$ wher...
173,748
173,748
1.00
0.00
0.00
select decode(failover_method,...
173,271
346,542
2.00
0.00
0.00
Ips.GateWay.exe
SELECT PARAMETER, VALUE FROM S...
152,271
152,268
1.00
0.00
0.00
Ips.GateWay.exe
begin dbms_output.get_lines(:o...
142,101
142,100
1.00
0.00
0.00
Ips.GateWay.exe
begin dbms_output.enable(60000...
98,533
98,491
1.00
0.00
0.00
select type#, blocks, extents,...
74,837
6,236
0.08
0.00
0.00
select job, nvl2(last_date, 1,...
63,756
63,755
1.00
0.00
0.00
Ips.GateWay.exe
Begin sp_exceptionhandlingtime...
63,756
63,754
1.00
0.00
0.00
Ips.GateWay.exe
Begin pkg_util.sp_exceptionhan...
61,272
61,272
1.00
0.00
0.00
Ips.GateWay.exe
SELECT COUNT(*) FROM TSYS_EXCE...
61,272
61,272
1.00
0.00
0.00
Ips.GateWay.exe
SELECT EXCEPTIONTIMESTAMP FROM...
SQL ordered by Parse Calls
Total Parse Calls: 3,155,771
Captured SQL account for 74.5% of Total
Parse Calls
Executions
% Total Parses
SQL Id
SQL Module
SQL Text
552,489
552,487
17.51
select privilege#, level from ...
183,678
183,678
5.82
select privilege# from sysauth...
173,759
173,759
5.51
select SYS_CONTEXT('USERENV', ...
173,759
173,759
5.51
select value$ from props$ wher...
173,748
173,748
5.51
select decode(failover_method,...
173,271
173,271
5.49
Ips.GateWay.exe
SELECT PARAMETER, VALUE FROM S...
152,270
152,271
4.83
Ips.GateWay.exe
begin dbms_output.get_lines(:o...
142,101
142,101
4.50
Ips.GateWay.exe
begin dbms_output.enable(60000...
98,533
98,533
3.12
select type#, blocks, extents,...
63,756
63,756
2.02
Ips.GateWay.exe
Begin sp_exceptionhandlingtime...
63,756
63,756
2.02
Ips.GateWay.exe
Begin pkg_util.sp_exceptionhan...
SQL ordered by Sharable Memory
Only Statements with Sharable Memory greater than 1048576
are displayed
Sharable Mem (b)
Executions
% Total
SQL Id
SQL Module
SQL Text
4,994,128
308
0.29
SELECT dbin.db_name, dbin.inst...
3,667,540
154
0.21
insert into wrh$_sess_time_sta...
2,671,866
154
0.16
insert into wrh$_instance_reco...
2,578,964
154
0.15
select 1, max(id) from sys.wri...
1,600,750
5,338
0.09
SELECT C.TARGET_GUID, C.METRIC...
1,600,638
74,837
0.09
select job, nvl2(last_date, 1,...
1,506,656
154
0.09
SELECT advisor_id FROM sys.wri...
1,506,620
154
0.09
SELECT status FROM sys.wri$_ad...
1,426,210
147
0.08
insert into wrh$_sga_target_ad...
1,384,773
19,946
0.08
INSERT INTO MGMT_SYSTEM_PERFOR...
1,310,421
154
0.08
SELECT owner# FROM sys.wri$_ad...
1,238,010
241
0.07
update seq$ set increment$=:2,...
1,154,387
17,497
0.07
update sys.mon_mods$ set inser...
SQL ordered by Version Count
Only Statements with Version Count greater than 20 are
displayed
Version Count
Executions
SQL Id
SQL Module
SQL Text
110
308
SELECT dbin.db_name, dbin.inst...
92
154
SELECT owner# FROM sys.wri$_ad...
92
154
SELECT owner# FROM sys.wri$_ad...
92
154
SELECT status FROM sys.wri$_ad...
92
154
SELECT advisor_id FROM sys.wri...
91
19,946
INSERT INTO MGMT_SYSTEM_PERFOR...
90
154
select 1, max(id) from sys.wri...
59
241
update seq$ set increment$=:2,...
56
154
SELECT name, datatype, value, ...
55
5,338
SELECT C.TARGET_GUID, C.METRIC...
55
17,497
lock table sys.mon_mods$ in ex...
55
74,837
select job, nvl2(last_date, 1,...
55
3,478
update sys.job$ set this_date=...
55
17,497
update sys.mon_mods$ set inser...
49
154
insert into wrh$_instance_reco...
49
154
insert into wrh$_sess_time_sta...
49
147
insert into wrh$_sga_target_ad...
33
61,272
Ips.GateWay.exe
SELECT EXCEPTIONTIMESTAMP FROM...
31
61,272
Ips.GateWay.exe
SELECT COUNT(*) FROM TSYS_EXCE...
2371
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
