O objetivo deste select \u00e9 facilitar a identifica\u00e7\u00e3o excessiva de eventos em espera.<\/p>\n
A an\u00e1lise da view v$system_event<\/strong> ser\u00e1 analisado as seguintes colunas:<\/p>\n Select consultando view v$system_event<\/p>\n Resultado.<\/p>\n Fonte:dba-oracle.com<\/a> , Oracle.com<\/a><\/p>\n","protected":false},"excerpt":{"rendered":" O objetivo deste select \u00e9 facilitar a identifica\u00e7\u00e3o excessiva de eventos em espera. A an\u00e1lise da view v$system_event ser\u00e1 analisado as seguintes colunas: EVENT = nome do evento de espera TOTAL_WAITS = N\u00famero total de esperas para o evento TIME_WAITED = Tempo total esperado pelo evento (em cent\u00e9simos de segundo) TOTAL_TIMEOUTS = N\u00famero total de…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[63,347,377],"class_list":["post-152","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-consultando-eventos-oracle-view-vsystem_event","tag-system_event","tag-vsystem_event"],"_links":{"self":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts\/152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=152"}],"version-history":[{"count":0,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts\/152\/revisions"}],"wp:attachment":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}EVENT = nome do evento de espera\nTOTAL_WAITS = N\u00famero total de esperas para o evento\nTIME_WAITED = Tempo total esperado pelo evento (em cent\u00e9simos de segundo)\nTOTAL_TIMEOUTS = N\u00famero total de tempos limite do evento\nAVERAGE_WAIT = Quantidade m\u00e9dia de tempo esperada pelo evento (em cent\u00e9simos de segundo)<\/pre>\n
set pages 999\nset lines 90\n\ncolumn c1 heading 'Event|Name' format a30\ncolumn c2 heading 'Total|Waits' format 999,999,999\ncolumn c3 heading 'Seconds|Waiting' format 999,999\ncolumn c4 heading 'Total|Timeouts' format 999,999,999\ncolumn c5 heading 'Average|Wait|(in secs)' format 99.999\n\nttitle 'System-wide Wait Analysis|for current wait events\n\nselect\n event c1,\n total_waits c2,\n time_waited \/ 100 c3,\n total_timeouts c4,\n average_wait \/100 c5\nfrom\n sys.v_$system_event\nwhere\n event not in (\n 'dispatcher timer',\n 'lock element cleanup',\n 'Null event',\n 'parallel query dequeue wait',\n 'parallel query idle wait - Slaves',\n 'pipe get',\n 'PL\/SQL lock timer',\n 'pmon timer',\n 'rdbms ipc message',\n 'slave wait',\n 'smon timer',\n 'SQL*Net break\/reset to client',\n 'SQL*Net message from client',\n 'SQL*Net message to client',\n 'SQL*Net more data to client',\n 'virtual circuit status',\n 'WMON goes to sleep'\n )\nAND\n event not like 'DFS%'\nand\n event not like '%done%'\nand\n event not like '%Idle%'\nAND\n event not like 'KXFX%'\norder by\n c2 desc\n;<\/pre>\n
Event Total Seconds Total Wait\nName Waits Waiting Timeouts (in secs)\n------------------------------ ------------ -------- ------------ ---------\nPGA memory operation 4,610,093 80 0 .000\nlog file parallel write 612,773 8,631 0 .014\nlog file sync 595,143 8,682 0 .015\ncontrol file sequential read 472,261 7 0 .000\nLGWR worker group idle 258,156 144,425 0 .559\njobq slave wait 227,071 112,822 224,079 .497\nDIAG idle wait 149,646 149,635 149,643 1.000\nDisk file operations I\/O 106,913 85 0 .001\nlog file sequential read 91,427 121 0 .001\nwait for unread message on bro 74,945 74,951 74,945 1.000\nadcast channel\n\nheartbeat redo informer 74,928 74,944 0 1.000\nSpace Manager: slave idle wait 73,101 331,945 0 4.541\nwatchdog main loop 49,958 149,858 0 3.000\nADR block file read 34,627 170 0 .005\ndb file sequential read 32,187 36 0 .001\ncontrol file parallel write 31,542 2,142 0 .068\nLGWR all worker groups 27,558 525 0 .019\npman timer 24,983 74,943 24,981 3.000\nOFS idle 24,982 74,951 24,981 3.000\nlreg timer 24,961 74,947 24,957 3.003\ndb file async I\/O submit 8,594 252 0 .029\nasynch descriptor resize 8,346 0 8,346 .000\nos thread creation 8,309 15 0 .002\noracle thread bootstrap 8,307 173 0 .021\nLGWR any worker group 5,819 145 0 .025\nStreams AQ: qmn coordinator id 5,417 74,933 1 13.833\nle wait\n\nclass slave wait 5,343 1 0 .000\ndb file scattered read 4,155 5 0 .001\nSQL*Net vector data to client 2,954 0 0 .000\nStreams AQ: qmn slave idle wai 2,743 74,933 0 27.318\nt\n\nreliable message 2,690 1 0 .000\nget branch\/thread\/sequence enq 2,671 0 0 .000\nueue\n\nData Guard: Timer 2,500 74,939 0 29.976\nAQPC idle 2,498 74,923 2,497 29.993\nlatch free 2,264 0 0 .000\nLGWR worker group ordering 1,969 12 0 .006\nLog archive I\/O 1,806 2 0 .001\nLog file init write 1,806 3 0 .002\ndirect path read 1,295 0 0 .000\nData Guard: Gap Manager 1,250 74,942 0 59.954\ndb file single write 1,014 2 0 .002\nSQL*Net more data from client 718 1 0 .001\nenq: CR - block range reuse ck 650 20 0 .031\npt\n\nrow cache mutex 633 0 0 .000\nLGWR wait for redo copy 316 0 0 .000\nlatch: shared pool 281 0 0 .000\nCompression analysis 264 0 0 .000\nbuffer busy waits 232 0 0 .000\nlog file single write 215 0 0 .002\nenq: JG - queue lock 204 1 0 .003\nlibrary cache: mutex X 200 0 0 .000\nParameter File I\/O 114 0 0 .000\nkdic_do_merge 102 0 0 .000\nlatch: In memory undo latch 94 0 0 .000\nenq: RO - fast object reuse 91 0 0 .000\nenq: CF - contention 89 27 0 .303\nADR file lock 86 0 0 .000\nRedo Transport Open 78 0 0 .000\nswitch logfile command 71 134 0 1.889\nrdbms ipc reply 69 1 0 .008\nADR block file write 65 0 0 .001\nlog buffer space 54 21 0 .392\nenq: PR - contention 51 1 0 .020\nrow cache read 47 0 0 .000\nlatch: cache buffers chains 42 0 0 .000\nlog file switch (checkpoint in 35 1 0 .027\ncomplete)\n\nundo segment extension 23 0 22 .011\ncursor: pin S 22 0 0 .001\nrow cache lock 22 0 0 .002\ncontrol file single write 21 1 0 .027\nlatch: call allocation 20 0 0 .000\nutl_file I\/O 19 0 0 .000\ndirect path write 19 0 0 .004\nFailed Logon Delay 18 18 18 1.000\nlog file switch completion 18 0 0 .023\nRMAN backup & recovery I\/O 16 0 0 .002\nlog file switch (private stran 15 3 0 .198\nd flush incomplete)\n\ncursor: pin S wait on X 15 0 0 .026\ndb file parallel read 13 0 0 .001\nexternal table write 12 0 0 .000\nlatch: redo allocation 11 0 0 .001\nlibrary cache load lock 11 0 0 .004\nlibrary cache: bucket mutex X 10 0 0 .000\nCSS initialization 7 0 0 .004\nASM file metadata operation 6 0 0 .000\nread by other session 5 0 0 .000\nresmgr:internal state change 5 1 1 .100\nenq: SQ - contention 4 0 0 .000\nlocal write wait 4 0 0 .003\ndirect path sync 4 0 0 .000\nData file init write 4 0 0 .003\nlatch: enqueue hash chains 4 0 0 .000\nksdxexeotherwait 4 0 0 .058\nKSV master wait 3 0 3 .000\nlibrary cache lock 3 0 0 .002\nAQ Background Master: slave st 2 0 0 .002\nart\n\nlatch: messages 2 0 0 .000\nexternal table open 2 0 0 .002\nAQPC: new master 2 0 0 .000\nlatch: redo writing 2 0 0 .000\ninstance state change 2 0 0 .000\nshared server idle wait 2 74,996 0 #######\nlatch: active service list 2 0 0 .000\nStreams AQ: load balancer idle 1 45 1 45.000\nexternal table read 1 2 0 2.121\nlibrary cache pin 1 0 0 .016\nenq: TX - contention 1 0 0 .008\nCRS call completion 1 0 0 .004\ncursor: mutex X 1 0 0 .000\nresmgr:plan change 1 0 0 .005\ncontrol file heartbeat 1 4 1 4.000\nSGA: allocation forcing compon 1 0 0 .148\nent growth\n\nDLM cross inst call completion 1 0 0 .001\nenq: TX - row lock contention 1 18 0 18.270\nenq: PV - syncstart 1 0 0 .019\nenq: HW - contention 1 0 0 .016\n\n116 rows selected.<\/pre>\n