{"id":401,"date":"2012-11-06T23:44:58","date_gmt":"2012-11-06T23:44:58","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=401"},"modified":"2016-06-03T04:48:34","modified_gmt":"2016-06-03T04:48:34","slug":"sessions-history","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=401","title":{"rendered":"Sessions history"},"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>\nHistory of running sessions between 2 dates (dd\/mm\/yyyy hh24:mi)<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nset linesize 250\r\nset verify off\r\nset pagesize 9999\r\n\r\nCOLUMN Time      \t\tFORMAT a20            HEADING 'Sample time'\r\nCOLUMN Username      \tFORMAT a12            HEADING 'User name'\r\nCOLUMN Session_id   \tFORMAT 99999          HEADING 'Sess ID'\r\nCOLUMN sql_id   \t\tFORMAT a14            HEADING 'SQL ID'\r\nCOLUMN program   \t\tFORMAT a33            HEADING 'Program'\r\nCOLUMN module   \t\tFORMAT a20            HEADING 'Module'\r\nCOLUMN event   \t\t\tFORMAT a25            HEADING 'Event name'\r\nCOLUMN time_waited  \tFORMAT 999,999,999    HEADING 'Time waited'\r\nCOLUMN owner1\t\t\tFORMAT a15            HEADING 'Owner 1'\r\nCOLUMN object_name\t\tFORMAT a15            HEADING 'Object name 1'\r\nCOLUMN owner1\t\t\tFORMAT a15            HEADING 'Owner 2'\r\nCOLUMN p2\t\t\t\tFORMAT a15            HEADING 'Object name 2'\r\n\r\nSELECT to_char(h.SAMPLE_TIME, 'dd\/mm\/yyyy hh24:mi:ss') time, u.username, h.blocking_session block_sid, h.session_id, h.sql_id, h.program, h.module, h.event, h.time_waited, o.owner, o.object_name, o1.owner, o1.object_name p2\r\nFROM  DBA_HIST_ACTIVE_SESS_HISTORY h, DBA_OBJECTS o, dba_users u, dba_objects o1\r\nwhere sample_time between to_date('&begin_date', 'dd\/mm\/yyyy hh24:mi') and to_date('&end_date', 'dd\/mm\/yyyy hh24:mi')\r\nand session_type != 'BACKGROUND'\r\nand o.object_id (+) = h.CURRENT_OBJ#\r\nand h.p2 = o1.object_id (+)\r\nand time_waited > 0\r\nand u.user_id = h.user_id\r\nand upper(event) like upper('%&EVENT_TO_FIND%')\r\norder by sample_id, sample_time\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 Time      \t\tFORMAT a20            HEADING 'Sample time'<br \/>\nCOLUMN Username      \tFORMAT a12            HEADING 'User name'<br \/>\nCOLUMN Session_id   \tFORMAT 99999          HEADING 'Sess ID'<br \/>\nCOLUMN sql_id   \t\tFORMAT a14            HEADING 'SQL ID'<br \/>\nCOLUMN program   \t\tFORMAT a33            HEADING 'Program'<br \/>\nCOLUMN module   \t\tFORMAT a20            HEADING 'Module'<br \/>\nCOLUMN event   \t\t\tFORMAT a25            HEADING 'Event name'<br \/>\nCOLUMN time_waited  \tFORMAT 999,999,999    HEADING 'Time waited'<br \/>\nCOLUMN owner1\t\t\tFORMAT a15            HEADING 'Owner 1'<br \/>\nCOLUMN object_name\t\tFORMAT a15            HEADING 'Object name 1'<br \/>\nCOLUMN owner1\t\t\tFORMAT a15            HEADING 'Owner 2'<br \/>\nCOLUMN p2\t\t\t\tFORMAT a15            HEADING 'Object name 2'<\/p>\n<p>SELECT to_char(h.SAMPLE_TIME, 'dd\/mm\/yyyy hh24:mi:ss') time, u.username, h.blocking_session block_sid, h.session_id, h.sql_id, h.program, h.module, h.event, h.time_waited, o.owner, o.object_name, o1.owner, o1.object_name p2<br \/>\nFROM  DBA_HIST_ACTIVE_SESS_HISTORY h, DBA_OBJECTS o, dba_users u, dba_objects o1<br \/>\nwhere sample_time between to_date('&begin_date', 'dd\/mm\/yyyy hh24:mi') and to_date('&end_date', 'dd\/mm\/yyyy hh24:mi')<br \/>\nand session_type != 'BACKGROUND'<br \/>\nand o.object_id (+) = h.CURRENT_OBJ#<br \/>\nand h.p2 = o1.object_id (+)<br \/>\nand time_waited > 0<br \/>\nand u.user_id = h.user_id<br \/>\nand upper(event) like upper('%&EVENT_TO_FIND%')<br \/>\norder by sample_id, sample_time<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nSessions history for last NNN minutes<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nset linesize 250\r\nset verify off\r\nset pagesize 9999\r\n\r\nCOLUMN Time      \t\tFORMAT a24            HEADING 'Sample time'\r\nCOLUMN User_id      \tFORMAT 99999          HEADING 'User ID'\r\nCOLUMN Session_id   \tFORMAT 99999          HEADING 'Sess ID'\r\nCOLUMN sql_id   \t\tFORMAT a14            HEADING 'SQL ID'\r\nCOLUMN program   \t\tFORMAT a33            HEADING 'Program'\r\nCOLUMN module   \t\tFORMAT a33            HEADING 'Module'\r\nCOLUMN event   \t\t\tFORMAT a25            HEADING 'Event name'\r\nCOLUMN time_waited  \tFORMAT 999,999,999    HEADING 'Time waited'\r\nCOLUMN owner\t\t\tFORMAT a15            HEADING 'Owner'\r\nCOLUMN object_name\t\tFORMAT a35            HEADING 'Object name'\r\n\r\nSELECT to_char(h.SAMPLE_TIME, 'dd\/mm\/yyyy hh24:mi:ss') time, h.user_id, h.session_id, h.sql_id, h.program, h.module, h.event, h.time_waited, o.owner, o.object_name\r\nFROM  v$ACTIVE_SESSION_HISTORY h, DBA_OBJECTS o\r\nwhere sample_time between sysdate-1\/(24*60\/&MINUTES_BACK) and sysdate\r\nand session_type != 'BACKGROUND'\r\nand o.object_id (+) = h.CURRENT_OBJ#\r\nand time_waited > 0\r\nand upper(event) like upper('%&EVENT_TO_FIND%')\r\norder by sample_id, sample_time\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 Time      \t\tFORMAT a24            HEADING 'Sample time'<br \/>\nCOLUMN User_id      \tFORMAT 99999          HEADING 'User ID'<br \/>\nCOLUMN Session_id   \tFORMAT 99999          HEADING 'Sess ID'<br \/>\nCOLUMN sql_id   \t\tFORMAT a14            HEADING 'SQL ID'<br \/>\nCOLUMN program   \t\tFORMAT a33            HEADING 'Program'<br \/>\nCOLUMN module   \t\tFORMAT a33            HEADING 'Module'<br \/>\nCOLUMN event   \t\t\tFORMAT a25            HEADING 'Event name'<br \/>\nCOLUMN time_waited  \tFORMAT 999,999,999    HEADING 'Time waited'<br \/>\nCOLUMN owner\t\t\tFORMAT a15            HEADING 'Owner'<br \/>\nCOLUMN object_name\t\tFORMAT a35            HEADING 'Object name'<\/p>\n<p>SELECT to_char(h.SAMPLE_TIME, 'dd\/mm\/yyyy hh24:mi:ss') time, h.user_id, h.session_id, h.sql_id, h.program, h.module, h.event, h.time_waited, o.owner, o.object_name<br \/>\nFROM  v$ACTIVE_SESSION_HISTORY h, DBA_OBJECTS o<br \/>\nwhere sample_time between sysdate-1\/(24*60\/&MINUTES_BACK) and sysdate<br \/>\nand session_type != 'BACKGROUND'<br \/>\nand o.object_id (+) = h.CURRENT_OBJ#<br \/>\nand time_waited > 0<br \/>\nand upper(event) like upper('%&EVENT_TO_FIND%')<br \/>\norder by sample_id, sample_time<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nSQL execution history (ASH)<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nset linesize 230\r\nset pagesize 9999\r\n\r\nselect cpu_time_delta,elapsed_time_delta,iowait_delta,plan_hash_value, EXECUTIONS_DELTA, round(elapsed_time_delta\/EXECUTIONS_DELTA\/1000\/1000, 4) avg_exec_time_sec,\r\n\t(select to_char(end_interval_time, 'dd\/mm\/yyyy hh24:mi') from dba_hist_snapshot hs where hs.snap_id = a.snap_id) end_date from dba_hist_sqlstat a , \r\n\tdba_hist_sqltext b where a.sql_id=b.sql_id and \r\n\tsnap_id in (select distinct snap_id from dba_hist_snapshot where end_interval_time>=sysdate-&days_back)\r\n\tand a.sql_id = '&sql_id'\r\n\tand EXECUTIONS_DELTA != 0\r\n\torder by snap_id;\t\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nset linesize 230<br \/>\nset pagesize 9999<\/p>\n<p>select cpu_time_delta,elapsed_time_delta,iowait_delta,plan_hash_value, EXECUTIONS_DELTA, round(elapsed_time_delta\/EXECUTIONS_DELTA\/1000\/1000, 4) avg_exec_time_sec,<br \/>\n\t(select to_char(end_interval_time, 'dd\/mm\/yyyy hh24:mi') from dba_hist_snapshot hs where hs.snap_id = a.snap_id) end_date from dba_hist_sqlstat a ,<br \/>\n\tdba_hist_sqltext b where a.sql_id=b.sql_id and<br \/>\n\tsnap_id in (select distinct snap_id from dba_hist_snapshot where end_interval_time>=sysdate-&days_back)<br \/>\n\tand a.sql_id = '&sql_id'<br \/>\n\tand EXECUTIONS_DELTA != 0<br \/>\n\torder by snap_id;<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nSQL execution history (cache)<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nset linesize 230\r\nset pagesize 9999\r\n\r\nselect cpu_time,elapsed_time,plan_hash_value, EXECUTIONS, round(elapsed_time\/EXECUTIONS\/1000\/1000, 4) avg_exec_time_sec\r\n from v$sqlstats where sql_id = '&SQL_ID';\t\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nset linesize 230<br \/>\nset pagesize 9999<\/p>\n<p>select cpu_time,elapsed_time,plan_hash_value, EXECUTIONS, round(elapsed_time\/EXECUTIONS\/1000\/1000, 4) avg_exec_time_sec<br \/>\n from v$sqlstats where sql_id = '&SQL_ID';<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>History of running sessions between 2 dates (dd\/mm\/yyyy hh24:mi) set linesize 250 set verify off set pagesize 9999 COLUMN Time FORMAT a20 HEADING &#8216;Sample time&#8217; COLUMN Username FORMAT a12 HEADING &#8216;User name&#8217; COLUMN Session_id FORMAT 99999 HEADING &#8216;Sess ID&#8217; COLUMN sql_id FORMAT a14 HEADING &#8216;SQL ID&#8217; COLUMN program FORMAT a33 HEADING &#8216;Program&#8217; COLUMN module FORMAT [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":253,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"","meta":{"footnotes":""},"class_list":["post-401","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-6t","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/401","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=401"}],"version-history":[{"count":10,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/401\/revisions"}],"predecessor-version":[{"id":561,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/401\/revisions\/561"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/253"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}