Search This Blog

Total Pageviews

Wednesday 5 May 2021

SQL History


ORACLE SQL History ....










define sql_id='XXXXXXX'

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='&&sql_id'
               union all
	select 'gv$sqlstats' frm ,  sql_fulltext sql_text from gv$sqlstats where sql_id='&&sql_id'
	union all
          select 'dba_hist', sql_text from dba_hist_sqltext where sql_id='&&sql_id' 
			   )
			 where 1=1
			 and rownum<2
			 ;



https://anuj-singh.blogspot.com/2017/09/sql-information-from-memory.html






-- with round 


define sql_id='9px0f4mv0tn0t'

VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1000 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

set lines 1000 pages 9999
COL instance_number FOR 9999 			HEA 'Inst'
COL end_time 							HEA 'End Time'
COL plan_hash_value 					HEA 'Plan|Hash Value'
COL executions_total 	for 999,999 	HEA 'Execs|Total'
COL rows_per_exec 	 	for 999999.999 	HEA 'Rows Per Exec'
COL et_secs_per_exec 	for 999999.999 	HEA 'Elap Secs|Per Exec'
COL cpu_secs_per_exec 	for 999999.999 	HEA 'CPU Secs|Per Exec'
COL io_secs_per_exec 	for 999999.999 	HEA 'IO Secs|Per Exec'
COL cl_secs_per_exec  	for 999999.999 	HEA 'Clus Secs|Per Exec'
COL ap_secs_per_exec 	for 999999.999 	HEA 'App Secs|Per Exec'
COL cc_secs_per_exec 	for 999999.999 	HEA 'Conc Secs|Per Exec'
COL pl_secs_per_exec 	for 999999.999 	HEA 'PLSQL Secs|Per Exec'
COL ja_secs_per_exec 	for 999999.999 	HEA 'Java Secs|Per Exec'
SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,s.snap_id,
TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI')   end_time,
h.sql_id,
h.plan_hash_value,
h.executions_total,
TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') 	rows_per_exec,
ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3) 						et_secs_per_exec,
ROUND(h.cpu_time_total / h.executions_total / 1e6, 3) 							cpu_secs_per_exec,
ROUND(h.iowait_total / h.executions_total / 1e6, 3) 							io_secs_per_exec,
ROUND(h.clwait_total / h.executions_total / 1e6, 3) 							cl_secs_per_exec,
ROUND(h.apwait_total / h.executions_total / 1e6, 3) 							ap_secs_per_exec,
ROUND(h.ccwait_total / h.executions_total / 1e6, 3) 							cc_secs_per_exec,
ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3) 						pl_secs_per_exec,
ROUND(h.javexec_time_total / h.executions_total / 1e6, 3) 						ja_secs_per_exec
FROM dba_hist_sqlstat h, dba_hist_snapshot s 
WHERE h.sql_id = '&sql_id'
AND h.executions_total > 0 
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid 
AND s.instance_number = h.instance_number
and s.snap_id > :BgnSnap 
UNION ALL
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,0 "snap_id",
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id, h.plan_hash_value, h.executions,
TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
ROUND(h.elapsed_time / h.executions / 1e6, 3) et_secs_per_exec,
ROUND(h.cpu_time / h.executions / 1e6, 3) cpu_secs_per_exec,
ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3) io_secs_per_exec,
ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3) cl_secs_per_exec,
ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3) ap_secs_per_exec,
ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3) cc_secs_per_exec,
ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3) pl_secs_per_exec,
ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3) ja_secs_per_exec
FROM gv$sqlarea_plan_hash h
WHERE h.sql_id = '&&sql_id'
AND h.executions > 0
order by SNAP_ID ;

*************************************************************







define sql_id='0jrngm5htnzuv'

set lines 500 pages 100
col instance_number FOR 9999    HEA 'Inst'
col end_time 			HEA 'End Time'
col plan_hash_value 	        HEA 'Plan|Hash Value'

col rows_per_exec    for a15		HEA 'Rows Per Exec'
col et_secs_per_exec  for a15	HEA 'Elap Secs|Per Exec'
col cpu_secs_per_exec  for a15	HEA 'CPU Secs|Per Exec'
col io_secs_per_exec  for a15	HEA 'IO Secs|Per Exec'
col cl_secs_per_exec  for a15	HEA 'Clus Secs|Per Exec'
col ap_secs_per_exec  for a15	HEA 'App Secs|Per Exec'
col cc_secs_per_exec  for a15	HEA 'Conc Secs|Per Exec'
col pl_secs_per_exec  for a15	HEA 'PLSQL Secs|Per Exec'
col ja_secs_per_exec  for a15	HEA 'Java Secs|Per Exec'
col executions_total  for a15  FOR 999,999 HEA 'Execs|Total'
col "End Time" for a27
col end_time for a27
select 'gv$dba_hist_sqlstat' source,h.instance_number,
to_char(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
to_char(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions_total,
to_char(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') 		rows_per_exec,
to_char(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') 	et_secs_per_exec,
to_char(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') 		cpu_secs_per_exec,
to_char(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') 		io_secs_per_exec,
to_char(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') 		cl_secs_per_exec,
to_char(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') 		ap_secs_per_exec,
to_char(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') 		cc_secs_per_exec,
to_char(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') 	pl_secs_per_exec,
to_char(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') 	ja_secs_per_exec
FROM dba_hist_sqlstat h,dba_hist_snapshot s
WHERE h.sql_id = '&sql_id'
AND h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
UNION ALL
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions,
to_char(ROUND(h.rows_processed / h.executions), '999,999,999,999') 				rows_per_exec,
to_char(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') 			et_secs_per_exec,
to_char(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') 				cpu_secs_per_exec,
to_char(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 		io_secs_per_exec,
to_char(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 		cl_secs_per_exec,
to_char(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 	ap_secs_per_exec,
to_char(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 		cc_secs_per_exec,
to_char(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') 		pl_secs_per_exec,
to_char(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') 		ja_secs_per_exec
FROM gv$sqlarea_plan_hash h
WHERE h.sql_id = '&sql_id'
--and h.inst_id=inst_id
AND h.executions > 0
order by source ;




***

 
 
define sql_id='gqkr2um43ga39'
 
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -60 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;


set lines 500 pages 100
col instance_number FOR 9999   			
col end_time 							
col plan_hash_value 	        		
col rows_per_exec    for  a20			
col et_secs_per_exec  for a20			
col cpu_secs_per_exec for a20			
col io_secs_per_exec  for a20			
col cl_secs_per_exec  for a20			
col ap_secs_per_exec  for a20			
col cc_secs_per_exec  for a20			
col pl_secs_per_exec  for a20			
col ja_secs_per_exec  for a20			
col executions_total  for a20  FOR 999,999 
col "End Time" for a27
col end_time for a27
select 'gv$dba_hist_sqlstat' source,h.instance_number,
to_char(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
to_char(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions_total,
to_char(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') 		rows_per_exec,
to_char(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') 	et_secs_per_exec,
to_char(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') 		cpu_secs_per_exec,
to_char(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') 		io_secs_per_exec,
to_char(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') 		cl_secs_per_exec,
to_char(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') 		ap_secs_per_exec,
to_char(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') 		cc_secs_per_exec,
to_char(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') 	pl_secs_per_exec,
to_char(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') 	ja_secs_per_exec
FROM dba_hist_sqlstat h,dba_hist_snapshot s
WHERE h.sql_id = '&sql_id'
AND h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
and s.snap_id > :BgnSnap 
UNION ALL
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,
TO_CHAR(FIRST_LOAD_TIME, 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(LAST_LOAD_TIME, 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions,
to_char(ROUND(h.rows_processed / h.executions), '999,999,999,999') 				rows_per_exec,
to_char(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') 			et_secs_per_exec,
to_char(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') 				cpu_secs_per_exec,
to_char(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 		io_secs_per_exec,
to_char(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 		cl_secs_per_exec,
to_char(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 	ap_secs_per_exec,
to_char(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') 		cc_secs_per_exec,
to_char(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') 		pl_secs_per_exec,
to_char(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') 		ja_secs_per_exec
FROM gv$sqlarea_plan_hash h
WHERE h.sql_id = '&sql_id'
--and h.inst_id=inst_id
AND h.executions > 0
and FIRST_LOAD_TIME > sysdate -4
order by source ;












 define sql_id='b3cyyrgtgxakt'
COL instance_number FOR 9999 HEA 'Inst'
COL end_time for a18 HEA 'End Time'

COL executions_total FOR 99,999,999 
COL rows_per_exec  FOR A12 		
COL et_secs_per_exec  FOR A16 	
COL cpu_secs_per_exec  FOR A16 	
COL io_secs_per_exec  FOR A16	
COL cl_secs_per_exec  FOR A16 	
COL ap_secs_per_exec  FOR A16	
COL cc_secs_per_exec  FOR A16	
COL pl_secs_per_exec  FOR A16	
COL ja_secs_per_exec  FOR A16	
-- col "End Time" for a27
SELECT h.instance_number,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
       h.plan_hash_value, 
       h.executions_total,
       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999999999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM dba_hist_sqlstat h, 
       dba_hist_snapshot s
 WHERE h.sql_id = '&sql_id'
   AND h.executions_total > 0 
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
 ORDER BY
       h.sql_id,
       h.instance_number,
       s.end_interval_time,
       h.plan_hash_value
/



from
https://github.com/tanelpoder/tpt-oracle/blob/master/awr/awr_sqlstats_per_exec.sql

alter session set nla_date_format-'dd-mm-yyyy hh24:mi';
define 1='dnm7fqpf08p8w'
DEF   3="TIMESTAMP'2023-07-01 01:00:00'"
DEF   4="TIMESTAMP'2023-07-15 03:15:00'"

define 2 =' '

SET TERMOUT OFF pagesize 5000 tab off verify off linesize 999 trimspool on trimout on null ""
SET TERMOUT ON


col BEGIN_INTERVAL_TIME for a27
COL executions       FOR 999999999990
COL ela_ms_per_exec  FOR 999999999990
COL rows_per_exec    FOR 999999999990.0
COL lios_per_exec    FOR 999999999990
COL blkrd_per_exec   FOR 999999999990
COL cpu_ms_per_exec  FOR 999999999990
COL iow_ms_per_exec  FOR 999999999990
COL avg_iow_ms       FOR 999999999990.0
COL clw_ms_per_exec  FOR 999999999990
COL apw_ms_per_exec  FOR 999999999990
COL ccw_ms_per_exec  FOR 999999999990

SELECT
   -- CAST(begin_interval_time AS DATE) begin_interval_time
begin_interval_time
  , sql_id
  , plan_hash_value
  , SUM(executions_delta)     executions
  , ROUND(SUM(elapsed_time_delta  ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         ela_ms_per_exec
  , ROUND(SUM(cpu_time_delta      ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         cpu_ms_per_exec
  , ROUND(SUM(rows_processed_delta) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta)),1)                            rows_per_exec
  , ROUND(SUM(buffer_gets_delta   ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta)),1)                            lios_per_exec
  , ROUND(SUM(disk_reads_delta    ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta)),1)                            blkrd_per_exec
  , ROUND(SUM(iowait_delta        ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         iow_ms_per_exec
  , ROUND(SUM(iowait_delta        ) / DECODE(SUM(physical_read_requests_delta)+SUM(physical_write_requests_delta),0,1
                                            ,SUM(physical_read_requests_delta)+SUM(physical_write_requests_delta))/1000,1)  avg_iow_ms
  , ROUND(SUM(clwait_delta        ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         clw_ms_per_exec
  , ROUND(SUM(apwait_delta        ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         apw_ms_per_exec
  , ROUND(SUM(ccwait_delta        ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         ccw_ms_per_exec
--  , ROUND(SUM(elapsed_time_delta - cpu_time_delta - iowait_delta - clwait_delta - apwait_delta - ccwait_delta) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta)) / 1000, 1) oth_ms_per_exec
FROM
    dba_hist_snapshot sn
  , dba_hist_sqlstat st
WHERE
    sn.snap_id = st.snap_id
AND sn.dbid    = st.dbid
AND sn.instance_number = st.instance_number
AND sql_id = '&1'
--AND plan_hash_value LIKE '&2'
AND begin_interval_time >= &3
AND end_interval_time   <= &4
AND (elapsed_time_delta != 0 AND cpu_time_delta != 0)
GROUP BY
 --   CAST(begin_interval_time AS DATE)
begin_interval_time
  , sql_id
  , plan_hash_value
ORDER BY
    begin_interval_time
  , sql_id
  , plan_hash_value
/




***********



 select DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) as sql_id,sql_handle,plan_name  from dba_sql_plan_baselines 
  where DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) = '&SQL_ID';
  



set linesize 32000  pagesize 1000 long 2000000000 longchunksize 1000  head off  verify off termout off



set head on  echo on  termout on  trimspool on 

column u new_value us noprint;
column n new_value ns noprint;
 
select name n from v$database;
select user u from dual;




column END_INTERVAL_TIME format a25

-- 48vf4pg4g5508
select sql_id, plan_hash_value, END_INTERVAL_TIME, executions_delta, ELAPSED_TIME_DELTA/(nonzeroexecutions*1000) "Elapsed Average ms", CPU_TIME_DELTA/(nonzeroexecutions*1000) "CPU Average ms", IOWAIT_DELTA/(nonzeroexecutions*1000) "IO Average ms", CLWAIT_DELTA/(nonzeroexecutions*1000) "Cluster Average ms", APWAIT_DELTA/(nonzeroexecutions*1000) "Application Average ms", CCWAIT_DELTA/(nonzeroexecutions*1000) "Concurrency Average ms", BUFFER_GETS_DELTA/nonzeroexecutions "Average buffer gets", DISK_READS_DELTA/nonzeroexecutions "Average disk reads", trunc(PHYSICAL_WRITE_BYTES_DELTA/(1024*1024*nonzeroexecutions)) "Average disk write megabytes", ROWS_PROCESSED_DELTA/nonzeroexecutions "Average rows processed" from (select ss.snap_id, ss.sql_id, ss.plan_hash_value, sn.END_INTERVAL_TIME, ss.executions_delta, case ss.executions_delta when 0 then 1 else ss.executions_delta end nonzeroexecutions, ELAPSED_TIME_DELTA, CPU_TIME_DELTA, IOWAIT_DELTA, CLWAIT_DELTA, APWAIT_DELTA, CCWAIT_DELTA, BUFFER_GETS_DELTA, DISK_READS_DELTA, PHYSICAL_WRITE_BYTES_DELTA, ROWS_PROCESSED_DELTA from DBA_HIST_SQLSTAT ss,DBA_HIST_SNAPSHOT sn where ss.sql_id = '48vf4pg4g5508' and ss.snap_id=sn.snap_id and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER) where ELAPSED_TIME_DELTA > 0 order by snap_id,sql_id;
===


define sql_id='48vf4pg4g5508'
set linesize 999
col avg_et_secs justify right format 9999999.99
col cost justify right format 9999999999
col timestamp justify center format a25
col parsing_schema_name justify center format a30
col inst_id format 999999999

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

select  'gv$sqlarea_plan_hash' source, INST_ID, SQL_ID, PLAN_HASH_VALUE,
                round(elapsed_time/decode(nvl(executions,0),0,1,executions)/1e6/
                decode(px_servers_executions,0,1,px_servers_executions)/decode(nvl(executions,0),0,1,executions),2)     avg_et_secs,
                px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
                optimizer_cost cost, LAST_LOAD_TIME timestamp, parsing_schema_name --FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SQL_PROFILE
from gv$sqlarea_plan_hash
where sql_id = nvl(trim('&&sql_id'),sql_id)
UNION
SELECT 'dba_hist_sql_plan' source, null INST_ID, t1.sql_id sql_id, t1.plan_hash_value plan_hash_value, t2.avg_et_secs avg_et_secs, t2.avg_px, t1.cost cost, t1.timestamp timestamp, NULL parsing_schema_name
FROM dba_hist_sql_plan t1,
        (
                SELECT sql_id, plan_hash_value, --round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6,2) avg_et_secs
                round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6/
                decode(SUM(px_servers_execs_total),0,1,SUM(px_servers_execs_total))/decode(SUM(executions_total),0,1,SUM(executions_total)),2)  avg_et_secs,
                SUM(px_servers_execs_total)/decode(SUM(executions_total),0,1,SUM(executions_total)) avg_px
                FROM dba_hist_sqlstat
                WHERE
                        executions_total > 0
                GROUP BY        sql_id, plan_hash_value
        ) t2
WHERE
        t1.sql_id = nvl(TRIM('&sql_id.'), t1.sql_id)
        AND t1.depth = 0
        AND t1.sql_id = t2.sql_id(+)
        AND t1.plan_hash_value = t2.plan_hash_value(+)
order by avg_et_secs, cost
/
undef sql_id










with cpu !!!

define sql_id='7grvag6ayaxn1'

SET lin 400 ver OFF
 
COL instance_number FOR 9999 HEA 'Inst'
COL end_time HEA 'End Time'


COL plan_hash_value HEA 'Plan|Hash Value'
COL executions_total FOR 999,999,999,999 HEA 'Execs|Total'
COL rows_per_exec   for a15	 HEA 'Rows Per Exec'
COL et_secs_per_exec   for a15	HEA 'Elap Secs|Per Exec'
COL cpu_secs_per_exec   for a15	HEA 'CPU Secs|Per Exec'
COL io_secs_per_exec   for a15	HEA 'IO Secs|Per Exec'
COL cl_secs_per_exec   for a15	HEA 'Clus Secs|Per Exec'
COL ap_secs_per_exec   for a15	HEA 'App Secs|Per Exec'
COL cc_secs_per_exec   for a15	HEA 'Conc Secs|Per Exec'
COL pl_secs_per_exec   for a15	HEA 'PLSQL Secs|Per Exec'
COL ja_secs_per_exec   for a15	HEA 'Java Secs|Per Exec'
COL cpu_per_exec   	for a15	HEA 'Cpu Secs|Per Exec'
 
SELECT h.instance_number,h.snap_id,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
	   h.con_id,
	   h.sql_id,
       h.plan_hash_value, 
       h.executions_total,
       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec,
	   TO_CHAR(ROUND(h.CPU_TIME_TOTAL / h.executions_total / 1e6, 3), '999,990.000') cpu_per_exec
  FROM dba_hist_sqlstat h, 
       dba_hist_snapshot s
 WHERE h.sql_id = '&sql_id.'
 --  AND h.executions_total > 16000000
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
 ORDER BY
       h.sql_id,
       h.instance_number,
       s.end_interval_time,
       h.plan_hash_value
/


set linesize 400 pagesize 300
col sql_text  for a80
select * from (
select count(sc.sql_id) cursor_count, a.con_id ,a.sql_id, a.sql_text from v$sqlarea a, v$sql_shared_cursor sc
where  a.sql_id = sc.sql_id
group by a.sql_id, a.con_id,a.sql_text
order by cursor_count desc)
where rownum <=10; 


Oracle version count from History 
define sql_id='xxxx'
 -- define sql_id            ='xxxxx'
set serveroutput on size 1000000  lines 200 pages 1000
set verify off
col begin_time for a16
col end_time for a10
col module for a20
col action for a20
col execs for 999,999,990 heading "Executions"
col physrd_exec for 999,999,990 heading "Disk|Reads|per Exec"
col logrds_exec for 999,999,990 heading "Buffer|Gets|per Exec"
col CPU_EXEC for 999,990.9999 heading "CPU|Time per|Exec(secs)"
col ELA_EXEC for 999,990.9999 heading "Elapsed|Time per|Exec(secs)"
col drwrites_exec for 999,999,990 heading "Direct|Writes|per Exec"
col rowsprc_exec for 999,999,990 heading "Rows|Processed|per Exec"
col fetch_EXEC for 999,990 heading "Fetches|per Exec"
col sharable_mb for 999,990.99 heading "Sharable|Mem(mb)"
col VERSION_COUNT for 999,990 heading "Version|Count"
col plan_hash for 999999999999
 
 
select to_char(b.BEGIN_INTERVAL_TIME,'DD-MON-RR HH24:MI') begin_time,
       to_char(b.END_INTERVAL_TIME,'HH24:MI') end_time,
       a.sql_id,
       a.plan_hash_value plan_hash,
       --a.invalidations_delta,
       --a.module,
           --a.action,
       a.executions_delta execs,
           (case when a.executions_delta > 0 then round((a.elapsed_time_delta/a.executions_delta)/1000000,4)
             when a.executions_delta = 0 then NULL end) ELA_EXEC,
           (case when a.executions_delta > 0 then round((a.cpu_time_delta/a.executions_delta)/1000000,4)
             when a.executions_delta = 0 then NULL end) CPU_EXEC,
       (case when a.executions_delta > 0 then round(a.buffer_gets_delta/a.executions_delta,0)
             when a.executions_delta = 0 then NULL end) logrds_exec,
       (case when a.executions_delta > 0 then round(a.disk_reads_delta/a.executions_delta,0)
             when a.executions_delta = 0 then NULL end) physrd_exec,
       (case when a.executions_delta > 0 then round(a.DIRECT_WRITES_DELTA/a.executions_delta,0)
             when a.executions_delta = 0 then NULL end) drwrites_exec,
       (case when a.executions_delta > 0 then round(a.ROWS_PROCESSED_DELTA/a.executions_delta,0)
             when a.executions_delta = 0 then NULL end) rowsprc_exec
       ,round(a.SHARABLE_MEM/1024/1024,2) sharable_mb
       ,a.VERSION_COUNT
       --a.disk_reads_delta physrds,
       --a.buffer_gets_delta logrds,
       --round(a.cpu_time_delta/1000000,0) "cpu_time(s)",
       --round(a.elapsed_time_delta/1000000,0) "ela_time(s)",
       --(case when a.executions_delta > 0 then round((a.FETCHES_DELTA/a.executions_delta)/1000000,0)
       --      when a.executions_delta = 0 then NULL end) fetch_EXEC
       --(case when a.executions_delta > 0 then round(a.disk_reads_delta/a.executions_delta,0) when a.executions_delta = 0 then NULL end)/(case when a.executions_delta > 0 then round(a.ROWS_PROCESSED_DELTA/a.executions_delta,0) when a.executions_delta = 0 then NULL end) pr_per_row
from   DBA_HIST_SQLSTAT a,
       DBA_HIST_SNAPSHOT b
where  1=1
-- a.snap_id between (SELECT MIN(SNAP_ID) FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME >= to_date('&start_in','DD-MON-RRRR HH24:MI:SS')) and (SELECT MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME < to_date('&end_in','DD-MON-RRRR HH24:MI:SS'))
and    a.snap_id = b.snap_id
and    a.instance_number = b.instance_number
and    a.sql_id = '&sql_id'
and    a.executions_delta > 0
order by b.BEGIN_INTERVAL_TIME
--,a.plan_hash_value
/



set linesize 300 pagesize 300
col is_bind_aware for a15
col is_bind_sensitive for a18
col is_shareable  for a18
select sql_id
    , child_number
    , is_bind_aware
    , is_bind_sensitive
    , is_shareable
    , to_char(exact_matching_signature) sig
    , executions
    , plan_hash_value
    from gv$sql
    where sql_id = '&sql_id'
	;
	

define sql_id='7grvag6ayaxn1'

SET lin 400 ver OFF
 
COL instance_number FOR 9999 HEA 'Inst'
COL end_time HEA 'End Time'


COL plan_hash_value HEA 'Plan|Hash Value'
COL executions_total FOR 999,999,999,999 HEA 'Execs|Total'
COL rows_per_exec   for a15	 HEA 'Rows Per Exec'
COL et_secs_per_exec   for a15	HEA 'Elap Secs|Per Exec'
COL cpu_secs_per_exec   for a15	HEA 'CPU Secs|Per Exec'
COL io_secs_per_exec   for a15	HEA 'IO Secs|Per Exec'
COL cl_secs_per_exec   for a15	HEA 'Clus Secs|Per Exec'
COL ap_secs_per_exec   for a15	HEA 'App Secs|Per Exec'
COL cc_secs_per_exec   for a15	HEA 'Conc Secs|Per Exec'
COL pl_secs_per_exec   for a15	HEA 'PLSQL Secs|Per Exec'
COL ja_secs_per_exec   for a15	HEA 'Java Secs|Per Exec'
COL cpu_per_exec   	for a15	HEA 'Cpu Secs|Per Exec'
 
SELECT h.instance_number,h.snap_id,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
	   h.con_id,
	   h.sql_id,
       h.plan_hash_value, 
       h.executions_total,
       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec,
	   TO_CHAR(ROUND(h.CPU_TIME_TOTAL / h.executions_total / 1e6, 3), '999,990.000') cpu_per_exec
  FROM dba_hist_sqlstat h, 
       dba_hist_snapshot s
 WHERE h.sql_id = '&sql_id.'
 --  AND h.executions_total > 16000000
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
 ORDER BY
       h.sql_id,
       h.instance_number,
       s.end_interval_time,
       h.plan_hash_value
/

--- ===
set linesize 400
col TIME for a30

-- def sql_id="  "
def days_history="10"
def interval_hours="1"
select hss.instance_number inst,
 to_char(trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)- (trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24,'dd.mm.yyyy hh24:mi:ss') time,
 plan_hash_value,
 hss.sql_id,
 sum(hss.executions_delta) executions,
 round(sum(hss.elapsed_time_delta)/1000000,3) elapsed_time_s,
 round(sum(hss.cpu_time_delta)/1000000,3) cpu_time_s,
 round(sum(hss.iowait_delta)/1000000,3) iowait_s,
 round(sum(hss.clwait_delta)/1000000,3) clwait_s,
 round(sum(hss.apwait_delta)/1000000,3) apwait_s,
 round(sum(hss.ccwait_delta)/1000000,3) ccwait_s,
 round(sum(hss.rows_processed_delta),3) rows_processed,
 round(sum(hss.buffer_gets_delta),3) buffer_gets,
 round(sum(hss.disk_reads_delta),3) disk_reads,
 round(sum(hss.direct_writes_delta),3) direct_writes
from dba_hist_sqlstat hss, dba_hist_snapshot hs
where hss.sql_id='&sql_id'
 and hss.snap_id=hs.snap_id
 and hs.begin_interval_time>=trunc(sysdate)-&days_history+1
group by hss.sql_id,hss.instance_number, trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24,plan_hash_value
having sum(hss.executions_delta)>0
order by hss.instance_number, trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24, 4 desc;

--- =====

from Web 
DEF days_of_history_accessed = '10'
DEF captured_at_least_x_times = '10'
DEF captured_at_least_x_days_apart = '5'
DEF med_elap_microsecs_threshold = '1e4'
DEF min_slope_threshold = '0.1'
DEF max_num_rows = '20'
 

WITH per_time AS
(
SELECT h.dbid,
h.sql_id,
SYSDATE - CAST(s.end_interval_time AS DATE) days_ago,
SUM(h.elapsed_time_total) / SUM(h.executions_total) time_per_exec
FROM dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
AND CAST(s.end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed
GROUP BY
h.dbid,
h.sql_id,
SYSDATE - CAST(s.end_interval_time AS DATE)
),
avg_time AS (
SELECT dbid,
sql_id,
MEDIAN(time_per_exec) med_time_per_exec,
STDDEV(time_per_exec) std_time_per_exec,
AVG(time_per_exec) avg_time_per_exec,
MIN(time_per_exec) min_time_per_exec,
MAX(time_per_exec) max_time_per_exec
FROM per_time
GROUP BY
dbid,
sql_id
HAVING COUNT(*) >= &&captured_at_least_x_times
AND MAX(days_ago) - MIN(days_ago) >= &&captured_at_least_x_days_apart.
AND MEDIAN(time_per_exec) > &&med_elap_microsecs_threshold
),
time_over_median AS (
SELECT h.dbid,
h.sql_id,
h.days_ago,
(h.time_per_exec / a.med_time_per_exec) time_per_exec_over_med,
a.med_time_per_exec,
a.std_time_per_exec,
a.avg_time_per_exec,
a.min_time_per_exec,
a.max_time_per_exec
FROM per_time h, avg_time a
WHERE a.sql_id = h.sql_id
),
ranked AS (
SELECT RANK () OVER (ORDER BY ABS(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) DESC) rank_num,
t.dbid,
t.sql_id,
CASE WHEN REGR_SLOPE(t.time_per_exec_over_med, t.days_ago) > 0 THEN 'IMPROVING' ELSE 'REGRESSING' END change,
ROUND(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago), 3) slope,
ROUND(AVG(t.med_time_per_exec)/1e6, 3) med_secs_per_exec,
ROUND(AVG(t.std_time_per_exec)/1e6, 3) std_secs_per_exec,
ROUND(AVG(t.avg_time_per_exec)/1e6, 3) avg_secs_per_exec,
ROUND(MIN(t.min_time_per_exec)/1e6, 3) min_secs_per_exec,
ROUND(MAX(t.max_time_per_exec)/1e6, 3) max_secs_per_exec
FROM time_over_median t
GROUP BY
t.dbid,
t.sql_id
HAVING ABS(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) > &&min_slope_threshold.
)
SELECT LPAD(ROWNUM, 2) row_n,
r.sql_id,
r.change,
TO_CHAR(r.slope, '990.000MI') slope,
TO_CHAR(r.med_secs_per_exec, '999,990.000') med_secs_per_exec,
TO_CHAR(r.std_secs_per_exec, '999,990.000') std_secs_per_exec,
TO_CHAR(r.avg_secs_per_exec, '999,990.000') avg_secs_per_exec,
TO_CHAR(r.min_secs_per_exec, '999,990.000') min_secs_per_exec,
TO_CHAR(r.max_secs_per_exec, '999,990.000') max_secs_per_exec,
(SELECT COUNT(DISTINCT p.plan_hash_value) FROM dba_hist_sql_plan p WHERE p.dbid = r.dbid AND p.sql_id = r.sql_id) plans, REPLACE((SELECT DBMS_LOB.SUBSTR(s.sql_text, 80) FROM dba_hist_sqltext s
 WHERE s.dbid = r.dbid AND s.sql_id = r.sql_id and rownum<2), CHR(10)) sql_text_80
FROM ranked r
WHERE r.rank_num <= &&max_num_rows.
ORDER BY
r.rank_num;

set linesize 300 
col LAST_SEEN 	for a25
col FIRST_SEEN 	for a25
col SQL_TEXT 	for a50 wrap
select   'CACHE' plan_source
, p.sql_id
, p.plan_hash_value
, min(to_date(p.first_load_time, 'YYYY-MM-DD/HH24:MI:SS')) first_seen
, max(to_date(p.last_active_time)) last_seen
, to_char(substr(p.sql_text, 1, 50)) sql_text 
from   gv$sqlarea p
where
  p.sql_id='&sql_id'
group by  p.sql_id, p.plan_hash_value, to_char(substr(p.sql_text, 1, 50))
union all
select   'AWR'
, h.sql_id
, h.plan_hash_value
, min(s.begin_interval_time)
, max(s.end_interval_time)
, to_char(substr(t.sql_text, 1, 50))
from 
  dba_hist_sqlstat h
, dba_hist_sql_plan p
, dba_hist_snapshot s
, dba_hist_sqltext t
where 
  h.snap_id=s.snap_id
  and h.dbid=p.dbid
  and h.sql_id=p.sql_id
  and h.sql_id='&sql_id'
  and t.sql_id=h.sql_id
group by   h.sql_id, h.plan_hash_value, to_char(substr(t.sql_text, 1, 50))
union all
select   'SQLSET'
, p.sql_id
, p.plan_hash_value
, p.plan_timestamp
, p.plan_timestamp
, to_char(p.sql_text) 
from   dba_sqlset_statements p
where   p.sql_id='&sql_id'
;
===
set linesize 300  pages 1200
col snap_id 		for 999999
col BEGIN_INTERVAL_TIME for a27
col INST 		for 9
col END_INTERVAL_TIME 	for a23
col INSTANCE_NUMBER 	for 99
col OPTIMIZER_COST 	for 9999999999
col PLSEXEC_TIME_TOTAL 	for 999
col STARTUP_TIME 	for a22
col PARSING_SCHEMA_ID 	for 9999999999999
col ELAPSED_TIME1 	for a15
select st.snap_id,
st.INSTANCE_NUMBER as INST,
st.sql_id ,
st.OPTIMIZER_COST,
sn.BEGIN_INTERVAL_TIME,
st.PLAN_HASH_VALUE, 
round( st.CPU_TIME_DELTA/1000000,3) 	as CPU,
round(st.IOWAIT_DELTA/1000000,3) 	as IO_Wait, 
round(st.ELAPSED_TIME_DELTA/1000000,3)	as elapsed_time,
round(st.BUFFER_GETS_DELTA/1000000,3) 	as buffer_gets_M,  
round(st.BUFFER_GETS_TOTAL/1000000,3) 	as BUFFER_GETS_TOTAL_M, 
round(st.ELAPSED_TIME_TOTAL/1000000,3)	as ELAPSED_TIME_TOTAL , 
round(st.FETCHES_DELTA,3) 		as Num_executions ,
case (ELAPSED_TIME_TOTAL-ELAPSED_TIME_DELTA) when 0 then 'start' end ELAPSED_TIME1 
from dba_hist_sqlstat st, dba_hist_snapshot sn
where st.snap_id=sn.snap_id 
and  st.INSTANCE_NUMBER=sn.INSTANCE_NUMBER 
and st.sql_id in  ('&sql_id') 
order by st.INSTANCE_NUMBER , st.SQL_ID, st.snap_id; =====

set linesize 999
col avg_et_secs justify right format 9999999.99
col cost justify right format 9999999999
col timestamp justify center format a25
col parsing_schema_name justify center format a30
col inst_id format 999999999
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
select 'gv$sqlarea_plan_hash' source, INST_ID, SQL_ID, PLAN_HASH_VALUE,
round(elapsed_time/decode(nvl(executions,0),0,1,executions)/1e6/decode(px_servers_executions,0,1,px_servers_executions)/decode(nvl(executions,0),0,1,executions),2) avg_et_secs,
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
optimizer_cost cost, LAST_LOAD_TIME timestamp, parsing_schema_name --FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SQL_PROFILE
from gv$sqlarea_plan_hash
where sql_id = nvl(trim('&sql_id'),sql_id)
UNION
SELECT 'dba_hist_sql_plan' source, null INST_ID, t1.sql_id sql_id, t1.plan_hash_value plan_hash_value, t2.avg_et_secs avg_et_secs, t2.avg_px, t1.cost cost, t1.timestamp timestamp, NULL parsing_schema_name
FROM dba_hist_sql_plan t1,
(
SELECT sql_id, plan_hash_value, --round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6,2) avg_et_secs
round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6/decode(SUM(px_servers_execs_total),0,1,SUM(px_servers_execs_total))/decode(SUM(executions_total),0,1,SUM(executions_total)),2) avg_et_secs,
SUM(px_servers_execs_total)/decode(SUM(executions_total),0,1,SUM(executions_total)) avg_px
FROM dba_hist_sqlstat
WHERE
executions_total > 0
GROUP BY sql_id, plan_hash_value
) t2
WHERE
t1.sql_id = nvl(TRIM('&sql_id'), t1.sql_id)
AND t1.depth = 0
AND t1.sql_id = t2.sql_id(+)
AND t1.plan_hash_value = t2.plan_hash_value(+)
order by avg_et_secs, cost
/


SOURCE                  INST_ID SQL_ID        PLAN_HASH_VALUE AVG_ET_SECS     AVG_PX        COST         TIMESTAMP              PARSING_SCHEMA_NAME
-------------------- ---------- ------------- --------------- ----------- ---------- ----------- ------------------------- ------------------------------
dba_hist_sql_plan               8fc3jxu7p0y25               0         .00          0           1 03-10-2021 15:18:50
gv$sqlarea_plan_hash          1 8fc3jxu7p0y25               0         .00          0           1 04-05-2022 02:53:02       SYS
gv$sqlarea_plan_hash          2 8fc3jxu7p0y25               0         .00          0           1 04-05-2022 03:38:06       SYS



--- ===
SET TERMOUT OFF pagesize 5000 tab off verify off linesize 999 trimspool on trimout on null ""
SET TERMOUT ON
COL exec_per_sec FOR 99999990
COL ela_ms_per_sec FOR 99999990
COL rows_per_sec FOR 99999990
COL lios_per_sec FOR 99999990
COL blkrd_per_sec FOR 99999990
COL cpu_ms_per_sec FOR 99999990
COL iow_ms_per_sec FOR 99999990
COL clw_ms_per_sec FOR 99999990
COL apw_ms_per_sec FOR 99999990
COL ccw_ms_per_sec FOR 99999990
SELECT
CAST(begin_interval_time AS DATE) begin_interval_time
, sql_id
, plan_hash_value
, ROUND(SUM(executions_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) exec_per_sec
, ROUND(SUM(elapsed_time_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ela_ms_per_sec
, ROUND(SUM(rows_processed_delta) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) rows_per_sec
, ROUND(SUM(buffer_gets_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) lios_per_sec
, ROUND(SUM(disk_reads_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) blkrd_per_sec
, ROUND(SUM(cpu_time_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) cpu_ms_per_sec
, ROUND(SUM(iowait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) iow_ms_per_sec
, ROUND(SUM(clwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) clw_ms_per_sec
, ROUND(SUM(apwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) apw_ms_per_sec
, ROUND(SUM(ccwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ccw_ms_per_sec
FROM
dba_hist_snapshot sn
, dba_hist_sqlstat st
WHERE
sn.snap_id = st.snap_id
AND sn.dbid = st.dbid
AND sn.instance_number = st.instance_number
AND sql_id = '&1'
AND plan_hash_value LIKE '&2'
AND begin_interval_time >= &3
AND end_interval_time <= &4
GROUP BY
CAST(begin_interval_time AS DATE)
, CAST(end_interval_time AS DATE)
, sql_id
, plan_hash_value
ORDER BY
begin_interval_time
, sql_id
, plan_hash_value
/

----===
set linesize 500 pagesize 300 
SELECT
    hsq.sql_id,
    hsq.plan_hash_value,
    NVL(SUM(hsq.executions_delta),0) AS total_exec,
    ROUND(SUM(hsq.elapsed_time_delta)/1000000,2) AS elapsed_time_total,
		ROUND(SUM(hsq.px_servers_execs_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),2) 				AS avg_px_servers_execs,
    ROUND(SUM(hsq.elapsed_time_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/1000000,2) 				AS avg_elapsed_time,
    ROUND(SUM(hsq.cpu_time_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/1000000,2) 					AS avg_cpu_time,
    ROUND(SUM(hsq.iowait_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/1000000,2) 						AS avg_iowait,
    ROUND(SUM(hsq.clwait_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/1000000,2) 						AS avg_cluster_wait,
    ROUND(SUM(hsq.apwait_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/1000000,2) 						AS avg_application_wait,
    ROUND(SUM(hsq.ccwait_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/1000000,2) 						AS avg_concurrency_wait,
    ROUND(SUM(hsq.rows_processed_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),2) 						AS avg_rows_processed,
    ROUND(SUM(hsq.buffer_gets_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),2) 							AS avg_buffer_gets,
    ROUND(SUM(hsq.disk_reads_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),2) 							AS avg_disk_reads,
    ROUND(SUM(hsq.direct_writes_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),2) 						AS avg_direct_writes,
    ROUND(SUM(hsq.io_interconnect_bytes_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/(1024*1024),0) 	AS avg_io_interconnect_mb,
    ROUND(SUM(hsq.physical_read_requests_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),0) 				AS avg_phys_read_requests,
    ROUND(SUM(hsq.physical_read_bytes_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/(1024*1024),0) 		AS avg_phys_read_mb,
    ROUND(SUM(hsq.physical_write_requests_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta)),0) 				AS avg_phys_write_requests,
    ROUND(SUM(hsq.physical_write_bytes_delta)/DECODE(SUM(hsq.executions_delta),0,NULL,SUM(hsq.executions_delta))/(1024*1024),0) 	AS avg_phys_write_mb
FROM dba_hist_sqlstat hsq
WHERE hsq.sql_id='&sql_id.'
GROUP BY hsq.sql_id, hsq.plan_hash_value;

from web 

set linesize 1500 pagesize 500
col sql_text for a70 wrap
col begin_interval_time    for a25                                              col end_interval_time for a25 
col sql_profile for a15
col module for a15
col action for a15
select
s.sql_id,
    round(DECODE(io_offload_elig_bytes_delta, 0, 0, 100 *(io_offload_elig_bytes_delta - io_interconnect_bytes_delta) / DECODE(io_offload_elig_bytes_delta , 0, 1, io_offload_elig_bytes_delta)), 6) "IO_SAVED_PCT",
    round(DECODE(io_offload_elig_bytes_delta, 0, 0, 100 *(io_offload_elig_bytes_delta) / DECODE(physical_read_bytes_delta  , 0, 1, physical_read_bytes_delta)), 6) "CELL_OFFLOAD_EFFICIENCY",
    begin_interval_time,
    end_interval_time,
    executions_delta,
    elapsed_time_delta / 1000000 "ELAPSED_TIME in SECONDS",
    round(elapsed_time_delta / nvl(nullif(executions_delta, 0), 1) / 1000000, 6) "ELAPSED_TIME  per ex in SEC",
    cpu_time_delta / 1000000 "CPU_TIME_delta in SECONDS",
    round(cpu_time_delta / nvl(nullif(executions_delta, 0), 1) / 1000000, 6) "CPU_TIME per ex in SEC",
    plan_hash_value   plan_hash,
    command_type,
    DECODE(io_offload_elig_bytes_delta, 0, 'No', 'Yes') offload,
    s.instance_number,
    module,
    action,
    sql_profile,
    fetches_delta,
    end_of_fetch_count_delta,
    sorts_delta,
    px_servers_execs_delta,
    loads_delta,
    invalidations_delta,
    parse_calls_delta,
    disk_reads_delta,
    buffer_gets_delta,
    rows_processed_delta,
    iowait_delta / 1000000 "IO WAIT in SECONDS",
    clwait_delta / 1000000 "CLUSTER WAIT in SECONDS",
    apwait_delta / 1000000 "APPLICATION WAIT in SECONDS",
    ccwait_delta / 1000000 "CONCURRENCY WAIT in SECONDS",
    plsexec_time_delta / 1000000 "PL/SQL WAIT in SECONDS",
    javexec_time_delta / 1000000 "Java EXEC WAIT in SECONDS",
    round(io_offload_elig_bytes_delta / 1024 / 1024, 2) "CELL_OFFLD_ELIG in MB",
    round(io_offload_elig_bytes_delta / nvl(nullif(executions_delta, 0), 1) / 1024 / 1024, 2) "CELL_OFFLD_ELIG per ex in MB",
    round(cell_uncompressed_bytes_delta / 1024 / 1024, 2) "CELL_UNCOMPRSD in MB",
    round(cell_uncompressed_bytes_delta / nvl(nullif(executions_delta, 0), 1) / 1024 / 1024, 2) "CELL_UNCOMPRSD per ex in MB",
    round(io_offload_return_bytes_delta / 1024 / 1024, 2) "CELL_OFFLD_RTN in MB",
    round(io_offload_return_bytes_delta / nvl(nullif(executions_delta, 0), 1) / 1024 / 1024, 2) "CELL_OFFLD_RTN per ex in MB",
    round(io_interconnect_bytes_delta / 1024 / 1024, 2) "IO_INTERCONNECT in MB",
    round(io_interconnect_bytes_delta / nvl(nullif(executions_delta, 0), 1) / 1024 / 1024, 2) "IO_INTERCONNECT per ex in MB",
    round(physical_read_bytes_delta / 1024 / 1024, 2) "PHYSICAL_READ in MB",
    round(physical_read_bytes_delta / nvl(nullif(executions_delta, 0), 1) / 1024 / 1024, 2) "PHYSICAL_READ per ex in MB",
    physical_read_requests_delta,
    round(physical_read_requests_delta / nvl(nullif(executions_delta, 0), 1)) "PHYSICAL_READ per ex",
    optimized_physical_reads_delta,
    round(optimized_physical_reads_delta / nvl(nullif(executions_delta, 0), 1)) "OPTIMIZED_READS per ex",
    round(physical_write_bytes_delta / 1024 / 1024, 2) "PHYSICAL_WRITE in MB",
    round(physical_write_bytes_delta / nvl(nullif(executions_delta, 0), 1) / 1024 / 1024, 2) "PHYSICAL_WRITE per ex in MB",
    physical_write_requests_delta,
    round(physical_write_requests_delta / nvl(nullif(executions_delta, 0), 1)) "OPTIMIZED_WRITES per ex",
    direct_writes_delta,
    dbms_lob.substr(sql_text, 4000, 1) sql_text
FROM  dba_hist_sqlstat s, dba_hist_sqltext t, dba_hist_snapshot u
WHERE 1=1
     -- and io_offload_elig_bytes_delta > 0
   and  s.sql_id = t.sql_id
    AND ( s.snap_id = u.snap_id    AND s.instance_number = u.instance_number )
and t.sql_id='&sql_id'
ORDER BY 2   DESC
    ;


SELECT
p.sql_id
,p.plan_hash_value
,p.child_number
,t.phv2
FROM
gv$sql_plan p
,xmltable('for $i in /other_xml/info
where $i/@type eq "plan_hash_2"
return $i'
passing xmltype(p.other_xml)
columns phv2 number path '/') t
WHERE p.sql_id = '&sql_id'
AND p.other_xml is not null;





set linesize 300 pagesize 300
col INDEX_OWNER  for a20
col COLUMN_NAME for a20 
col TABLE_NAME for a20 
col INDEX_NAME for a20
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('&sql_id', 0, 'BASIC'))
   )
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
/

INDEX_OWNER          INDEX_NAME           TABLE_NAME           COLUMN_NAME             COL_POS LAST_ANALYZED  SAMPLE_SIZE NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM       NUM_BUCKETS
-------------------- -------------------- -------------------- -------------------- ---------- -------------- ----------- ------------ ---------- ---------- --------------- -----------
SYS                  T_R_I1               T_RANGE              ID                            1                                                               NONE
SYS                  T_R_I1               T_RANGE              C1                            2                                                               NONE



==========================
/* --from 

--https://www.williamrobertson.net/documents/performance-for-sqlid.html
-- Diagnostic queries for a specified SQL ID
-- PL/SQL Developer format - open in an SQL window, select-all and execute, entering SQL ID at the prompt.
-- William Robertson, 2015
https://www.williamrobertson.net/documents/
*/





define sql_id='22356bkgsdcnh'  ---!!!!!


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 = '&sql_id'
group by sql_id,plan_hash_value
having sum(executions_calc) > 0
order by plan_hash_value;


set linesize 700 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
col AVG_TIME for a15
col AVG_TIME_PER_ROW for a15
select  
--h.snap_id,
s.sql_id ,case when s.plan_hash_value =
          ( select plan_hash_value from
                   ( select plan_hash_value, row_number() over (order by timestamp desc) as seq
                     from   gv$sql_plan p
                     where  p.sql_id = '&sql_id'
                    -- and    p.child_number = :sql_child_number
                    -- and    p.inst_id = :instance 
)
            where seq = 1 )
          then 'Y'
       end as current_plan
     , trunc(cast (h.begin_interval_time as date)) as exec_date
     , plan_hash_value
     , sum(executions_delta) executions
     , cast(numtodsinterval(sum(elapsed_time_delta)/1E6,'SECOND') as interval day(1) to second(2)) as total_time
     , cast(numtodsinterval(sum(elapsed_time_delta)/ nullif(sum(executions_delta),0)/1E6,'SECOND') as interval day(1) to second(1)) as avg_time
     , sum(buffer_gets_delta) buffer_gets
     , round(sum(buffer_gets_delta)/nullif(sum(executions_delta),0)) as buffer_gets_per_exec
     , sum(rows_processed_delta) as "ROWS"
     , round(sum(rows_processed_delta) / nullif(sum(executions_delta),0),1) as rows_per_exec
     , cast(numtodsinterval(sum(elapsed_time_delta)/nullif(sum(rows_processed_delta),0)/1E6,'SECOND') as interval day(1) to second(3)) as avg_time_per_row
     , round(sum(buffer_gets_delta)/nullif(sum(rows_processed_delta),0)) as buffer_gets_per_row
from   dba_hist_sqlstat s
       join dba_hist_snapshot h on h.snap_id = s.snap_id and h.dbid = s.dbid
where  s.sql_id = '&sql_id'
group by  
--h.snap_id,
s.sql_id ,trunc(cast (h.begin_interval_time as date)), s.plan_hash_value
order by trunc(cast (h.begin_interval_time as date)) desc, 1 nulls last, s.plan_hash_value
;

==============

set linesize 700 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
col PLSQL_EXEC_TIME for a15
col TOTAL_TIME for a15
col JAVA_EXEC_TIME for a15
col AVERAGE_TIME  for a15
select sql_id
     , plan_hash_value
     , last_active_time
     , executions
     , round(100 * parse_calls/nullif(executions,0),1)  as "Parsed%"
     , parse_calls
     , cast(numtodsinterval(elapsed_time/1E6,'SECOND')  as interval day(0) to second(0)) as total_time
     , cast(numtodsinterval(elapsed_time / nullif(executions,0) / 1E6,'SECOND') as interval day(0) to second(0)) as average_time
     , round(100 * cpu_time / elapsed_time,1) "CPU%"
     , round(100 * user_io_wait_time / elapsed_time,1) "IO%"
     , round(100 * concurrency_wait_time / elapsed_time,1) "CONCURRRENCY%"
     , round(100 * application_wait_time / elapsed_time,1) "APPLICATION%"
     , round(100 * plsql_exec_time / elapsed_time,1) "PL/SQL%"
     , buffer_gets buffer_gets_total
     , round(buffer_gets / nullif(executions,0))        as buffer_gets_per_exec
     , disk_reads
     , round(rows_processed / nullif(fetches,0),1)      as rows_per_fetch
     , round(rows_processed / nullif(executions,0),1)   as rows_per_exec
     , direct_writes
     , rows_processed
     , fetches
     , end_of_fetch_count
     , loads
     , version_count
     , invalidations
     , px_servers_executions
     , round(avg_hard_parse_time / 1E6,2)               as avg_hard_parse_secs
     , cluster_wait_time
     , cast(numtodsinterval(plsql_exec_time/1E6,'SECOND') as interval day(0) to second(0)) as plsql_exec_time
     , cast(numtodsinterval(java_exec_time/1E6,'SECOND') as interval day(0) to second(0)) as java_exec_time
     , sorts
     , sharable_mem
     , total_sharable_mem
     , last_active_child_address
     , serializable_aborts
from   gv$sqlstats s
where  s.sql_id = '&sql_id'
;



with parsing schema

set linesize 300 pagesize 200
column_object_name  for a31
column owner   for a15
column "%TOT%"   for a6
column SQL_TEXT  for a80 wrap on
col parsing_schema_name for A12
select distinct sql_id1,sub.parsing_schema_name,
-- replace(replace(replace(DBMS_LOB.SUBSTR(DHST.sql_text,4000,1),chr(10),' '),chr(9),' '),' ',' ') SQL_TEXT,
sub.EXECUTIONS,round((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)||'%' "%TOT%",
sub.DISK_READS,round((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)||'%' "%TOT%",
sub.BUFFER_GETS,round((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)||'%' "%TOT%",
sub.ELAPSED_TIME,round((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100)||'%' "%TOT%",
sub.IOWAIT,sub.ROWS_PROCESSED,
sub.SEC_PER_EXEC "SEC/EXE",
round((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)+round((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)+round((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)+round((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100) RANK ,
replace(replace(replace(DBMS_LOB.SUBSTR(DHST.sql_text,4000,1),chr(10),' '),chr(9),' '),' ',' ') SQL_TEXT
from DBA_HIST_SQLTEXT DHST,
(
select distinct
SQL_ID sql_id1,
PARSING_SCHEMA_NAME,
round(sum(EXECUTIONS_DELTA))   as EXECUTIONS,
round(sum(PARSE_CALLS_DELTA))   as PARSE_CALLS,
round(sum(DISK_READS_DELTA))   as DISK_READS,
round(sum(BUFFER_GETS_DELTA))   as BUFFER_GETS,
round(sum(ROWS_PROCESSED_DELTA))  as ROWS_PROCESSED,
round(sum(CPU_TIME_DELTA/1000000))  as CPU_TIME,
round(sum(ELAPSED_TIME_DELTA/1000000)) ELAPSED_TIME,
round(sum(IOWAIT_DELTA)/1000000)  as IOWAIT,
sum(ELAPSED_TIME_DELTA/1000000)/decode(sum(EXECUTIONS_DELTA),0,1,sum(EXECUTIONS_DELTA)) SEC_PER_EXEC
from
dba_hist_snapshot
natural join
dba_hist_sqlstat DHS
natural join
dba_hist_sql_plan DHSP
where 1=1
and SQL_ID='&sql_id'
group by SQL_ID,PARSING_SCHEMA_NAME
) sub,
(
select decode(round(sum(EXECUTIONS_DELTA)),0,1,round(sum(EXECUTIONS_DELTA))) as EXECUTIONS_TOTAL,
decode(round(sum(DISK_READS_DELTA)),0,1,round(sum(DISK_READS_DELTA))) as DISK_READS_TOTAL,
decode(round(sum(BUFFER_GETS_DELTA)),0,1,round(sum(BUFFER_GETS_DELTA))) as BUFFER_GETS_TOTAL,
decode(round(sum(ELAPSED_TIME_DELTA/1000000)),0,1,round(sum(ELAPSED_TIME_DELTA/1000000))) as ELAPSED_TIME_TOTAL
from
dba_hist_snapshot
natural join
dba_hist_sqlstat DHS
natural join
dba_hist_sql_plan DHSP
where
1=1 
) sub2
where DHST.sql_id = sub.sql_id1
order by RANK Desc
/




col NAME for a20 
col VALUE_STRING for a20 
select name, datatype_string, last_captured, value_string
from   ( select name
              , datatype_string
              , last_captured
              , value_string
              , row_number() over(partition by position order by last_captured nulls last) as seq
         from   gv$sql_bind_capture
         where  sql_id = '&sql_id'
        -- and    child_number = :sql_child_number
         --and    address = :sql_address
        -- and    hash_value = :sql_hash_value
)
where seq = 1
order by last_captured
;


--=============


select * from
(select sql_id, nonshared_reason, count(*) from gv$sql_shared_cursor
unpivot
(nonshared_value for nonshared_reason in (
UNBOUND_CURSOR as 'UNBOUND_CURSOR',
SQL_TYPE_MISMATCH as 'SQL_TYPE_MISMATCH',
OPTIMIZER_MISMATCH as 'OPTIMIZER_MISMATCH',
OUTLINE_MISMATCH as 'OUTLINE_MISMATCH',
STATS_ROW_MISMATCH as 'STATS_ROW_MISMATCH',
LITERAL_MISMATCH as 'LITERAL_MISMATCH',
FORCE_HARD_PARSE as 'FORCE_HARD_PARSE',
EXPLAIN_PLAN_CURSOR as 'EXPLAIN_PLAN_CURSOR',
BUFFERED_DML_MISMATCH as 'BUFFERED_DML_MISMATCH',
PDML_ENV_MISMATCH as 'PDML_ENV_MISMATCH',
INST_DRTLD_MISMATCH as 'INST_DRTLD_MISMATCH',
SLAVE_QC_MISMATCH as 'SLAVE_QC_MISMATCH',
TYPECHECK_MISMATCH as 'TYPECHECK_MISMATCH',
AUTH_CHECK_MISMATCH as 'AUTH_CHECK_MISMATCH',
BIND_MISMATCH as 'BIND_MISMATCH',
DESCRIBE_MISMATCH as 'DESCRIBE_MISMATCH',
LANGUAGE_MISMATCH as 'LANGUAGE_MISMATCH',
TRANSLATION_MISMATCH as 'TRANSLATION_MISMATCH',
BIND_EQUIV_FAILURE as 'BIND_EQUIV_FAILURE',
INSUFF_PRIVS as 'INSUFF_PRIVS',
INSUFF_PRIVS_REM as 'INSUFF_PRIVS_REM',
REMOTE_TRANS_MISMATCH as 'REMOTE_TRANS_MISMATCH',
LOGMINER_SESSION_MISMATCH as 'LOGMINER_SESSION_MISMATCH',
INCOMP_LTRL_MISMATCH as 'INCOMP_LTRL_MISMATCH',
OVERLAP_TIME_MISMATCH as 'OVERLAP_TIME_MISMATCH',
EDITION_MISMATCH as 'EDITION_MISMATCH',
MV_QUERY_GEN_MISMATCH as 'MV_QUERY_GEN_MISMATCH',
USER_BIND_PEEK_MISMATCH as 'USER_BIND_PEEK_MISMATCH',
TYPCHK_DEP_MISMATCH as 'TYPCHK_DEP_MISMATCH',
NO_TRIGGER_MISMATCH as 'NO_TRIGGER_MISMATCH',
FLASHBACK_CURSOR as 'FLASHBACK_CURSOR',
ANYDATA_TRANSFORMATION as 'ANYDATA_TRANSFORMATION',
PDDL_ENV_MISMATCH as 'PDDL_ENV_MISMATCH',
TOP_LEVEL_RPI_CURSOR as 'TOP_LEVEL_RPI_CURSOR',
DIFFERENT_LONG_LENGTH as 'DIFFERENT_LONG_LENGTH',
LOGICAL_STANDBY_APPLY as 'LOGICAL_STANDBY_APPLY',
DIFF_CALL_DURN as 'DIFF_CALL_DURN',
BIND_UACS_DIFF as 'BIND_UACS_DIFF',
PLSQL_CMP_SWITCHS_DIFF as 'PLSQL_CMP_SWITCHS_DIFF',
CURSOR_PARTS_MISMATCH as 'CURSOR_PARTS_MISMATCH',
STB_OBJECT_MISMATCH as 'STB_OBJECT_MISMATCH',
CROSSEDITION_TRIGGER_MISMATCH as 'CROSSEDITION_TRIGGER_MISMATCH',
PQ_SLAVE_MISMATCH as 'PQ_SLAVE_MISMATCH',
TOP_LEVEL_DDL_MISMATCH as 'TOP_LEVEL_DDL_MISMATCH',
MULTI_PX_MISMATCH as 'MULTI_PX_MISMATCH',
BIND_PEEKED_PQ_MISMATCH as 'BIND_PEEKED_PQ_MISMATCH',
MV_REWRITE_MISMATCH as 'MV_REWRITE_MISMATCH',
ROLL_INVALID_MISMATCH as 'ROLL_INVALID_MISMATCH',
OPTIMIZER_MODE_MISMATCH as 'OPTIMIZER_MODE_MISMATCH',
PX_MISMATCH as 'PX_MISMATCH',
MV_STALEOBJ_MISMATCH as 'MV_STALEOBJ_MISMATCH',
FLASHBACK_TABLE_MISMATCH as 'FLASHBACK_TABLE_MISMATCH',
LITREP_COMP_MISMATCH as 'LITREP_COMP_MISMATCH',
PLSQL_DEBUG as 'PLSQL_DEBUG',
LOAD_OPTIMIZER_STATS as 'LOAD_OPTIMIZER_STATS',
ACL_MISMATCH as 'ACL_MISMATCH',
FLASHBACK_ARCHIVE_MISMATCH as 'FLASHBACK_ARCHIVE_MISMATCH',
LOCK_USER_SCHEMA_FAILED as 'LOCK_USER_SCHEMA_FAILED',
REMOTE_MAPPING_MISMATCH as 'REMOTE_MAPPING_MISMATCH',
LOAD_RUNTIME_HEAP_FAILED as 'LOAD_RUNTIME_HEAP_FAILED',
HASH_MATCH_FAILED as 'HASH_MATCH_FAILED',
PURGED_CURSOR as 'PURGED_CURSOR',
BIND_LENGTH_UPGRADEABLE as 'BIND_LENGTH_UPGRADEABLE',
USE_FEEDBACK_STATS as 'USE_FEEDBACK_STATS'
))
where nonshared_value = 'Y'
group by sql_id, nonshared_reason
)
where 1=1
--and sql_id = :sql_id
and rownum <30
order by 3 desc 

SQL_ID        NONSHARED_REASON                COUNT(*)
------------- ----------------------------- ----------
01xv155rhts3j ROLL_INVALID_MISMATCH                 17
03gumnj26cbhx ROLL_INVALID_MISMATCH                 11
02mh01mykt89k ROLL_INVALID_MISMATCH                  9
062savj8zgzut ROLL_INVALID_MISMATCH                  7
05tfhbgcrxa1t ROLL_INVALID_MISMATCH                  5
04ws1hx445575 ROLL_INVALID_MISMATCH                  5
04g72vx2dm3hj ROLL_INVALID_MISMATCH                  5
04kug40zbu4dm OPTIMIZER_MISMATCH                     4

---



define  sql_id ='8m0g708ggsfsm'
set serveroutput on
DECLARE
  v_count number;
  v_sql varchar2(500);
  v_sql_id varchar2(30) := '&sql_id';
BEGIN
  v_sql_id := lower(v_sql_id);
  dbms_output.put_line(chr(13)||chr(10));
  dbms_output.put_line('sql_id: '||v_sql_id);
  dbms_output.put_line('------------------------');
  FOR c1 in
    (select column_name 
       from dba_tab_columns
      where table_name ='V_$SQL_SHARED_CURSOR'
        and column_name not in ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER', 'REASON', 'CON_ID')
      order by column_id)
  LOOP
    v_sql := 'select count(*) from V_$SQL_SHARED_CURSOR
              where sql_id='||''''||v_sql_id||''''||'
              and '||c1.column_name||'='||''''||'Y'||'''';
    execute immediate v_sql into v_count;
    IF v_count > 0
    THEN
      dbms_output.put_line(' - '||rpad(c1.column_name,30)||' count: '||v_count);
    END IF;
  END LOOP;
END;
/



11.2.0.3: _cursor_obsolete_threshold=100
11.2.0.4: _cursor_obsolete_threshold=1024
12.1.0.1: _cursor_obsolete_threshold=1024
12.1.0.2: _cursor_obsolete_threshold=1024 
12.2.0.1: _cursor_obsolete_threshold=8192   <<<<< _cursor_obsolete_threshold     1024  OD  
18.3.0: _cursor_obsolete_threshold=8192


_cursor_obsolete_threshold 


http://anuj-singh.blogspot.com/2021/05/sql-history.html



-- tab=Executions (historical)

set linesize 500
col "Run date" for a25
select run_date            as "Run date"
      , sql_id
     , "First"
     , "Last"
     , 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 s.sql_id,trunc(cast(t.begin_interval_time as date)) as run_date
              , 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  1=1
and sql_id = '&sql_id'
and rownum <10
         group by sql_id,trunc(cast(t.begin_interval_time as date)), s.plan_hash_value )
order by 1, 2, plan_hash_value;

   

-- tab=Executions (recent)

set linesize 1000
col Average time"    for a20                                                              
col "Average time PX"  for a20   
col "Average time" for a20

col "Total time" for a20
select 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  1=1
and sql_id = '&sql_id'
group by trunc(last_active_time), plan_hash_value
having sum(executions_calc) > 0
order by trunc(last_active_time), plan_hash_value;

      
-- tab=Recent ASH history

set linesize 500 
col TOP_LEVEL_CALL for a50
col CURRENT_PROCEDURE for a70 wrap
select ash.sql_id, ash.sql_exec_start, ash.sql_exec_id, ash.sql_plan_hash_value, ash.sql_child_number
     , cast(numtodsinterval(count(*),'SECOND') as interval day(0) to second(0)) as elapsed
     , ash.qc_session_id, ash.session_id, ash.session_serial#
     -- , sum(io.block_gets) as block_gets, sum(io.consistent_gets) as consistent_gets, sum(io.physical_reads) as physical_reads, sum(io.consistent_changes) as consistent_changes
     , tls.sql_text as top_level_call
     , rtrim(p.owner ||'.'|| p.object_name ||'.'|| p.procedure_name,'.') as current_procedure
from   gv$active_session_history ash
       left join dba_procedures p on p.object_id = ash.plsql_object_id and nvl(p.subprogram_id,.5) = nvl(ash.plsql_subprogram_id,.5)
       left join gv$sqlstats tls on tls.sql_id = ash.top_level_sql_id
where  1=1
and ash.sql_id = '&sql_id'
-- and    ash.sql_exec_id is not null
and rownum<10
group by ash.sql_id, ash.sql_exec_start,ash.sql_plan_hash_value, ash.sql_child_number, ash.qc_session_id, ash.session_id, ash.session_serial#, ash.sql_exec_id
     , tls.sql_text  , p.owner, p.object_name, p.procedure_name
order by min(ash.sample_time), ash.sql_exec_start, ash.sql_exec_id, ash.qc_session_id nulls first;




-- tab=Recent ASH history with wait objs

set linesize 500 
col USERNAME for a25
col WAIT_OBJECT for a15
col ELAPSED for a25
select ash.sql_id, ash.sql_exec_start, ash.sql_exec_id, ash.sql_plan_hash_value
     , ash.session_id, ash.session_serial#, u.username
     , case when current_obj# > 0 then
          ( select distinct o.owner || '.' || o.object_name || rtrim('.' || o.subobject_name,'.')
            from   dba_objects o where o.object_id = current_obj# )
       end as wait_object
     , round(100*(ratio_to_report(count(*)) over())) as percent
     , cast(numtodsinterval(count(*),'SECOND') as interval day(0) to second(0)) as elapsed
     , sum(ash.delta_read_io_bytes) as read_bytes
     , sum(ash.delta_write_io_requests) as write_bytes
     , ash.sql_child_number, ash.qc_session_id
from   gv$active_session_history ash
       join dba_users u on u.user_id = ash.user_id
where  1=1
and ash.sql_id = '&sql_id'
and    ash.sql_exec_id is not null
and rownum<10
group by ash.sql_id, ash.sql_exec_start,ash.sql_plan_hash_value, ash.sql_child_number, ash.qc_session_id, ash.session_id, ash.session_serial#, u.username, ash.sql_exec_id, ash.current_obj#
order by ash.sql_exec_start;



-- tab=SQL Stats
set linesize 1000
col PARSING_SCHEMA_NAME for a20 
col SQL_TEXT for a70 wrap
col SOURCE for a20
col TOTAL_TIME for a15                                                                 
col AVG_TIME for a15 
col PLSQL_EXEC_TIME  for a15                                                             
col JAVA_EXEC_TIME  for a15  
select sql_id
     , s.child_number
     , s.parsing_schema_name
     , rtrim(ltrim(o.owner || '.' || o.object_name || '.', '.') ||
       ( select regexp_substr(min(ltrim(upper(sp1.text))) keep (dense_rank first order by sp1.line desc),'[^( ]+',1,2)
         from   dba_source sp1
         where  sp1.owner = o.owner and sp1.name = o.object_name and sp1.type = o.object_type
         and    sp1.line < s.program_line#
         and    regexp_like(ltrim(upper(sp1.text)),'^(PROCEDURE|FUNCTION)\s') )
       , '.') as source
     , s.program_line# as source_line
     , plan_hash_value
     , last_active_time
     , executions
     , parse_calls
     , least(100, round(100 * parse_calls/nvl(nullif(executions,0),1),1)) as "Parsed%"
     , cast(numtodsinterval(elapsed_time/1E6,'SECOND') as interval day(0) to second(0)) as total_time
     , cast(numtodsinterval(elapsed_time / nvl(nullif(executions,0),1) / 1E6,'SECOND') as interval day(0) to second(4)) as avg_time
     , round(100 * cpu_time / nullif(elapsed_time,0),1) "CPU%"
     , round(100 * user_io_wait_time / nullif(elapsed_time,0),1) "IO%"
     , round(100 * concurrency_wait_time / nullif(elapsed_time,0),1) "CONCURRRENCY%"
     , round(100 * application_wait_time / nullif(elapsed_time,0),1) "APPLICATION%"
     , round(100 * plsql_exec_time / nullif(elapsed_time,0),1) "PL/SQL%"
     , buffer_gets buffer_gets_total
     , round(buffer_gets / nvl(nullif(executions,0),1)) as buffer_gets_per_exec
     , disk_reads
     , round(rows_processed / nullif(fetches,0),1)            as rows_per_fetch
     , round(rows_processed / nvl(nullif(executions,0),1),1)  as rows_per_exec
     , direct_writes
     , rows_processed
     , fetches
     , end_of_fetch_count
     , loads
     , s.loaded_versions version_count
     , invalidations
     , px_servers_executions
     , cluster_wait_time
     , cast(numtodsinterval(plsql_exec_time/1E6,'SECOND') as interval day(0) to second(0)) as plsql_exec_time
     , cast(numtodsinterval(java_exec_time/1E6,'SECOND') as interval day(0) to second(0)) as java_exec_time
     , sorts
     , sharable_mem
     , serializable_aborts
  , sql_text
from   gv$sql s
       left join dba_objects o on o.object_id = s.program_id
where  1=1 
--and rownum<10
and sql_id = '&sql_id'
;
                                                                                                                                                                                          
-- sql text

set linesize 300 pagesize 300
col SQL_TEXT for a100 wrap
 select *
from (
select sql_id,address
, hash_value
, count(*) cnt,
max(decode(piece,0,sql_text))||
max(decode(piece,1,sql_text))||
max(decode(piece,2,sql_text))||
max(decode(piece,3,sql_text))||
max(decode(piece,4,sql_text))||
max(decode(piece,5,sql_text))||
max(decode(piece,6,sql_text))||
 max(decode(piece,7,sql_text))||
 max(decode(piece,8,sql_text))||
 max(decode(piece,9,sql_text))||
 max(decode(piece,10,sql_text))||
 max(decode(piece,11,sql_text))||
 max(decode(piece,12,sql_text))||
 max(decode(piece,13,sql_text))||
 max(decode(piece,14,sql_text))||
 max(decode(piece,15,sql_text))||
 max(decode(piece,16,sql_text))||
 max(decode(piece,17,sql_text))||
 max(decode(piece,18,sql_text))||
max(decode(piece,19,sql_text))||
 max(decode(piece,20,sql_text))||
 max(decode(piece,21,sql_text))||
 max(decode(piece,22,sql_text))||
 max(decode(piece,23,sql_text))||
 max(decode(piece,24,sql_text) )||
  max(decode(piece,25,sql_text))||
 max(decode(piece,26,sql_text))||
 max(decode(piece,27,sql_text))||
 max(decode(piece,28,sql_text))||
 max(decode(piece,29,sql_text))||
 max(decode(piece,30,sql_text))||
 max(decode(piece,31,sql_text))||
 max(decode(piece,32,sql_text))||
 max(decode(piece,33,sql_text))||
 max(decode(piece,34,sql_text))||
 max(decode(piece,35,sql_text))||
 max(decode(piece,36,sql_text))||
 max(decode(piece,37,sql_text))||
 max(decode(piece,38,sql_text))||
 max(decode(piece,39,sql_text))||
 max(decode(piece,40,sql_text)) ||
 max(decode(piece,41,sql_text))||
 max(decode(piece,42,sql_text))||
 max(decode(piece,43,sql_text))||
 max(decode(piece,44,sql_text))||
 max(decode(piece,45,sql_text))||
 max(decode(piece,46,sql_text))||
 max(decode(piece,47,sql_text)) ||
 max(decode(piece,48,sql_text))||
 max(decode(piece,49,sql_text) )||
 max(decode(piece,50,sql_text))||
 max(decode(piece,51,sql_text))||
 max(decode(piece,52,sql_text))||
 max(decode(piece,53,sql_text))||
 max(decode(piece,54,sql_text))||
 max(decode(piece,55,sql_text))||
 max(decode(piece,56,sql_text))||
 max(decode(piece,57,sql_text))||
 max(decode(piece,58,sql_text))||
 max(decode(piece,59,sql_text) )||
 max(decode(piece,60,sql_text) )||
  max(decode(piece,61,sql_text)
  --)||
/* max(decode(piece,62,sql_text)
 */
 ) sql_text
 from gv$sqltext
 where 1=1
 and sql_id='&sql_id'
 group by sql_id,address , hash_value
 order by 3 desc
 )
 where rownum = 1






CREATE OR REPLACE FUNCTION GET_CLOB (srcclob in clob ,s in integer, e in integer)
   RETURN CLOB
   IS
       newclob clob ;
       l_buffer VARCHAR2 (32767);
       l_amount integer := 24000;
         dev integer;
         md integer;    
    BEGIN
       dev:=floor((e-s+1)/l_amount);
       md:=mod(e-s+1,l_amount);
       dbms_output.put_line('DEV:' || dev || ' MOD:' || md);
       dbms_output.put_line('e:' || e || ' s:' || s);
       FOR i in 1 .. dev LOOP
       newclob:=newclob || dbms_lob.substr(srcclob,l_amount,(i-1)*l_amount +s);
       END LOOP ;
       newclob:=newclob || dbms_lob.substr(srcclob,md,dev*l_amount+s);
       RETURN(newclob);
    END;
    /
	
	
	-- Select GET_CLOB( <CLOB_COLUMN> , 1 , dbms_lob.getlength(<CLOB_COLUMN>))  from Table_name ;
	

set long 10000 linesize 300  longchunksize 30000
Select sql_id,GET_CLOB( sql_fulltext , 1 , dbms_lob.getlength(sql_fulltext)) sql_text  from gv$sql where sql_id in ('c8bj35pys60g5','72y7hnnquc17u','g9m7yw866z4d4','6a82k6u4sbaav');
	
define sql_id='6a82k6u4sbaav'
set long 10000 linesize 300  longchunksize 30000
Select GET_CLOB( sql_fulltext , 1 , dbms_lob.getlength(sql_fulltext)) sql_text from gv$sql where sql_id in ('&sql_id'); 
	

drop FUNCTION GET_CLOB ;





from https://asktom.oracle.com/pls/apex/f?p=100:11::::RP:P11_QUESTION_ID:9539494200346328435

set pagesize 0
with data as 
 ( select sql_fulltext, dbms_lob.getlength(sql_fulltext) len from gv$sql 
where 1=1
--and sql_text like '%ayyyyyyyyyyyyyyyyyyyy%'
and sql_id='c93a0ckz9rh38'
)
select
  dbms_lob.substr(sql_fulltext,4000,1) piece1,
  case when len > 4000 then dbms_lob.substr(sql_fulltext,4000,4001) end piece2,
  case when len > 8000 then dbms_lob.substr(sql_fulltext,4000,8001) end piece3,
  case when len > 12000 then dbms_lob.substr(sql_fulltext,4000,12001) end piece4,
  case when len > 16000 then dbms_lob.substr(sql_fulltext,4000,165001) end piece5
from data
;
set pagesize 80




-- search text 

set pagesize 0
with data as 
 ( select sql_fulltext, dbms_lob.getlength(sql_fulltext) len from gv$sql 
where 1=1
--and sql_id='c93a0ckz9rh38'
and ( sql_text like '%I_OBJ#%' or sql_text like '%I_OBJ#%' )
and    sql_text not like '%select sql_id, sql_text from gv$sql%'
)
select
  dbms_lob.substr(sql_fulltext,4000,1) piece1,
  case when len > 4000 then dbms_lob.substr(sql_fulltext,4000,4001) end piece2,
  case when len > 8000 then dbms_lob.substr(sql_fulltext,4000,8001) end piece3,
  case when len > 12000 then dbms_lob.substr(sql_fulltext,4000,12001) end piece4,
  case when len > 16000 then dbms_lob.substr(sql_fulltext,4000,165001) end piece5
from data
;
set pagesize 80



====with sql _id

set pagesize 0
with data as 
 ( select sql_id,sql_fulltext, dbms_lob.getlength(sql_fulltext) len from gv$sql 
where 1=1
--and sql_id='c93a0ckz9rh38'
and ( sql_text like '%MERGE INTO %' or sql_text like '%MERGE INTO %' )
and    sql_text not like '%select sql_id, sql_text from gv$sql%'
)
select
sql_id,
  dbms_lob.substr(sql_fulltext,4000,1) piece1,
  case when len > 4000 then dbms_lob.substr(sql_fulltext,4000,4001) end piece2,
  case when len > 8000 then dbms_lob.substr(sql_fulltext,4000,8001) end piece3,
  case when len > 12000 then dbms_lob.substr(sql_fulltext,4000,12001) end piece4,
  case when len > 16000 then dbms_lob.substr(sql_fulltext,4000,165001) end piece5
from data
;
set pagesize 80






set lines 400
undef sql_id
define sql_id='9zg9qd9bm4spu'

col apwpx               format 99999.999        head 'AppWms|PerX'
col bgpx                format 999999999        head 'BGets|PerX'
col conw                format 99999.999        head 'Conwms|PerX'
col cpx                 format 99999999.999     head 'CPUms|PerX'
col drpx                format 9999999.99       head 'DReads|PerX'
col fetchx              format 99999            head 'Fetchs|PerX'
col sortx               format 999              head 'Sorts|PerX'
col elpx                format 99999999.999     head 'Elapms|PerX'
col exec                format 999999999999     head 'Execs'
col iowpx               format 99999999.999     head 'IOWms|PerX'
col latime              format a11              head 'Last Active'
col lltime              format a14              head 'Last Load'
col maxsnapid           format 999999           head 'Max|SnapId'
col m                   format a01      trunc
col minsnapid           format 999999           head 'Min|SnapId'
col module              format a14      trunc   head 'Module'
col o                   format a01              head 'O'                trunc
col opt_mode            format a08      trunc   head 'Opt|Mode'         trunc
col parse_usr           format a08              head 'ParsUser'         trunc
col phash               format 9999999999       head 'PlanHash'
col phashp              format a12              head 'PlanHash   P'
col rwpx                format 999999.99        head 'RwsP|PerX'
col s_cn                format a07              head 'S:Child'          trunc
col sql_id              format a15              head 'SQL Id'
col sqltext             format a12      trunc   head 'Sql Text'
col ue                  format 999
col cpct                format 999              head 'CPU|Pct'          trunc
col ipct                format 999              head 'IO|Pct'           trunc
col btime               format a11              head 'Begin Time'
col smem                format 99999            head 'ShrMem|KB'

-- profile/baseline associated with sql_id
col name        format a26
col category    format a04              trunc
col created     format a14
col type        format a03              trunc
col status      format a03              trunc
col sql_text    format a80              trunc
col min_ago     format 9999             trunc   head 'Mins|Ago'
select   parsing_schema_name                                            parse_usr
        ,sql_id
,con_id
        ,lpad(plan_hash_value,10,' ')||' '||
         case when sql_profile is not null then 'P'
              when sql_plan_baseline is not null then 'B'
              else ' '
         end                                                            phashp
        --,is_bind_sensitive||is_bind_aware||is_shareable       pas
        --,optimizer_mode                                       m
        --,is_obsolete                                          o
        ,users_executing                                                ue
        ,substr(object_status,1,1)||':'||lpad(child_number,5,' ')       s_cn
       ,to_char(to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS'),'MMDD HH24MI')       lltime
        ,to_char(last_active_time,'MMDD HH24:MI')                       latime
        ,(sysdate-last_active_time)*1440                                min_ago
        ,decode(executions,0,1,executions)                              exec
        ,cpu_time/decode(executions,0,1,executions)/1000                cpx
        ,elapsed_time/decode(executions,0,1,executions)/1000            elpx
        ,(cpu_time/elapsed_time)*100                                    cpct
        ,buffer_gets/decode(executions,0,1,executions)                  bgpx
        ,user_io_wait_time/decode(executions,0,1,executions)/1000       iowpx
        ,(user_io_wait_time/elapsed_time)*100                           ipct
        ,disk_reads/decode(executions,0,1,executions)                   drpx
        ,application_wait_time/decode(executions,0,1,executions)/1000   apwpx
        ,concurrency_wait_time/decode(executions,0,1,executions)/1000   conw
        ,rows_processed/decode(executions,0,1,executions)               rwpx
        ,sorts/decode(executions,0,1,executions)                        sortx
        ,fetches/decode(executions,0,1,executions)                      fetchx
        ,module                                                         module
        ,replace(sql_text,chr(30))                                      sqltext
from     gv$sql
where    sql_id                 = '&&sql_id'
and      last_active_time       > sysdate-7
order by parsing_schema_name
        ,last_active_time
;

      






-- tab=Object stats

col OBJECT_OWNER for a20 
col OBJECT_TYPE for a20 
col PARTITION_START for a20
col OBJECT_NAME for a25
col OPERATION  for a25
col PARTITION_STOP for a25
select * from (
with plan_objects as
     ( select --+ materialize
              p.object_owner
            , p.object_name
            , p.object_type
            , p.partition_start
            , p.partition_stop
            , p.cardinality
            , p.operation
            , p.options
            , count(*) as occurs_in_plan
       from   gv$sql_plan_statistics_all p
       where 1=1 
       and    p.sql_id = '&sql_id'
       and    p.plan_hash_value =
              ( select plan_hash_value from
                       ( select plan_hash_value, row_number() over (order by timestamp desc) as seq
                         from   gv$sql_plan p
                         where 1=1
and  p.sql_id = '&sql_id'
                         and    p.inst_id = 1 )
                where seq = 1 )
       and    p.object_type != 'VIEW'
       group by p.object_owner, p.object_name, p.object_type, p.partition_start, p.partition_stop, p.cardinality, p.operation, p.options )
   , object_stats as
     ( select ts.owner as object_owner
            , ts.table_name as object_name
            , ts.table_name as display_name
            , ts.num_rows
            , ts.blocks
            , ts.last_analyzed
            , ts.stale_stats
       from   dba_tab_statistics ts
       where  (ts.owner, ts.table_name)  in
              (select object_owner, object_name from plan_objects where object_type like 'TABLE%')
       and    ts.partition_name is null
       union
       select xs.owner
            , xs.index_name
            , '(' || xs.table_name || ') ' || index_name as display_name
            , xs.num_rows
            , xs.leaf_blocks as blocks
            , xs.last_analyzed
            , xs.stale_stats
       from   dba_ind_statistics xs
       where  (xs.owner, xs.index_name) in
              (select object_owner, object_name from plan_objects where object_type like 'INDEX%')
       and    xs.partition_name is null
     )
select --+ dynamic_sampling(8)
       object_owner
     , o.object_type
     , nvl(s.display_name,object_name) as object_name
     , s.stale_stats as "Stale?"
     -- , o.occurs_in_plan
     , o.operation || ' ' || o.options as operation
     , o.cardinality
     , s.num_rows as "Rows (global)"
     , s.blocks
     , s.last_analyzed
     , o.partition_start
     , o.partition_stop
from   plan_objects o
       left join object_stats s using(object_owner, object_name)
order by
      case object_owner when 'SYS' then 2 else 1 end
    , object_owner
    , ltrim(object_name,'(')
);

===


set linesize 300 
col SOURCE for a30
col BEGIN_TIME for a25
-- define sql_id='4wrkq5qmp8004'

SELECT *
    FROM (SELECT '1.v$sql'||'Instance number:'||GV$SQL.inst_id source,   SQL_ID,
                 plan_hash_value,  TO_CHAR (FIRST_LOAD_TIME) begin_time,  ' In the cursor cache' end_time,       executions "No. of exec",    (buffer_gets / executions) "LIO/exec", (cpu_time / executions / 1000000) "CPUTIM/exec",
                 (elapsed_time / executions / 1000000) "ETIME/exec ",   (disk_reads / executions) "PIO/exec",    (ROWS_PROCESSED / executions) "ROWs/exec"
            FROM Gv$SQL
           WHERE sql_id ='&sql_id'
          UNION ALL
          SELECT '2.sqltuning set' source,  sql_id,   plan_hash_value,       'JUST SQLSET NO DATE' begin_time,  'JUST SQLSET NO DATE' end_time,
                 EXECUTIONS "No. of exec",   (buffer_gets / executions) "LIO/exec",         (cpu_time / executions / 1000000) "CPUTIM/exec",
                 (elapsed_time / executions / 1000000) "ETIME/exec",         (disk_reads / executions) "PIO/exec",
                 (ROWS_PROCESSED / executions) "ROWs/exec"
            FROM dba_sqlset_statements
           WHERE SQL_ID ='&sql_id'
          UNION ALL
          SELECT '3.dba_advisor_sqlstats' source,    sql_id,
                 plan_hash_value,
                 'JUST SQLSET NO DATE' begin_time,
                 'JUST SQLSET NO DATE' end_time,
                 EXECUTIONS "No. of exec",
                 (buffer_gets / executions) "LIO/exec",
                 (cpu_time / executions / 1000000) "CPUTIM/exec",
                 (elapsed_time / executions / 1000000) "ETIME/exec",
                 (disk_reads / executions) "PIO/exec",
                 (ROWS_PROCESSED / executions) "ROWs/exec"
            FROM dba_sqlset_statements
           WHERE SQL_ID = '&sql_id'
   UNION ALL
          SELECT DISTINCT  '4.dba_hist_sqlstat' ||'Instance number:' || SQL.INSTANCE_NUMBER     source,
                 sql_id,
                 PLAN_HASH_VALUE,
                 TO_CHAR (s.BEGIN_INTERVAL_TIME ,'YYYY-MM-DD hh24:mi:ss') begin_time,
                 TO_CHAR (s. END_INTERVAL_TIME,'YYYY-MM-DD hh24:mi:ss') end_time,
                 SQL.executions_delta,
                 SQL.buffer_gets_delta  / DECODE (NVL (SQL.executions_delta, 0),  0, 1, SQL.executions_delta)             "LIO/exec",
                 (SQL.cpu_time_delta / 1000000)  / DECODE (NVL (SQL.executions_delta, 0), 0, 1, SQL.executions_delta)            "CPUTIM/exec",
                 (SQL.elapsed_time_delta / 1000000)    / DECODE (NVL (SQL.executions_delta, 0),  0, 1, SQL .executions_delta)    "ETIME/exec",
                 SQL.DISK_READS_DELTA   / DECODE (NVL (SQL.executions_delta, 0), 0, 1,   SQL.executions_delta)          "PIO/exec",
                 SQL.ROWS_PROCESSED_DELTA   / DECODE (NVL (SQL.executions_delta, 0), 0, 1, SQL.executions_delta)      "ROWs/exec"
            FROM dba_hist_sqlstat SQL, dba_hist_snapshot s
           WHERE SQL.INSTANCE_NUMBER = s.INSTANCE_NUMBER
                 --AND SQL.dbid FROM v$database)
                 AND s.snap_id = SQL.snap_id
                 AND sql_id IN ('&sql_id')
)
ORDER BY source, begin_time DESC;


SOURCE                         SQL_ID        PLAN_HASH_VALUE BEGIN_TIME                END_TIME             No. of exec   LIO/exec CPUTIM/exec ETIME/exec    PIO/exec  ROWs/exec
------------------------------ ------------- --------------- ------------------------- -------------------- ----------- ---------- ----------- ----------- ---------- ----------
1.v$sqlInstance number:1       4wrkq5qmp8004       873175999 2021-09-12/11:10:05        In the cursor cache           1         14     .002351     .001692          0         29

===


SET TERMOUT OFF pagesize 5000 tab off verify off linesize 999 trimspool on trimout on null ""
SET TERMOUT ON


COL exec_per_sec    FOR 99999990
COL ela_ms_per_sec  FOR 99999990
COL rows_per_sec    FOR 99999990
COL lios_per_sec    FOR 99999990
COL blkrd_per_sec   FOR 99999990
COL cpu_ms_per_sec  FOR 99999990
COL iow_ms_per_sec  FOR 99999990
COL clw_ms_per_sec  FOR 99999990
COL apw_ms_per_sec  FOR 99999990
COL ccw_ms_per_sec  FOR 99999990


SELECT
    CAST(begin_interval_time AS DATE) begin_interval_time
  , sql_id
  , plan_hash_value
  , ROUND(SUM(executions_delta    )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) exec_per_sec
  , ROUND(SUM(elapsed_time_delta  ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ela_ms_per_sec
  , ROUND(SUM(rows_processed_delta)        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) rows_per_sec
  , ROUND(SUM(buffer_gets_delta   )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) lios_per_sec
  , ROUND(SUM(disk_reads_delta    )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) blkrd_per_sec
  , ROUND(SUM(cpu_time_delta      ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) cpu_ms_per_sec
  , ROUND(SUM(iowait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) iow_ms_per_sec
  , ROUND(SUM(clwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) clw_ms_per_sec
  , ROUND(SUM(apwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) apw_ms_per_sec
  , ROUND(SUM(ccwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ccw_ms_per_sec
FROM
    dba_hist_snapshot sn
  , dba_hist_sqlstat st
WHERE
    sn.snap_id = st.snap_id
AND sn.dbid    = st.dbid
AND sn.instance_number = st.instance_number
AND sql_id = '&sql_id'
--AND plan_hash_value LIKE '2'
--AND begin_interval_time >= 3
--AND end_interval_time   <= 4
-- AND begin_interval_time > sysdate -1
GROUP BY
    CAST(begin_interval_time AS DATE)
  , CAST(end_interval_time AS DATE)
  , sql_id
  , plan_hash_value
ORDER BY
    begin_interval_time
  , sql_id
  , plan_hash_value
/


OR


COL exec_per_sec    FOR 99999990
COL ela_ms_per_sec  FOR 99999990
COL rows_per_sec    FOR 99999990
COL lios_per_sec    FOR 99999990
COL blkrd_per_sec   FOR 99999990
COL cpu_ms_per_sec  FOR 99999990
COL iow_ms_per_sec  FOR 99999990
COL clw_ms_per_sec  FOR 99999990
COL apw_ms_per_sec  FOR 99999990
COL ccw_ms_per_sec  FOR 99999990


SELECT
    CAST(begin_interval_time AS DATE) begin_interval_time
  , sql_id
  , plan_hash_value
  , ROUND(SUM(executions_delta    )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) exec_per_sec
  , ROUND(SUM(elapsed_time_delta  ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ela_ms_per_sec
  , ROUND(SUM(rows_processed_delta)        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) rows_per_sec
  , ROUND(SUM(buffer_gets_delta   )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) lios_per_sec
  , ROUND(SUM(disk_reads_delta    )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) blkrd_per_sec
  , ROUND(SUM(cpu_time_delta      ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) cpu_ms_per_sec
  , ROUND(SUM(iowait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) iow_ms_per_sec
  , ROUND(SUM(clwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) clw_ms_per_sec
  , ROUND(SUM(apwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) apw_ms_per_sec
  , ROUND(SUM(ccwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ccw_ms_per_sec
FROM
    dba_hist_snapshot sn
  , dba_hist_sqlstat st
WHERE
    sn.snap_id = st.snap_id
AND sn.dbid    = st.dbid
AND sn.instance_number = st.instance_number
AND sql_id = '&sql_id'
--AND plan_hash_value LIKE '2'
AND begin_interval_time >= sysdate -1
--AND end_interval_time   <= 4
GROUP BY
    CAST(begin_interval_time AS DATE)
  , CAST(end_interval_time AS DATE)
  , sql_id
  , plan_hash_value
ORDER BY
    begin_interval_time
  , sql_id
  , plan_hash_value
/

==============

https://github.com/iusoltsev/sqlplus/blob/master/ash_plsqlmon.sql

define 1='2p9fv35c7zxtg'

set feedback on heading on timi off pages 500 lines 500 echo off  VERIFY OFF

col PLAN_OPERATION for a180
col WAIT_PROFILE for a200
col SQL_TEXT for a80
col MIN_TIME for a8
col MAX_TIME for a8

PROMPT
PROMPT ***** Summary by SQL execs *****

with hash as (select /*+ INLINE*/ * from gv$active_session_history where (sql_id = '&&1' or top_level_sql_id = '&&1'))
, ash as (
  select count(distinct sh.session_id||sh.session_serial#) as SID_COUNT,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sh.SQL_ID,
         nvl(sql_plan_hash_value, 0)                         as SQL_PLAN_HASH_VALUE,
         decode(session_state,'WAITING',event,session_state) as EVENT,
         count(*)                                            as WAIT_COUNT,
         count( distinct SQL_EXEC_ID)                        as EXECS,
         MIN(SAMPLE_TIME)                                    as MIN_SAMPLE_TIME,
         max(SAMPLE_TIME)                                    as MAX_SAMPLE_TIME
    from hash sh
   group by sh.sql_id, nvl(sql_plan_hash_value, 0), decode(session_state,'WAITING',event,session_state),PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID)
select  sql_id,
        sql_plan_hash_value,
        sum(WAIT_COUNT)                                         as ASH_ROWS,
        max(EXECS)                                              as EXECS,
        to_char(min(min_sample_time),'hh24:mi:ss')              as MIN_TIME,
        to_char(max(max_sample_time),'hh24:mi:ss')              as MAX_TIME,
        trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9))) as sql_text,
        substr(rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ').extract('//text()') order by WAIT_COUNT desc),'; '),1,200) as WAIT_PROFILE
from ash left join dba_hist_sqltext using (sql_id)
group by sql_id,
         sql_plan_hash_value,
         trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9)))
order by sum(WAIT_COUNT) desc
/


SQL_ID        SQL_PLAN_HASH_VALUE   ASH_ROWS      EXECS MIN_TIME MAX_TIME SQL_TEXT                                                                         WAIT_PROFILE
------------- ------------------- ---------- ---------- -------- -------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2p9fv35c7zxtg          2367040129         11          5 18:19:49 07:05:57 select /* KSXM:LOAD_DML_INF *//*+ leading(o) index(m) use_nl(m) */            nv ON CPU(6); ON CPU(5)

1 row selected.







set linesize 300
col PLAN_OPERATION for a50
col OBJECT_OWNER for a20 
col WAIT_PROFILE for a20
col OBJECT_NAME for a20
col QBLOCK_NAME for a20
PROMPT
PROMPT
PROMPT ***** SQL Plan/PLSQL execs details *****

with 
hash as (select /*+ INLINE*/ * from gv$active_session_history where (sql_id = '&&1' or top_level_sql_id = '&&1')),
ash as
 (select count(distinct sh.session_id || sh.session_serial#) as SID_COUNT,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sh.SQL_ID,
         nvl(sql_plan_hash_value, 0) as SQL_PLAN_HASH_VALUE,
         nvl(sql_plan_line_id, 0) as SQL_PLAN_LINE_ID,
         decode(session_state, 'WAITING', event, session_state) as EVENT,
         count(*) as WAIT_COUNT,
         count(distinct SQL_EXEC_ID) as EXECS,
         min(sample_time) as MIN_SAMPLE_TIME,
         max(sample_time) as MAX_SAMPLE_TIME
    from hash sh
   group by sh.sql_id,
            nvl(sql_plan_hash_value, 0),
            nvl(sql_plan_line_id, 0),
            decode(session_state, 'WAITING', event, session_state),
            PLSQL_ENTRY_OBJECT_ID,
            PLSQL_ENTRY_SUBPROGRAM_ID),
ash_stat as
 (                                     -- all SQL exec stats
  select sql_id,
         sql_plan_hash_value,
         sql_plan_line_id,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sum(WAIT_COUNT) as ASH_ROWS,
         substr(rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ').extract('//text()') order by WAIT_COUNT desc),'; '),1,200) as WAIT_PROFILE,
         max(EXECS) as EXECS,
         max(MAX_SAMPLE_TIME) as MAX_SAMPLE_TIME
    from ash
   group by sql_id,
             sql_plan_hash_value,
             sql_plan_line_id,
             PLSQL_ENTRY_OBJECT_ID,
             PLSQL_ENTRY_SUBPROGRAM_ID),
pt as                                  -- Plan Tables for all excuted SQLs (direct+recursive)
 (select sql_id,
          plan_hash_value,
          id,
          operation,
          options,
          object_owner,
          object_name,
          qblock_name,
          nvl(parent_id, -1) as parent_id
    from dba_hist_sql_plan
   where (sql_id, plan_hash_value) in
         (select sql_id, sql_plan_hash_value from ash)
  union all                            -- for plans not in dba_hist_sql_plan yet
  select distinct sql_id,
                  plan_hash_value,
                  id,
                  operation,
                  options,
                  object_owner,
                  object_name,
                  qblock_name,
                  nvl(parent_id, -1) as parent_id
    from gv$sql_plan
   where (sql_id, plan_hash_value) in (select sql_id, sql_plan_hash_value from ash)
     and not exists
        (select 1 from dba_hist_sql_plan where (sql_id, plan_hash_value) in (select sql_id, sql_plan_hash_value from ash)))
SELECT                                 -- standard recursive SQLs 
       decode(pt.id, 0, 'SQL Query', null)        as SQL_PLSQL,
       decode(pt.id, 0, pt.sql_id, null)          as SQL_ID,
       decode(pt.id, 0, pt.plan_hash_value, null) as PLAN_HASH_VALUE,
       pt.id,
       lpad(' ', 2 * level) || pt.operation || ' ' || pt.options as PLAN_OPERATION,
       pt.object_owner,
       pt.object_name,
       pt.qblock_name,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  FROM pt
  left join ash_stat
    on pt.id = NVL(ash_stat.sql_plan_line_id, 0)
   and pt.sql_id = ash_stat.sql_id
   and pt.plan_hash_value = ash_stat.sql_plan_hash_value
 where pt.sql_id in (select sql_id from ash_stat)
CONNECT BY PRIOR pt.id = pt.parent_id
       and PRIOR pt.sql_id = pt.sql_id
       and PRIOR pt.plan_hash_value = pt.plan_hash_value
 START WITH pt.id = 0
UNION ALL
select 'PL/SQL' as SQL_PLSQL,          -- non-identified by SQL or PLSQL exec stats
       sql_id,
       ash_stat.sql_plan_hash_value as plan_hash_value,
       ash_stat.sql_plan_line_id,
       nvl2(p.object_name, p.owner||'.'||p.object_name||'.'||p.procedure_name||'"', trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9)))) as PLAN_OPERATION,
       null,
       null,
       null,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat left join dba_hist_sqltext using (sql_id)
                left join dba_procedures p on ash_stat.PLSQL_ENTRY_OBJECT_ID = p.object_id and ash_stat.PLSQL_ENTRY_SUBPROGRAM_ID = p.subprogram_id
 where sql_id is null
    or (sql_plan_hash_value = 0 and sql_id not in (select sql_id from pt))
UNION ALL
select 'SQL w/o plan' as SQL_PLSQL,    -- SQL with non-identified plan stats
       sql_id,
       ash_stat.sql_plan_hash_value as plan_hash_value,
       ash_stat.sql_plan_line_id,
       trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9))) as PLAN_OPERATION,
       null,
       null,
       null,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat left join dba_hist_sqltext using (sql_id)
 where sql_id not in (select sql_id from pt)
   and sql_id is not null
   and sql_plan_hash_value != 0
/
set VERIFY ON timi on

SQL_PLSQL    SQL_ID        PLAN_HASH_VALUE         ID PLAN_OPERATION                                     OBJECT_OWNER         OBJECT_NAME          QBLOCK_NAME               EXECS   ASH_ROWS WAIT_PROFILE
------------ ------------- --------------- ---------- -------------------------------------------------- -------------------- -------------------- -------------------- ---------- ---------- --------------------
SQL Query    2p9fv35c7zxtg      2367040129          0   SELECT STATEMENT                                                                                                         2          4 ON CPU(4)
                                                    1     COUNT STOPKEY                                                                            SEL$1
                                                    2       NESTED LOOPS OUTER
                                                    3         VIEW                                                                                 SET$1                         1          1 ON CPU(1)
                                                    4           UNION-ALL                                                                          SET$1                         1          1 ON CPU(1)
                                                    5             TABLE ACCESS CLUSTER                   SYS                  TAB$                 SEL$2
                                                    6               INDEX UNIQUE SCAN                    SYS                  I_OBJ#               SEL$2
                                                    7             TABLE ACCESS BY INDEX ROWID            SYS                  TABPART$             SEL$3                         1          1 ON CPU(1)
                                                    8               INDEX UNIQUE SCAN                    SYS                  I_TABPART_OBJ$       SEL$3
                                                    9             TABLE ACCESS BY INDEX ROWID            SYS                  TABCOMPART$          SEL$4
                                                   10               INDEX UNIQUE SCAN                    SYS                  I_TABCOMPART$        SEL$4
                                                   11             TABLE ACCESS BY INDEX ROWID            SYS                  TABSUBPART$          SEL$5
                                                   12               INDEX UNIQUE SCAN                    SYS                  I_TABSUBPART$_OBJ$   SEL$5
                                                   13         TABLE ACCESS BY INDEX ROWID                SYS                  MON_MODS_ALL$        SEL$1
                                                   14           INDEX UNIQUE SCAN                        SYS                  I_MON_MODS_ALL$_OBJ  SEL$1
SQL Query    2p9fv35c7zxtg      2367040129          0   SELECT STATEMENT                                                                                                         4          4 ON CPU(4)
                                                    1     COUNT STOPKEY                                                                            SEL$1
                                                    2       NESTED LOOPS OUTER
                                                    3         VIEW                                                                                 SET$1                         1          1 ON CPU(1)
                                                    4           UNION-ALL                                                                          SET$1                         1          1 ON CPU(1)
                                                    5             TABLE ACCESS CLUSTER                   SYS                  TAB$                 SEL$2
                                                    6               INDEX UNIQUE SCAN                    SYS                  I_OBJ#               SEL$2
                                                    7             TABLE ACCESS BY INDEX ROWID            SYS                  TABPART$             SEL$3                         1          1 ON CPU(1)
                                                    8               INDEX UNIQUE SCAN                    SYS                  I_TABPART_OBJ$       SEL$3
                                                    9             TABLE ACCESS BY INDEX ROWID            SYS                  TABCOMPART$          SEL$4
                                                   10               INDEX UNIQUE SCAN                    SYS                  I_TABCOMPART$        SEL$4
                                                   11             TABLE ACCESS BY INDEX ROWID            SYS                  TABSUBPART$          SEL$5
                                                   12               INDEX UNIQUE SCAN                    SYS                  I_TABSUBPART$_OBJ$   SEL$5
                                                   13         TABLE ACCESS BY INDEX ROWID                SYS                  MON_MODS_ALL$        SEL$1
                                                   14           INDEX UNIQUE SCAN                        SYS                  I_MON_MODS_ALL$_OBJ  SEL$1

30 rows selected.

====


set linesize 300 pagesize 300 
VARIABLE snap_start NUMBER
VARIABLE snap_end NUMBER
VARIABLE dbid NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :snap_start from dba_hist_snapshot ;
exec select max(snap_id) into :snap_end from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;

define 1 ='8cnh50qfgwg73'
define 2=40199
define 3=''
set feedback on heading on timi off pages 500 lines 500 echo off  VERIFY OFF

col PLAN_OPERATION for a180
col WAIT_PROFILE for a200
col SQL_TEXT for a80
col MIN_TIME for a8
col MAX_TIME for a8

PROMPT
PROMPT ***** Summary by SQL execs *****

with hash as (select /*+ INLINE*/ * from dba_hist_active_sess_history
               where 1=1
--  and (sql_id = '&&1' or top_level_sql_id = '&&1') 
   and (:snap_start is null OR snap_id between :snap_start and nvl(:snap_end, :snap_start))
   --and ('&&2' is null OR snap_id between '&&2' and nvl('&&3', '&&2'))
-- and  sql_id='&&1'
  )
, ash as (
  select count(distinct sh.session_id||sh.session_serial#) as SID_COUNT,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sh.SQL_ID,
         nvl(sql_plan_hash_value, 0)                         as SQL_PLAN_HASH_VALUE,
         decode(session_state,'WAITING',event,session_state) as EVENT,
         count(*)                                            as WAIT_COUNT,
         count( distinct SQL_EXEC_ID)                        as EXECS,
         MIN(SAMPLE_TIME)                                    as MIN_SAMPLE_TIME,
         max(SAMPLE_TIME)                                    as MAX_SAMPLE_TIME
    from hash sh
   group by sh.sql_id, nvl(sql_plan_hash_value, 0), decode(session_state,'WAITING',event,session_state),PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID)
select  sql_id,
        sql_plan_hash_value,
        sum(WAIT_COUNT)                                         as ASH_ROWS,
        max(EXECS)                                              as EXECS,
        to_char(min(min_sample_time),'hh24:mi:ss')              as MIN_TIME,
        to_char(max(max_sample_time),'hh24:mi:ss')              as MAX_TIME,
        trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9))) as sql_text,
        substr(rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ').extract('//text()') order by WAIT_COUNT desc),'; '),1,200) as WAIT_PROFILE
from ash left join dba_hist_sqltext using (sql_id)
group by sql_id,
         sql_plan_hash_value,
         trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9)))
order by sum(WAIT_COUNT) desc
/


SQL_ID        SQL_PLAN_HASH_VALUE   ASH_ROWS      EXECS MIN_TIME MAX_TIME SQL_TEXT                                                                         WAIT_PROFILE
------------- ------------------- ---------- ---------- -------- -------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8cnh50qfgwg73          3673574621        681          7 21:00:51 21:59:54 SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME = :B1     gc buffer busy acquire(110); db file sequential read(110); gc buffer busy acquire(110); db file sequential read(108); gc cr disk read(44); read by other session(44); gc cr disk read(43); ON CPU(39); O

1 row selected.


set linesize 300
col PLAN_OPERATION for a50
col OBJECT_OWNER for a20 
col WAIT_PROFILE for a20
col OBJECT_NAME for a20
col QBLOCK_NAME for a20

define 1 ='8cnh50qfgwg73'
with 
hash as (select /*+ INLINE*/ * from dba_hist_active_sess_history
              where 1=1
and (sql_id = '&&1' or top_level_sql_id = '&&1') and ('&&2' is null OR snap_id between '&&2' and nvl('&&3', '&&2'))
-- and (:snap_start is null OR snap_id between :snap_start and nvl(:snap_end, :snap_start))
  ),
ash as
 (select count(distinct sh.session_id || sh.session_serial#) as SID_COUNT,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sh.SQL_ID,
         nvl(sql_plan_hash_value, 0) as SQL_PLAN_HASH_VALUE,
         nvl(sql_plan_line_id, 0) as SQL_PLAN_LINE_ID,
         decode(session_state, 'WAITING', event, session_state) as EVENT,
         count(*) as WAIT_COUNT,
         count(distinct SQL_EXEC_ID) as EXECS,
         min(sample_time) as MIN_SAMPLE_TIME,
         max(sample_time) as MAX_SAMPLE_TIME
    from hash sh
   group by sh.sql_id,
            nvl(sql_plan_hash_value, 0),
            nvl(sql_plan_line_id, 0),
            decode(session_state, 'WAITING', event, session_state),
            PLSQL_ENTRY_OBJECT_ID,
            PLSQL_ENTRY_SUBPROGRAM_ID),
ash_stat as
 (                                     -- all SQL exec stats
  select sql_id,
         sql_plan_hash_value,
         sql_plan_line_id,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sum(WAIT_COUNT) as ASH_ROWS,
         substr(rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ').extract('//text()') order by WAIT_COUNT desc),'; '),1,200) as WAIT_PROFILE,
         max(EXECS) as EXECS,
         max(MAX_SAMPLE_TIME) as MAX_SAMPLE_TIME
    from ash
   group by sql_id,
             sql_plan_hash_value,
             sql_plan_line_id,
             PLSQL_ENTRY_OBJECT_ID,
             PLSQL_ENTRY_SUBPROGRAM_ID),
pt as                                  -- Plan Tables for all excuted SQLs (direct+recursive)
 (select sql_id,
          plan_hash_value,
          id,
          operation,
          options,
          object_owner,
          object_name,
          qblock_name,
          nvl(parent_id, -1) as parent_id
    from dba_hist_sql_plan
   where (sql_id, plan_hash_value) in
         (select sql_id, sql_plan_hash_value from ash)
  union all                            -- for plans not in dba_hist_sql_plan yet
  select distinct sql_id,
                  plan_hash_value,
                  id,
                  operation,
                  options,
                  object_owner,
                  object_name,
                  qblock_name,
                  nvl(parent_id, -1) as parent_id
    from gv$sql_plan
   where (sql_id, plan_hash_value) in (select sql_id, sql_plan_hash_value from ash)
     and not exists
        (select 1 from dba_hist_sql_plan where (sql_id, plan_hash_value) in (select sql_id, sql_plan_hash_value from ash)))
SELECT                                 -- standard recursive SQLs 
       decode(pt.id, 0, 'SQL Query', null)        as SQL_PLSQL,
       decode(pt.id, 0, pt.sql_id, null)          as SQL_ID,
       decode(pt.id, 0, pt.plan_hash_value, null) as PLAN_HASH_VALUE,
       pt.id,
       lpad(' ', 2 * level) || pt.operation || ' ' || pt.options as PLAN_OPERATION,
       pt.object_owner,
       pt.object_name,
       pt.qblock_name,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  FROM pt
  left join ash_stat
    on pt.id = NVL(ash_stat.sql_plan_line_id, 0)
   and pt.sql_id = ash_stat.sql_id
   and pt.plan_hash_value = ash_stat.sql_plan_hash_value
 where pt.sql_id in (select sql_id from ash_stat)
CONNECT BY PRIOR pt.id = pt.parent_id
       and PRIOR pt.sql_id = pt.sql_id
       and PRIOR pt.plan_hash_value = pt.plan_hash_value
 START WITH pt.id = 0
UNION ALL
select 'PL/SQL' as SQL_PLSQL,          -- non-identified by SQL or PLSQL exec stats
       sql_id,
       ash_stat.sql_plan_hash_value as plan_hash_value,
       ash_stat.sql_plan_line_id,
       nvl2(p.object_name, p.owner||'.'||p.object_name||'.'||p.procedure_name||'"', trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9)))) as PLAN_OPERATION,
       null,
       null,
       null,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat left join dba_hist_sqltext using (sql_id)
                left join dba_procedures p on ash_stat.PLSQL_ENTRY_OBJECT_ID = p.object_id and ash_stat.PLSQL_ENTRY_SUBPROGRAM_ID = p.subprogram_id
 where sql_id is null
    or (sql_plan_hash_value = 0 and sql_id not in (select sql_id from pt))
UNION ALL
select 'SQL w/o plan' as SQL_PLSQL,    -- SQL with non-identified plan stats
       sql_id,
       ash_stat.sql_plan_hash_value as plan_hash_value,
       ash_stat.sql_plan_line_id,
       trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9))) as PLAN_OPERATION,
       null,
       null,
       null,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat left join dba_hist_sqltext using (sql_id)
 where sql_id not in (select sql_id from pt)
   and sql_id is not null
   and sql_plan_hash_value != 0
/
set VERIFY ON timi on



SQL_PLSQL    SQL_ID        PLAN_HASH_VALUE         ID PLAN_OPERATION                                     OBJECT_OWNER         OBJECT_NAME          QBLOCK_NAME               EXECS   ASH_ROWS WAIT_PROFILE
------------ ------------- --------------- ---------- -------------------------------------------------- -------------------- -------------------- -------------------- ---------- ---------- --------------------
SQL Query    8cnh50qfgwg73      3673574621          0   SELECT STATEMENT
                                                    1     SORT AGGREGATE                                                                           SEL$1
                                                    2       VIEW                                         SYS                  DBA_FREE_SPACE       SET$1
                                                    3         UNION-ALL                                                                            SET$1
                                                    4           NESTED LOOPS                                                                       SEL$2
                                                    5             NESTED LOOPS
                                                    6               TABLE ACCESS BY INDEX ROWID          SYS                  TS$                  SEL$2
                                                    7                 INDEX UNIQUE SCAN                  SYS                  I_TS1                SEL$2
                                                    8               TABLE ACCESS CLUSTER                 SYS                  FET$                 SEL$2
                                                    9                 INDEX UNIQUE SCAN                  SYS                  I_TS#                SEL$2
                                                   10             INDEX UNIQUE SCAN                      SYS                  I_FILE2              SEL$2
                                                   11           NESTED LOOPS                                                                       SEL$3
                                                   12             NESTED LOOPS
                                                   13               TABLE ACCESS BY INDEX ROWID          SYS                  TS$                  SEL$3
                                                   14                 INDEX UNIQUE SCAN                  SYS                  I_TS1                SEL$3
                                                   15               FIXED TABLE FIXED INDEX              SYS                  X$KTFBFE (ind:1)     SEL$3
                                                   16             INDEX UNIQUE SCAN                      SYS                  I_FILE2              SEL$3
                                                   17           NESTED LOOPS                                                                       SEL$4
                                                   18             HASH JOIN
                                                   19               NESTED LOOPS
                                                   20                 STATISTICS COLLECTOR
                                                   21                   NESTED LOOPS
        



====

define 1='8cnh50qfgwg73'

col PLAN_OPERATION for a40 
col QBLOCK_NAME for a20 
col OBJECT_NAME for a20
col OBJECT_OWNER for a20
PROMPT
PROMPT
PROMPT ***** SQL Plan/PLSQL execs details *****

with 
hash as (select /*+ INLINE*/ * from dba_hist_active_sess_history
              where 1=1
  and sql_id = '&&1'
-- and (sql_id = '&&1' or top_level_sql_id = '&&1') and ('&&2' is null OR snap_id between '&&2' and nvl('&&3', '&&2'))
  ),
ash as
 (select count(distinct sh.session_id || sh.session_serial#) as SID_COUNT,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sh.SQL_ID,
         nvl(sql_plan_hash_value, 0) as SQL_PLAN_HASH_VALUE,
         nvl(sql_plan_line_id, 0) as SQL_PLAN_LINE_ID,
         decode(session_state, 'WAITING', event, session_state) as EVENT,
         count(*) as WAIT_COUNT,
         count(distinct SQL_EXEC_ID) as EXECS,
         min(sample_time) as MIN_SAMPLE_TIME,
         max(sample_time) as MAX_SAMPLE_TIME
    from hash sh
   group by sh.sql_id,
            nvl(sql_plan_hash_value, 0),
            nvl(sql_plan_line_id, 0),
            decode(session_state, 'WAITING', event, session_state),
            PLSQL_ENTRY_OBJECT_ID,
            PLSQL_ENTRY_SUBPROGRAM_ID),
ash_stat as
 (                                     -- all SQL exec stats
  select sql_id,
         sql_plan_hash_value,
         sql_plan_line_id,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sum(WAIT_COUNT) as ASH_ROWS,
         substr(rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ').extract('//text()') order by WAIT_COUNT desc),'; '),1,200) as WAIT_PROFILE,
         max(EXECS) as EXECS,
         max(MAX_SAMPLE_TIME) as MAX_SAMPLE_TIME
    from ash
   group by sql_id,
             sql_plan_hash_value,
             sql_plan_line_id,
             PLSQL_ENTRY_OBJECT_ID,
             PLSQL_ENTRY_SUBPROGRAM_ID),
pt as                                  -- Plan Tables for all excuted SQLs (direct+recursive)
 (select sql_id,
          plan_hash_value,
          id,
          operation,
          options,
          object_owner,
          object_name,
          qblock_name,
          nvl(parent_id, -1) as parent_id
    from dba_hist_sql_plan
   where (sql_id, plan_hash_value) in
         (select sql_id, sql_plan_hash_value from ash)
  union all                            -- for plans not in dba_hist_sql_plan yet
  select distinct sql_id,
                  plan_hash_value,
                  id,
                  operation,
                  options,
                  object_owner,
                  object_name,
                  qblock_name,
                  nvl(parent_id, -1) as parent_id
    from gv$sql_plan
   where (sql_id, plan_hash_value) in (select sql_id, sql_plan_hash_value from ash)
     and not exists
        (select 1 from dba_hist_sql_plan where (sql_id, plan_hash_value) in (select sql_id, sql_plan_hash_value from ash)))
SELECT                                 -- standard recursive SQLs 
       decode(pt.id, 0, 'SQL Query', null)        as SQL_PLSQL,
       decode(pt.id, 0, pt.sql_id, null)          as SQL_ID,
       decode(pt.id, 0, pt.plan_hash_value, null) as PLAN_HASH_VALUE,
       pt.id,
       lpad(' ', 2 * level) || pt.operation || ' ' || pt.options as PLAN_OPERATION,
       pt.object_owner,
       pt.object_name,
       pt.qblock_name,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  FROM pt
  left join ash_stat
    on pt.id = NVL(ash_stat.sql_plan_line_id, 0)
   and pt.sql_id = ash_stat.sql_id
   and pt.plan_hash_value = ash_stat.sql_plan_hash_value
 where pt.sql_id in (select sql_id from ash_stat)
CONNECT BY PRIOR pt.id = pt.parent_id
       and PRIOR pt.sql_id = pt.sql_id
       and PRIOR pt.plan_hash_value = pt.plan_hash_value
 START WITH pt.id = 0
UNION ALL
select 'PL/SQL' as SQL_PLSQL,          -- non-identified by SQL or PLSQL exec stats
       sql_id,
       ash_stat.sql_plan_hash_value as plan_hash_value,
       ash_stat.sql_plan_line_id,
       nvl2(p.object_name, p.owner||'.'||p.object_name||'.'||p.procedure_name||'"', trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9)))) as PLAN_OPERATION,
       null,
       null,
       null,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat left join dba_hist_sqltext using (sql_id)
                left join dba_procedures p on ash_stat.PLSQL_ENTRY_OBJECT_ID = p.object_id and ash_stat.PLSQL_ENTRY_SUBPROGRAM_ID = p.subprogram_id
 where sql_id is null
    or (sql_plan_hash_value = 0 and sql_id not in (select sql_id from pt))
UNION ALL
select 'SQL w/o plan' as SQL_PLSQL,    -- SQL with non-identified plan stats
       sql_id,
       ash_stat.sql_plan_hash_value as plan_hash_value,
       ash_stat.sql_plan_line_id,
       trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9))) as PLAN_OPERATION,
       null,
       null,
       null,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat left join dba_hist_sqltext using (sql_id)
 where sql_id not in (select sql_id from pt)
   and sql_id is not null
   and sql_plan_hash_value != 0
/
set VERIFY ON timi on


=====



define 1='22rh3t38yfuxg'

set feedback on heading on timi off pages 500 lines 500 echo off  VERIFY OFF

col PLAN_OPERATION for a180
col WAIT_PROFILE for a30
col SQL_TEXT for a80
col MIN_TIME for a8
col MAX_TIME for a8

PROMPT
PROMPT ***** Summary by SQL execs *****

with hash as (select /*+ INLINE*/ * from dba_hist_active_sess_history
              where 1=1
  and sql_id = '&1'
-- and (sql_id = '&&1' or top_level_sql_id = '&&1') 
  -- and ('&&2' is null OR snap_id between '&&2' and nvl('&&3', '&&2'))
  )
, ash as (
  select count(distinct sh.session_id||sh.session_serial#) as SID_COUNT,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sh.SQL_ID,
         nvl(sql_plan_hash_value, 0)                         as SQL_PLAN_HASH_VALUE,
         decode(session_state,'WAITING',event,session_state) as EVENT,
         count(*)                                            as WAIT_COUNT,
         count( distinct SQL_EXEC_ID)                        as EXECS,
         MIN(SAMPLE_TIME)                                    as MIN_SAMPLE_TIME,
         max(SAMPLE_TIME)                                    as MAX_SAMPLE_TIME
    from hash sh
   group by sh.sql_id, nvl(sql_plan_hash_value, 0), decode(session_state,'WAITING',event,session_state),PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID)
select  sql_id,
        sql_plan_hash_value,
        sum(WAIT_COUNT)                                         as ASH_ROWS,
        max(EXECS)                                              as EXECS,
        to_char(min(min_sample_time),'hh24:mi:ss')              as MIN_TIME,
        to_char(max(max_sample_time),'hh24:mi:ss')              as MAX_TIME,
        substr(rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ').extract('//text()') order by WAIT_COUNT desc),'; '),1,200) as WAIT_PROFILE,
trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9))) as sql_text
from ash left join dba_hist_sqltext using (sql_id)
group by sql_id,
         sql_plan_hash_value,
         trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9)))
order by sum(WAIT_COUNT) desc
/


====

-- tab=Last captured binds


set linesize 300 pagesize 300
col name for a25
col VALUE_STRING for a25
select sql_id,name, value_string, datatype_string, last_captured
from   ( select distinct sql_id,name, value_string, datatype_string, b.last_captured, dense_rank() over(partition by name order by last_captured desc) as capture_seq
         from   dba_hist_sqlbind b
         where  1=1
and b.sql_id = '&sql_id'
         and    b.was_captured = 'YES' )
where  capture_seq = 1
-- order by lpad(ltrim(name,':B'),30)
;


-- tab=SQL text
select s.sql_id,  a.name as command_type,s.sql_text
from   dba_hist_sqltext s
       left join ( select action, name from audit_actions union select 189, 'MERGE' from dual ) a on a.action = s.command_type
where  1=1
 and s.sql_id  = '&sql_id'
;



-- tab=Plans (AWR)
col PLAN_TABLE_OUTPUT for a100
select * from table(dbms_xplan.display_awr('&sql_id', null, null, 'ADVANCED'));


-- tab=Plan (Current)
col PLAN_TABLE_OUTPUT for a100
select * from table(dbms_xplan.display_cursor('&sql_id', null, 'ADVANCED'));

====
-- Hint info

col HINT for a60
-- define 1='01xv155rhts3j'
set verify off feedback off timi off lines 500

 
select distinct plan_hash_value, hint
  from (select plan_hash_value, b.hint
          from gv$sql_plan m,
               xmltable('/other_xml/outline_data/hint' passing
                        xmltype(m.OTHER_XML) columns hint varchar2(4000) path '/hint') b
         where sql_id = '&&1'
           and plan_hash_value = nvl('&&2', plan_hash_value)
           and trim(OTHER_XML) is not null
        union all
        select plan_hash_value, b.hint
          from dba_hist_sql_plan m,
               xmltable('/other_xml/outline_data/hint' passing
                        xmltype(m.OTHER_XML) columns hint varchar2(4000) path '/hint') b
         where sql_id = '&sql_id'
           and plan_hash_value = nvl('&&2', plan_hash_value)
           and trim(OTHER_XML) is not null)
 where 1=1
 -- and hint like upper('%&&3%')
 order by 1
/


SELECT sql_id,
       object_owner,
       object_name,
       policy_group,
       policy,
       policy_function_owner,
       predicate
  FROM gv$vpd_policy
 where sql_id = '&sql_id'
/

=======================================
-- undefine sql_id
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='&&sql_id'
               union all
               select 'dba_hist', sql_text from dba_hist_sqltext where sql_id='&&sql_id' 
			   );


col PLAN_TABLE_OUTPUT for a150
select   t.plan_table_output from   table(dbms_xplan.display_cursor('&sql_id', '', 'advanced rows bytes cost last')) t

select   t.plan_table_output from   table(dbms_xplan.display_cursor('&sql_id', '', 'last')) t


select   t.plan_table_output from   table(dbms_xplan.display_awr('&sql_id')) t ;


=====================================
-- Monitor 

set linesize 260 pagesize 200 trimspool on long 200000
 
column text_line format a254
set heading off
 
define sql_id = '8cnh50qfgwg73'
 SELECT  dbms_sqltune.report_sql_monitor(
                sql_id=> v.sql_id,
                sql_exec_id => v.max_sql_exec_id
        ) text_line
from     (
        select
                sql_id,
                max(sql_exec_id)        max_sql_exec_id
        from
                v$sql_monitor
        where
                sql_id = '&sql_id'
    --    and     status like 'DONE%'
        group by
                sql_id
        )       v
;
================


--- select max(snap_id) as snap_id from dba_hist_snapshot;

VARIABLE v_snap_id NUMBER
exec select max(snap_id) into :v_snap_id from dba_hist_snapshot ;

define sql_id='8gf11rgyym9fv'

set linesize 150 pagesize 300
select
  s.elapsed_time_delta,
  s.buffer_gets_delta,
  s.disk_reads_delta,
  cursor(select * from table(dbms_xplan.display_awr(t.sql_id, s.plan_hash_value)))
from  dba_hist_sqltext t,  dba_hist_sqlstat s
where 1=1
  and t.dbid = s.dbid
  and t.sql_id = s.sql_id
  and s.snap_id between :v_snap_id-2 and :v_snap_id
 -- and t.sql_text like 'select /*+ Anuj */%'
and s.sql_id='&sql_id'
;





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='&sql_id'
;


==============


set line 700 pagesize 500
col sql_profile        for a20                                               
col sql_plan_baseline for a20 
col is_bind_sensitive for a15
col is_bind_aware for a12
col is_shareable for a12
col module for a14
select  *  from
(   select
        module,
        sql_id,
        child_number,
        plan_hash_value,
        executions,
        case
        when elapsed_time > 0 then    elapsed_time/1000
                              else  0
        end elapsed_time_ms,
        case
        when executions > 0 then    round(elapsed_time/nvl(executions, 1)/1000, 2)
                            else   0
        end elapsed_time_per_exec_ms,
        rows_processed,
        px_servers_executions,
        sorts,
        invalidations,
        parse_calls,
        buffer_gets,
        disk_reads,
        optimizer_mode,
        is_bind_sensitive,
        is_bind_aware,
        is_shareable,
        sql_profile,
        sql_plan_baseline,
        sql_text
    from
        gv$sql
where 1=1
and sql_id='&sql_id'
    order by elapsed_time_per_exec_ms desc
)
where   rownum <= 50

====

set linesize 400 pagesize 300 col sql_text for a100 word_wrap SELECT dbms_lob.substr(sql_text,4000,1) sql_text FROM dba_hist_sqltext WHERE sql_id = '&sql_id' and rownum<2 /
set linesize 400 pagesize 300 
col sql_text for a100 word_wrap
SELECT sql_id,dbms_lob.substr(sql_text,4000,1) sql_text
  FROM dba_hist_sqltext
 WHERE LOWER ( TRIM ( TO_CHAR ( SUBSTR ( sql_text, 1, 100 ) ) ) ) like  'select file#, block#, type%';

===

set linesize 500 pagesize 300
COL sqlmem_structure HEAD STRUCTURE FOR A20
COL sqlmem_function  HEAD FUNCTION  FOR A20
COL sqlmem_chunk_com HEAD CHUNK_COM FOR A20
COL sqlmem_heap_desc HEAD HEAP_ADDR FOR A16
col sql_text for a50 wrap
col is_bind_sensitive for a15
col is_bind_aware     for a15
col IS_OBSOLETE like is_bind_aware
col IS_SHAREABLE like is_bind_aware
col sql_text for a50 wrap
select
INST_ID,
con_id,
sql_id,
hash_value,
users_executing,
    child_number
  , sharable_mem
  , persistent_mem
  , runtime_mem
--  , typecheck_mem
,is_bind_sensitive
,is_bind_aware
,IS_OBSOLETE
,IS_SHAREABLE
,LAST_LOAD_TIME
,sql_text sql_text
FROM
    gv$sql
WHERE 1=1
    and sql_id = '&sql_id'
   and rownum<10
/



--define sql_id='frccccnh76gtx'

SELECT /*+ NO_MERGE(@sel$2) NO_MERGE(@sel$3) LEADING(@sel$3 c) */
-- SELECT /*+ NO_MERGE(@sel$2) NO_MERGE(@sel$3) LEADING(@sel$4 c) xNO_MERGE(@"SEL$4") */
--    sql_text
--  , sql_fulltext
--    hash_value
 sql_id
   , sum(chunk_size) total_size
  , trunc(avg(chunk_size)) avg_size
  , count(*) chunks
  , alloc_class
  , chunk_type
  , structure     sqlmem_structure
  , function      sqlmem_function
  , chunk_com     sqlmem_chunk_com
  , heap_desc     sqlmem_heap_desc
--  , chunk_ptr
--  , subheap_desc
FROM
    gv$sql_shared_memory s
WHERE 1=1
   and sql_id = '&sql_id'
--    hash_value = 1
GROUP BY
    hash_value
  , sql_id
  , heap_desc
  , structure
  , function
  , chunk_com
--  , chunk_ptr
  , alloc_class
  , chunk_type
--  , subheap_desc
ORDER BY
    total_size DESC
/


====


VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -12 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

--and snap_id between :BgnSnap and nvl(:EndSnap, :BgnSnap)


col ELA_PER_EXEC for 999,999,999,999
col CPU_PER_EXEC for 999,999,999,999
col GETS_PER_EXEC for 999,999,999,999
col IOWAITS_PER_EXEC for 999,999,999,999
col CLWAITS_PER_EXEC_uS for 999,999,999,999
col APWAITS_PER_EXEC for 999,999,999,999
col CCWAITS_PER_EXEC for 999,999,999,999
col SQL_PROFILE for a20

select 
    instance_number as inst,
    (snap_id - 1) as Begin_Snap_id,
    to_char(sn.begin_interval_time,'dd.mm hh24:mi') as begin_snap_time,
    round(st.executions_delta) as execs,
--    round(st.executions_delta * (st.rows_processed_delta/decode(st.executions_delta,0,1,st.executions_delta))) as rows_processed,
    st.rows_processed_delta as rows_processed,
    st.sql_id,
    st.plan_hash_value as plan,
    st.SQL_PROFILE,
    st.optimizer_cost as cost,
    round(st.parse_calls_delta/decode(st.executions_delta,0,1,st.executions_delta))                   as PARSE_PER_EXEC,
    round(st.elapsed_time_delta/decode(st.executions_delta,0,1,st.executions_delta))                    as ELA_PER_EXEC,
    round(st.cpu_time_delta/decode(st.executions_delta,0,1,st.executions_delta))                        as CPU_PER_EXEC,
    round(st.buffer_gets_delta/decode(st.executions_delta,0,1,st.executions_delta))                     as GETS_PER_EXEC,
    round(st.disk_reads_delta/decode(st.executions_delta,0,1,st.executions_delta))                      as disk_reads_per_exec,
    round(st.physical_read_bytes_delta/decode(st.executions_delta,0,1,st.executions_delta)/1024/1024)   as READ_MB_PER_EXEC,
    round(st.physical_read_requests_delta/decode(st.executions_delta,0,1,st.executions_delta))          as READS_PER_EXEC,
    round(st.physical_write_bytes_delta/decode(st.executions_delta,0,1,st.executions_delta)/1024/1024)  as WRITES_MB_PER_EXEC,
    round(st.physical_write_requests_delta/decode(st.executions_delta,0,1,st.executions_delta))         as WRITES_PER_EXEC,
    round(st.direct_writes_delta/decode(st.executions_delta,0,1,st.executions_delta))                   as DIRECT_WRITES_PER_EXEC,
    round(st.rows_processed_delta/decode(st.executions_delta,0,1,st.executions_delta))                  as ROWS_PER_EXEC,
    round(st.fetches_delta/decode(st.executions_delta,0,1,st.executions_delta))                         as FETCHES_PER_EXEC,
    round(st.iowait_delta/decode(st.executions_delta,0,1,st.executions_delta))                          as IOWAITS_PER_EXEC,
    round(st.clwait_delta/decode(st.executions_delta,0,1,st.executions_delta))                          as CLWAITS_PER_EXEC_uS,
    round(st.apwait_delta/decode(st.executions_delta,0,1,st.executions_delta))                          as APWAITS_PER_EXEC,
    round(st.ccwait_delta/decode(st.executions_delta,0,1,st.executions_delta))                          as CCWAITS_PER_EXEC,
    round(st.parse_calls_delta/decode(st.executions_delta,0,1,st.executions_delta))                     as PARSE_PER_EXEC,
    round(st.plsexec_time_delta/decode(st.executions_delta,0,1,st.executions_delta))                    as PLSQL_PER_EXEC,
    round(st.px_servers_execs_delta/decode(st.executions_delta,0,1,st.executions_delta))                as PX_PER_EXEC,
    round(st.clwait_delta/1000000) as clwaits_sec
from dba_hist_sqlstat st join dba_hist_snapshot sn using(snap_id,instance_number)
 where 1=1
 and sql_id = '&sql_id'
--   and snap_id between &&2 and nvl('&&3', &&2)
 --and snap_id > 39933 ---:BgnSnap
 and snap_id between :BgnSnap and nvl(:EndSnap, :BgnSnap)
--   and executions_delta > 0
and (st.elapsed_time_delta > 0 and st.executions_delta is not null)
order by snap_id, instance_number

 
=========================



set linesize 500 pagesize 500

VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER

exec select max(snap_id) -30 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;




define 1='8cnh50qfgwg73'
col ELA_PER_EXEC for 999,999,999,999
col CPU_PER_EXEC for 999,999,999,999
col GETS_PER_EXEC for 999,999,999,999
col IOWAITS_PER_EXEC for 999,999,999,999
col CLWAITS_PER_EXEC_uS for 999,999,999,999
col APWAITS_PER_EXEC for 999,999,999,999
col CCWAITS_PER_EXEC for 999,999,999,999
col DURATION for a27
col MIN_SAMPLE_TIME for a25
col MAX_SAMPLE_TIME for a25
with ash as
( select --+ parallel(4) materialize
  instance_number as inst
, sql_id
, sql_plan_hash_value
, sql_exec_id
, count(*) as ash_rows
, (cast(max(sample_time) as date)-cast(min(sample_time) as date)) as durn
, max(sample_time) - min(sample_time) as dur
, min(sample_time) as min_sample_time
, max(sample_time) as max_sample_time
, min(snap_id) as min_snap_id
, max(snap_id) as max_snap_id
, count(distinct session_id) as px
  from dba_hist_active_sess_history
 where 1=1
 and (snap_id between :bid and nvl(:eid, :bid))
   and sql_id = '&1'
  and sql_exec_id > 0
 group by instance_number, sql_id, sql_plan_hash_value, sql_exec_id
 having (cast(max(sample_time) as date)-cast(min(sample_time) as date)) < 1 and (cast(max(sample_time) as date)-cast(min(sample_time) as date)) > 0
 order by 3)
select inst, sql_id, sql_plan_hash_value, sql_exec_id, ash_rows
, round(durn*86400) as seconds
, max_sample_time-min_sample_time as duration
, min_sample_time
, max_sample_time
, min_snap_id
, max_snap_id
, px
,(select min(round(st.rows_processed_delta / decode(st.executions_delta, 0, 1, st.executions_delta)))
         ||' / '||
         max(round(st.rows_processed_delta / decode(st.executions_delta, 0, 1, st.executions_delta)))
  from dba_hist_sqlstat st
 where st.snap_id between min_snap_id and max_snap_id
   and st.instance_number = inst
   and st.sql_id = '&1'
   and st.plan_hash_value = sql_plan_hash_value
   and st.snap_id between :bid and nvl(:eid, :bid)) as min_max_rows
from ash
order by sql_plan_hash_value       , min_sample_time
/



=======


set ver off pages 50000 lines 400 tab off
undef sql_id
undef days_history
undef interval_hours
undef grby_inst
def sql_id="8cnh50qfgwg73"  ---- <<<<<<
def days_history="1"
def interval_hours="1"
def grby_inst="1"
col time for a19
col inst for 9999
col executions for 9999999999
col rows_processed_1exec for 9999999.999
col elapsed_time_s_total for 9999999.999
col javexec_time_s_1exec for 9999999.999
col buffer_gets_1exec for 999999999999.999
col disk_reads_1exec for 999999999999.999
col direct_writes_1exec for 999999999999.999
select to_char(trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24,'yyyy-mm-dd hh24:mi:ss') time,
    nvl(sum(hss.executions_delta),0) executions,
    round(sum(hss.elapsed_time_delta)/1000000,3) elapsed_time_s_total,
    round(sum(hss.elapsed_time_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) elapsed_time_s_1exec,
    round(sum(hss.cpu_time_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) cpu_time_s_1exec,
    round(sum(hss.iowait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) iowait_s_1exec,
    round(sum(hss.clwait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) clwait_s_1exec,
    round(sum(hss.apwait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) apwait_s_1exec,
    round(sum(hss.ccwait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) ccwait_s_1exec,
    round(sum(hss.rows_processed_delta)/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) rows_processed_1exec,
    round(sum(hss.buffer_gets_delta)/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) buffer_gets_1exec,
    round(sum(hss.disk_reads_delta)/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) disk_reads_1exec,
    round(sum(hss.direct_writes_delta)/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) direct_writes_1exec
from dba_hist_sqlstat hss, (select snap_id, min(hs2.begin_interval_time) begin_interval_time from dba_hist_snapshot hs2 group by snap_id) hs
where hss.sql_id(+)='&sql_id'
and hss.snap_id(+)=hs.snap_id
and hs.begin_interval_time>=trunc(sysdate)-&days_history+1
group by trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24
order by trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24;
BREAK ON inst SKIP 1


TIME                 EXECUTIONS ELAPSED_TIME_S_TOTAL ELAPSED_TIME_S_1EXEC CPU_TIME_S_1EXEC IOWAIT_S_1EXEC CLWAIT_S_1EXEC APWAIT_S_1EXEC CCWAIT_S_1EXEC ROWS_PROCESSED_1EXEC BUFFER_GETS_1EXEC  DISK_READS_1EXEC DIRECT_WRITES_1EXEC
------------------- ----------- -------------------- -------------------- ---------------- -------------- -------------- -------------- -------------- -------------------- ----------------- ----------------- -------------------
2022-03-10 00:00:00         108             7216.581                66.82           20.334         28.706         26.735              0           .005                1.000        419494.222         65840.722                .000
2022-03-10 01:00:00          90             7086.855               78.743           21.476         36.847         29.679              0           .006                1.000        434268.778         68373.689                .000
2022-03-10 02:00:00         108             7190.316               66.577           19.615         29.719         26.035              0           .004                1.000        396152.481         62198.833                .000
2022-03-10 03:00:00         108             7211.468               66.773            19.98          29.27         26.378              0           .004                1.000        406881.704         63974.361                .000
2022-03-10 04:00:00         105             7210.533               68.672           20.047         30.463         27.012              0           .004                1.000              .000         63597.400                .000
2022-03-10 05:00:00          93             7212.631               77.555            22.56         34.287         30.824              0           .004                1.000        457872.032         71817.903                .000
2022-03-10 06:00:00         108             7211.188                66.77           19.714         28.951         26.615              0           .004                1.000        402315.056         63165.185                .000

7 rows selected.

================

objects of a specific sql id

set lines 300 set pages 300
col table_name for a40
col owner for a30 
select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked
  from dba_tab_statistics
  where (owner, table_name) in
  (select distinct owner, table_name
          from dba_tables
          where ( table_name)
          in ( select object_name
                  from gv$sql_plan
                  where upper(sql_id) = upper('&sql_id') and object_name is not null))
  --and STALE_STATS='YES'
/



======
set linesize 500 pagesize 500

VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;

define sql_text='WITH a as (SELECT'

set pagesize 999
set lines 200
col sql_text format a36 trunc
col inst for 99
col total_row for 99999999
col execs for 9,999,999
col avg_etime for 99,999.999
col avg_lio for 999,999,999.9
col avg_pio for 999,999,999.9
col begin_interval_time for a30
col hash_value for 9999999999
col "interval time" for a20
break on hash_value on startup_time skip 1
 
select to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') "interval time", ss.instance_number inst, s.sql_id, plan_hash_value hash_value,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
trim(replace(dbms_lob.substr(sql_text,36, 1),chr(9),'')) sql_text
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, DBA_HIST_SQLTEXT ST
where 1=1
-- and dbms_lob.substr(sql_text,3999,1) like  '&sql_text&'
and  ST.sql_id='92yv7gw4g8b94'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and s.sql_id = st.sql_id
and executions_delta > 0
and ss.snap_id >= :bid
and ss.snap_id<=:eid
and s.sql_id = st.sql_id
order by begin_interval_time desc
;
undef sql_text

======

from web https://github.com/soliverr/oracle-tpt/blob/master/nonshared.sql

define 1='8y2uufyruukhs'

set serverout on size 1000000
prompt Show why existing SQL child cursors were not reused (gV$SQL_SHARED_CURSOR)...
prompt

def cmd="select * from gv$sql_shared_cursor where sql_id = ''&1''"


declare
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    procedure execute_immediate( p_sql in varchar2 )
    is
    BEGIN
        dbms_sql.parse(l_theCursor,p_sql,dbms_sql.native);
        l_status := dbms_sql.execute(l_theCursor);
    END;
begin
    execute_immediate( 'alter session set nls_date_format= ''dd-mon-yyyy hh24:mi:ss'' ');
    dbms_sql.parse(  l_theCursor, replace( '&cmd', '"', ''''),    dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
    for i in 1 .. l_colCnt loop
        dbms_sql.define_column( l_theCursor, i,   l_columnValue, 4000 );
    end loop;
    l_status := dbms_sql.execute(l_theCursor);
    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            if l_columnValue != 'N' then 
            dbms_output.put_line  ( rpad( l_descTbl(i).col_name, 30 ) || ': ' || l_columnValue );
        end if;
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;
    execute_immediate( 'alter session set nls_date_format= ''dd-MON-yy'' ');
exception
    when others then
        execute_immediate( 'alter session set  nls_date_format=''dd-MON-yy'' ');
        raise;
end;
/



new  17:                      replace( 'select * from gv$sql_shared_cursor where sql_id = ''8y2uufyruukhs''', '"', ''''),
INST_ID                       : 1
SQL_ID                        : 8y2uufyruukhs
ADDRESS                       : 00000000F816ADA0
CHILD_ADDRESS                 : 00000000ED335850
CHILD_NUMBER                  : 0
HASH_MATCH_FAILED             : Y
REASON                        : <ChildNode><ChildNumber>0</ChildNumber><ID>36</ID><reason>Authorization Check failed(4)</reason><size>5x4</size><translation_table_position>0</translation_table_position><original_handle>4068010336</original_handle><temp_handle>3992620960</temp_handle><schema>0</schema><synonym_object_number>0</synonym_object_number></ChildNode>
CON_ID                        : 0
-----------------




select SQL_ID,SQL_TEXT from gv$sql 
where 1=1
and SQL_TEXT like '%from t_acs%'    ---<<<<<
and   sql_text not like 'select SQL_ID,SQL_TEXT from gv$sql where%'
and   sql_text not like 'select SQL_ID,SQL_TEXT from v$sql where%'

SQL_ID        SQL_TEXT
------------- --------------------------------------------------
fbac69md98mr5  select count(*) from t_acs where n2 <= :ln2
3pkkd8g9u2946  select count(*) from t_acs where n2 = :ln2
8y2uufyruukhs select count(*) from t_acs where n2 = :ln2


alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 500 pagesize 500
col bind for a50 wrap
col is_obsolete for a15
col  is_bind_sensitive for a15
col is_bind_aware for a15
col is_shareable for a15
col sql_text for a50 wrap
select sql_id, plan_hash_value, child_number , is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, substr(sql_text,1,50) sql_text, dbms_sqltune.extract_binds(bind_data) bind
from gv$sql where sql_id = '&sql_id';

==================

https://tanelpoder.com/2013/03/17/asqlmon-sql-sql-monitoring-like-execution-plan-line-level-drilldown-into-sql-response-time/

                                               asqlmon.sql


var sqlid VARCHAR2(13);
 begin :sqlid := '20ufdw96tvyz8'; end; ----- change sql id
/


define 2=''
define 3=''
define 4=''

set linesize 500
set pages 9999
set verify off
COL asqlmon_operation FOR a100
COL session_state FOR a15
COL EVENT FOR a30
COL AVG_P3 FOR 999999999.99
COL asqlmon_predicates FOR a100 word_wrap
COL options   FOR a30

COL asqlmon_plan_hash_value HEAD PLAN_HASH_VALUE
COL asqlmon_sql_id          HEAD SQL_ID  NOPRINT
COL asqlmon_sql_child       HEAD CHILD#  NOPRINT
COL asqlmon_sample_time     HEAD SAMPLE_HOUR
COL projection FOR A520

COL pct_child HEAD "Activity %" FOR A8
COL pct_child_vis HEAD "Visual" FOR A12

COL asqlmon_id        HEAD "Line ID" FOR 9999
COL asqlmon_parent_id HEAD "Parent"  FOR 9999

col OBJ_ALIAS_QBC_NAME for a25
col ASQLMON_OPERATION for a45
BREAK ON asqlmon_plan_hash_value SKIP 1 ON asqlmon_sql_id SKIP 1 ON asqlmon_sql_child SKIP 1 ON asqlmon_sample_time SKIP 1 DUP ON asqlmon_operation

WITH  sample_times AS (
    select * from dual
), 
sq AS (
SELECT
  --  to_char(ash.sample_time, 'YYYY-MM-DD HH24') sample_time
    count(*) samples
  , ash.sql_id
  , ash.sql_child_number
  , ash.sql_plan_hash_value
  , ash.sql_plan_line_id
  , ash.sql_plan_operation
  , ash.sql_plan_options
  , ash.session_state
  , ash.event
  , AVG(ash.p3) avg_p3
  , sum(ash.time_waited) sum_time_waited
FROM
    dba_hist_active_sess_history ash
WHERE
    1=1
--AND ash.session_id = 8 AND ash.session_serial# =     35019
AND ash.sql_id = :sqlid
--AND ash.sql_plan_hash_value = &2
--AND ash.snap_id >= &3 and ash.snap_id<=&4
GROUP BY
  --to_char(ash.sample_time, 'YYYY-MM-DD HH24')
    ash.sql_id
  , ash.sql_child_number
  , ash.sql_plan_hash_value
  , ash.sql_plan_line_id
  , ash.sql_plan_operation
  , ash.sql_plan_options
  , ash.session_state
  , ash.event
)
SELECT
    plan.sql_id            asqlmon_sql_id
--  , plan.plan_hash_value asqlmon_plan_hash_value
  , sq.samples seconds
  , LPAD(TO_CHAR(ROUND(RATIO_TO_REPORT(sq.samples) OVER (PARTITION BY sq.sql_id, sq.sql_plan_hash_value) * 100, 1), 999.9)||' %',8) pct_child
  , '|'||RPAD( NVL( LPAD('#', ROUND(RATIO_TO_REPORT(sq.samples) OVER (PARTITION BY sq.sql_id, sq.sql_plan_hash_value) * 10), '#'), ' '), 10,' ')||'|' pct_child_vis
    , sq.session_state
  , sq.event
  , sq.sum_time_waited
  --, sq.avg_p3 
  --, sq.sample_time         asqlmon_sample_time
  --, LPAD(plan.id,4)||CASE WHEN parent_id IS NULL THEN '    ' ELSE ' <- ' END||LPAD(plan.parent_id,4) asqlmon_plan_id
  , plan.id asqlmon_id
  , nvl(plan.parent_id,'-1') asqlmon_parent_id
  , LPAD(' ', depth) || plan.operation ||' '|| plan.options || NVL2(plan.object_name, ' ['||plan.object_name ||']', null) asqlmon_operation
  , plan.object_alias || CASE WHEN plan.qblock_name IS NOT NULL THEN ' ['|| plan.qblock_name || ']' END obj_alias_qbc_name
FROM
    dba_hist_sql_plan plan
  , sq
WHERE
    1=1
AND sq.sql_id(+) = plan.sql_id
AND sq.sql_plan_line_id(+) = plan.id
AND sq.sql_plan_hash_value(+) = plan.plan_hash_value
AND plan.sql_id = :sqlid
ORDER BY
    plan.plan_hash_value
  , plan.id
/



===

ASH report 

-- last 24hr report

var BgnSnap number;
var EndSnap number;
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
select * from table(dbms_workload_repository.awr_sql_report_text((select dbid from v$database), (select instance_number from v$instance), :BgnSnap,:EndSnap, '&sql_id'));


====
Expalin Plan 

define sql_id='f705bwx3q0ydq'
set linesize 400 pagesize 300
col PLAN_TABLE_OUTPUT for a200
select *
from (
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'))
)




define sql_id='f705bwx3q0ydq'
set linesize 400 pagesize 300
col OWNER for a20
col index_name for a50
col TABLE_NAME for a30
col global_stats  for a20
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('&sql_id', null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
ORDER BY owner, table_name, index_name
/

===
set linesize 300
 col id format 99
 col operation format a30
 col options format a30
 col object_name format a30
 col cost format 9999

 SELECT vs.sql_id,id, operation, options, object_name, cost   FROM gv$sql_plan VP, gv$sql VS
    WHERE VP.address = VS.address
      AND VP.hash_value = VS.hash_value
     -- AND sql_text LIKE '%1%'
      AND sql_text NOT LIKE '%v$sql%'
   -- and object_name=:h
      and vs.sql_id='&sql_id'
and rownum < 20 ORDER BY id /


set lines 1000 pages 300
col name for a30
col task_exec_name for a16
col category for a10
col created for a30
col sql_text for a150
col signature for 9999999999999999999999999
select sql.sql_id,sql.child_number as child , prof.name, prof.category, prof.created, prof.task_exec_name,prof.force_matching, prof.status, prof.signature,prof.sql_text
from dba_sql_profiles prof,gv$sql sql
where sql.sql_id in ('&sql_id')
order by created;



set long 100000 linesize 100 pagesize 300
select other_xml from v$sql_plan where sql_id like '&sql_id' and id=1;
===


set linesize 200  pagesize 0

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'&sql_id', format=>'ALLSTATS LAST +cost +bytes +outline +PEEKED_BINDS +adaptive')); 

select * from table(dbms_xplan.display_awr(sql_id=>'&sql_id',   format=>'ALLSTATS LAST +cost +bytes +outline +PEEKED_BINDS +adaptive'));


col SQL_TEXT for a70 wrap
select sql_id,child_number,HASH_VALUE,EXACT_MATCHING_SIGNATURE,sql_text from gv$sql where sql_text like '%select count(*) from func_test%';

define sql_id='2yh5y6s2vh1yg'
--define sql_id='01twva1ky0bp9'
set linesize 200  pagesize 0
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&sql_id', cursor_child_no => 2, FORMAT => 'TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));

--

 set linesize 300 pagesize 300

col OWNER for a20
col OBJECT_NAME for a20
col EVENT for a27
define sql_id='8cnh50qfgwg73'

 
 VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
 


SELECT ss.sql_id,
       ss.event,
       ss.current_obj#,
       ss.owner,
       ss.object_name,
       ss.object_type,
       ss.time_waited,
       ss.counts_waited,
       tt.total_time,
       ROUND((ss.time_waited*100/tt.total_time),1) percent
FROM
     (SELECT s.sql_id,
             s.event,
             s.current_obj#,
             o.owner,
             o.object_name,
             o.object_type,
             COUNT(*) counts_waited,
             SUM(time_waited) time_waited
      FROM dba_hist_active_sess_history s,
           dba_hist_seg_stat_obj o,
           v$database d,
           v$instance i
      WHERE s.dbid = d.dbid
      AND   s.instance_number = i.instance_number
      AND   s.sql_id = '&sql_id'
     -- AND   s.event = '<wait name>'
      AND   o.dbid (+) = s.dbid
      AND   o.obj# (+) = s.current_obj#
      AND   s.snap_id > :BgnSnap AND s.snap_id <= :EndSnap
      GROUP BY s.sql_id, s.event, s.current_obj#, o.owner, o.object_name, o.object_type) ss,
     (SELECT SUM(time_waited) total_time
      FROM dba_hist_active_sess_history t,
           v$database d,
           v$instance i
      WHERE t.dbid = d.dbid
      AND   t.instance_number = i.instance_number
     AND   t.sql_id = '&sql_id'
    --  AND   t.event = '<wait name>'
      AND   t.snap_id > :BgnSnap AND t.snap_id <= :EndSnap ) tt 
ORDER BY ss.counts_waited DESC;



=====

phv2.sql
SELECT
  p.sql_id
 ,p.plan_hash_value
 ,p.child_number
 ,t.phv2
FROM   gv$sql_plan p
 ,xmltable('for $i in /other_xml/info
             where $i/@type eq "plan_hash_2"
             return $i'
             passing xmltype(p.other_xml)
             columns phv2 number path '/'
            ) t
WHERE      p.sql_id = '&sql_id'
AND p.other_xml is not null;
*******






INIT.ORA: _cursor_obsolete_threshold=1024
or
SPFILE: SQL> alter system set "_cursor_obsolete_threshold"=1024 scope=spfile;


define 1='cursor'
set linesize 300 pagesize 300
col parameter for a30
col session for a28
col instance for a12
col s for a1
col i for a1
col d for a1
col description for a70

SELECT
a.ksppinm "Parameter",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
from x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
where a.indx = b.indx and a.indx = c.indx
and p.name(+) = a.ksppinm
and upper(a.ksppinm) like upper('%&1%')
order by a.ksppinm
;





Parameter                      Session                      Instance     S I D Description
------------------------------ ---------------------------- ------------ - - - ----------------------------------------------------------------------
_cursor_obsolete_threshold     1024                         1024         T F F Number of cursors per parent before obsoletion.





SQL> def
DEFINE _DATE           = "13-DEC-22" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)



set lines 300 pagesize 500
col PARSING_SCHEMA_NAME for a20 
col REASON 		for a35
col SQL_TEXT 		for a50

select INST_ID,sql_id,sc.address,version_count,parsing_schema_name,reason,lpad(' ',180,'-')||replace(sql_text,(13)) sql_text from (
select
 INST_ID,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 gv$sql_shared_cursor
group by  INST_ID,sql_id, address
) sc join gv$sqlarea sq using(sql_id,inst_id) 
where version_count	> 20
and parsing_schema_name not in ('SYS')
order by sql_id, version_count 
;


define sql_id='aca4xvmz0rzup'
col instance_name for a16
col status for a16

select instance_name, instance_number, status from v$instance;


prompt
prompt

prompt Different versions of the query.
prompt -----------------------------------

prompt (This should return only a handful of rows, or no binds have been used)
prompt

column vl_inst_id     new_value l_inst_id       noprint
select instance_number vl_inst_id from v$instance;

col sql_id for a20
col last_active_time for a16
col last_load_time for a20
col instance for a16

select a.sql_id, a.child_number
, case 
   when a.inst_id = &l_inst_id then 'THIS ONE'
   else (select instance_name 
         from gv$instance 
         where instance_number=a.inst_id) 
   end instance, a.last_load_time
, to_char(a.last_active_time, 'DD-MON HH24:MI:SS') last_active_time
,      a.loaded_versions, a.open_versions, a.users_opening
from gv$sql a
where sql_id='&sql_id';





set long 50000
col sql_fulltext for a100 wrap

select sql_fulltext
from gv$sql
where sql_id='&sql_id'
and child_number=&childnr;

set heading on



$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

define sql_id='aca4xvmz0rzup'
define childnr=0


col OWNER for a20
col object_name for a20
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20

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('&sql_id', &childnr, 'BASIC'))
		   )
	where plan_table_output like '%TABLE ACCESS%'
  )
ORDER BY owner, table_name
/



col OWNER for a20
col object_name for a20
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20

SELECT ob.owner, ob.object_name, sth.PARTITION_NAME, sth.SUBPARTITION_NAME, sth.STATS_UPDATE_TIME
FROM dba_objects ob
,    DBA_TAB_STATS_HISTORY sth
WHERE ob.object_type in ('TABLE')
AND   ob.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('&sql_id', &childnr, 'BASIC'))
		UNION ALL
		SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
		   )
	where plan_table_output like '%TABLE ACCESS%'
  )
and ob.object_name=sth.table_name
and ob.owner=sth.owner
order by ob.owner, ob.object_name, sth.partition_name, sth.subpartition_name, sth.STATS_UPDATE_TIME asc
;



col OWNER for a20
col object_name for a20
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20

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('&sql_id', &childnr, 'BASIC'))
		UNION ALL
		SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
		   )
	where plan_table_output like '%TABLE ACCESS%'
  )
and object_id=obj#
order by ob.owner, ob.object_name, analyzetime asc
;


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('&sql_id', &childnr, 'BASIC'))
		UNION ALL
		SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
		   )
	where plan_table_output like '%TABLE ACCESS%'
  )
ORDER BY table_owner, table_name, partition_name
/



col OWNER for a20
col object_name for a20
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20

SELECT ob.owner, ob.object_name, ts.PARTITION_NAME, ts.partition_position par_pos, ts. SUBPARTITION_NAME, ts.SUBPARTITION_POSITION subpar_pos, ts.last_analyzed
, ts.stale_stats, ts.global_stats, ts.user_stats, ts.STATTYPE_LOCKED
FROM dba_objects ob
,    dba_tab_statistics ts
WHERE ob.object_type in ('TABLE')
AND   ob.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('&sql_id', &childnr, 'BASIC'))
		UNION ALL
		SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
		   )
	where plan_table_output like '%TABLE ACCESS%'
  )
and ob.object_name=ts.table_name
and ob.owner=ts.owner
and ts.STALE_STATS='YES'
order by ob.owner, ob.object_name, ts.PARTITION_NAME, ts. SUBPARTITION_NAME, ts.last_analyzed asc
;


col OWNER for a20
col object_name for a20
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
SELECT ob.owner, ob.object_name, mod.PARTITION_NAME, mod.SUBPARTITION_NAME, mod.inserts, mod.updates, mod.deletes, mod.timestamp, mod.truncated, mod.drop_segments
FROM dba_objects ob
,    dba_tab_modifications mod
WHERE ob.object_type in ('TABLE')
AND   ob.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('&sql_id', &childnr, 'BASIC'))
		UNION ALL
		SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
		   )
	where plan_table_output like '%TABLE ACCESS%'
  )
and ob.object_name=mod.table_name
and ob.owner=mod.table_owner
order by ob.owner, ob.object_name, mod.partition_name, mod.subpartition_name, mod.timestamp asc
;



col OWNER for a20
col INDEX_NAME for a25

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('&sql_id', &childnr, 'BASIC'))
		UNION ALL
		SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
		   )
	where plan_table_output like '%INDEX%'
  )
ORDER BY owner, table_name, index_name
/
=====
=============================================

define  sql_id='86zty3s0kh2fc'

TTITLE 'SQL Statements With Bind Sensitivity Enabled|(from V$SQL)'
COL sql_id          FORMAT A16          HEADING 'SQL ID'
COL hash_value      FORMAT 99999999999  HEADING 'Hash|Value'
COL plan_hash_value FORMAT 99999999999  HEADING 'Plan|Hash|Value'
col ibs_flag  		FORMAT A15   
COL iba_flag        FORMAT A15          HEADING 'Bind|Aware?'
COL sql_text        FORMAT A80          HEADING 'SQL Text'
SELECT 
     sql_id
    ,hash_value
    ,plan_hash_value
    ,is_bind_sensitive ibs_flag
    ,is_bind_aware iba_flag
    ,sql_text
  FROM gv$sql
 WHERE ((is_bind_sensitive <> 'N') OR (is_bind_aware <> 'N'))
--and 1=1
and sql_id='&&sql_id'
ORDER BY hash_value
;

TTITLE OFF





TTITLE 'Histograms for Adaptive Cursor Sharing|(from V$SQL_CS_HISTOGRAM)'
COL hash_value      FORMAT 99999999999  HEADING 'Hash|Value'
COL sql_id          FORMAT A16          HEADING 'SQL ID'
COL child_number    FORMAT 9999         HEADING 'Chld|#'
COL bucket_id       FORMAT 9999         HEADING 'Bckt|ID#'
COL count           FORMAT 999999       HEADING 'Exec-|ution|Count'     
SELECT
    sql_id
     ,hash_value
     ,child_number
    ,bucket_id
    ,count
  FROM gv$sql_cs_histogram
  where 1=1
  and sql_id='&&sql_id'
;
TTITLE OFF



TTITLE 'Selectivity Metrics for Adaptive Cursor Sharing|(from V$SQL_CS_STATISTICS)'
COL hash_value              FORMAT 99999999999  HEADING 'Hash|Value'
COL sql_id                  FORMAT A16          HEADING 'SQL ID'
COL child_number            FORMAT 9999         HEADING 'Chld|#'
COL bind_set_hash_value     FORMAT 99999999999  HEADING 'Hash|Value'
COL peeked                  FORMAT A05          HEADING 'Peek?'
COL executions              FORMAT 999999       HEADING '# of|Exec-|utions'
COL rows_processed          FORMAT 999999       HEADING '# of|Rows'
COL buffer_gets             FORMAT 999999       HEADING 'Buffer|Gets'
COL cpu_time                FORMAT 999999       HEADING 'CPU|Time'
SELECT
     sql_id
     ,hash_value
     ,child_number
    ,bind_set_hash_value
    ,peeked
    ,executions
    ,rows_processed
    ,buffer_gets
    ,cpu_time
  FROM gv$sql_cs_statistics
  where 1=1
  and sql_id='&&sql_id'
;
TTITLE OFF



TTITLE 'Selectivity Metrics for Adaptive Cursor Sharing|(from V$SQL_CS_SELECTIVITY)'
COL hash_value      FORMAT 99999999999  HEADING 'Hash|Value'
COL sql_id          FORMAT A16          HEADING 'SQL ID'
COL child_number    FORMAT 9999         HEADING 'Chld|#'
COL range_id        FORMAT 9999         HEADING 'Rng|ID#'
COL low             FORMAT A12          HEADING 'Low Value'
COL high            FORMAT A12          HEADING 'High Value'
COL predicate       FORMAT A80          HEADING 'Predicates'
SELECT
   sql_id
    , hash_value
    ,child_number
    ,range_id
    ,low
    ,high
    ,predicate
  FROM gv$sql_cs_selectivity
  where 1=1
  --and sql_id='&&sql_id'
;
TTITLE OFF





define sql_id='8cnh50qfgwg73'

-- Shared Pool
alter session set "_rowsource_execution_statistics" = TRUE;
col PLAN_TABLE_OUTPUT for a200
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&sql_id', cursor_child_no => 0, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));


SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t 
WHERE 1=1
--and sql_text LIKE '%XXX%'
and sql_id = '&sql_id'
;



set pagesize 80

define sql_id='8cnh50qfgwg73'
select * from table(dbms_xplan.display_awr(sql_id=>'&sql_id', format=>'ALLSTATS LAST +cost +bytes +outline +PEEKED_BINDS +adaptive'));


 --with plan 
define sql_id='b92h6psjy1xxx'
define plan_hash_value=984047205
SELECT * FROM table(dbms_xplan.display_awr(nvl('&sql_id','a96b61z6vp3un'),nvl('&plan_hash_value',null),null,'ADVANCED'));


define sql_id='b92h6psjy1zga'
col begin_time for a25
col end_time for a11
col inst for 99999
col snapid for 999999
col PARSING_SCHEMA_NAME for a20
set lines 200 pages 200
select snap_id snapid,
(select substr(BEGIN_INTERVAL_TIME,1,18)||' '||substr(BEGIN_INTERVAL_TIME,24,2) from dba_hist_snapshot b 
where b.snap_id=a.snap_id 
and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) begin_time,(select substr(end_INTERVAL_TIME,11,8)||' '||substr(end_INTERVAL_TIME,24,2) from dba_hist_snapshot b
where b.snap_id=a.snap_id 
and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) end_time
,INSTANCE_NUMBER inst , sql_id,PLAN_HASH_VALUE,PARSING_SCHEMA_NAME,
EXECUTIONS_DELTA Executions,
ROWS_PROCESSED_DELTA rows1,
round( CPU_TIME_DELTA /1000000,0) cpu_time,round(IOWAIT_DELTA /1000000,0) io_wait,
round( ELAPSED_TIME_DELTA /1000000,0) elapsed
from sys.wrh$_sqlstat a 
where sql_id in('&sql_id')
order by snap_id, INSTANCE_NUMBER
;



set linesize 300 pagesize 300 	
SELECT
  s.elapsed_time_delta,
  s.buffer_gets_delta,
  s.disk_reads_delta,
  cursor(select * from table(dbms_xplan.display_awr(t.sql_id, s.plan_hash_value)))
FROM   dba_hist_sqltext t,  dba_hist_sqlstat s
WHERE
  t.dbid = s.dbid
  AND t.sql_id = s.sql_id
 -- AND s.snap_id between v_snap_id-2 and v_snap_id
  AND t.sql_id = '&sqlid'
  and rownum <2
  ;


set linesize 300 pagesize 300 	
col PLAN_TABLE_OUTPUT for a150 
SELECT tf.* FROM DBA_HIST_SQLTEXT ht, table (DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null,  'ALL' )) tf 
 WHERE 1=1
 -- ht.sql_text like '%XXXX%'
 and sql_id='&sqlid'
;





VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -10 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
col PARSING_SCHEMA_NAME for a20
set linesize 300 pagesize 300
col PLAN_TABLE_OUTPUT for a150
select
--t.CON_ID,
PARSING_SCHEMA_NAME,
  s.elapsed_time_delta,
  s.buffer_gets_delta,
  s.disk_reads_delta,
  cursor(select * from table(dbms_xplan.display_awr(t.sql_id, s.plan_hash_value, format=>'ADVANCED')))
 -- cursor(select * from table(dbms_xplan.display_awr(t.sql_id, s.plan_hash_value, format=>'ALLSTATS LAST +cost +bytes +outline +PEEKED_BINDS +adaptive')))
from
   dba_hist_sqltext t,
  dba_hist_sqlstat s
where
  t.dbid = s.dbid
  and t.sql_id = s.sql_id
and s.SQL_ID='a73cmq8jvsjdq'
--and s.plan_hash_value=869451858
 and s.snap_id between :BgnSnap and :EndSnap
  --and t.sql_text like 'select /*+ awr */%'
--and PARSING_SCHEMA_NAME
and PARSING_SCHEMA_NAME not in ('SVCDBEM7ADM', 'GSMADMIN_INTERNAL','SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7','DP_EM7_ADM')
;



alter session set "_rowsource_execution_statistics" = TRUE;
col PLAN_TABLE_OUTPUT for a200
SELECT * FROM TABLE(dbms_xplan.display_awr(sql_id => '&sql_id' ,FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));

sql monitor 
http://anuj-singh.blogspot.com/2022/
=========================================
set linesize 300 pagesize 300 
VARIABLE snap_start NUMBER
VARIABLE snap_end NUMBER
VARIABLE dbid NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :snap_start from dba_hist_snapshot ;
exec select max(snap_id) into :snap_end from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;


-- define last_x_days=1
select /*+parallel(4) */ * from (
select p1, sql_id,count(*),(ratio_to_report(count(*)) over ())*100 pct from dba_hist_active_sess_history
where event='library cache: mutex X'
--and snap_id between :snap_start and :snap_end
and dbid = :dbid
--and	 sample_time >= sysdate - &&last_x_days
group by p1, sql_id
order by count(*) desc)
where rownum <= 10;





define sql_id='41770y3h89crb'

col PLAN_TABLE_OUTPUT for a150
-- tab=Plans (AWR)
col PLAN_TABLE_OUTPUT for a100
select * from table(dbms_xplan.display_awr('&sql_id', null, null, 'ADVANCED'));


set linesize 200
-- tab=Plan (Current)
col PLAN_TABLE_OUTPUT for a150
select * from table(dbms_xplan.display_cursor('&sql_id', null, 'ADVANCED'));


===============================================================================
alter session set "_push_join_predicate" = FALSE ;

SET PAGESIZE 5000 LINESIZE 1000 TRIMSPOOL ON TRIMOUT ON TAB OFF

VAR dbid NUMBER
VAR inst_id NUMBER



BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database;
END;
/


COL bdate NEW_VALUE def_bdate
COL edate NEW_VALUE def_edate

SET TERMOUT OFF

SELECT
    TO_CHAR(SYSDATE-1/24, 'YYYY-MM-DD HH24:MI') bdate
  , TO_CHAR(SYSDATE     , 'YYYY-MM-DD HH24:MI') edate
FROM
    dual
/



define bdate='2023-01-16 01:01'
define edate='2023-01-16 08:01'



DEFINE 3="timestamp'2023-01-16 14:52:00'"
DEFINE 4="timestamp'2023-01-16 15:52:00'"


define sqlid='15j17u2rhgzca'



set linesize 300

SELECT
    TO_CHAR(sample_time, 'YYYY-MM-DD HH24')||':00' sample_hour
  , sql_id
  , sql_plan_hash_value
  , COUNT(*) * 10 seconds
  , ROUND(COUNT(*) * 10 / 3600, 2) AAS
  , COUNT(DISTINCT sql_exec_start||sql_exec_id) sampled_execs
FROM 
    dba_hist_active_sess_history
WHERE 1=1
   --  dbid = :dbid
-- AND instance_number = :inst_id
AND sample_time BETWEEN &3 AND &4
AND sql_id LIKE '&sqlid'
GROUP BY
    TO_CHAR(sample_time, 'YYYY-MM-DD HH24')||':00'
  , sql_id
  , sql_plan_hash_value
ORDER BY
    sample_hour
  , sql_id
/






define bdate='2023-01-16 01:01'
define edate='2023-01-16 08:01'


SET PAGESIZE 0 HEADING OFF




col PLAN_TABLE_OUTPUT for a120

PROMPT
PROMPT =======================================================================================================
PROMPT =========================================== Plan (Current) ============================================
PROMPT =======================================================================================================
PROMPT
-- tab=Plan (Current)
select * from table(dbms_xplan.display_cursor('&sqlid', null, 'ADVANCED'));

PROMPT
PROMPT =======================================================================================================
PROMPT =========================================== DISPLAY_SQLSET ============================================
PROMPT =======================================================================================================
PROMPT

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQLSET('&sqlset_name', '&sqlid', null, 'TYPICAL +ALIAS +MEMSTATS +NOTE'));

PROMPT
PROMPT =======================================================================================================
PROMPT ============================================= DISPLAY_AWR =============================================
PROMPT =======================================================================================================
PROMPT

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid', null, null, 'ALL'))
/

PROMPT
PROMPT =======================================================================================================
PROMPT =============================== ASH Report for SQL ID &sqlid ===================================
PROMPT =======================================================================================================
PROMPT

define sql_id='ga5m3ggy97233'
SET LINESIZE 200 PAGESIZE 100
SELECT * FROM table(dbms_xplan.display_cursor(sql_id=>'&sql_id',format=>'ALLSTATS LAST +COST +BYTES +predicate +note +adaptive +report +outline'));

ww
define sql_id='36psp2j8kf741'
SET LINESIZE 200 PAGESIZE 100
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(sql_id=>'&sql_id',format=>'ALLSTATS LAST +COST +BYTES +predicate +note +adaptive +report +outline'));


set linesize 200 pagesize 0
select * from table(dbms_xplan.display_awr(sql_id=>'&sql_id', format=>'ALLSTATS LAST +cost +bytes +outline +PEEKED_BINDS +adaptive'));

19c
set linesize 150 pagesize 300
select * from dbms_xplan.display_cursor(sql_id=>'&sql_id',format=>'+HINT_REPORT');


VAR dbid NUMBER
VAR inst_id NUMBER
BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database;
END;
/



SET VERIFY ON
-- Oracle 10.2 requires the :inst_id to be present (and not NULL)
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'YYYY-MM-DD HH24:MI'), TO_DATE('&edate', 'YYYY-MM-DD HH24:MI'), null, null, null, DECODE('&sqlid', '%', NULL, '&sqlid')))
WHERE exists (SELECT version FROM v$instance WHERE version LIKE '10%')
UNION ALL
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, NULL, TO_DATE('&bdate', 'YYYY-MM-DD HH24:MI'), TO_DATE('&edate', 'YYYY-MM-DD HH24:MI'), null, null, null, DECODE('&sqlid', '%', NULL, '&sqlid')))
WHERE exists (SELECT version FROM v$instance WHERE version LIKE '12%')
/
 last 24 Hr 
 
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select min(snap_id)  into :BgnSnap from dba_hist_snapshot where 1=1 and BEGIN_INTERVAL_TIME> sysdate -1;

exec select max(snap_id) into :EndSnap from dba_hist_snapshot where 1=1 and BEGIN_INTERVAL_TIME> sysdate -1;

exec select DBID into :DID from v$database;

 
set linesize 200 trimspool on pagesize 60 verify off
column begin_interval_time format a35
column end_interval_time format a35

break on sql_id skip 1 on instance_number

column sdate new_value sdt noprint

select to_char(sysdate, 'DD-MM-YYYY HH:MI') sdate from dual;



prompt 
prompt  Historic
prompt 
prompt  Elapsed by exec
prompt 




set linesize 500 pagesize 500
 
 define 1=3600  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<----------------


select distinct x.instance_number, x.sql_id, x.time_per_exec, x.elapsed_time_total, s.begin_interval_time, s.end_interval_time
from
(select instance_number, sql_id, snap_id,
       round((elapsed_time_total/1000000)/(case when executions_total = 0 then 1 else executions_total end),4) time_per_exec,
       round(elapsed_time_total/1000000, 4) elapsed_time_total
from dba_hist_sqlstat) x, (select snap_id, 
                                  max(begin_interval_time) begin_interval_time, 
                                  max(end_interval_time) end_interval_time
                           from dba_hist_snapshot 
						   where 1=1
						  AND snap_id BETWEEN :BgnSnap AND :EndSnap
                           group by snap_id) s
where s.snap_id = x.snap_id 
and x.time_per_exec > &&1
and x.time_per_exec <> x.elapsed_time_total
order by 2 asc, 3 desc, 6 desc
/



INSTANCE_NUMBER SQL_ID        TIME_PER_EXEC ELAPSED_TIME_TOTAL BEGIN_INTERVAL_TIME                 END_INTERVAL_TIME
--------------- ------------- ------------- ------------------ ----------------------------------- -----------------------------------
              1 c45yd6m5kjfch    22946.2387         68838.7162 20-FEB-23 11.45.01.927 PM           21-FEB-23 12.00.05.625 AM
                                 22069.6875          44139.375 20-FEB-23 11.00.50.677 PM           20-FEB-23 11.15.54.481 PM
                                 21564.9571         64694.8714 20-FEB-23 11.30.58.344 PM           20-FEB-23 11.45.01.927 PM
                                 18233.7789         54701.3368 20-FEB-23 11.15.54.481 PM           20-FEB-23 11.30.58.344 PM
                                 16728.0404         33456.0808 20-FEB-23 10.45.46.776 PM           20-FEB-23 11.00.50.677 PM

              1 cn6j67uy5wtuv    23579.6219         94318.4875 20-FEB-23 10.00.34.840 PM           20-FEB-23 10.15.38.902 PM
                                 21425.9366         64277.8097 20-FEB-23 09.15.22.528 PM           20-FEB-23 09.30.26.852 PM
                                  21423.494          42846.988 20-FEB-23 08.45.14.057 PM           20-FEB-23 09.00.18.314 PM
                                 21422.2596         85689.0382 20-FEB-23 09.45.30.905 PM           20-FEB-23 10.00.34.840 PM
                                 18719.6677         74878.6707 20-FEB-23 09.30.26.852 PM           20-FEB-23 09.45.30.905 PM
                                 17813.0843         53439.2528 20-FEB-23 09.00.18.314 PM           20-FEB-23 09.15.22.528 PM
                                 16032.9147         32065.8294 20-FEB-23 08.30.08.081 PM           20-FEB-23 08.45.14.057 PM


12 rows selected.





clear breaks

 alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

col last_active_time for a37
prompt 
prompt  Elapsed time total, current statements
prompt 

select inst_id,
       sql_id,
       executions,
       round(elapsed_time/1000000, 6) elapsed_sec,
       round((elapsed_time/1000000)/(case when executions = 0 then 1 else executions end), 6) elapsed_per_exec,
       last_active_time
from gv$sqlstats
where elapsed_time/1000000 > &&1
and LAST_ACTIVE_TIME > sysdate -1
order by 4 desc
/





prompt 
prompt  Elapsed per exec, current statements
prompt 

select inst_id,
       sql_id,
       executions,
       round(elapsed_time/1000000, 6) elapsed_sec,
       round((elapsed_time/1000000)/(case when executions = 0 then 1 else executions end), 6) elapsed_per_exec,
       last_active_time
from gv$sqlstats
where elapsed_time/1000000 > &&1
and LAST_ACTIVE_TIME > sysdate -1
order by 5 desc
/




prompt
prompt SQL text for current statements
prompt

set long 1000000 linesize 200 trimspool on
column sql_text format a70 wrap

select sql_id, sql_text
from dba_hist_sqltext
where sql_id in
(
select sql_id
from gv$sqlstats
where elapsed_time/1000000 > &&1
and LAST_ACTIVE_TIME > sysdate -1
);









set linesize 80 trimspool off


SET TERMOUT ON PAGESIZE 300 HEADING ON

====

define Enter_SQL_Text='ORDER BY owner, object_type, object_name'

col SQL_TEXT for a100 wrap 
select CON_ID,sql_id,SQL_TEXT from dba_hist_sqltext 
where 1=1
and sql_text like '%&Enter_SQL_Text%'
;




define Enter_SQL_Text='FROM all_ind_statistics'
set linesize 200 pagesize 300
Select plan_table_output From dba_hist_sqltext s,Table(dbms_xplan.display_cursor(s.sql_id)) t
Where s.sql_text like '%&Enter_SQL_Text%'
;


for csv file 

set linesize 500 pagesize 300
 alter session set nls_date_format='dd-mm-yyyy hh24:mi'; 
select 
ss.con_id||'~'||
BEGIN_INTERVAL_TIME||'~'||sys_context('userenv','instance_name')||'~'||stat.sql_id||'~'||rpad(parsing_schema_name,10)||'~'||elapsed_time_total/1000000||'~'||disk_reads_total||'~'||stat.executions_total||'~'||ltrim(rtrim(replace(sql_text,'~'))) "SQL detail"
from dba_hist_sqlstat stat,
dba_hist_sqltext txt,
dba_hist_snapshot ss
where stat.sql_id = txt.sql_id
and stat.dbid = txt.dbid
and ss.dbid = stat.dbid
and ss.instance_number = stat.instance_number
and stat.snap_id = ss.snap_id
--and parsing_schema_name not like 'sys%'
and ss.begin_interval_time >= sysdate-30
and stat.elapsed_time_total/1000000 > 20
--and parsing_schema_name not in ('SYS','SYSMAN','SYSTEM') 
and txt.sql_id='cdmqjc2dtv99z'
order by elapsed_time_total desc
;

====
-- sqlid.sql
define 1='byprhpurth000'
define 2='%'



set linesize 500
col cpu_sec_exec   FOR 999999.999
col ela_sec_exec   FOR 999999.999
col lios_per_exec  FOR 9999999999
col pios_per_exec  FOR 9999999999
col SQL_SQL_TEXT for a70 wrap


select 
inst_id,
sql_id,
con_id,
	hash_value,
	plan_hash_value,
	child_number	sql_child_number,
	sql_text sql_sql_text
from 
	gv$sql 
where 
	sql_id = ('&1')
and child_number like '&2'
order by
	sql_id,
	hash_value,
	child_number
/


col PARSING_SCHEMA_NAME for a15
select 
sql_id,
con_id,
PARSING_SCHEMA_NAME,
	child_number	sql_child_number,
	plan_hash_value plan_hash,
	parse_calls parses,
	loads h_parses,
	executions,
	fetches,
	rows_processed,
  rows_processed/nullif(fetches,0) rows_per_fetch,
	ROUND(cpu_time/NULLIF(executions,0)/1000000,3)     cpu_sec_exec,
	ROUND(elapsed_time/NULLIF(executions,0)/1000000,3) ela_sec_exec,
	ROUND(buffer_gets/NULLIF(executions,0),3)  lios_per_exec,
	ROUND(disk_reads/NULLIF(executions,0),3)   pios_per_exec,
	ROUND(cpu_time/1000000,3) total_cpu_sec,
	ROUND(elapsed_time/1000000,3) total_ela_sec,
  user_io_wait_time/1000000 total_iowait_sec,
	buffer_gets total_LIOS,
	disk_reads total_pios,
	sorts
  ,	users_executing
  , last_active_time
	, address		parent_handle
	, child_address   object_handle
from 
	gv$sql
where 
	sql_id = ('&1')
and child_number like '&2'
order by
	sql_id,
	hash_value,
	child_number
/


select 
st.sql_id,
con_id ,
st.inst_id as "INST",
    st.executions as execs,
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
from gv$sqlstats st
where sql_id in ('&&1')
/
====
define sql_id='5u4sk8mw75cas' SELECT TOP_SESSIONS.sql_id, TOP_SESSIONS.sql_plan_hash_value, HIST_SQLTEXT.sql_text FROM( SELECT SESS_HISTORY.sql_id, SESS_HISTORY.sql_plan_hash_value, SUM(10) ash_secs FROM DBA_HIST_SNAPSHOT HIST_SNAPSHOT, DBA_HIST_ACTIVE_SESS_HISTORY SESS_HISTORY WHERE 1=1 -- AND SESS_HISTORY.sample_time BETWEEN (SYSDATE-5) AND (SYSDATE-1) and SESS_HISTORY.snap_id = HIST_SNAPSHOT.snap_id AND SESS_HISTORY.dbid = HIST_SNAPSHOT.dbid AND SESS_HISTORY.instance_number = HIST_SNAPSHOT.instance_number -- AND SESS_HISTORY.module = 'MY_MODULE' GROUP BY SESS_HISTORY.sql_id, SESS_HISTORY.sql_plan_hash_value ORDER BY ash_secs DESC )TOP_SESSIONS, DBA_HIST_SQLTEXT HIST_SQLTEXT WHERE TOP_SESSIONS.sql_id = HIST_SQLTEXT.sql_id -- AND HIST_SQLTEXT.sql_text LIKE '%ORDER BY%' AND HIST_SQLTEXT.sql_id='5u4sk8mw75cas' AND ROWNUM < 30; set linesize 300 pagesize 300 col ASH for a100 col WAIT_CLASS for a15 --Select sample_time , session_id , username, sql_id , sql_plan_hash_value, session_state , blocking_session , event , wait_class , program , machine --, select 'SELECT * FROM TABLE (DBMS_XPLAN.display_awr ('''||sql_id||''','''||sql_plan_hash_value||''', NULL,''ADVANCED''))' "ASH" from dba_hist_active_sess_history a , dba_users b where 1=1 --and session_state= 'WAITING' --and sample_time between to_date('27-DEC-2019 01:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('27-DEC-2019 01:40:10','DD-MON-YYYY HH24:MI:SS') and a.user_id = b.user_id -- and session_id = 501 --and machine like '%TPV%' and sql_id='5u4sk8mw75cas' order by sample_time ; ASH ---------------------------------------------------------------------------------------------------- SELECT * FROM TABLE (DBMS_XPLAN.display_awr ('5u4sk8mw75cas','3583698577', NULL,'ADVANCED'))


===
from web alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; define sql_id='5u4sk8mw75cas' set linesize 500 pagesize 300 col USERNAME for a20 col ELAPSED for a20 col WAIT_OBJECT for a40 select con_id,ash.sql_id, ash.sql_exec_start, ash.sql_exec_id, ash.sql_plan_hash_value , ash.session_id, ash.session_serial#, u.username , case when current_obj# > 0 then ( select distinct o.owner || '.' || o.object_name || rtrim('.' || o.subobject_name,'.') from dba_objects o where o.object_id = current_obj# ) end as wait_object , round(100*(ratio_to_report(count(*)) over())) as percent , cast(numtodsinterval(count(*),'SECOND') as interval day(0) to second(0)) as elapsed , sum(ash.delta_read_io_bytes) as read_bytes , sum(ash.delta_write_io_requests) as write_bytes , ash.sql_child_number, ash.qc_session_id from gv$active_session_history ash join dba_users u on u.user_id = ash.user_id where ash.sql_id = '&sql_id' and ash.sql_exec_id is not null group by con_id,ash.sql_id, ash.sql_exec_start,ash.sql_plan_hash_value, ash.sql_child_number, ash.qc_session_id, ash.session_id, ash.session_serial#, u.username, ash.sql_exec_id, ash.current_obj# order by ash.sql_exec_start; define sql_id='5u4sk8mw75cas' set linesize 500 pagesize 300 col USERNAME for a20 col ELAPSED for a20 col WAIT_OBJECT for a40 col CURRENT_PROCEDURE for a20 col TOP_LEVEL_CALL for a20 select ash.con_id,ash.sql_id, ash.sql_exec_start, ash.sql_exec_id, ash.sql_plan_hash_value, ash.sql_child_number , cast(numtodsinterval(count(*),'SECOND') as interval day(0) to second(0)) as elapsed , ash.qc_session_id, ash.session_id, ash.session_serial# -- , sum(io.block_gets) as block_gets, sum(io.consistent_gets) as consistent_gets, sum(io.physical_reads) as physical_reads, sum(io.consistent_changes) as consistent_changes , tls.sql_text as top_level_call , rtrim(p.owner ||'.'|| p.object_name ||'.'|| p.procedure_name,'.') as current_procedure from gv$active_session_history ash left join dba_procedures p on p.object_id = ash.plsql_object_id and nvl(p.subprogram_id,.5) = nvl(ash.plsql_subprogram_id,.5) left join v$sqlstats tls on tls.sql_id = ash.top_level_sql_id where ash.sql_id = '&sql_id' -- and ash.sql_exec_id is not null group by ash.con_id,ash.sql_id, ash.sql_exec_start,ash.sql_plan_hash_value, ash.sql_child_number, ash.qc_session_id, ash.session_id, ash.session_serial#, ash.sql_exec_id , tls.sql_text , p.owner, p.object_name, p.procedure_name order by min(ash.sample_time), ash.sql_exec_start, ash.sql_exec_id, ash.qc_session_id nulls first; select * from ( with plan_objects as ( select --+ materialize p.object_owner , p.object_name , p.object_type , p.partition_start , p.partition_stop , p.cardinality , p.operation , p.options , count(*) as occurs_in_plan from gv$sql_plan_statistics_all p where 1=1 -- and p.sql_id = '&sql_id' and p.plan_hash_value = ( select plan_hash_value from ( select plan_hash_value, row_number() over (order by timestamp desc) as seq from gv$sql_plan p where 1=1 -- and p.sql_id = '&sql_id' and p.inst_id = 1 ) where seq = 1 ) and p.object_type != 'VIEW' group by p.object_owner, p.object_name, p.object_type, p.partition_start, p.partition_stop, p.cardinality, p.operation, p.options ) , object_stats as ( select ts.owner as object_owner , ts.table_name as object_name , ts.table_name as display_name , ts.num_rows , ts.blocks , ts.last_analyzed , ts.stale_stats from dba_tab_statistics ts where (ts.owner, ts.table_name) in (select object_owner, object_name from plan_objects where object_type like 'TABLE%') and ts.partition_name is null union select xs.owner , xs.index_name , '(' || xs.table_name || ') ' || index_name as display_name , xs.num_rows , xs.leaf_blocks as blocks , xs.last_analyzed , xs.stale_stats from dba_ind_statistics xs where (xs.owner, xs.index_name) in (select object_owner, object_name from plan_objects where object_type like 'INDEX%') and xs.partition_name is null ) select --+ dynamic_sampling(8) object_owner , o.object_type , nvl(s.display_name,object_name) as object_name , s.stale_stats as "Stale?" -- , o.occurs_in_plan , o.operation || ' ' || o.options as operation , o.cardinality , s.num_rows as "Rows (global)" , s.blocks , s.last_analyzed , o.partition_start , o.partition_stop from plan_objects o left join object_stats s using(object_owner, object_name) order by case object_owner when 'SYS' then 2 else 1 end , object_owner , ltrim(object_name,'(') ); define sql_id='gz5ctcz5fwr2f' set linesize 300 col NAME for a20 col VALUE_STRING for a30 select sql_id,name, value_string, datatype_string, last_captured from ( select distinct b.sql_id,name, value_string, datatype_string, b.last_captured, dense_rank() over(partition by name order by last_captured desc) as capture_seq from dba_hist_sqlbind b where 1=1 and b.sql_id = '&sql_id' and b.was_captured = 'YES' ) where capture_seq = 1 order by lpad(ltrim(name,':B'),30); set long 50000 select s.sql_id, a.name as command_type, s.sql_text from dba_hist_sqltext s left join ( select action, name from audit_actions union select 189, 'MERGE' from dual ) a on a.action = s.command_type where s.sql_id = '&sql_id' ;


-- SQL_HISTORY.sql undefine sql_id define sql_id='gw99xca0x0d0h' set null null --set lines 420 set pages 99 set trimspool on col snap_beg format a17 col iowait_delta format 99999999.99 heading io|wait|delta|(ms) col iowait_total format 99999999.99 heading io|wait|total|(ms) col ELAPSED_TIME_DELTA format 99999999.99 heading elapsd|time|delta|(ms) col CPU_TIME_DELTA format 99999999.99 heading cpu|time|delta|(ms) col PLAN_HASH_VALUE heading plan_hash|value col CONCURRENCY_WAIT_delta format 99999999.99 heading conc|wait|delta|(ms) col CLUSTER_WAIT_DELTA format 99999999.99 heading clust|wait|delta|(ms) col PX_SERVERS_EXECS_DELTA format 99999 heading PXServ|Exec|delta col APWAIT_DELTA format 99999 heading appl|wait|time|delta(micro) col PLSEXEC_TIME_DELTA format 99999 heading plsql|exec|time|delta(micro) col JAVAEXEC_TIME_DELTA format 99999 heading java|exec|time|delta(micro) col optimizer_cost format 9999 heading opt|cost col optimizer_mode format a10 heading optim|mode col kept_versions format 999 heading kept|vers col invalidations_delta format 999 heading inv|alid|dlt col parse_calls_delta format 99999 heading parse|calls|delta col executions_delta format 999999 heading exec|delta col fetches_delta format 9999999 heading fetches|delta col end_of_fetch_count_delta format 99999 heading end|of|fetch|call|delta col buffer_gets_delta format 99999999999 heading buffer|gets|delta col disk_reads_delta format 9999999999 heading disk|reads|delta col DIRECT_WRITES_DELTA format 99999999 heading direct|writes|delta col rows_processed_delta format 999999999 heading rows|processed|delta col rows_ex format 99999999 heading rows|exec col snap_id format 99999 heading snap|id col ela_ex format 99999999.99 heading elapsed|per|execution col cwt_ex format 99999999.99 heading cwt|per|execution col cc_ex format 99999999.99 heading cc|per|execution col io_ex format 99999999.99 heading io|per|execution col instance_number format 99 heading in|ID select dba_hist_sqlstat.instance_number, sql_id, plan_hash_value, dba_hist_sqlstat.snap_id, to_char(dba_hist_snapshot.BEGIN_INTERVAL_TIME,'DY dd-mm hh24:mi') snap_beg, invalidations_delta, parse_calls_delta, executions_delta, px_servers_execs_delta, fetches_delta, buffer_gets_delta, disk_reads_delta, direct_writes_delta, rows_processed_delta, elapsed_time_delta/1000 elapsed_time_delta, cpu_time_delta/1000 cpu_time_delta, iowait_delta/1000 iowait_delta, clwait_delta/1000 cluster_wait_delta, ccwait_delta/1000 concurrency_wait_delta, substr(optimizer_mode,1,3) opt, case when executions_delta = 0 then NULL when cpu_time_delta = 0 then NULL else (cpu_time_delta/executions_delta)/1000 end cpu_ex, case when executions_delta = 0 then NULL when elapsed_time_delta = 0 then NULL else (elapsed_time_delta/executions_delta)/1000 end ela_ex ,substr(SQL_PROFILE,1,32) sql_profile from dba_hist_sqlstat, dba_hist_snapshot where sql_id='&&sql_id' and dba_hist_sqlstat.snap_id=dba_hist_snapshot.snap_id and dba_hist_sqlstat.instance_number=dba_hist_snapshot.instance_number order by dba_hist_sqlstat.instance_number, plan_hash_value, dba_hist_sqlstat.snap_id / select dba_hist_sqlstat.instance_number, sql_id, plan_hash_value, dba_hist_sqlstat.snap_id, to_char(dba_hist_snapshot.BEGIN_INTERVAL_TIME,'DY dd-mm hh24:mi') snap_beg, invalidations_delta, parse_calls_delta, executions_delta, elapsed_time_delta/1000 elapsed_time_delta, cpu_time_delta/1000 cpu_time_delta, iowait_delta/1000 iowait_delta, clwait_delta/1000 cluster_wait_delta, ccwait_delta/1000 concurrency_wait_delta, substr(optimizer_mode,1,3) opt, case when executions_delta = 0 then NULL when rows_processed_delta = 0 then NULL else (rows_processed_delta/executions_delta) end rows_ex, case when executions_delta = 0 then NULL when iowait_delta = 0 then NULL else (iowait_delta/executions_delta)/1000 end io_ex, case when executions_delta = 0 then NULL when clwait_delta = 0 then NULL else (clwait_delta/executions_delta)/1000 end cwt_ex, case when executions_delta = 0 then NULL when ccwait_delta = 0 then NULL else (ccwait_delta/executions_delta)/1000 end cc_ex, case when executions_delta = 0 then NULL when cpu_time_delta = 0 then NULL else (cpu_time_delta/executions_delta)/1000 end cpu_ex, case when executions_delta = 0 then NULL when elapsed_time_delta = 0 then NULL else (elapsed_time_delta/executions_delta)/1000 end ela_ex from dba_hist_sqlstat, dba_hist_snapshot where sql_id='&&sql_id' and dba_hist_sqlstat.snap_id=dba_hist_snapshot.snap_id and dba_hist_sqlstat.instance_number=dba_hist_snapshot.instance_number order by dba_hist_sqlstat.instance_number, plan_hash_value, dba_hist_sqlstat.snap_id / select plan_table_output from table (dbms_xplan.display_awr('&&sql_id',null, null, 'ADVANCED +PEEKED_BINDS')); select plan_table_output from table (dbms_xplan.display_cursor('&&sql_id', null, 'ADVANCED +PEEKED_BINDS')); set lines 120 set verify off undefine SQL_ID accept SQL_ID char prompt 'Enter SQL ID: ' column "Cost" format 999,999,999 column "Module" format a15 column "Schema" format a15 column "Buffer Gets" format 999,999,999 column "Elapsed Time" format 999,999,999 column "Snapshot Time" format a30 SELECT optimizer_cost "Cost", module "Module", parsing_schema_name "Schema", buffer_gets_total "Buffer Gets", elapsed_time_delta "Elapsed Time", end_interval_time "Snapshot Time" FROM dba_hist_sqlstat st, dba_hist_snapshot ss WHERE st.SQL_ID = '&SQL_ID' AND ss.snap_id = st.snap_id ORDER BY "Snapshot Time" DESC / define t='' --define sql_id='gw99xca0x0d0h' set lines 400 pages 300 col parsed format a15 col sql_text format a40 select stat.snap_id, txt.sql_id, to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime, parsing_schema_name as parsed, elapsed_time_delta/1000/1000 as elapsed_sec, sql_text from dba_hist_sqlstat stat, dba_hist_sqltext txt, dba_hist_snapshot snap where 1=1 and stat.sql_id=txt.sql_id and stat.snap_id=snap.snap_id and snap.begin_interval_time>=sysdate-1 -- and lower(sql_text) like '%&t%' -- and parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS') and txt.sql_id='&sql_id' order by elapsed_time_delta asc;

===
select inst_id,min(sample_time) starttime,max(sample_time)endtime,count(distinct sample_time) snaps from gv$active_session_history group by inst_id; set linesize 300 col STARTTIME for a27 col ENDTIME for a27 select inst_id, extract (hour from sample_time) hh, min(sample_time) starttime, max(sample_time) endtime, count(distinct sample_time) snaps from gv$active_session_history group by inst_id, extract (hour from sample_time) order by 1,2; set linesize 300 col STARTTIME for a27 col ENDTIME for a27 select INSTANCE_NUMBER, extract (hour from sample_time) hh, min(sample_time) starttime, max(sample_time) endtime, count(distinct sample_time) snaps from dba_hist_active_sess_history group by INSTANCE_NUMBER, extract (hour from sample_time) order by 1,2; define sql_id='gw99xca0x0d0h' col DURING for a27 select inst_id, sql_id, min(sample_time) starttime, max(sample_time) endtime, max(sample_time)-min(sample_time) during from gv$active_session_history --where SQL_PLAN_HASH_VALUE=2391750817 where sql_id='&sql_id' group by inst_id, sql_id order by 2,3; -- Analysis problem SQL gw99xca0x0d0h -- Execution plan for time consumption select inst_id, IS_SQLID_CURRENT, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, SQL_PLAN_OPERATION, count(*) es_sec from gv$active_session_history where sql_id='&sql_id' group by inst_id, IS_SQLID_CURRENT, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, SQL_PLAN_OPERATION order by 6 desc; col IS_SQLID_CURRENT for a20 select inst_id, IS_SQLID_CURRENT, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, SQL_PLAN_OPERATION, count(*) es_sec from gv$active_session_history where sql_id='&sql_id' group by inst_id, IS_SQLID_CURRENT, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, SQL_PLAN_OPERATION order by 3 desc; col IS_SQLID_CURRENT for a20 select inst_id, IS_SQLID_CURRENT, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, SQL_PLAN_OPERATION, count(*) es_sec from gv$active_session_history where sql_id='&sql_id' group by inst_id, IS_SQLID_CURRENT, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, SQL_PLAN_OPERATION order by 3 desc; col IS_SQLID_CURRENT for a20 select inst_id, IS_SQLID_CURRENT, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, SQL_PLAN_OPERATION, count(*) es_sec from gv$active_session_history where sql_id='&sql_id' group by inst_id, IS_SQLID_CURRENT, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, SQL_PLAN_OPERATION order by 3 desc; -- top event select inst_id, CASE WHEN h.SESSION_STATE != 'WAITING' THEN 'On CPU / runqueue' ELSE event end as event , SUM(h.wait_time + h.time_waited)/1000000 "Total Wait Time", SESSION_STATE, BLOCKING_SESSION, count(*) es_sec from gv$active_session_history h where sql_id='&sql_id' group by inst_id, event, SESSION_STATE, BLOCKING_SESSION order by 6 desc; -- top call col EVENT for a27 col TOP_LEVEL_CALL_NAME for a18 col IN_PARSE for a12 col IN_SQL_EXECUTION for a20 select inst_id, CASE WHEN h.SESSION_STATE != 'WAITING' THEN 'On CPU / runqueue' ELSE event end as event , SUM(h.wait_time + h.time_waited)/1000000 "Total Wait Time", TOP_LEVEL_CALL_NAME, IN_PARSE, IN_SQL_EXECUTION , count(*) es_sec from gv$active_session_history h where sql_id='&sql_id' -- and SQL_PLAN_LINE_ID=36 group by inst_id, SESSION_STATE, event, TOP_LEVEL_CALL_NAME, IN_PARSE, IN_SQL_EXECUTION; select inst_id, CASE WHEN h.SESSION_STATE != 'WAITING' THEN 'On CPU / runqueue' ELSE event end as event , SUM(h.wait_time + h.time_waited)/1000000 "Total Wait Time", program,TOP_LEVEL_CALL_NAME , count(*) es_sec from gv$active_session_history h where sql_id='&sql_id' --and SQL_PLAN_LINE_ID=36 group by inst_id, program, event, TOP_LEVEL_CALL_NAME, SESSION_STATE ;
===


set linesize 500 pagesize 300 define start_date='23-06-23 08:44' define end_date='23-06-23 08:55' alter session set nls_date_format='dd-mm-YY HH24:MI'; alter session set nls_timestamp_format='dd-mm-YY HH24:MI'; col END_TIME for a27 select con_id, sql_id, starting_time, end_time, (EXTRACT(HOUR FROM run_time) * 3600 + EXTRACT(MINUTE FROM run_time) * 60 + EXTRACT(SECOND FROM run_time)) run_time_sec, READ_IO_BYTES, PGA_ALLOCATED PGA_ALLOCATED_BYTES, TEMP_ALLOCATED TEMP_ALLOCATED_BYTES from ( select con_id, sql_id, max(sample_time - sql_exec_start) run_time, max(sample_time) end_time, sql_exec_start starting_time, sum(DELTA_READ_IO_BYTES) READ_IO_BYTES, sum(DELTA_PGA) PGA_ALLOCATED, sum(DELTA_TEMP) TEMP_ALLOCATED from ( select con_id, sql_id, sample_time, sql_exec_start, DELTA_READ_IO_BYTES, sql_exec_id, greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_PGA, greatest(TEMP_SPACE_ALLOCATED - first_value(TEMP_SPACE_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_TEMP from dba_hist_active_sess_history where 1=1 and sample_time BETWEEN to_date('&start_date','DD-MM-YY HH24:MI') AND to_date('&end_date','DD-MM-YY HH24:MI') and sql_exec_start is not null and IS_SQLID_CURRENT='Y' ) group by con_id, sql_id,SQL_EXEC_ID,sql_exec_start order by sql_id ) where 1=1 -- and sql_id = 'sql_id' order by end_time desc ;


================
define sql_id='1rn43zb7tm2jg' set pages 500 col Snap for a25 col SQL_PROFILE for a40 select distinct a.SQL_ID,b.BEGIN_INTERVAL_TIME as Snap, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes, a.PLAN_HASH_VALUE as Plan, a.EXECUTIONS_DELTA as EXECUTIONS, a.ELAPSED_TIME_DELTA/1000000 as ELAPSED_sec, a.ROWS_PROCESSED_DELTA as "Rows", a.ROWS_PROCESSED_DELTA/CASE WHEN a.EXECUTIONS_DELTA = 0 THEN -1 ELSE a.EXECUTIONS_DELTA END "Avg Rows", a.ELAPSED_TIME_DELTA/1000000/CASE WHEN a.EXECUTIONS_DELTA = 0 THEN -1 ELSE a.EXECUTIONS_DELTA END "Avg Elapsed", a.optimizer_cost, a.SQL_PROFILE from DBA_HIST_SQLSTAT a,DBA_HIST_SNAPSHOT b where a.SQL_ID ='&sql_id' and a.snap_id = b.snap_id order by b.BEGIN_INTERVAL_TIME; set serveroutput on size 1000000 set lines 200 pages 1000 set verify off col begin_time for a16 col end_time for a5 col module for a20 col action for a20 col execs for 999,999,990 heading "Executions" col physrd_exec for 999,999,990 heading "Disk|Reads|per Exec" col logrds_exec for 999,999,990 heading "Buffer|Gets|per Exec" col CPU_EXEC for 999,990.9999 heading "CPU|Time per|Exec(secs)" col ELA_EXEC for 999,990.9999 heading "Elapsed|Time per|Exec(secs)" col drwrites_exec for 999,999,990 heading "Direct|Writes|per Exec" col rowsprc_exec for 999,999,990 heading "Rows|Processed|per Exec" col fetch_EXEC for 999,990 heading "Fetches|per Exec" col sharable_mb for 999,990.99 heading "Sharable|Mem(mb)" col VERSION_COUNT for 999,990 heading "Version|Count" col plan_hash for 999999999999 define start_in='' 25-NOV-2015 07:00 define end_in='' 27-NOV-2015 10:00 select to_char(b.BEGIN_INTERVAL_TIME,'DD-MON-RR HH24:MI') begin_time, to_char(b.END_INTERVAL_TIME,'HH24:MI') end_time, --a.sql_id, a.plan_hash_value plan_hash, --a.invalidations_delta, --a.module, --a.action, a.executions_delta execs, (case when a.executions_delta > 0 then round((a.elapsed_time_delta/a.executions_delta)/1000000,4) when a.executions_delta = 0 then NULL end) ELA_EXEC, (case when a.executions_delta > 0 then round((a.cpu_time_delta/a.executions_delta)/1000000,4) when a.executions_delta = 0 then NULL end) CPU_EXEC, (case when a.executions_delta > 0 then round(a.buffer_gets_delta/a.executions_delta,0) when a.executions_delta = 0 then NULL end) logrds_exec, (case when a.executions_delta > 0 then round(a.disk_reads_delta/a.executions_delta,0) when a.executions_delta = 0 then NULL end) physrd_exec, (case when a.executions_delta > 0 then round(a.DIRECT_WRITES_DELTA/a.executions_delta,0) when a.executions_delta = 0 then NULL end) drwrites_exec, (case when a.executions_delta > 0 then round(a.ROWS_PROCESSED_DELTA/a.executions_delta,0) when a.executions_delta = 0 then NULL end) rowsprc_exec ,round(a.SHARABLE_MEM/1024/1024,2) sharable_mb ,a.VERSION_COUNT --a.disk_reads_delta physrds, --a.buffer_gets_delta logrds, --round(a.cpu_time_delta/1000000,0) "cpu_time(s)", --round(a.elapsed_time_delta/1000000,0) "ela_time(s)", --(case when a.executions_delta > 0 then round((a.FETCHES_DELTA/a.executions_delta)/1000000,0) -- when a.executions_delta = 0 then NULL end) fetch_EXEC -- ,(case when a.executions_delta > 0 then round(a.disk_reads_delta/a.executions_delta,0) when a.executions_delta = 0 then NULL end)/(case when a.executions_delta > 0 then round(a.ROWS_PROCESSED_DELTA/a.executions_delta,0) when a.executions_delta = 0 then NULL end) pr_per_row from DBA_HIST_SQLSTAT a, DBA_HIST_SNAPSHOT b where 1=1 --and a.snap_id between (SELECT MIN(SNAP_ID) FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME >= to_date('&start_in','DD-MON-RRRR HH24:MI:SS')) and (SELECT MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME < to_date('&end_in','DD-MON-RRRR HH24:MI:SS')) and a.snap_id = b.snap_id and a.instance_number = b.instance_number and a.sql_id = '&&sql_id' and a.executions_delta > 0 and BEGIN_INTERVAL_TIME > sysdate - 2 order by b.BEGIN_INTERVAL_TIME,a.plan_hash_value / set long 5000 lines 160 select sql_text from dba_hist_sqltext where sql_id = '&sql_id'; col PLAN_TABLE_OUTPUT for 150 select * from table(dbms_xplan.display_cursor('&sql_id')); select * from table(dbms_xplan.display_awr('&sql_id'));
===
define 1='5dqz0hqtp9fru' define 2='' set linesize 500 col sql_sql_text head SQL_TEXT format a100 word_wrap col sql_child_number head CH# for 999 prompt Show SQL text, child cursors and execution stats for SQLID &1 child &2 select hash_value, plan_hash_value, child_number sql_child_number, sql_text sql_sql_text from gv$sql where sql_id = ('&1') --and child_number like '&2' order by sql_id, hash_value, child_number / select child_number sql_child_number, address parent_handle, child_address object_handle, plan_hash_value plan_hash, parse_calls parses, loads h_parses, executions, fetches, rows_processed, rows_processed/nullif(fetches,0) rows_per_fetch, cpu_time/1000000 cpu_sec, cpu_time/NULLIF(executions,0)/1000000 cpu_sec_exec, elapsed_time/1000000 ela_sec, buffer_gets LIOS, disk_reads PIOS, sorts -- address, -- sharable_mem, -- persistent_mem, -- runtime_mem, -- , PHYSICAL_READ_REQUESTS -- , PHYSICAL_READ_BYTES -- , PHYSICAL_WRITE_REQUESTS -- , PHYSICAL_WRITE_BYTES -- , OPTIMIZED_PHY_READ_REQUESTS -- , IO_CELL_OFFLOAD_ELIGIBLE_BYTES -- , IO_INTERCONNECT_BYTES -- , IO_CELL_UNCOMPRESSED_BYTES -- , IO_CELL_OFFLOAD_RETURNED_BYTES , users_executing from gv$sql where sql_id = ('&1') --and child_number like '&2' order by sql_id, hash_value, child_number / select sql_id, child_number sql_child_number , plan_hash_value plan_hash , CASE WHEN io_cell_offload_eligible_bytes > 0 THEN 'YES' ELSE 'NO' END offload_attempted , ROUND((1-(io_cell_offload_returned_bytes/NULLIF(io_cell_offload_eligible_bytes,0)))*100) scan_offl_saving , ROUND(io_interconnect_bytes / 1048576) tot_ic_xfer_mb , ROUND((1-(io_interconnect_bytes/NULLIF(physical_read_bytes,0)))*100) tot_ic_xfer_saving , ROUND(physical_read_bytes / NULLIF(executions,0) / 1048576) avg_mb_rd_exec , ROUND(physical_read_bytes / NULLIF(physical_read_requests,0) / 1024 ) avg_kb_rd_io , ROUND(physical_write_bytes / NULLIF(executions,0) / 1048576) avg_mb_wr_exec , ROUND(physical_write_bytes / NULLIF(physical_write_requests,0) / 1024 ) avg_kb_wr_io , ROUND(optimized_phy_read_requests / NULLIF(physical_read_requests,0) * 100) pct_optim -- , io_cell_uncompressed_bytes from gv$sql where sql_id = ('&1') --and child_number like '&2' order by sql_id, hash_value, child_number / ====
from Web

set ver off pages 50000 lines 700 tab off set linesize 700 undef sql_id undef days_history undef interval_hours def sql_id='dnm7fqpf08p8w' def days_history="4" def interval_hours="1" col inst for 9999 col time for a19 col executions for 9999999999 col end_time for a20 col rows_processed_1exec for 9999999.999 col elapsed_time_s_1exec for 9999999.999 col cpu_time_s_1exec for 9999999.999 col iowait_s_1exec for 9999999.999 col clwait_s_1exec for 9999999.999 col apwait_s_1exec for 9999999.999 col ccwait_s_1exec for 9999999.999 col plsexec_time_s_1exec for 9999999.999 col javexec_time_s_1exec for 9999999.999 col buffer_gets_1exec for 999999999999.999 col disk_reads_1exec for 999999999999.999 col direct_writes_1exec for 999999999999.999 BREAK ON inst SKIP 1 select hss.instance_number inst, hss.sql_id, hss.plan_hash_value, to_char(trunc(sysdate-&days_history+1)+trunc((cast(hs.end_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24,'dd.mm.yyyy hh24:mi:ss') end_time, sum(hss.executions_delta) executions, round(sum(hss.rows_processed_delta)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) rows_processed_1exec, round(sum(hss.elapsed_time_delta)/1000000/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),3) elapsed_time_s_1exec, round(sum(hss.cpu_time_delta)/1000000/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),3) cpu_time_s_1exec, round(sum(hss.iowait_delta)/1000000/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),3) iowait_s_1exec, round(sum(hss.clwait_delta)/1000000/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),3) clwait_s_1exec, round(sum(hss.apwait_delta)/1000000/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),3) apwait_s_1exec, round(sum(hss.ccwait_delta)/1000000/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),3) ccwait_s_1exec, round(sum(hss.plsexec_time_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) plsexec_time_s_1exec, round(sum(hss.javexec_time_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) javexec_time_s_1exec, round(sum(hss.buffer_gets_delta)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) buffer_gets_1exec, round(sum(hss.disk_reads_delta)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) disk_reads_1exec, round(sum(hss.direct_writes_delta)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) direct_writes_1exec, round(sum(hss.FETCHES_DELTA)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) fetches_1exec, round(sum(hss.PX_SERVERS_EXECS_DELTA)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) PX_1exec, round(sum(hss.PHYSICAL_READ_REQUESTS_DELTA)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) physical_reads_1exec, round(sum(hss.PHYSICAL_WRITE_REQUESTS_DELTA)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) physical_writes_1exec, round(sum(hss.PHYSICAL_READ_BYTES_DELTA/1024/1024)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) physical_reads_Mbytes_1exec, round(sum(hss.PHYSICAL_WRITE_BYTES_DELTA/1024/1024)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) physical_writes_Mbytes_1exec, round(sum(hss.IO_OFFLOAD_ELIG_BYTES_DELTA/1024/1024)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) IO_OFFLOAD_ELIG_MBYTES_1exec, round(sum(hss.IO_INTERCONNECT_BYTES_DELTA/1024/1024)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) IO_INTERCONNECT_MBYTES_1exec, round(sum(hss.OPTIMIZED_PHYSICAL_READS_DELTA)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) OPTIMIZED_PHYSICAL_READS_1exec, round(sum(hss.CELL_UNCOMPRESSED_BYTES_DELTA/1024/1024)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) CELL_UNCOMPRESSED_MBYTES_1exec, round(sum(hss.IO_OFFLOAD_RETURN_BYTES_DELTA/1024/1024)/decode(sum(hss.executions_delta),0,1,sum(hss.executions_delta)),0) IO_OFFLOAD_RETURN_Mbytes_1exec from dba_hist_sqlstat hss, dba_hist_snapshot hs where hss.sql_id in ('&sql_id') and hss.snap_id=hs.snap_id and hss.instance_number=hs.instance_number and hs.end_interval_time>=trunc(sysdate)-&days_history+1 group by hss.instance_number,hss.sql_id,hss.plan_hash_value, trunc(sysdate-&days_history+1)+trunc((cast(hs.end_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24 order by hss.instance_number,hss.plan_hash_value, trunc(sysdate-&days_history+1)+trunc((cast(hs.end_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24; ====

define 1='gmdt6kx159t8c' col OUTLINE_HINTS for a150 select -- sql_id -- , child_number -- , plan_hash_value substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints from xmltable('/*/outline_data/hint' passing ( select xmltype(other_xml) as xmlval from dba_hist_sql_plan where sql_id = '&1' and other_xml is not null ) ) d / OUTLINE_HINTS ------------------------------------------------------------------------------------------------------------------------------------------------------ IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$3") OUTLINE_LEAF(@"SET$1") FULL(@"SEL$3" "T"@"SEL$3") INDEX(@"SEL$3" "S"@"SEL$3" "I_FILE#_BLOCK#") INDEX_RS_ASC(@"SEL$3" "TCP"@"SEL$3" ("TABCOMPART$"."OBJ#")) INDEX(@"SEL$3" "TAB"@"SEL$3" "I_OBJ#") LEADING(@"SEL$3" "T"@"SEL$3" "S"@"SEL$3" "TCP"@"SEL$3" "TAB"@"SEL$3") USE_NL(@"SEL$3" "S"@"SEL$3") USE_NL(@"SEL$3" "TCP"@"SEL$3") USE_NL(@"SEL$3" "TAB"@"SEL$3") FULL(@"SEL$2" "S"@"SEL$2") FULL(@"SEL$2" "T"@"SEL$2") INDEX(@"SEL$2" "TAB"@"SEL$2" "I_OBJ#") LEADING(@"SEL$2" "S"@"SEL$2" "T"@"SEL$2" "TAB"@"SEL$2") USE_HASH(@"SEL$2" "T"@"SEL$2") USE_NL(@"SEL$2" "TAB"@"SEL$2") FULL(@"SEL$1" "S"@"SEL$1") FULL(@"SEL$1" "T"@"SEL$1") LEADING(@"SEL$1" "S"@"SEL$1" "T"@"SEL$1") USE_HASH(@"SEL$1" "T"@"SEL$1")


==========

column hash_value new_value hash_value set linesize 300 pagesize 300 col kill for a17 select --DISTINCT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill , s.con_id, s.sql_id,hash_value, sql_text from gv$session s, gv$sqltext t where s.sql_address = t.address -- and s.status = 'ACTIVE' -- and s.sid = &SID order by piece / define sql_id='808hg773r9845'
SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SET LINESIZE 1000 SET PAGESIZE 0 SET TRIM ON SET TRIMSPOOL ON SET ECHO OFF SET FEEDBACK OFF SELECT DBMS_SQLTUNE.report_sql_monitor( sql_id => '&sql_id', type=>'TEXT' , report_level => 'ALL') from dual / define sql_id='808hg773r9845' set pagesize 300 linesize 300 SELECT PLANOUTPUT.* FROM gv$sql s, TABLE(dbms_xplan.display_cursor(s.sql_id, s.child_number,'ALLSTATS LAST')) PLANOUTPUT WHERE 1=1 -- and sql_text LIKE '%XXX%' and s.sql_id in ('&sql_id') and rownum<80 ;

SQL statement will see stale optimizer statistics define sql_id='b24ara4z08008' set pagesize 100 set linesize 150 set trims off set tab off set verify off column table_name format a50 column index_name format a50 column object_type format a40 column owner format a40 PROMPT ========== PROMPT Tables PROMPT ========== with plan_tables as ( select distinct object_name,object_owner, object_type from gv$sql_plan where object_type like 'TABLE%' and sql_id = '&sql_id') select t.object_owner owner, t.object_name table_name, t.object_type object_type, decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness from dba_tab_statistics s, plan_tables t where s.table_name = t.object_name and s.owner = t.object_owner and s.partition_name is null and s.subpartition_name is null order by t.object_owner, t.object_name; PROMPT ========== PROMPT Indexes PROMPT ========== with plan_indexes as ( select distinct object_name,object_owner, object_type from gv$sql_plan where object_type like 'INDEX%' and sql_id = '&sql_id') select i.object_owner owner, i.object_name index_name, i.object_type object_type, decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness from dba_ind_statistics s, plan_indexes i where s.index_name = i.object_name and s.owner = i.object_owner and s.partition_name is null and s.subpartition_name is null order by i.object_owner, i.object_name;

===========================
undef sql_id undef in_phv
set lines 320 pages 300 column begin_interval_time format A30 column prior_pl_hash format 9999999999999999999999 column first_ph format 9999999999999999999999 column first_ph_time format A30 column last_ph format 9999999999999999999999 column last_ph_time format A30 define sql_id='94qn6y14kw01g' with planhashes as ( SELECT s.dbid, s.instance_number, s.snap_id, sn.begin_interval_time, s.sql_id, s.plan_hash_value, LAG(plan_hash_value,1) OVER ( partition by s.dbid,s.instance_number, s.snap_id, s.sql_id ORDER BY s.snap_id ) prior_pl_hash FROM dba_hist_sqlstat s, dba_hist_snapshot sn WHERE s.snap_id = sn.snap_id AND sn.dbid = s.dbid AND s.sql_id ='&sql_id' AND sn.instance_number = s.instance_number -- AND sn.begin_interval_time >= sysdate -(6/24) -- AND sn.begin_interval_time <= sysdate order by s.dbid, s.instance_number, s.snap_id, s.sql_id, sn.begin_interval_time ) select distinct s.dbid, s.instance_number, s.sql_id, first_value(s.plan_hash_value) over ( partition by s.dbid,s.instance_number, s.sql_id, s.plan_hash_value order by s.snap_id rows between unbounded preceding and unbounded following ) first_ph, first_value(s.begin_interval_time) over ( partition by s.dbid,s.instance_number,s.sql_id, s.plan_hash_value order by s.snap_id rows between unbounded preceding and unbounded following ) first_ph_time, last_value(s.plan_hash_value) over ( partition by s.dbid,s.instance_number, s.sql_id, s.plan_hash_value order by s.snap_id rows between unbounded preceding and unbounded following ) last_ph, last_value(s.begin_interval_time) over ( partition by s.dbid,s.instance_number,s.sql_id, s.plan_hash_value order by s.snap_id rows between unbounded preceding and unbounded following ) last_ph_time from planhashes s order by 1,2,3,5,4 ; DBID INSTANCE_NUMBER SQL_ID FIRST_PH FIRST_PH_TIME LAST_PH LAST_PH_TIME ---------- --------------- ------------- ----------------------- ------------------------------ ----------------------- ------------------------------ 948908154 1 94qn6y14kw01g 1388734953 02-AUG-23 06.45.02.508 AM 1388734953 13-AUG-23 02.15.45.997 AM col PLAN_TABLE_OUTPUT for a125 define sql_id='94qn6y14kw01g' define plan_hash_value=1388734953 select plan_table_output from table(dbms_xplan.display_awr('&&sql_id', &&plan_hash_value, null, 'ALL -ALIAS'));

===========


define sql_id='5f5um1hj44z7k' define sql_text='' VARIABLE BgnSnap NUMBER VARIABLE EndSnap NUMBER VARIABLE DID NUMBER VARIABLE INST_NUMBER number exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ; exec select max(snap_id) into :EndSnap from dba_hist_snapshot ; set pages 9999 set long 32000 col sql_text format a40 col execs for 999,999,999 col etime for 999,999,999.9 col avg_etime for 999,999.999 col lio for 999,999,999,999 col avg_lio for 999,999,999,999 col avg_pio for 999,999,999,999 col rows_proc for 999,999,999,999 head rows col begin_interval_time for a30 col node for 99999 col versions for 99999 col percent_of_total for 999.99 break on report compute sum of percent_of_total on report select sql_id, sql_text, avg_pio, avg_lio, avg_etime, execs, rows_proc from ( select dbms_lob.substr(sql_text,3999,1) sql_text, b.* from dba_hist_sqltext a, ( select sql_id, sum(execs) execs, sum(etime) etime, sum(etime)/sum(execs) avg_etime, sum(pio)/sum(execs) avg_pio, sum(lio)/sum(execs) avg_lio, sum(rows_proc) rows_proc from ( select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, nvl(executions_delta,0) execs, elapsed_time_delta/1000000 etime, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, buffer_gets_delta lio, disk_reads_delta pio, rows_processed_delta rows_proc, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, (rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows, (disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where ss.snap_id = S.snap_id and ss.instance_number = S.instance_number -- and ss.snap_id between nvl('starting_snap_id',0) and nvl('ending_snap_id',999999999) and ss.snap_id between :BgnSnap and :EndSnap and executions_delta > 0 ) group by sql_id order by 5 desc ) b where a.sql_id = b.sql_id and execs > 1 ) where rownum <100 --and upper(sql_text) like upper(nvl('%&sql_text%',sql_text)) --and sql_id like nvl('&sql_id',sql_id) -- group by sql_id, sql_text order by etime desc ;


--- Top sql top_level_sql_id <<<<<<<<!!!
set linesize 400 pagesize 300 col SQL_OPNAME for a15 col MODULE for a30 col SQL_END_TIME for a28 col in_hard_parse for a12 col EVENT for a25 with pivot2 as ( SELECT ASH.inst_id, ASH.con_id, ASH.user_id, ASH.session_id sid, ASH.session_serial# serial#, ASH.sql_opname, ASH.module, ASH.top_level_sql_id, ASH.sql_id, ASH.sql_exec_id, ASH.SQL_PLAN_HASH_VALUE, ASH.in_hard_parse, NVL(ASH.sql_exec_start, min(sample_time)) sql_start_time, MAX(sample_time) sql_end_time, (CAST(MAX(sample_time) AS DATE) - CAST( NVL(ASH.sql_exec_start, min(sample_time)) AS DATE)) * 3600*24 etime_secs ,event from gv$active_session_history ASH WHERE 1=1 and ASH.session_type = 'FOREGROUND' and ASH.sql_id is not null and ash.sample_time > sysdate - interval '15' minute and MODULE not in ('OEM','emagent_SQL_oracle_database') group by ASH.inst_id,ASH.con_id, ASH.user_id, ASH.session_id, ASH.session_serial#, ASH.sql_opname, ASH.module, ASH.top_level_sql_id, ASH.sql_id, ASH.sql_exec_id, ASH.sql_plan_hash_value, ASH.in_hard_parse, sql_exec_start,event ) select SM.* from pivot2 SM inner join dba_users T1 on T1.user_id = SM.user_id order by sql_start_time;



*** stale_stats

set linesize 400 define sql_id='3rju5whzbh017' col OWNER for a15 col INDEX_NAME for a15 col TABLE_NAME for a15 col index_name for a15 col partitioned for a15 col global_stats for a15 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('&sql_id', null, 'BASIC')) UNION ALL SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL')) ) where plan_table_output like '%INDEX%' or plan_table_output like '%TABLE%' ) ORDER BY owner, table_name, index_name /

col LAST_ANALYZED for a27 col owner for a15 col index_name for a27 col TABLE_NAME for a27 col partitioned for a15 col global_stats for a12 col PARTITION_NAME for a16 select /*+parallel 4 */ table_name,owner ,partition_name,stale_stats ,LAST_ANALYZED from dba_IND_statistics where stale_stats = 'YES' --or stale_stats is null and table_name in ( SELECT i.table_name FROM dba_indexes i WHERE 1=1 and i.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('&sql_id', null, 'BASIC')) UNION ALL SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL')) ) where plan_table_output like '%INDEX%' ) -- ORDER BY owner, table_name, index_name );

alter session set "_push_join_predicate" = FALSE ; col LAST_ANALYZED for a27 col owner for a15 col index_name for a27 col TABLE_NAME for a27 col partitioned for a15 col global_stats for a12 col PARTITION_NAME for a16 col stale_stats for a15 select table_name,owner ,partition_name,stale_stats ,LAST_ANALYZED from dba_tab_statistics where stale_stats = 'YES' --or stale_stats is null and table_name in ( SELECT i.table_name FROM dba_tables i --,DBA_IND_STATISTICS s WHERE 1=1 and i.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('&sql_id', null, 'BASIC')) UNION ALL SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL')) ) where plan_table_output like '%TABLE%' ) -- ORDER BY owner, table_name, index_name );

set pagesize 100 set linesize 150 set trims off set tab off set verify off column table_name format a50 column index_name format a50 column object_type format a40 column owner format a40 --accept sql_id prompt 'Enter the SQL ID: ' PROMPT ========== PROMPT Tables PROMPT ========== with plan_tables as ( select distinct object_name,object_owner, object_type from v$sql_plan where object_type like 'TABLE%' and sql_id = '&sql_id') select t.object_owner owner, t.object_name table_name, t.object_type object_type, decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness from dba_tab_statistics s, plan_tables t where s.table_name = t.object_name and s.owner = t.object_owner and s.partition_name is null and s.subpartition_name is null order by t.object_owner, t.object_name; PROMPT ========== PROMPT Indexes PROMPT ========== with plan_indexes as ( select distinct object_name,object_owner, object_type from v$sql_plan where object_type like 'INDEX%' and sql_id = '&sql_id') select i.object_owner owner, i.object_name index_name, i.object_type object_type, decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness from dba_ind_statistics s, plan_indexes i where s.index_name = i.object_name and s.owner = i.object_owner and s.partition_name is null and s.subpartition_name is null order by i.object_owner, i.object_name; =====


set linesize 400 col SOURCE for a35 col BEGIN_TIME for a27 col END_TIME for a27 define sql_id='9s45v5rhut05y' SELECT * FROM (SELECT '1.v$sql'||'Instance number:'||GV$SQL.inst_id source, SQL_ID, plan_hash_value, TO_CHAR (FIRST_LOAD_TIME) begin_time, 'In cursor cache' end_time, executions "No. of exec", (buffer_gets / executions) "LIO/exec", (cpu_time / executions / 1000000) "CPUTIM/exec", (elapsed_time / executions / 1000000) "ETIME/exec", (disk_reads / executions) "PIO/exec", (ROWS_PROCESSED / executions) "ROWs/exec" FROM Gv$SQL WHERE sql_id = '&sql_id' UNION ALL SELECT '2.sqltuning set' source, sql_id, plan_hash_value, 'JUST SQLSET NO DATE' begin_time, 'JUST SQLSET NO DATE' end_time, EXECUTIONS "No. of exec", (buffer_gets / executions) "LIO/exec", (cpu_time / executions / 1000000) "CPUTIM/exec", (elapsed_time / executions / 1000000) "ETIME/exec", (disk_reads / executions) "PIO/exec", (ROWS_PROCESSED / executions) "ROWs/exec" FROM dba_sqlset_statements WHERE SQL_ID = '&sql_id' UNION ALL SELECT '3.dba_advisor_sqlstats' source, sql_id, plan_hash_value, 'JUST SQLSET NO DATE' begin_time, 'JUST SQLSET NO DATE' end_time, EXECUTIONS "No. of exec", (buffer_gets / executions) "LIO/exec", (cpu_time / executions / 1000000) "CPUTIM/exec", (elapsed_time / executions / 1000000) "ETIME/exec", (disk_reads / executions) "PIO/exec", (ROWS_PROCESSED / executions) "ROWs/exec" FROM dba_sqlset_statements WHERE SQL_ID = '&sql_id' UNION ALL SELECT DISTINCT '4.dba_hist_sqlstat' || 'Instance number:' || SQL.INSTANCE_NUMBER source, sql_id, PLAN_HASH_VALUE, TO_CHAR (s.BEGIN_INTERVAL_TIME ,'YYYY-MM-DD hh24:mi:ss') begin_time, TO_CHAR (s.END_INTERVAL_TIME,'YYYY-MM-DD hh24:mi:ss') end_time, SQL.executions_delta, SQL.buffer_gets_delta / DECODE (NVL (SQL.executions_delta, 0), 0, 1, SQL.executions_delta) "LIO/exec", (SQL.cpu_time_delta / 1000000) / DECODE (NVL (SQL.executions_delta, 0), 0, 1, SQL.executions_delta) "CPUTIM/exec", (SQL.elapsed_time_delta / 1000000) / DECODE (NVL (SQL.executions_delta, 0), 0, 1, SQL.executions_delta) "ETIME/exec", SQL.DISK_READS_DELTA / DECODE (NVL (SQL.executions_delta, 0), 0, 1, SQL.executions_delta) "PIO/exec", SQL.ROWS_PROCESSED_DELTA / DECODE (NVL (SQL.executions_delta, 0), 0, 1, SQL.executions_delta) "ROWs/exec" FROM dba_hist_sqlstat SQL, dba_hist_snapshot s WHERE SQL.INSTANCE_NUMBER = s.INSTANCE_NUMBER AND SQL.dbid = (SELECT dbid FROM v$database) AND s.snap_id = SQL.snap_id AND sql_id IN ('&sql_id')) ORDER BY source, begin_time DESC;

== set lines 150 pages 150 col BEGIN_INTERVAL_TIME for a23 col PLAN_HASH_VALUE for 9999999999 col date_time for a30 col snap_id heading 'SnapId' col executions_delta heading "No. of exec" col sql_profile heading "SQL|Profile" for a7 col date_time heading 'Date time' col avg_lio heading 'LIO/exec' for 99999999999.99 col avg_cputime heading 'CPUTIM/exec' for 9999999.99 col avg_etime heading 'ETIME/exec' for 9999999.99 col avg_pio heading 'PIO/exec' for 9999999.99 col avg_row heading 'ROWs/exec' for 9999999.99 SELECT distinct s.snap_id , PLAN_HASH_VALUE, to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time, SQL.executions_delta, SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_lio, --SQL.ccwait_delta, (SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_cputime , (SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_etime, SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_pio, SQL.rows_processed_total/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_row ,SQL.sql_profile FROM dba_hist_sqlstat SQL, dba_hist_snapshot s WHERE SQL.instance_number =(select instance_number from v$instance) and SQL.dbid =(select dbid from v$database) and s.snap_id = SQL.snap_id AND sql_id in ('&sql_id') order by s.snap_id /



===
http://anuj-singh.blogspot.com/2023/ dbms_xplan.display_awr




http://anuj-singh.blogspot.com/2021/02/

 SQL Report ....   / SQL info ... 



https://github.com/MarisElsins/presentation-scripts/blob/master/MiningAWRv1/DEMO_LOG.txt cat awr_top_by_plan_snaps.sql set ver off pages 50000 lines 32000 tab off long 9999999 timing off echo off VARIABLE BgnSnap NUMBER VARIABLE EndSnap NUMBER VARIABLE DID NUMBER VARIABLE INST_NUMBER number exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ; exec select max(snap_id) into :EndSnap from dba_hist_snapshot ; undef snap_id_from undef snap_id_to undef sort_col_nr undef top_n def sort_col_nr="6" ---ELAPSED_TIME_S def top_n="50" col executions for 9999999999 col rows_processed for 99999999999 col elapsed_time_s for 9999999.999 col cpu_time_s for 9999999.999 col iowait_s for 9999999.999 col clwait_s for 9999999.999 col apwait_s for 9999999.999 col ccwait_s for 9999999.999 col buffer_gets for 9999999999999999 col disk_reads for 9999999999999999 col direct_writes for 9999999999999999 col diff_sqlid for a13 col diff_plan for a10 col diff_fms for a20 select * from ( select sql_id,to_char(plan_hash_value) diff_plan,hs.con_id, decode(count(unique(sql_id)),1,max(sql_id),'#'||count(unique(sql_id))) diff_sqlid, decode(count(unique(force_matching_signature)),1,to_char(max(force_matching_signature)),'#'||count(unique(force_matching_signature))) diff_fms, sum(hss.executions_delta) executions, round(sum(hss.elapsed_time_delta)/1000000,3) elapsed_time_s, round(sum(hss.cpu_time_delta)/1000000,3) cpu_time_s, round(sum(hss.iowait_delta)/1000000,3) iowait_s, -- round(sum(hss.clwait_delta)/1000000,3) clwait_s, -- round(sum(hss.apwait_delta)/1000000,3) apwait_s, -- round(sum(hss.ccwait_delta)/1000000,3) ccwait_s, round(sum(hss.rows_processed_delta),3) rows_processed, round(sum(hss.buffer_gets_delta),3) buffer_gets, round(sum(hss.disk_reads_delta),3) disk_reads, round(sum(hss.direct_writes_delta),3) direct_writes from dba_hist_sqlstat hss, dba_hist_snapshot hs where hss.snap_id=hs.snap_id and hs.snap_id between :BgnSnap and :EndSnap group by sql_id,plan_hash_value,hs.con_id order by &sort_col_nr desc) where rownum<=&top_n; SQL_ID DIFF_PLAN CON_ID DIFF_SQLID DIFF_FMS EXECUTIONS ELAPSED_TIME_S CPU_TIME_S IOWAIT_S ROWS_PROCESSED BUFFER_GETS DISK_READS DIRECT_WRITES ------------- ---------- ---------- ------------- -------------------- ----------- -------------- ------------ ------------ -------------- ----------------- ----------------- ----------------- 48vf4pg4g5508 0 48vf4pg4g5508 #0 3456.328 3411.205 gqkr2um43ga39 0 gqkr2um43ga39 #0 34.678 18.293 3qk9bh5x55an0 120807157 3qk9bh5x55an0 7997816833973082146 32020667 301.965 303.556 .000 32020667 64742877 0 0 f7550vmqqrfgw 0 f7550vmqqrfgw 0 16923855 744.111 506.931 .000 16923271 0 0 0 58wxd0yshrs26 0 58wxd0yshrs26 0 16923797 773.566 482.681 .000 16923133 0 0 0 4hzpudc5usj5c 2443842040 4hzpudc5usj5c 5955594287014665241 9402882 481.988 280.386 .001 131240 76615605 1 0 6vmfwcwmdrmby 0 6vmfwcwmdrmby 0 7451240 557.628 437.449 .000 7451094 0 0 0 abjms4bcpbwqk 0 abjms4bcpbwqk 0 6804625 2301.370 1632.748 1.153 6804452 77329194 1356

set linesize 500 pagesize 300 col sql_profile for a30 col is_bind_aware for a15 col sql_patch for a15 col sql_plan_baseline for a20 col signature for a20 col is_shareable like is_bind_aware col is_obsolete like is_bind_aware col is_bind_sensitive like is_bind_aware col is_reoptimizable like is_bind_aware col is_resolved_adaptive_plan for a20 col is_rolling_invalid for a15 col is_rolling_refresh_invalid for a20 col is_resolved_adaptive_plan for a15 col sql_text for a15 select sql_id ,con_id ,child_number ,hash_value ,plan_hash_value ,is_obsolete ,is_shareable ,is_bind_aware ,is_bind_sensitive ,is_reoptimizable ,is_rolling_invalid ,is_rolling_refresh_invalid ,is_resolved_adaptive_plan ,to_char(exact_matching_signature) signature ,executions ,sql_patch ,sql_profile ,sql_plan_baseline ,substr(sql_text,1,15) sql_text from gv$sql where 1=1 and sql_id = 'gqkr2um43ga39' -- and is_shareable = 'Y' ;
=======

DBMS_UTILITY.EXPAND_SQL_TEXT procedure expands any references to a view within a query SET serveroutput ON DECLARE l_clob CLOB; BEGIN dbms_utility.Expand_sql_text(input_sql_text => '&text', output_sql_text => l_clob); dbms_output.Put_line(l_clob); END; /

=====

define sql_id='xxxxxx' col NAME for a20 col DATATYPE for a20 col CSI for a10 col frm for a10 col PRE for a10 col MAXLENGTH for a15 col CAPTURED for a15 col VALUE for a20 col PPO for a20 col SCL for a20 set linesize 300 pagesize 300 select inst_id, sql_id, child_number, plan_hash_value, EXTRACTVALUE(VALUE(D), '/bind/@nam') as NAME, to_number(EXTRACTVALUE(VALUE(D), '/bind/@pos')) as POSITION, EXTRACTVALUE(VALUE(D), '/bind/@ppo') as PPO, EXTRACTVALUE(VALUE(D), '/bind/@dty') as DATATYPE, EXTRACTVALUE(VALUE(D), '/bind/@csi') as CSI, EXTRACTVALUE(VALUE(D), '/bind/@frm') as FRM, EXTRACTVALUE(VALUE(D), '/bind/@pre') as PRE, EXTRACTVALUE(VALUE(D), '/bind/@scl') as SCL, EXTRACTVALUE(VALUE(D), '/bind/@mxl') as MAXLENGTH, EXTRACTVALUE(VALUE(D), '/bind/@captured') as CAPTURED, EXTRACTVALUE(VALUE(D), '/bind') as VALUE FROM gv$sql_plan, TABLE(XMLSEQUENCE(EXTRACT(xmltype(other_xml), '/*/peeked_binds/bind'))) D where sql_id = '&sql_id' and other_xml is not null order by inst_id, child_number, POSITION /


===


set linesize 600 pagesize 300 col SQL_TEXT for a50 col kill for a15 col USERNAME for a15 col PLSQL_ENTRY_OBJECT for a15 col PLSQL_ENTRY_SUBPROGRAM for a20 col PLSQL_ENTRY_SUBPROGRAM for a20 SELECT ''''||se.sid ||','|| se.serial#||',@'||se.inst_id ||'''' kill ,con_id , username , ( SELECT max( substr( sql_text , 1, 40 )) SQL_TEXT FROM gv$sql sq WHERE sq.sql_id = se.sql_id ) AS sql_text , ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object , ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram , ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = 0) AS plsql_entry_object , ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = PLSQL_SUBPROGRAM_ID) AS plsql_entry_subprogram --, se.* ,se.sql_id FROM gv$session se WHERE 1=1 AND se.status = 'ACTIVE' -- AND sid = xxx AND plsql_entry_object_id IS NOT NULL and USERNAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) --and sql_id='2u2jkj3sgctvn' ;




define sql_id='0k8522rmdzg8j' WITH p AS ( SELECT plan_hash_value FROM gv$sql_plan WHERE sql_id = TRIM('&&sql_id') AND other_xml IS NOT NULL UNION SELECT plan_hash_value FROM dba_hist_sql_plan WHERE sql_id = TRIM('&&sql_id') AND other_xml IS NOT NULL ), m AS ( SELECT plan_hash_value, SUM(elapsed_time)/SUM(executions) avg_et_secs FROM gv$sql WHERE sql_id = TRIM('&&sql_id') AND executions > 0 GROUP BY plan_hash_value ), a AS ( SELECT plan_hash_value, SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs FROM dba_hist_sqlstat WHERE sql_id = TRIM('&&sql_id') AND executions_total > 0 GROUP BY plan_hash_value ) SELECT p.plan_hash_value, ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs FROM p, m, a WHERE p.plan_hash_value = m.plan_hash_value(+) AND p.plan_hash_value = a.plan_hash_value(+) ORDER BY avg_et_secs NULLS LAST; PLAN_HASH_VALUE AVG_ET_SECS --------------- ----------- 2057665657 0

===



sql monitor !!!!
SELECT /*+ monitor */ * FROM scott.emp WHERE sal> 100; to find out sql_id SELECT * FROM TABLE(DBMS_XPLAN.display_cursor( format=>'ALLSTATS LAST +cost +bytes')); SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'8yhjskws85xcr',format=>'ALLSTATS LAST +outline'));SQL> SQL_ID 8yhjskws85xcr, child number 0 ------------------------------------- SELECT /*+ monitor */ * FROM scott.emp WHERE sal> 100 Plan hash value: 3956160932 ------------------------------------------- | Id | Operation | Name | E-Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | TABLE ACCESS FULL| EMP | 14 | ------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "EMP"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("SAL">100) set linesize 1000 pagesize 100 verify off trimout on trimspool on echo off head off feed off timing off termout off long 1000000 longchunksize 1000000 set serveroutput on size 1000000 SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'8yhjskws85xcr',format=>'ALLSTATS LAST +cost +bytes')); set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'&sql_id', report_level=>'ALL', type=>'TEXT') from dual; SQL Text ------------------------------ SELECT /*+ monitor */ * FROM scott.emp WHERE sal> 100 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SYS (1592:16515) SQL ID : 8yhjskws85xcr SQL Execution ID : 16777216 Execution Started : 03/17/2024 07:43:21 First Refresh Time : 03/17/2024 07:43:21 Last Refresh Time : 03/17/2024 07:43:21 Duration : .000164s Module/Action : sqlplus@ (TNS V1-V3)/- Service : SYS$USERS Program : sqlplus@ (TNS V1-V3) Fetch Calls : 2 Global Stats ================================================= | Elapsed | Cpu | Other | Fetch | Buffer | | Time(s) | Time(s) | Waits(s) | Calls | Gets | ================================================= | 0.00 | 0.00 | 0.00 | 2 | 7 | ================================================= SQL Plan Monitoring Details (Plan Hash Value=3956160932) ========================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ========================================================================================================================= | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 14 | | | | 1 | TABLE ACCESS FULL | EMP | 14 | 2 | 1 | +0 | 1 | 14 | | | =========================================================================================================================




Oracle DBA

anuj blog Archive