Search This Blog

Total Pageviews

Tuesday 9 February 2021

SQL Report ....

  

 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
   ;
   
   

Oracle DBA

anuj blog Archive