{"id":468,"date":"2013-08-29T06:06:08","date_gmt":"2013-08-29T06:06:08","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=468"},"modified":"2013-10-17T05:20:57","modified_gmt":"2013-10-17T05:20:57","slug":"avg-wait-time","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=468","title":{"rendered":"Avg wait time"},"content":{"rendered":"<p><code><br \/>\n<script type=\"text\/javascript\"> \nfunction copy(what) {\n    window.clipboardData.setData('Text',what.value);\n}\nfunction paste(what) {\n    what.value = window.clipboardData.getData('Text');\n}\n<\/script><\/p>\n<h2>\nAverage wait time last N days for M event<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSET LINESIZE 230\r\nSET PAGESIZE 9999\r\nSET VERIFY   OFF\r\nselect\r\n\t   etime,\r\n\t   event_name,\r\n       round((time_ms_end-time_ms_beg)\/nullif(count_end-count_beg,0),1) avg_ms\r\nfrom (\r\nselect\r\n       s.dbid,\r\n       to_char(s.BEGIN_INTERVAL_TIME,'DD\/MM\/YY HH24:MI')  btime,\r\n\t   to_char(s.END_INTERVAL_TIME,'DD\/MM\/YY HH24:MI')  etime,\r\n\t   e.event_name,\r\n       total_waits count_end,\r\n       time_waited_micro\/1000 time_ms_end,\r\n       Lag (e.time_waited_micro\/1000)\r\n              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,\r\n       Lag (e.total_waits)\r\n              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg\r\nfrom\r\n       DBA_HIST_SYSTEM_EVENT e,\r\n       DBA_HIST_SNAPSHOT s\r\nwhere\r\n       s.snap_id=e.snap_id\r\n\t   and s.begin_interval_time>=trunc(sysdate) - &days_back and s.end_interval_time <= sysdate\r\n   and upper(e.event_name) like upper('%&#038;wait_event_name%')\r\n   and  s.dbid=e.dbid\r\n)\r\nwhere round((time_ms_end-time_ms_beg)\/nullif(count_end-count_beg,0),1) > 10\r\norder by btime, event_name\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSET LINESIZE 230<br \/>\nSET PAGESIZE 9999<br \/>\nSET VERIFY   OFF<br \/>\nselect<br \/>\n\t   etime,<br \/>\n\t   event_name,<br \/>\n       round((time_ms_end-time_ms_beg)\/nullif(count_end-count_beg,0),1) avg_ms<br \/>\nfrom (<br \/>\nselect<br \/>\n       s.dbid,<br \/>\n       to_char(s.BEGIN_INTERVAL_TIME,'DD\/MM\/YY HH24:MI')  btime,<br \/>\n\t   to_char(s.END_INTERVAL_TIME,'DD\/MM\/YY HH24:MI')  etime,<br \/>\n\t   e.event_name,<br \/>\n       total_waits count_end,<br \/>\n       time_waited_micro\/1000 time_ms_end,<br \/>\n       Lag (e.time_waited_micro\/1000)<br \/>\n              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,<br \/>\n       Lag (e.total_waits)<br \/>\n              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg<br \/>\nfrom<br \/>\n       DBA_HIST_SYSTEM_EVENT e,<br \/>\n       DBA_HIST_SNAPSHOT s<br \/>\nwhere<br \/>\n       s.snap_id=e.snap_id<br \/>\n\t   and s.begin_interval_time>=trunc(sysdate) - &days_back and s.end_interval_time <= sysdate\n   and upper(e.event_name) like upper('%&#038;wait_event_name%')\n   and  s.dbid=e.dbid\n)\nwhere round((time_ms_end-time_ms_beg)\/nullif(count_end-count_beg,0),1) > 10<br \/>\norder by btime, event_name<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nCritical wait events<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSET LINESIZE 230\r\nSET PAGESIZE 9999\r\nSET VERIFY   OFF\r\nselect\r\n\t   etime,\r\n\t   event_name,\r\n       round((time_ms_end-time_ms_beg)\/nullif(count_end-count_beg,0),1) avg_ms\r\nfrom (\r\nselect\r\n       s.dbid,\r\n       to_char(s.BEGIN_INTERVAL_TIME,'DD\/MM\/YY HH24:MI')  btime,\r\n\t   to_char(s.END_INTERVAL_TIME,'DD\/MM\/YY HH24:MI')  etime,\r\n\t   e.event_name,\r\n       total_waits count_end,\r\n       time_waited_micro\/1000 time_ms_end,\r\n       Lag (e.time_waited_micro\/1000)\r\n              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,\r\n       Lag (e.total_waits)\r\n              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg\r\nfrom\r\n       DBA_HIST_SYSTEM_EVENT e,\r\n       DBA_HIST_SNAPSHOT s\r\nwhere\r\n       s.snap_id=e.snap_id\r\n\t   and s.begin_interval_time>=trunc(sysdate) - &days_back and s.end_interval_time <= sysdate\r\n   and e.event_name in (\r\n                  'log file sync',\r\n                  'log file parallel write',\r\n\t\t\t\t  'buffer busy waits',\r\n\t\t\t\t  'db file sequential read',\r\n\t\t\t\t  'enq: TX - row lock contention',\r\n\t\t\t\t  'enq: SQ - contention',\r\n\t\t\t\t  'log file switch (checkpoint incomplete)',\r\n\t\t\t\t  'log file switch completion'\r\n                )\r\n   and  s.dbid=e.dbid\r\n)\r\nwhere round((time_ms_end-time_ms_beg)\/nullif(count_end-count_beg,0),1) > &avg_wait_ms\r\norder by btime, event_name\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSET LINESIZE 230<br \/>\nSET PAGESIZE 9999<br \/>\nSET VERIFY   OFF<br \/>\nselect<br \/>\n\t   etime,<br \/>\n\t   event_name,<br \/>\n       round((time_ms_end-time_ms_beg)\/nullif(count_end-count_beg,0),1) avg_ms<br \/>\nfrom (<br \/>\nselect<br \/>\n       s.dbid,<br \/>\n       to_char(s.BEGIN_INTERVAL_TIME,'DD\/MM\/YY HH24:MI')  btime,<br \/>\n\t   to_char(s.END_INTERVAL_TIME,'DD\/MM\/YY HH24:MI')  etime,<br \/>\n\t   e.event_name,<br \/>\n       total_waits count_end,<br \/>\n       time_waited_micro\/1000 time_ms_end,<br \/>\n       Lag (e.time_waited_micro\/1000)<br \/>\n              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,<br \/>\n       Lag (e.total_waits)<br \/>\n              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg<br \/>\nfrom<br \/>\n       DBA_HIST_SYSTEM_EVENT e,<br \/>\n       DBA_HIST_SNAPSHOT s<br \/>\nwhere<br \/>\n       s.snap_id=e.snap_id<br \/>\n\t   and s.begin_interval_time>=trunc(sysdate) - &days_back and s.end_interval_time <= sysdate\n   and e.event_name in (\n                  'log file sync',\n                  'log file parallel write',\n\t\t\t\t  'buffer busy waits',\n\t\t\t\t  'db file sequential read',\n\t\t\t\t  'enq: TX - row lock contention',\n\t\t\t\t  'enq: SQ - contention',\n\t\t\t\t  'log file switch (checkpoint incomplete)',\n\t\t\t\t  'log file switch completion'\n                )\n   and  s.dbid=e.dbid\n)\nwhere round((time_ms_end-time_ms_beg)\/nullif(count_end-count_beg,0),1) > &avg_wait_ms<br \/>\norder by btime, event_name<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Average wait time last N days for M event SET LINESIZE 230 SET PAGESIZE 9999 SET VERIFY OFF select etime, event_name, round((time_ms_end-time_ms_beg)\/nullif(count_end-count_beg,0),1) avg_ms from ( select s.dbid, to_char(s.BEGIN_INTERVAL_TIME,&#8217;DD\/MM\/YY HH24:MI&#8217;) btime, to_char(s.END_INTERVAL_TIME,&#8217;DD\/MM\/YY HH24:MI&#8217;) etime, e.event_name, total_waits count_end, time_waited_micro\/1000 time_ms_end, Lag (e.time_waited_micro\/1000) OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg, Lag (e.total_waits) OVER( PARTITION BY e.event_name ORDER [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":59,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-468","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-7y","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/468","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=468"}],"version-history":[{"count":4,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/468\/revisions"}],"predecessor-version":[{"id":491,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/468\/revisions\/491"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/59"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=468"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}