107 lines
4.6 KiB
SQL
107 lines
4.6 KiB
SQL
/*
|
|
|
|
Usage:
|
|
@sqlmon_sqlid <sql_id> <start_time> <end_time>
|
|
|
|
Examples:
|
|
|
|
@sqlmon_sqlid 8c2mb6gp8w8zy sysdate-1 sysdate
|
|
@sqlmon_sqlid 8c2mb6gp8w8zy "timestamp'2023-04-22 09:30:45'" "timestamp'2023-04-22 12:00:00'"
|
|
*/
|
|
|
|
@@myheader.sql
|
|
|
|
PROMPT
|
|
PROMPT ***********************************************************************
|
|
PROMPT * List SQL Monitor reports
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - sql_id = &&1
|
|
PROMPT * - min timestamp = &&2
|
|
PROMPT * - min timestamp = &&3
|
|
PROMPT ***********************************************************************
|
|
|
|
set pages 50
|
|
|
|
column report_id format 99999999999 heading 'Report|ID'
|
|
column status format a15 heading 'Status'
|
|
column sql_id format a17 heading 'SQL_ID'
|
|
column plan_hash format a15 heading 'Plan|hash value'
|
|
column sql_exec_start format a21 heading 'SQL|start'
|
|
column sql_exec_id format a12 heading 'SQL|exec ID'
|
|
column elap_sec format 9999.99 heading 'Elapsed|sec'
|
|
column cpu_sec format 9999.99 heading 'CPU|sec'
|
|
column io_sec format 9999.99 heading 'IO|sec'
|
|
column buffer_gets format a9 heading 'Buffer|gets'
|
|
column px_servers_requested format a3 heading 'PX|req'
|
|
column px_servers_allocated format a3 heading 'PX|all'
|
|
column sess format a16 heading 'Session'
|
|
column username format a15 heading 'Username'
|
|
|
|
|
|
SELECT /*+ NO_XML_QUERY_REWRITE */
|
|
t.report_id
|
|
, t.key1 sql_id
|
|
, t.key2 sql_exec_id
|
|
, to_char(to_date(t.key3,'MM:DD:YYYY HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') sql_exec_start
|
|
, x.plan_hash
|
|
, x.elapsed_time/1000000 elap_sec
|
|
, x.cpu_time/1000000 cpu_sec
|
|
, x.user_io_wait_time/1000000 io_sec
|
|
, x.buffer_gets
|
|
, x.username
|
|
, ''''||t.session_id||','||t.session_serial#||'@'||t.instance_number||'''' sess
|
|
, x.px_servers_requested
|
|
, x.px_servers_allocated
|
|
, x.status
|
|
FROM dba_hist_reports t
|
|
, xmltable('/report_repository_summary/sql'
|
|
PASSING xmlparse(document t.report_summary)
|
|
COLUMNS
|
|
sql_id path '@sql_id'
|
|
, sql_exec_start path '@sql_exec_start'
|
|
, sql_exec_id path '@sql_exec_id'
|
|
, status path 'status'
|
|
, sql_text path 'sql_text'
|
|
, first_refresh_time path 'first_refresh_time'
|
|
, last_refresh_time path 'last_refresh_time'
|
|
, refresh_count path 'refresh_count'
|
|
, inst_id path 'inst_id'
|
|
, session_id path 'session_id'
|
|
, session_serial path 'session_serial'
|
|
, user_id path 'user_id'
|
|
, username path 'user'
|
|
, con_id path 'con_id'
|
|
, con_name path 'con_name'
|
|
, modul path 'module'
|
|
, action path 'action'
|
|
, service path 'service'
|
|
, program path 'program'
|
|
, plan_hash path 'plan_hash'
|
|
, is_cross_instance path 'is_cross_instance'
|
|
, dop path 'dop'
|
|
, instances path 'instances'
|
|
, px_servers_requested path 'px_servers_requested'
|
|
, px_servers_allocated path 'px_servers_allocated'
|
|
, duration path 'stats/stat[@name="duration"]'
|
|
, elapsed_time path 'stats/stat[@name="elapsed_time"]'
|
|
, cpu_time path 'stats/stat[@name="cpu_time"]'
|
|
, user_io_wait_time path 'stats/stat[@name="user_io_wait_time"]'
|
|
, application_wait_time path 'stats/stat[@name="application_wait_time"]'
|
|
, concurrency_wait_time path 'stats/stat[@name="concurrency_wait_time"]'
|
|
, cluster_wait_time path 'stats/stat[@name="cluster_wait_time"]'
|
|
, plsql_exec_time path 'stats/stat[@name="plsql_exec_time"]'
|
|
, other_wait_time path 'stats/stat[@name="other_wait_time"]'
|
|
, buffer_gets path 'stats/stat[@name="buffer_gets"]'
|
|
, read_reqs path 'stats/stat[@name="read_reqs"]'
|
|
, read_bytes path 'stats/stat[@name="read_bytes"]'
|
|
) x
|
|
where
|
|
t.key1= '&&1'
|
|
and t.COMPONENT_NAME='sqlmonitor'
|
|
and to_date(t.key3,'MM:DD:YYYY HH24:MI:SS') between &&2 and &&3
|
|
order by sql_exec_start
|
|
/
|
|
|
|
@@myfooter.sql
|