SQL Report .... / SQL info ...
var sqlid VARCHAR2(13); begin :sqlid := 'g0bggfqrddc4w'; end; ----- change sql id /
set long 50000 pagesize 500 linesize 300
col frm heading from
select * from (select 'gv$sql' frm , sql_fulltext from gv$sql where sql_id=:sqlid
union all
select 'dba_hist', sql_text from dba_hist_sqltext where sql_id=:sqlid
);
col plan_table_output for a150 select plan_table_output from table(dbms_xplan.display_cursor(:sql_id, null, 'BASIC')) union all select * from table(dbms_xplan.display_awr(:sql_id, null, null, 'ALL'))
col plan_table_output for a150
select plan_table_output
from table(dbms_xplan.display_cursor('&sql_id', null, 'BASIC'))
union all
select * from table(dbms_xplan.display_awr('&sql_id', null, null, 'ALL'))
set linesize 300 pagesize 300
col sql_id for a20
col last_active_time for a26
col last_load_time for a20
col instance for a16
col instance_name for a20
col status for a20
col PLAN_TABLE_OUTPUT for a70
select a.sql_id, a.child_number
, to_char(a.last_active_time, 'DD-MON-YYYY HH24:MI:SS') last_active_time
, a.loaded_versions, a.open_versions, a.users_opening
from gv$sql a
where sql_id=:sqlid;
col last_active_time for a16
col last_load_time for a20
select a.sql_id, a.child_number
, (select instance_name
from gv$instance
where instance_number=a.inst_id) instance_name
, a.hash_value, a.old_hash_value, a.plan_hash_value
from gv$sql a
where a.sql_id=:sqlid;
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 1000
column SQL_TEXT for a100 word_wrap
col kill for a15
col event for a20
break on sql_text skip 1
SELECT DISTINCT ''''||S.sid ||','|| S.serial#||',@'||S.inst_id ||'''' kill, s.last_call_et,
s.sql_id,
s.username,
s.status,
s.blocking_session,
s.BLOCKING_INSTANCE,
sq.LAST_ACTIVE_TIME,
s.event,
dbms_lob.substr(sq.sql_fulltext,4000,1) ||dbms_lob.substr(sq.sql_fulltext,4000,4001) SQL_TEXT
FROM gv$session s, gv$sql sq
WHERE sq.address = s.sql_address
AND sq.hash_value = s.SQL_HASH_VALUE
AND sq.sql_id = s.sql_id
AND s.status = 'ACTIVE'
-- AND s.last_call_et >= 500
--AND s.sql_id IS NOT NULL
-- AND SQ.SQL_TEXT NOT LIKE '%$%'
and sq.sql_id= :sqlid
;
-- define sql_id='7jycxu86n60qh' set linesize 300 col begin_interval_time for a28 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, (disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio, (rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows, (CPU_TIME_DELTA/decode(nvl(CPU_TIME_DELTA,0),0,1,executions_delta))/1000000 avg_cpu_wait, (IOWAIT_DELTA/decode(nvl(IOWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_user_io_wait, (CLWAIT_DELTA/decode(nvl(CLWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_clu_wait, (APWAIT_DELTA/decode(nvl(APWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_app_wait, (CCWAIT_DELTA/decode(nvl(CCWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_concurrent_wait from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = :sql_id --sql_id and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3 ;
col inst for 99999999
col sid for 9990
col serial# for 999990
col username for a12
col osuser for a16
col program for a10 trunc
col Locked for a6
col status for a1 trunc print
col "hh:mm:ss" for a8
col SQL_ID for a15
col seq# for 99990
col event heading 'Current/LastEvent' for a25 trunc
col state head 'State (sec)' for a14
col kill for a15
select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, username,
ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser,
substr(program,instr(program,'/',-1)+1,
decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')-1),instr(program,'@')-1)) program, decode(lockwait,NULL,' ','L') locked, status,
to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss",
SQL_ID, seq# , event,
decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6),'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state,substr(module,1,25) module, substr(action,1,20) action
from GV$SESSION
where type = 'USER'
and audsid != 0 -- to exclude internal processess
and sql_id= :sqlid
order by inst_id, status, last_call_et desc, sid
/
set pagesize 200 linesize 300
col spid for a10
col PROGRAM for a10
col action for a10
col logon_time for a16
col module for a13
col cli_process for a7
col cli_mach for a15
col status for a10
col SCHEMANAME for a10
col last_call_et_Hrs for 9999.99
col sql_hash_value for 9999999999999
col username for a10
col "Last SQL" for a60
col elapsed_time for 999999999999
col kill for a15
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,p.spid,s.SCHEMANAME, s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,s.sql_id,PREV_SQL_ID,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and p.addr=s.paddr
--and s.status='INACTIVE'
-- and s.last_call_et > (3600)
--and s.last_call_et > ('&TIME_HRS' * 3600)
--and s.program='JDBC Thin Client'
and s.sql_id= :sqlid
order by last_call_et;
----------------------------
set long 50000
col sql_fulltext for a100 wrap
select sql_fulltext from gv$sql
where sql_id=:sqlid;
--and child_number=&childnr
;
set long 30000 pagesize 500 linesize 300
col frm heading from
select * from (select 'gv$sql' frm , sql_fulltext sql_text from gv$sql where sql_id=:sqlid
union all
select 'dba_hist', sql_text from dba_hist_sqltext where sql_id=:sqlid
)
;
-------
set pagesize 300 linesize 300
SELECT t.* FROM gv$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t
WHERE 1=1
-- and sql_text LIKE '%select id2_65%'
and sql_id =:sqlid
;
set linesize 300 pagesize 1000
column SQL_TEXT format a100 word_wrap
break on sql_text skip 1
SELECT DISTINCT sq.sql_id,dbms_lob.substr(sq.sql_fulltext,4000,1) ||dbms_lob.substr(sq.sql_fulltext,4000,4001) SQL_TEXT from gv$sql sq
where 1=1
and sq.sql_id=:sqlid;
col sql_text for a100 wrap
select *
from sys.WRH$_SQLTEXT
where sql_id=:sqlid;
col sql_id for a20
col plan_name for a35
col created for a30
col FORCE_MATCHING_SIGNATURE for 9999999999999999999
select sql.sql_id, sql.child_number, sql.inst_id, sql.force_matching_signature, sql.plan_hash_value, bl.plan_name, bl.enabled, bl.accepted, bl.fixed, bl.optimizer_cost
from gv$sql sql
, dba_sql_plan_baselines bl
where sql.sql_id=:sqlid
--and sql.child_number=&childnr
and sql.force_matching_signature=bl.SIGNATURE;
col SQL_HANDLE for a30
col origin for a16
col last_modified for a30
col last_verified for a30
select sql_handle, plan_name, origin, created, last_modified, last_verified
from dba_sql_plan_baselines
where signature in (select force_matching_signature
from gv$sql
where sql_id=:sqlid
--and child_number=&childnr
);
col name for a30
col task_exec_name for a16
col category for a10
select sql.sql_id, prof.name, prof.category, prof.created, prof.task_exec_name, prof.status
from DBA_SQL_PROFILES prof
, gv$sql sql
where sql.sql_id=:sqlid
-- and sql.child_number=&childnr
and sql.force_matching_signature=prof.SIGNATURE;
-- set heading off
col outline_hints for a132
select extractvalue(value(d), '/hint') as outline_hints
from xmltable('/*/outline_data/hint' passing
(select xmltype(other_xml) as xmlval
from gv$sql_plan
where sql_id=:sqlid
-- and child_number=childnr
and other_xml is not null
)
) d;
col sql_profile for a30
col sql_patch for a30
col sql_plan_baseline for a35
select sql_id, child_number, inst_id, sql_profile, sql_plan_baseline, sql_patch from gv$sql
where sql_id=:sqlid
--and child_number=&childnr
order by sql_id, inst_id, child_number;
select executions, parse_calls, loads, rows_processed, sorts from gv$sql
where sql_id=:sqlid
--and child_number=&childnr
;
select trunc(elapsed_time/1000000,1) elapsed_time, trunc(application_wait_time/1000000,1) applic_wait_time, trunc(cpu_time/1000000,1) cpu_time,
trunc(user_io_wait_time/1000000,1) user_io_wait_time, trunc(concurrency_wait_time/1000000,1) concurr_time
from gv$sql
where sql_id=:sqlid
--and child_number=&childnr
;
select trunc(elapsed_time/1000000,1) elapsed_time, trunc(application_wait_time/1000000,1) applic_wait_time, trunc(cpu_time/1000000,1) cpu_time,
trunc(user_io_wait_time/1000000,1) user_io_wait_time, trunc(concurrency_wait_time/1000000,1) concurr_time
from gv$sql
where sql_id=:sqlid
--and child_number=&childnr
;
col username for a30
col module for a40
col action for a30
col service for a30
col PARSING_SCHEMA_NAME for a30
select u.username, s.PARSING_SCHEMA_NAME, s.SERVICE, s.MODULE, s.ACTION
from gv$sql s , dba_users u
where s.sql_id=:sqlid
--and s.child_number=&childnr
and u.user_id=s.PARSING_USER_ID;
COLUMN id FORMAT 99
COLUMN operation FORMAT a18
COLUMN options FORMAT a11
COLUMN actual_time FORMAT 99.999 HEADING "Actual|Time"
COLUMN object_name FORMAT a17 HEADING "Object|Name"
COLUMN last_starts FORMAT 9999999 HEADING "Last|Starts"
COLUMN actual_rows FORMAT 9999999 HEADING "Actual|Rows"
SELECT id
,LPAD (' ', DEPTH) || operation operation
,options
,last_elapsed_time / 1000000 actual_time
,object_name
,last_starts
,last_output_rows actual_rows
FROM gv$sql_plan_statistics_all
WHERE sql_id = :sqlid
ORDER BY id;---=========
/* V$SQL_CS_HISTOGRAM – Distributes the frequency (three-bucket histogram) at which Oracle used to decide if a SQL statement was bind-sensitive, including how many times a particular child cursor has been executed. V$SQL_CS_SELECTIVITY – Contains information about the relative selectivity of a SQL statement’s predicates, including the predicates themselves, and their high and low value ranges. V$SQL_CS_STATISTICS – Lists the statistics of whether and/or how often an Adaptive Cursor has been shared. The column PEEKED = Y, then the bind set has been used to build the adaptive cursor. */
col IS_OBSOLETE for a11 col IS_BIND_SENSITIVE for a17 col IS_BIND_AWARE for a13 col IS_SHAREABLE for a12 select IS_OBSOLETE, IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE from gv$sql where sql_id=:sqlid --and child_number=&childnr ; col PREDICATE for a30 select inst_id, sql_id, child_number, predicate,range_id, low, high from GV$SQL_CS_SELECTIVITY where sql_id = :sqlid order by inst_id, child_number; select * from GV$SQL_CS_HISTOGRAM where sql_id = :sqlid order by inst_id, child_number;
col is_bind_sensitive for a20 col is_bind_aware like is_bind_sensitive col is_shareable like is_bind_sensitive select sql_id,child_number, parse_calls,executions, buffer_gets, rows_processed, plan_hash_value, is_bind_sensitive, is_bind_aware, is_shareable from gv$sql where sql_id=:sqlid select sql_id, child_number,predicate, range_id, low, high from gv$sql_cs_selectivity where sql_id in (select sql_id from gv$sql where sql_id=':sqlid') select sql_id,child_number,bind_set_hash_value, peeked, executions, rows_processed, buffer_gets, cpu_time from gv$sql_cs_statistics where sql_id in (select sql_id from gv$sql where sql_id=:sqlid); select * from gv$sql_cs_histogram where sql_id in (select sql_id from gv$sql where sql_id=:sqlid);
--- =================
select buffer_gets, disk_reads, (sharable_mem+persistent_mem+runtime_mem) sql_area_used from gv$sql
where sql_id=:sqlid
--and child_number=&childnr
;
select *
from
xmltable( 'for $a at $i in /ROWSET/ROW
,$r in $a/*
return element ROW{
element ROW_NUM{$i}
,element COL_NAME{$r/name()}
,element COL_VALUE{$r/text()}
}'
passing xmltype(cursor( select *
from gv$sql_shared_cursor
where sql_id=:sqlid
-- and child_number=&childnr
))
columns
row_num int
,col_name varchar2(30)
,col_value varchar2(100)
);
col name for a10
col value_string for a50
col datatype_string for a50
select child_number, name, position
, case datatype
when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
when 12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
else value_string
end value_string
, datatype_string
from gv$sql_bind_capture
where sql_id=:sqlid
-- and child_number=&childnr
order by position;
select 'variable '||replace(name, ':', 'BIND_')||' '||decode(datatype_string, 'TIMESTAMP', 'VARCHAR2(128)', datatype_string) datatype_string
from gv$sql_bind_capture
where sql_id=:sqlid
-- and child_number=&childnr
order by position;
prompt
select 'exec '||replace(name, ':', ':BIND_')||' := '||
case datatype
when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
when 12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
else value_string
end value_string
from gv$sql_bind_capture
where sql_id=:sqlid
--and child_number=&childnr
and datatype_string NOT LIKE '%CHAR%'
and datatype_string NOT IN ('DATE', 'CLOB', 'TIMESTAMP')
order by position;
col exec for a100 wrap
select 'exec '||replace(name, ':', ':BIND_')||' := '''||value_string||'''' exec
from (
select name
, case datatype
when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
when 12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
else value_string
end value_string
, position
from gv$sql_bind_capture
where 1=1
and sql_id=:sqlid
-- and child_number=&childnr
and (datatype_string LIKE '%CHAR%'
OR datatype_string IN ('DATE', 'CLOB', 'TIMESTAMP'))
)
order by position;
col name for a10
col value_string for a50
col datatype_string for a50
select child_number, name, position
, case datatype
when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
when 12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
else value_string
end value_string
, datatype_string
from gv$sql_bind_capture
where 1=1
and sql_id=:sqlid
-- and child_number <> &childnr
order by child_number, position;
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'));
column v_sql_handle new_value l_sql_handle noprint
select distinct plan.SQL_HANDLE v_sql_handle
from dba_sql_plan_baselines plan
, gv$sql sql
where sql.sql_id=:sqlid
--and sql.child_number=&childnr
and sql.force_matching_signature=plan.SIGNATURE;
SELECT * FROM TABLE(dbms_xplan.display_sql_plan_baseline(sql_handle=>'&l_sql_handle'));
select distinct 'select sys.dbms_spm.DROP_SQL_PLAN_BASELINE('''||sql_handle||''') from dual;' "Based on SQL_HANDLE"
from dba_sql_plan_baselines
where signature in (select force_matching_signature
from gv$sql
where sql_id=:sqlid
-- and child_number=&childnr
);
select 'declare v_pls PLS_INTEGER; BEGIN v_pls :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'''||sql_handle||''', plan_name=>'''||plan_name||'''); END;'
from dba_sql_plan_baselines
where signature in (select force_matching_signature
from gv$sql
where sql_id=:sqlid
-- and child_number=&childnr
);
set heading on
SELECT owner, table_name, last_analyzed, sample_size, num_rows, avg_row_len, blocks, partitioned, global_stats
FROM dba_tables
WHERE table_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%TABLE ACCESS%'
)
ORDER BY owner, table_name
/
SELECT ob.owner, ob.object_name, ob.subobject_name, ob.object_type, to_char(savtime, 'DD-MON-YY HH24:MI:SS') savtime, rowcnt, blkcnt, avgrln ,samplesize, analyzetime
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE object_type in ('TABLE')
AND object_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%TABLE ACCESS%'
)
and object_id=obj#
order by ob.owner, ob.object_name, analyzetime asc
;
SELECT owner, index_name, table_name, last_analyzed, sample_size, num_rows, partitioned, global_stats FROM dba_indexes
WHERE index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid,null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
ORDER BY owner, table_name, index_name
/
SELECT table_owner, table_name, partition_name, subpartition_count, last_analyzed, sample_size, num_rows, avg_row_len
FROM dba_tab_partitions
WHERE table_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid,null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%TABLE ACCESS%'
)
ORDER BY table_owner, table_name, partition_name
/
SELECT index_owner, index_name, partition_name, subpartition_count, last_analyzed, sample_size, num_rows
FROM dba_ind_partitions
WHERE index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
ORDER BY index_owner, index_name, partition_name
/
SELECT ob.owner, ob.object_name, ob.subobject_name, ob.object_type,to_char(savtime, 'DD-MON-YY HH24:MI:SS') savtime
, rowcnt, BLEVEL , LEAFCNT, DISTKEY, CLUFAC, samplesize, analyzetime
FROM sys.WRI$_OPTSTAT_IND_HISTORY, dba_objects ob
WHERE object_type in ('INDEX')
AND object_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
and object_id=obj#
order by ob.owner, ob.object_name, analyzetime asc
;
SELECT ic.index_owner, ic.index_name, ic.table_name, ic.column_name, ic.column_position col_pos, tc.last_analyzed, tc. sample_size, tc.num_distinct, tc.num_nulls, tc.density, tc.histogram, tc.num_buckets
FROM dba_ind_columns ic
, dba_tab_columns tc
WHERE ic.index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
AND ic.table_owner=tc.owner
AND ic.table_name=tc.table_name
AND ic.column_name=tc.column_name
ORDER BY ic.table_owner, ic.table_name, ic.index_name, ic.column_position
/
select distinct 'exec sys.dbms_shared_pool.purge('''||address||', '||hash_value||''', ''c'')'
from gv$sql
where sql_id = :sqlid
--and child_number=&childnr
;
-- C or c cursor --'P' or 'p' is stands for procedure --'T' or 't' is stands for type --'R' or 'r' is stands for trigger --'Q' or 'q' is stands for sequence @coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
SELECT ob.owner, ob.object_name, col.COLUMN_NAME, to_char(his.savtime, 'DD-MON-YY HH24:MI:SS') savtime
, his.NULL_CNT, his.DISTCNT, his.DENSITY, his.SAMPLE_DISTCNT, his.sample_size, his.TIMESTAMP#
FROM sys.WRI$_OPTSTAT_HISTHEAD_HISTORY his
, dba_objects ob
, dba_tab_columns col
, dba_ind_columns ic
WHERE ic.index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
AND ic.table_owner=col.owner
AND ic.table_name=col.table_name
AND ic.column_name=col.column_name
and ob.object_type in ('TABLE')
and ob.object_id=his.obj#
and col.COLUMN_ID=his.INTCOL#
and ob.object_name=col.TABLE_NAME
and ob.owner=col.owner
order by col.owner, col.table_name, ic.column_position, col.column_name, savtime asc
;
alter session set NLS_TIMESTAMP_FORMAT = 'DD-MM-YYYY HH24:MI:SS.FF';
set headin on
col BEGIN_INTERVAL_TIME for a40
col FORCE_MATCHING_SIGNATURE for 9999999999999999999999
select a.BEGIN_INTERVAL_TIME
, a.INSTANCE_NUMBER
, b.PLAN_HASH_VALUE
, b.FORCE_MATCHING_SIGNATURE F_MATCHING_SIGN
, b.SQL_PROFILE
, b.executions_delta EXEC_DELTA
, round(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000,2) "Elapsed time (sec.)"
, b.PX_SERVERS_EXECS_TOTAL PX_SERV_TOT
, b.PX_SERVERS_EXECS_DELTA PX_SERV_DELTA
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
order by a.BEGIN_INTERVAL_TIME
/
select a.BEGIN_INTERVAL_TIME
,b.sql_id
, a.INSTANCE_NUMBER
, b.PLAN_HASH_VALUE
, b.OPTIMIZER_COST
, b.LOADED_VERSIONS
, b.VERSION_COUNT
, b.MODULE
, b.ACTION
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
order by a.BEGIN_INTERVAL_TIME
/
col DATATYPE_STRING for a30
col VALUE_STRING for a30
select a.BEGIN_INTERVAL_TIME
, a.INSTANCE_NUMBER
, b.PLAN_HASH_VALUE
, b.INVALIDATIONS_TOTAL
, c.POSITION
, c.DATATYPE_STRING
, case c.datatype
when 180 then to_char(anydata.accesstimestamp(c.value_anydata),'DD-MON-YYYY HH24:MI:SS')
when 12 then to_char(anydata.accessdate(c.value_anydata),'DD-MON-YYYY HH24:MI:SS')
else c.value_string
end value_string
from dba_hist_snapshot a,
dba_hist_sqlstat b,
table(dbms_sqltune.extract_binds(b.bind_data)) c
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
and b.bind_data is not null
order by a.BEGIN_INTERVAL_TIME, c.POSITION
/
select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, round(max(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "MAX (sec)"
, round(min(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "MIN (sec)"
, round(avg(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "AVG (sec)"
, round(stddev(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "STDDEV"
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
/
select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, max(OPTIMIZER_COST) "MAX COST"
, min(OPTIMIZER_COST) "MIN COST"
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
/
select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, b.MODULE
, b.ACTION
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE, b.MODULE, b.ACTION
/
select a.INSTANCE_NUMBER,b.sql_id, b.PLAN_HASH_VALUE
, round(sum(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "ELAPSED (sec)"
, round(sum(b.CPU_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "CPU (sec)"
, round(sum(b.IOWAIT_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "IO WAIT (sec)"
, round(sum(b.APWAIT_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "APP WAIT (sec)"
, round(sum(b.CLWAIT_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "CLUSTER WAIT (sec)"
, round(sum(b.CCWAIT_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "CONCUR WAIT (sec)"
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a, dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER,b.sql_id, b.PLAN_HASH_VALUE
/
select a.INSTANCE_NUMBER,b.sql_id, b.PLAN_HASH_VALUE
, b.FORCE_MATCHING_SIGNATURE
, b.SQL_PROFILE
, sum(b.SHARABLE_MEM) "SHARABLE MEM"
, sum(b.PARSE_CALLS_DELTA) "TOT PARSE"
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER,b.sql_id, b.PLAN_HASH_VALUE, b.FORCE_MATCHING_SIGNATURE, b.SQL_PROFILE
order by a.INSTANCE_NUMBER
/
select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, sum(b.BUFFER_GETS_DELTA) "BUFFER GETS"
, sum(b.DISK_READS_DELTA) "DISK READS"
, sum(b.DIRECT_WRITES_DELTA) "DIRECT WRITES"
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a, dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
/
select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, sum(b.ROWS_PROCESSED_DELTA) "ROWS_PROCESSED"
, sum(b.SORTS_DELTA) "SORTS"
, sum(b.INVALIDATIONS_DELTA) "INVALIDATIONS"
, sum(b.PX_SERVERS_EXECS_DELTA) "PX_SERVERS_EXECS"
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
order by a.INSTANCE_NUMBER
/
set heading off
SELECT 'UNBOUND_CURSOR: '||SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))),
'SQL_TYPE_MISMATCH: '||SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))),
'OPTIMIZER_MISMATCH: '||SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))),
'OUTLINE_MISMATCH: '||SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))),
'STATS_ROW_MISMATCH: '||SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))),
'LITERAL_MISMATCH: '||SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))),
'FORCE_HARD_PARSE: '||SUM(TO_NUMBER(DECODE(force_hard_parse,'Y',1,'N','0'))),
'EXPLAIN_PLAN_CURSOR: '||SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))),
'BUFFERED_DML_MISMATCH: '||SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))),
'PDML_ENV_MISMATCH: '||SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))),
'INST_DRTLD_MISMATCH: '||SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))),
'SLAVE_QC_MISMATCH: '||SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))),
'TYPECHECK_MISMATCH: '||SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))),
'AUTH_CHECK_MISMATCH: '||SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))),
'BIND_MISMATCH: '||SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))),
'DESCRIBE_MISMATCH: '||SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))),
'LANGUAGE_MISMATCH: '||SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))),
'TRANSLATION_MISMATCH: '||SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))),
'BIND_EQUIV_FAILURE: '||SUM(TO_NUMBER(DECODE(bind_equiv_failure,'Y',1,'N','0'))),
'INSUFF_PRIVS: '||SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))),
'INSUFF_PRIVS_REM: '||SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))),
'REMOTE_TRANS_MISMATCH: '||SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))),
'LOGMINER_SESSION_MISMATCH: '||SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))) ,
'INCOMP_LTRL_MISMATCH: '||SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))),
'OVERLAP_TIME_MISMATCH: '||SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))),
'EDITION_MISMATCH: '||SUM(TO_NUMBER(DECODE(edition_mismatch,'Y',1,'N','0'))),
'MV_QUERY_GEN_MISMATCH: '||SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))),
'USER_BIND_PEEK_MISMATCH: '||SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))),
'TYPCHK_DEP_MISMATCH: '||SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))),
'NO_TRIGGER_MISMATCH: '||SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))),
'FLASHBACK_CURSOR: '||SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))),
'ANYDATA_TRANSFORMATION: '||SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))),
-- 'INCOMPLETE_CURSOR: '||SUM(TO_NUMBER(DECODE(incomplete_cursor,'Y',1,'N','0'))),
'TOP_LEVEL_RPI_CURSOR: '||SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))),
'DIFFERENT_LONG_LENGTH: '||SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))),
'LOGICAL_STANDBY_APPLY: '||SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))),
'DIFF_CALL_DURN: '||SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))),
'BIND_UACS_DIFF: '||SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))),
'PLSQL_CMP_SWITCHS_DIFF: '||SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))),
'CURSOR_PARTS_MISMATCH: '||SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))),
'STB_OBJECT_MISMATCH: '||SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))),
'CROSSEDITION_TRIGGER_MISMATCH : '||SUM(TO_NUMBER(DECODE(crossedition_trigger_mismatch,'Y',1,'N','0'))),
'PQ_SLAVE_MISMATCH: '||SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))),
'TOP_LEVEL_DDL_MISMATCH: '||SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))),
'MULTI_PX_MISMATCH: '||SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))),
'BIND_PEEKED_PQ_MISMATCH: '||SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))),
'MV_REWRITE_MISMATCH: '||SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))),
'ROLL_INVALID_MISMATCH: '||SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))),
'OPTIMIZER_MODE_MISMATCH: '||SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))),
'PX_MISMATCH: '||SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))),
'MV_STALEOBJ_MISMATCH: '||SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))),
'FLASHBACK_TABLE_MISMATCH: '||SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))),
'LITREP_COMP_MISMATCH: '||SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))),
'PLSQL_DEBUG: '||SUM(TO_NUMBER(DECODE(plsql_debug,'Y',1,'N','0'))),
'LOAD_OPTIMIZER_STATS: '||SUM(TO_NUMBER(DECODE(load_optimizer_stats,'Y',1,'N','0'))),
'ACL_MISMATCH: '||SUM(TO_NUMBER(DECODE(acl_mismatch,'Y',1,'N','0'))),
'FLASHBACK_ARCHIVE_MISMATCH: '||SUM(TO_NUMBER(DECODE(flashback_archive_mismatch,'Y',1,'N','0'))),
'LOCK_USER_SCHEMA_FAILED: '||SUM(TO_NUMBER(DECODE(lock_user_schema_failed,'Y',1,'N','0'))),
'REMOTE_MAPPING_MISMATCH: '||SUM(TO_NUMBER(DECODE(remote_mapping_mismatch,'Y',1,'N','0'))),
'LOAD_RUNTIME_HEAP_FAILED: '||SUM(TO_NUMBER(DECODE(load_runtime_heap_failed,'Y',1,'N','0'))),
'HASH_MATCH_FAILED: '||SUM(TO_NUMBER(DECODE(hash_match_failed,'Y',1,'N','0'))),
'PURGED_CURSOR: '||SUM(TO_NUMBER(DECODE(purged_cursor,'Y',1,'N','0'))),
'BIND_LENGTH_UPGRADEABLE: '||SUM(TO_NUMBER(DECODE(bind_length_upgradeable,'Y',1,'N','0')))
FROM gv$sql_shared_cursor
WHERE address IN (SELECT address
FROM gv$sqlarea
WHERE sql_id = :sqlid)
/
===
column 01 format a2 column 02 format a2 column 03 format a2 column 04 format a2 column 05 format a2 column 06 format a2 column 07 format a2 column 08 format a2 column 09 format a2 column 10 format a2 column 11 format a2 column 12 format a2 column 13 format a2 column 14 format a2 column 15 format a2 column 16 format a2 column 17 format a2 column 18 format a2 column 19 format a2 column 20 format a2 column 21 format a2 column 22 format a2 column 23 format a2 column 24 format a2 column 25 format a2 column 26 format a2 column 27 format a2 column 28 format a2 column 29 format a2 column 30 format a2 column 31 format a2 column 32 format a2 column 33 format a2 column 34 format a2 column 35 format a2 column 36 format a2 column 37 format a2 column 38 format a2 column 39 format a2 column 40 format a2 column 41 format a2 column 42 format a2 column 43 format a2 column 44 format a2 column 45 format a2 column 46 format a2 column 47 format a2 column 48 format a2 column 49 format a2 column 50 format a2 column 51 format a2 column 52 format a2 column 53 format a2 column 54 format a2 column 55 format a2 column 56 format a2 column 57 format a2 column 58 format a2 column 59 format a2 column 60 format a2 column 61 format a2 column 62 format a2 column 63 format a2 select sql_id ,child_number , CON_ID ,sql_type_mismatch "01" ,optimizer_mismatch "02" ,outline_mismatch "03" ,stats_row_mismatch "04" ,literal_mismatch "05" ,force_hard_parse "06" ,explain_plan_cursor "07" ,buffered_dml_mismatch "08" ,pdml_env_mismatch "09" ,inst_drtld_mismatch "10" ,slave_qc_mismatch "11" ,typecheck_mismatch "12" ,auth_check_mismatch "13" ,bind_mismatch "14" ,describe_mismatch "15" ,language_mismatch "16" ,translation_mismatch "17" ,bind_equiv_failure "18" ,insuff_privs "19" ,insuff_privs_rem "20" ,remote_trans_mismatch "21" ,logminer_session_mismatch "22" ,incomp_ltrl_mismatch "23" ,overlap_time_mismatch "24" ,edition_mismatch "25" ,mv_query_gen_mismatch "26" ,user_bind_peek_mismatch "27" ,typchk_dep_mismatch "28" ,no_trigger_mismatch "29" ,flashback_cursor "30" ,anydata_transformation "31" ,pddl_env_mismatch "32" ,top_level_rpi_cursor "33" ,different_long_length "34" ,logical_standby_apply "35" ,diff_call_durn "36" ,bind_uacs_diff "37" ,plsql_cmp_switchs_diff "38" ,cursor_parts_mismatch "39" ,stb_object_mismatch "40" ,crossedition_trigger_mismatch "41" ,pq_slave_mismatch "42" ,top_level_ddl_mismatch "43" ,multi_px_mismatch "44" ,bind_peeked_pq_mismatch "45" ,mv_rewrite_mismatch "46" ,roll_invalid_mismatch "47" ,optimizer_mode_mismatch "48" ,px_mismatch "49" ,mv_staleobj_mismatch "50" ,flashback_table_mismatch "51" ,litrep_comp_mismatch "52" ,plsql_debug "53" ,load_optimizer_stats "54" ,acl_mismatch "55" ,flashback_archive_mismatch "56" ,lock_user_schema_failed "57" ,remote_mapping_mismatch "58" ,load_runtime_heap_failed "59" ,hash_match_failed "60" ,purged_cursor "61" ,bind_length_upgradeable "62" ,use_feedback_stats "63" from gv$sql_shared_cursor where 1=1 -- sql_id = '&sql_id' and CHILD_NUMBER>10 order by child_number ; SQL_ID CHILD_NUMBER CON_ID 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 ------------- ------------ ---------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 3wrrjm9qtr2my 11 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N 3y9bat7nwcjy5 11 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N Y N N N N N N N N N N N N N N N N 6fjdakmfd1d9f 11 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N Y N N N N N N N N N N N N N N N N
set pagesize 400
col SQL_TEXT for a50 wrap
col REASON for a40
col PARSING_SCHEMA_NAME for a20
set lines 180
select sql_id,sc.address,version_count,parsing_schema_name,reason,lpad(' ',10,'-')||replace(sql_text,(13)) sql_text from (
select
sql_id, address, '** reason => '
||decode(max(UNBOUND_CURSOR),'Y','UNBOUND_CURSOR '||': '||count(*)||' | ')
||decode(max(SQL_TYPE_MISMATCH),'Y','SQL_TYPE_MISMATCH '||': '||count(*)||' | ')
||decode(max(OPTIMIZER_MISMATCH),'Y','OPTIMIZER_MISMATCH '||': '||count(*)||' | ')
||decode(max(OUTLINE_MISMATCH),'Y','OUTLINE_MISMATCH '||': '||count(*)||' | ')
||decode(max(STATS_ROW_MISMATCH),'Y','STATS_ROW_MISMATCH '||': '||count(*)||' | ')
||decode(max(LITERAL_MISMATCH),'Y','LITERAL_MISMATCH '||': '||count(*)||' | ')
||decode(max(FORCE_HARD_PARSE),'Y','FORCE_HARD_PARSE '||': '||count(*)||' | ')
||decode(max(EXPLAIN_PLAN_CURSOR),'Y','EXPLAIN_PLAN_CURSOR '||': '||count(*)||' | ')
||decode(max(BUFFERED_DML_MISMATCH),'Y','BUFFERED_DML_MISMATCH '||': '||count(*)||' | ')
||decode(max(PDML_ENV_MISMATCH),'Y','PDML_ENV_MISMATCH '||': '||count(*)||' | ')
||decode(max(INST_DRTLD_MISMATCH),'Y','INST_DRTLD_MISMATCH '||': '||count(*)||' | ')
||decode(max(SLAVE_QC_MISMATCH),'Y','SLAVE_QC_MISMATCH '||': '||count(*)||' | ')
||decode(max(TYPECHECK_MISMATCH),'Y','TYPECHECK_MISMATCH '||': '||count(*)||' | ')
||decode(max(AUTH_CHECK_MISMATCH),'Y','AUTH_CHECK_MISMATCH '||': '||count(*)||' | ')
||decode(max(BIND_MISMATCH),'Y','BIND_MISMATCH '||': '||count(*)||' | ')
||decode(max(DESCRIBE_MISMATCH),'Y','DESCRIBE_MISMATCH '||': '||count(*)||' | ')
||decode(max(LANGUAGE_MISMATCH),'Y','LANGUAGE_MISMATCH '||': '||count(*)||' | ')
||decode(max(TRANSLATION_MISMATCH),'Y','TRANSLATION_MISMATCH '||': '||count(*)||' | ')
||decode(max(BIND_EQUIV_FAILURE),'Y','BIND_EQUIV_FAILURE '||': '||count(*)||' | ')
||decode(max(INSUFF_PRIVS),'Y','INSUFF_PRIVS '||': '||count(*)||' | ')
||decode(max(INSUFF_PRIVS_REM),'Y','INSUFF_PRIVS_REM '||': '||count(*)||' | ')
||decode(max(REMOTE_TRANS_MISMATCH),'Y','REMOTE_TRANS_MISMATCH '||': '||count(*)||' | ')
||decode(max(LOGMINER_SESSION_MISMATCH),'Y','LOGMINER_SESSION_MISMATCH '||': '||count(*)||' | ')
||decode(max(INCOMP_LTRL_MISMATCH),'Y','INCOMP_LTRL_MISMATCH '||': '||count(*)||' | ')
||decode(max(OVERLAP_TIME_MISMATCH),'Y','OVERLAP_TIME_MISMATCH '||': '||count(*)||' | ')
||decode(max(EDITION_MISMATCH),'Y','EDITION_MISMATCH '||': '||count(*)||' | ')
||decode(max(MV_QUERY_GEN_MISMATCH),'Y','MV_QUERY_GEN_MISMATCH '||': '||count(*)||' | ')
||decode(max(USER_BIND_PEEK_MISMATCH),'Y','USER_BIND_PEEK_MISMATCH '||': '||count(*)||' | ')
||decode(max(TYPCHK_DEP_MISMATCH),'Y','TYPCHK_DEP_MISMATCH '||': '||count(*)||' | ')
||decode(max(NO_TRIGGER_MISMATCH),'Y','NO_TRIGGER_MISMATCH '||': '||count(*)||' | ')
||decode(max(FLASHBACK_CURSOR),'Y','FLASHBACK_CURSOR '||': '||count(*)||' | ')
||decode(max(ANYDATA_TRANSFORMATION),'Y','ANYDATA_TRANSFORMATION '||': '||count(*)||' | ')
||decode(max(PDDL_ENV_MISMATCH),'Y','PDDL_ENV_MISMATCH '||': '||count(*)||' | ')
||decode(max(TOP_LEVEL_RPI_CURSOR),'Y','TOP_LEVEL_RPI_CURSOR '||': '||count(*)||' | ')
||decode(max(DIFFERENT_LONG_LENGTH),'Y','DIFFERENT_LONG_LENGTH '||': '||count(*)||' | ')
||decode(max(LOGICAL_STANDBY_APPLY),'Y','LOGICAL_STANDBY_APPLY '||': '||count(*)||' | ')
||decode(max(DIFF_CALL_DURN),'Y','DIFF_CALL_DURN '||': '||count(*)||' | ')
||decode(max(BIND_UACS_DIFF),'Y','BIND_UACS_DIFF '||': '||count(*)||' | ')
||decode(max(PLSQL_CMP_SWITCHS_DIFF),'Y','PLSQL_CMP_SWITCHS_DIFF '||': '||count(*)||' | ')
||decode(max(CURSOR_PARTS_MISMATCH),'Y','CURSOR_PARTS_MISMATCH '||': '||count(*)||' | ')
||decode(max(STB_OBJECT_MISMATCH),'Y','STB_OBJECT_MISMATCH '||': '||count(*)||' | ')
||decode(max(CROSSEDITION_TRIGGER_MISMATCH),'Y','CROSSEDITION_TRIGGER_MISMATCH '||': '||count(*)||' | ')
||decode(max(PQ_SLAVE_MISMATCH),'Y','PQ_SLAVE_MISMATCH '||': '||count(*)||' | ')
||decode(max(TOP_LEVEL_DDL_MISMATCH),'Y','TOP_LEVEL_DDL_MISMATCH '||': '||count(*)||' | ')
||decode(max(MULTI_PX_MISMATCH),'Y','MULTI_PX_MISMATCH '||': '||count(*)||' | ')
||decode(max(BIND_PEEKED_PQ_MISMATCH),'Y','BIND_PEEKED_PQ_MISMATCH '||': '||count(*)||' | ')
||decode(max(MV_REWRITE_MISMATCH),'Y','MV_REWRITE_MISMATCH '||': '||count(*)||' | ')
||decode(max(ROLL_INVALID_MISMATCH),'Y','ROLL_INVALID_MISMATCH '||': '||count(*)||' | ')
||decode(max(OPTIMIZER_MODE_MISMATCH),'Y','OPTIMIZER_MODE_MISMATCH '||': '||count(*)||' | ')
||decode(max(PX_MISMATCH),'Y','PX_MISMATCH '||': '||count(*)||' | ')
||decode(max(MV_STALEOBJ_MISMATCH),'Y','MV_STALEOBJ_MISMATCH '||': '||count(*)||' | ')
||decode(max(FLASHBACK_TABLE_MISMATCH),'Y','FLASHBACK_TABLE_MISMATCH '||': '||count(*)||' | ')
||decode(max(LITREP_COMP_MISMATCH),'Y','LITREP_COMP_MISMATCH '||': '||count(*)||' | ')
||decode(max(PLSQL_DEBUG),'Y','PLSQL_DEBUG '||': '||count(*)||' | ')
||decode(max(LOAD_OPTIMIZER_STATS),'Y','LOAD_OPTIMIZER_STATS '||': '||count(*)||' | ')
||decode(max(ACL_MISMATCH),'Y','ACL_MISMATCH '||': '||count(*)||' | ')
||decode(max(FLASHBACK_ARCHIVE_MISMATCH),'Y','FLASHBACK_ARCHIVE_MISMATCH '||': '||count(*)||' | ')
||decode(max(LOCK_USER_SCHEMA_FAILED),'Y','LOCK_USER_SCHEMA_FAILED '||': '||count(*)||' | ')
||decode(max(REMOTE_MAPPING_MISMATCH),'Y','REMOTE_MAPPING_MISMATCH '||': '||count(*)||' | ')
||decode(max(LOAD_RUNTIME_HEAP_FAILED),'Y','LOAD_RUNTIME_HEAP_FAILED '||': '||count(*)||' | ')
||decode(max(HASH_MATCH_FAILED),'Y','HASH_MATCH_FAILED '||': '||count(*)||' | ')
||decode(max(PURGED_CURSOR),'Y','PURGED_CURSOR '||': '||count(*)||' | ')
||decode(max(BIND_LENGTH_UPGRADEABLE),'Y','BIND_LENGTH_UPGRADEABLE '||': '||count(*)||' | ')
||decode(max(USE_FEEDBACK_STATS),'Y','USE_FEEDBACK_STATS '||': '||count(*)||' | ')
reason
from
v$sql_shared_cursor
group by
sql_id, address
) sc join v$sqlarea sq using(sql_id)
where version_count > 200
and parsing_schema_name not in ('SYS')
order by sql_id, version_count
;
=== set heading on
ref
https://github.com/Marcel-Jan/oracle_performance/blob/master/sqlperf.sql
=================================
set linesize 500
col "Total time" for a15
col "Average time" for a15
col "Average time PX" for a15
col "Avg time/1M rows" for a15
col "Avg time/row" for a15
-- tab=Executions (recent)
select sql_id
,trunc(last_active_time) as "Run date"
, to_char(min(last_active_time),'HH24:MI:SS') as "First"
, to_char(max(last_active_time),'HH24:MI:SS') as "Last"
, plan_hash_value as "Plan hash"
, sum(executions_calc) as "Times called"
, sum(end_of_fetch_count) as "Times completed"
, least(100, round(100 * sum(end_of_fetch_count) / sum(executions_calc),1)) as "Success %"
, cast(numtodsinterval(sum(elapsed_time)/1e6,'SECOND') as interval day(1) to second(2)) as "Total time"
-- , round(sum(elapsed_time)/1e6) as "Total seconds"
, round(sum(elapsed_time)/1e6 / nvl(sum(executions_calc),1),1) as "Average (s)"
, round(sum(elapsed_time)/1e6 / nvl(sum(executions_calc),1)/nvl(nullif(sum(px_servers_executions),0),1)) as "Average (s) PX"
, cast(numtodsinterval(sum(elapsed_time)/1e6 / nvl(sum(executions_calc),1),'SECOND') as interval day(1) to second(1)) as "Average time"
, cast(numtodsinterval(sum(elapsed_time)/1e6 / nvl(sum(executions_calc),1) /nvl(nullif(sum(px_servers_executions),0),1),'SECOND') as interval day(1) to second(1)) as "Average time PX"
-- , sum(buffer_gets) as "Buffer gets"
, round(sum(buffer_gets)/sum(executions_calc)) as "Buffer gets/exec"
, round(sum(buffer_gets)/nullif(sum(rows_processed),0)) as "Buffer gets/row"
, sum(rows_processed) as "Rows"
, round(sum(rows_processed) / sum(executions_calc)) as "Rows/exec"
, cast(numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0)/1e6,'SECOND') as interval day(1) to second(3)) as "Avg time/row"
, cast
( case
when sum(elapsed_time)/nullif(sum(rows_processed),0) < 2147483647 then -- 2**31 -1, limit for 32 bit integers
numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0),'SECOND')
else numtodsinterval(sum(elapsed_time/3600)/nullif(sum(rows_processed),0),'HOUR')
end -- Avoid "ORA-01873: the leading precision of the interval is too small" for large values
as interval day(5) to second(0)) as "Avg time/1M rows"
, sum(px_servers_executions) as "PX server executions"
from ( select s.sql_id
, s.last_active_time
, plan_hash_value
--, executions
, case executions when 0 then 1 else executions end as executions_calc -- to use in expressions without NULLIF
, px_servers_executions
, elapsed_time
, buffer_gets
, rows_processed
, end_of_fetch_count
from gv$sqlstats s )
where sql_id = trim('&sql_id')
group by trunc(last_active_time), plan_hash_value,sql_id
having sum(executions_calc) > 0
order by trunc(last_active_time), plan_hash_value;
select run_date as "Run date"
, "First"
, "Last"
, sql_id
, plan_hash_value as "Plan hash"
, executions as "Times called"
, end_of_fetch_count as "Times completed"
, success_rate as "Success %"
, elapsed_time as "Total time"
, avg_time as "Avg time"
, avg_s as "Avg seconds"
, round(avg_s * (sum(avg_rows) over() / greatest(sum(avg_s) over(),1))) as "Avg s scaled to rows" -- for charting time vs rows
, avg_rows as "Avg rows"
, avg_bg as "Avg Buffer gets"
, bg_per_row as "Buffer gets/row"
, avg_time_per_row as "Time/row"
, px_servers_execs as "PX server executions"
from
( select trunc(cast(t.begin_interval_time as date)) as run_date
,s.sql_id
, plan_hash_value
, to_char(min(cast(t.begin_interval_time as date)),'HH24:MI:SS') as "First"
, to_char(max(cast(t.end_interval_time as date)),'HH24:MI:SS') as "Last"
, sum(s.executions_delta) as executions
, sum(s.end_of_fetch_count_delta) as end_of_fetch_count
, max(s.executions_total) as executions_total
, max(s.end_of_fetch_count_total) as end_of_fetch_count_total
, least(100, round(100 * max(s.end_of_fetch_count_total) / nullif(max(s.executions_total),0),1)) as success_rate
, cast(numtodsinterval(max(s.elapsed_time_total)/1e6,'SECOND') as interval day(1) to second(2)) as elapsed_time
, cast(numtodsinterval(max(s.elapsed_time_total)/1e6 / nvl(nullif(max(s.executions_total),0),1),'SECOND') as interval day(1) to second(1)) as avg_time
, round(max(s.elapsed_time_total)/1e6 / nvl(nullif(max(s.executions_total),0),1),1) as avg_s
, round(max(s.buffer_gets_total)/nullif(max(s.executions_total),0)) as avg_bg
, round(max(s.buffer_gets_total)/nullif(max(s.rows_processed_total),0)) as bg_per_row
, max(s.rows_processed_total) as rows_processed
, round(max(s.rows_processed_total) / nullif(max(s.executions_total),0)) as avg_rows
, cast(numtodsinterval(max(s.elapsed_time_total)/nullif(max(s.rows_processed_total),0)/1e6,'SECOND') as interval day(1) to second(3)) as avg_time_per_row
, max(s.elapsed_time_total)/nullif(max(s.rows_processed_total),0)/1e6 as avg_s_per_row
, max(s.px_servers_execs_total) as px_servers_execs
from dba_hist_sqlstat s
join dba_hist_snapshot t on t.snap_id = s.snap_id and t.dbid = s.dbid and t.instance_number = s.instance_number
where s.sql_id = trim('&sql_id')
group by trunc(cast(t.begin_interval_time as date)), s.plan_hash_value,s.sql_id )
order by 1, 2, plan_hash_value;
======
set linesize 500 pagesize 300
col "Avg time/1M rows" for a15
col "Avg time/row" for a15
col "Total time" for a15
col "Average seconds" for 999999999999
col "Average time" for a15
select
sql_id ,
plan_hash_value as "Plan hash"
, sum(executions_calc) as "Times called"
, sum(end_of_fetch_count) as "Times completed"
, round(100 * sum(end_of_fetch_count) / sum(executions_calc),1) as "Success %"
, cast(numtodsinterval(sum(elapsed_time)/1E6,'SECOND') as interval day(1) to second(2)) as "Total time"
-- , round(sum(elapsed_time)/1e6) as "Total seconds"
, round(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1),1) as "Average seconds"
, cast(numtodsinterval(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1),'SECOND') as interval day(1) to second(1)) as "Average time"
-- , sum(buffer_gets) as "Buffer gets"
, round(sum(buffer_gets)/sum(executions_calc)) as "Buffer gets/exec"
, round(sum(buffer_gets)/nullif(sum(rows_processed),0)) as "Buffer gets/row"
, sum(rows_processed) as "Rows"
, round(sum(rows_processed) / sum(executions_calc)) as "Rows/exec"
, cast(numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0)/1E6,'SECOND') as interval day(1) to second(3)) as "Avg time/row"
, cast
( case
when sum(elapsed_time)/nullif(sum(rows_processed),0) < 2147483647 then -- 2**31 -1, limit for 32 bit integers and a
numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0),'SECOND')
else numtodsinterval(sum(elapsed_time/3600)/nullif(sum(rows_processed),0),'HOUR')
end -- Avoid "ORA-01873: the leading precision of the interval is too small" for large values
as interval day(5) to second(0)) as "Avg time/1M rows"
, sum(px_servers_executions) as "PX server executions"
from ( select s.sql_id sql_id
, trunc(s.last_active_time) as exec_date
, plan_hash_value
--, executions
, case executions when 0 then 1 else executions end as executions_calc -- to use in expressions without NULLIF
, px_servers_executions
, elapsed_time
, buffer_gets
, rows_processed
, end_of_fetch_count
from gv$sqlstats s
union
select s.sql_id
, trunc(cast(h.begin_interval_time as date)) as exec_date
, plan_hash_value
--, executions_delta executions
, nullif(executions_delta,0) executions_calc
, px_servers_execs_delta as px_servers_executions
, elapsed_time_delta as elapsed_time
, buffer_gets_delta as buffer_gets
, rows_processed_delta as rows_processed
, end_of_fetch_count_delta as end_of_fetch_count
from dba_hist_sqlstat s
join dba_hist_snapshot h on h.snap_id = s.snap_id and h.dbid = s.dbid and h.instance_number = s.instance_number
)
where 1=1
--and sql_id = trim('&sql_id')
group by sql_id,plan_hash_value
having sum(executions_calc) > 0
order by plan_hash_value;
set linesize 80
======
set linesize 500
col sql_text for a50
define 1='3s1yukp05bzg6'
select * from (select st.inst_id ,
st.con_id,
st.executions as execs,
st.sql_id,
st.plan_hash_value as plan,
round(st.elapsed_time/decode(st.executions,0,1,st.executions)) as ela_per_exec,
round(st.cpu_time/decode(st.executions,0,1,st.executions)) as cpu_per_exec,
round(st.buffer_gets/decode(st.executions,0,1,st.executions)) as gets_per_exec,
round(st.concurrency_wait_time/decode(st.executions,0,1,st.executions)) as conc_per_exec,
round(st.cluster_wait_time/decode(st.executions,0,1,st.executions)) as clu_per_exec,
round(st.user_io_wait_time/decode(st.executions,0,1,st.executions)) as uio_per_exec,
round(st.physical_read_bytes/decode(st.executions,0,1,st.executions)/1024/1024) as read_mb_per_exec,
round(st.physical_read_requests/decode(st.executions,0,1,st.executions)) as reads_per_exec,
round(st.disk_reads/decode(st.executions,0,1,st.executions)/1024/1024) as disk_reads_per_exec,
round(st.physical_write_bytes/decode(st.executions,0,1,st.executions)/1024/1024) as writes_mb_per_exec,
round(st.physical_write_requests/decode(st.executions,0,1,st.executions)) as writes_per_exec,
round(st.direct_writes/decode(st.executions,0,1,st.executions)) as direct_writes_per_exec,
round(st.rows_processed/decode(st.executions,0,1,st.executions)) as rows_per_exec,
round(st.px_servers_executions/decode(st.executions,0,1,st.executions)) as px_per_exec,
substr(sql_text,1,50) sql_text
from gv$sqlstats st
where 1=1
-- and st.con_id!=0
-- and sql_id in ('&&1')
-- order by ela_per_exec
-- order by cpu_per_exec
-- order by gets_per_exec
-- order by conc_per_exec
-- order by clu_per_exec
-- order by read_mb_per_exec
-- order by reads_per_exec
-- order by disk_reads_per_exec
-- order by writes_mb_per_exec
-- order by writes_per_exec
-- order by direct_writes_per_exec
order by rows_per_exec
)
where 1=1
and rownum <51
/=====
SQL Monitor reports
col SQL_EXEC_ID for a20
col SQL_EXEC_START for a27
col SQL_ID for a20
SELECT * from ( select report_id,
EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_id') sql_id,
EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_exec_id') sql_exec_id,
EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_exec_start') sql_exec_start
FROM dba_hist_reports
WHERE component_name = 'sqlmonitor'
and report_id > ( select max(report_id) -10 from dba_hist_reports)
)
where 1=1
--and to_date(SQL_EXEC_START,'mm/dd/yyyy') >sysdate -1
;
REPORT_ID SQL_ID SQL_EXEC_ID SQL_EXEC_START
---------- -------------------- -------------------- ---------------------------
11202388 0nkzfyf2zgmn4 16777216 12/16/2023 11:22:22
11202123 ghzqum97067kv 16777486 12/16/2023 10:30:56
11202204 f254uv2p53y7j 16778741 12/16/2023 10:47:38
11202205 4pzfupdrwqxud 16778793 12/16/2023 10:47:38
11202486 0rkyj8cvvf3w2 16809402 12/16/2023 11:51:49
11202491 3uc5umkn5dm2w 16777470 12/16/2023 11:53:15
11202512 99k1tpada3gsk 16777216 12/16/2023 11:57:43
set long 10000000 longchunksize 10000000 pages 0 linesize 300
select dbms_auto_report.report_repository_detail(RID => 727796, TYPE => 'text') FROM dual;
=====
set linesize 400 pagesize 300
define sql_id='f705bwx3q0ydq'
col KEY1 for a15
col KEY2 for a15
col KEY3 for a15
col KEY4 for a15
col REPORT_PARAMETERS for a20
col REPORT_SUMMARY for a50 wrap
col COMPONENT_NAME for a20
col REPORT_NAME for a20
col report_id new_v report_id
select * from
(
select * from DBA_HIST_REPORTS
where 1=1
and key1 = '&&sql_id'
and COMPONENT_NAME = 'sqlmonitor'
order by generation_time desc
)
where rownum = 1;
====
set linesize 500 pagesize 300
col report_summary for a50
col strt for a22
col plan_hash for a17
col dop for a15
col username for a15
col txt for a15
col ins for a15
col dop_reqs for a15
col dop_alloc for a15
col act for a15
col mod for a15
col sql_exec_id for a17
col sql_id for a17
col sql_text for a20 wrap
SELECT /*+ NO_XML_QUERY_REWRITE */
t.report_id rid,
to_number(user_fetch_count) fetches,
X1.sql_exec_start strt,
x1.sql_id,
x1.plan_hash,
X1.dop,
to_number(x1.duration) elp,
round(x1.elapsed_time / 1000000) Etot,
round(x1.cpu_time / 1000000) cpu_time,
round(x1.user_io_wait_time / 1000000) io_time,
round(to_number(x1.read_bytes) / 1024 / 1024/1024) GBr,
round(to_number(x1.write_bytes) / 1024 / 1024/1024) GBw,
to_char(substr(x1.sql_text,1,4000)) txt,
inst_id ins,
t.session_id sid,
t.SESSION_SERIAL# ser,
x1.action act,
x1.module mod,
x1.username,
x1.sql_text,
round(application_wait_time / 1000000) app,
round(concurrency_wait_time / 1000000) con,
round(cluster_wait_time / 1000000) clustert,
round(plsql_exec_time / 1000000) plsql,
round(other_wait_time / 1000000) other,
round(sum(x1.elapsed_time) over(partition by t.snap_id) / 1000000) elp_snap,
t.snap_id snap,
x1.px_servers_requested dop_reqs,
x1.px_servers_allocated dop_alloc,
x1.sql_exec_id
,t.report_summary
--to_char(substr(b.sql_text,1,4000))
FROM dba_hist_reports t,
--dba_hist_sqltext b ,
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',
module 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"]',
user_fetch_count path
'stats/stat[@name="user_fetch_count"]',
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"]',
write_bytes path 'stats/stat[@name="write_bytes"]'
) x1,
dba_hist_snapshot C
where 1=1
--and x1.elapsed_time / 1000000 > 200
and t.COMPONENT_NAME = 'sqlmonitor'
and C.SNAP_ID = t.snap_id
and c.instance_number = inst_id
and upper( x1.action) like '%%'
--and to_char(c.BEGIN_INTERVAL_TIME, 'HH24') in (23,0,1,2)--- and 8
--and X1.dop > 0
--and t.snap_id between 42620 and 42626
--and t.key1 in (/*'8r3pnzg9ncard','6judcbbtju567',*/'3n2s2zpkqu016')
--and t.snap_id=43994
--and c.BEGIN_INTERVAL_TIME > sysdate-50
--and c.BEGIN_INTERVAL_TIME < sysdate - 1
--and c.BEGIN_INTERVAL_TIME > sysdate - 15
and c.BEGIN_INTERVAL_TIME > sysdate - interval '60' minute
order by abs( to_number(x1.duration) ) desc
;
