O objetivo deste select é facilitar a identificação excessiva de eventos em espera. A análise da view v$system_event será analisado as seguintes colunas:
|
1 2 3 4 5 |
EVENT = nome do evento de espera TOTAL_WAITS = Número total de esperas para o evento TIME_WAITED = Tempo total esperado pelo evento (em centésimos de segundo) TOTAL_TIMEOUTS = Número total de tempos limite do evento AVERAGE_WAIT = Quantidade média de tempo esperada pelo evento (em centésimos de segundo) |
Select consultando view v$system_event
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
set pages 999 set lines 90 column c1 heading 'Event|Name' format a30 column c2 heading 'Total|Waits' format 999,999,999 column c3 heading 'Seconds|Waiting' format 999,999 column c4 heading 'Total|Timeouts' format 999,999,999 column c5 heading 'Average|Wait|(in secs)' format 99.999 ttitle 'System-wide Wait Analysis|for current wait events select event c1, total_waits c2, time_waited / 100 c3, total_timeouts c4, average_wait /100 c5 from sys.v_$system_event where event not in ( 'dispatcher timer', 'lock element cleanup', 'Null event', 'parallel query dequeue wait', 'parallel query idle wait - Slaves', 'pipe get', 'PL/SQL lock timer', 'pmon timer', 'rdbms ipc message', 'slave wait', 'smon timer', 'SQL*Net break/reset to client', 'SQL*Net message from client', 'SQL*Net message to client', 'SQL*Net more data to client', 'virtual circuit status', 'WMON goes to sleep' ) AND event not like 'DFS%' and event not like '%done%' and event not like '%Idle%' AND event not like 'KXFX%' order by c2 desc ; |
Resultado.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 |
Event Total Seconds Total Wait Name Waits Waiting Timeouts (in secs) ------------------------------ ------------ -------- ------------ --------- PGA memory operation 4,610,093 80 0 .000 log file parallel write 612,773 8,631 0 .014 log file sync 595,143 8,682 0 .015 control file sequential read 472,261 7 0 .000 LGWR worker group idle 258,156 144,425 0 .559 jobq slave wait 227,071 112,822 224,079 .497 DIAG idle wait 149,646 149,635 149,643 1.000 Disk file operations I/O 106,913 85 0 .001 log file sequential read 91,427 121 0 .001 wait for unread message on bro 74,945 74,951 74,945 1.000 adcast channel heartbeat redo informer 74,928 74,944 0 1.000 Space Manager: slave idle wait 73,101 331,945 0 4.541 watchdog main loop 49,958 149,858 0 3.000 ADR block file read 34,627 170 0 .005 db file sequential read 32,187 36 0 .001 control file parallel write 31,542 2,142 0 .068 LGWR all worker groups 27,558 525 0 .019 pman timer 24,983 74,943 24,981 3.000 OFS idle 24,982 74,951 24,981 3.000 lreg timer 24,961 74,947 24,957 3.003 db file async I/O submit 8,594 252 0 .029 asynch descriptor resize 8,346 0 8,346 .000 os thread creation 8,309 15 0 .002 oracle thread bootstrap 8,307 173 0 .021 LGWR any worker group 5,819 145 0 .025 Streams AQ: qmn coordinator id 5,417 74,933 1 13.833 le wait class slave wait 5,343 1 0 .000 db file scattered read 4,155 5 0 .001 SQL*Net vector data to client 2,954 0 0 .000 Streams AQ: qmn slave idle wai 2,743 74,933 0 27.318 t reliable message 2,690 1 0 .000 get branch/thread/sequence enq 2,671 0 0 .000 ueue Data Guard: Timer 2,500 74,939 0 29.976 AQPC idle 2,498 74,923 2,497 29.993 latch free 2,264 0 0 .000 LGWR worker group ordering 1,969 12 0 .006 Log archive I/O 1,806 2 0 .001 Log file init write 1,806 3 0 .002 direct path read 1,295 0 0 .000 Data Guard: Gap Manager 1,250 74,942 0 59.954 db file single write 1,014 2 0 .002 SQL*Net more data from client 718 1 0 .001 enq: CR - block range reuse ck 650 20 0 .031 pt row cache mutex 633 0 0 .000 LGWR wait for redo copy 316 0 0 .000 latch: shared pool 281 0 0 .000 Compression analysis 264 0 0 .000 buffer busy waits 232 0 0 .000 log file single write 215 0 0 .002 enq: JG - queue lock 204 1 0 .003 library cache: mutex X 200 0 0 .000 Parameter File I/O 114 0 0 .000 kdic_do_merge 102 0 0 .000 latch: In memory undo latch 94 0 0 .000 enq: RO - fast object reuse 91 0 0 .000 enq: CF - contention 89 27 0 .303 ADR file lock 86 0 0 .000 Redo Transport Open 78 0 0 .000 switch logfile command 71 134 0 1.889 rdbms ipc reply 69 1 0 .008 ADR block file write 65 0 0 .001 log buffer space 54 21 0 .392 enq: PR - contention 51 1 0 .020 row cache read 47 0 0 .000 latch: cache buffers chains 42 0 0 .000 log file switch (checkpoint in 35 1 0 .027 complete) undo segment extension 23 0 22 .011 cursor: pin S 22 0 0 .001 row cache lock 22 0 0 .002 control file single write 21 1 0 .027 latch: call allocation 20 0 0 .000 utl_file I/O 19 0 0 .000 direct path write 19 0 0 .004 Failed Logon Delay 18 18 18 1.000 log file switch completion 18 0 0 .023 RMAN backup & recovery I/O 16 0 0 .002 log file switch (private stran 15 3 0 .198 d flush incomplete) cursor: pin S wait on X 15 0 0 .026 db file parallel read 13 0 0 .001 external table write 12 0 0 .000 latch: redo allocation 11 0 0 .001 library cache load lock 11 0 0 .004 library cache: bucket mutex X 10 0 0 .000 CSS initialization 7 0 0 .004 ASM file metadata operation 6 0 0 .000 read by other session 5 0 0 .000 resmgr:internal state change 5 1 1 .100 enq: SQ - contention 4 0 0 .000 local write wait 4 0 0 .003 direct path sync 4 0 0 .000 Data file init write 4 0 0 .003 latch: enqueue hash chains 4 0 0 .000 ksdxexeotherwait 4 0 0 .058 KSV master wait 3 0 3 .000 library cache lock 3 0 0 .002 AQ Background Master: slave st 2 0 0 .002 art latch: messages 2 0 0 .000 external table open 2 0 0 .002 AQPC: new master 2 0 0 .000 latch: redo writing 2 0 0 .000 instance state change 2 0 0 .000 shared server idle wait 2 74,996 0 ####### latch: active service list 2 0 0 .000 Streams AQ: load balancer idle 1 45 1 45.000 external table read 1 2 0 2.121 library cache pin 1 0 0 .016 enq: TX - contention 1 0 0 .008 CRS call completion 1 0 0 .004 cursor: mutex X 1 0 0 .000 resmgr:plan change 1 0 0 .005 control file heartbeat 1 4 1 4.000 SGA: allocation forcing compon 1 0 0 .148 ent growth DLM cross inst call completion 1 0 0 .001 enq: TX - row lock contention 1 18 0 18.270 enq: PV - syncstart 1 0 0 .019 enq: HW - contention 1 0 0 .016 116 rows selected. |
Fonte:dba-oracle.com , Oracle.com