{"id":328,"date":"2012-03-08T00:01:02","date_gmt":"2012-03-08T00:01:02","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=328"},"modified":"2013-04-17T04:45:16","modified_gmt":"2013-04-17T04:45:16","slug":"user-waits","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=328","title":{"rendered":"User waits"},"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>\nSystem waits (user, non-idle)<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nset linesize 250\r\nset verify off\r\nset pagesize 9999\r\n\r\nCOLUMN Username     \tFORMAT a14\t\t\t  HEADING 'User'\r\nCOLUMN Sid   \t\t\tFORMAT 99999          HEADING 'Sess ID'\r\nCOLUMN blocking_session\tFORMAT 99999          HEADING 'Block SID'\r\nCOLUMN sql_id   \t\tFORMAT a14            HEADING 'SQL ID'\r\nCOLUMN sql_hash_value\tFORMAT 99999999999    HEADING 'SQL Hash'\r\nCOLUMN machine   \t\tFORMAT a14            HEADING 'Machine'\r\nCOLUMN program   \t\tFORMAT a20            HEADING 'Program'\r\nCOLUMN last_call_et   \tFORMAT 999,999,999    HEADING 'Last call ET'\r\nCOLUMN event   \t\t\tFORMAT a25            HEADING 'Event name'\r\nCOLUMN sec_wait  \t\tFORMAT 999,999,999    HEADING 'Time waited'\r\nCOLUMN state   \t\t\tFORMAT a10            HEADING 'State'\r\nCOLUMN name   \t\t\tFORMAT a25            HEADING 'Event details'\r\nCOLUMN wait_object_name FORMAT a25            HEADING 'Object Name'\r\n\r\nselect s.username, s.sid, s.sql_id, s.blocking_session, s.sql_hash_value, s.machine, s.program, s.last_call_et,\r\ndecode(w.event,'latch free',w.event||': '||l.name||' ',w.event) event, w.seconds_in_wait sec_wait, s.state,\r\ndecode(w.event,\r\n'db file sequential read',substr(substr(d.name,instr(d.name,'\\',-1)+1,length(d.name)),instr(substr(d.name,instr(d.name,'\\',-1)+1,length(d.name)),'\/',-1)+1,\r\nlength(substr(d.name,instr(d.name,'\\',-1)+1,length(d.name))))||' '||w.p1text||'='||w.p1||' '||w.p2text||'='||w.p2||' '||w.p3text||'='||w.p3,\r\n'db file scattered read' ,substr(substr(d.name,instr(d.name,'\\',-1)+1,length(d.name)),instr(substr(d.name,instr(d.name,'\\',-1)+1,length(d.name)),'\/',-1)+1,\r\nlength(substr(d.name,instr(d.name,'\\',-1)+1,length(d.name))))||' '||w.p1text||'='||w.p1||' '||w.p2text||'='||w.p2||' '||w.p3text||'='||w.p3,\r\nw.p1text||'='||w.p1||' '||decode(w.p2text,null,'',w.p2text||'='||w.p2)||' '||decode(w.p3text,null,'',w.p3text||'='||w.p3)\r\n) name, \r\no.owner || '.' ||o.object_name wait_object_name\r\n  from\r\nv$session_wait w,\r\nv$session s,\r\nv$datafile d, v$latch l, v$process p,\r\ndba_objects o\r\nwhere w.sid = s.sid and w.p2 = l.latch#(+) and p.addr = s.paddr\r\nand w.event not in ('Idle', 'SQL*Net message from client')\r\nand s.type = 'USER'\r\nand w.p1=d.file#(+)\r\nand s.row_wait_obj# = o.object_id(+)\r\norder by s.sql_hash_value,1\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nset linesize 250<br \/>\nset verify off<br \/>\nset pagesize 9999<\/p>\n<p>COLUMN Username     \tFORMAT a14\t\t\t  HEADING 'User'<br \/>\nCOLUMN Sid   \t\t\tFORMAT 99999          HEADING 'Sess ID'<br \/>\nCOLUMN blocking_session\tFORMAT 99999          HEADING 'Block SID'<br \/>\nCOLUMN sql_id   \t\tFORMAT a14            HEADING 'SQL ID'<br \/>\nCOLUMN sql_hash_value\tFORMAT 99999999999    HEADING 'SQL Hash'<br \/>\nCOLUMN machine   \t\tFORMAT a14            HEADING 'Machine'<br \/>\nCOLUMN program   \t\tFORMAT a20            HEADING 'Program'<br \/>\nCOLUMN last_call_et   \tFORMAT 999,999,999    HEADING 'Last call ET'<br \/>\nCOLUMN event   \t\t\tFORMAT a25            HEADING 'Event name'<br \/>\nCOLUMN sec_wait  \t\tFORMAT 999,999,999    HEADING 'Time waited'<br \/>\nCOLUMN state   \t\t\tFORMAT a10            HEADING 'State'<br \/>\nCOLUMN name   \t\t\tFORMAT a25            HEADING 'Event details'<br \/>\nCOLUMN wait_object_name FORMAT a25            HEADING 'Object Name'<\/p>\n<p>select s.username, s.sid, s.sql_id, s.blocking_session, s.sql_hash_value, s.machine, s.program, s.last_call_et,<br \/>\ndecode(w.event,'latch free',w.event||': '||l.name||' ',w.event) event, w.seconds_in_wait sec_wait, s.state,<br \/>\ndecode(w.event,<br \/>\n'db file sequential read',substr(substr(d.name,instr(d.name,'\\',-1)+1,length(d.name)),instr(substr(d.name,instr(d.name,'\\',-1)+1,length(d.name)),'\/',-1)+1,<br \/>\nlength(substr(d.name,instr(d.name,'\\',-1)+1,length(d.name))))||' '||w.p1text||'='||w.p1||' '||w.p2text||'='||w.p2||' '||w.p3text||'='||w.p3,<br \/>\n'db file scattered read' ,substr(substr(d.name,instr(d.name,'\\',-1)+1,length(d.name)),instr(substr(d.name,instr(d.name,'\\',-1)+1,length(d.name)),'\/',-1)+1,<br \/>\nlength(substr(d.name,instr(d.name,'\\',-1)+1,length(d.name))))||' '||w.p1text||'='||w.p1||' '||w.p2text||'='||w.p2||' '||w.p3text||'='||w.p3,<br \/>\nw.p1text||'='||w.p1||' '||decode(w.p2text,null,'',w.p2text||'='||w.p2)||' '||decode(w.p3text,null,'',w.p3text||'='||w.p3)<br \/>\n) name,<br \/>\no.owner || '.' ||o.object_name wait_object_name<br \/>\n  from<br \/>\nv$session_wait w,<br \/>\nv$session s,<br \/>\nv$datafile d, v$latch l, v$process p,<br \/>\ndba_objects o<br \/>\nwhere w.sid = s.sid and w.p2 = l.latch#(+) and p.addr = s.paddr<br \/>\nand w.event not in ('Idle', 'SQL*Net message from client')<br \/>\nand s.type = 'USER'<br \/>\nand w.p1=d.file#(+)<br \/>\nand s.row_wait_obj# = o.object_id(+)<br \/>\norder by s.sql_hash_value,1<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>System waits (user, non-idle) set linesize 250 set verify off set pagesize 9999 COLUMN Username FORMAT a14 HEADING &#8216;User&#8217; COLUMN Sid FORMAT 99999 HEADING &#8216;Sess ID&#8217; COLUMN blocking_session FORMAT 99999 HEADING &#8216;Block SID&#8217; COLUMN sql_id FORMAT a14 HEADING &#8216;SQL ID&#8217; COLUMN sql_hash_value FORMAT 99999999999 HEADING &#8216;SQL Hash&#8217; COLUMN machine FORMAT a14 HEADING &#8216;Machine&#8217; COLUMN program [&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-328","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-5i","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/328","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=328"}],"version-history":[{"count":4,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/328\/revisions"}],"predecessor-version":[{"id":438,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/328\/revisions\/438"}],"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=328"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}