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 rounddefine 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 offset head on echo on termout on trimspool oncolumn 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 300col PLAN_TABLE_OUTPUT for a200select *from (SELECT plan_table_outputFROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', null, 'BASIC'))UNION ALLSELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL')))
define sql_id='f705bwx3q0ydq'set linesize 400 pagesize 300col OWNER for a20col index_name for a50col TABLE_NAME for a30col global_stats for a20SELECT owner, index_name, table_name,last_analyzed, sample_size, num_rows, partitioned, global_stats FROM dba_indexesWHERE 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_outputFROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', null, 'BASIC'))UNION ALLSELECT * 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 wrapselect 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 0SELECT * 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 a20col OBJECT_NAME for a20col EVENT for a27define sql_id='8cnh50qfgwg73'
VARIABLE BgnSnap NUMBERVARIABLE EndSnap NUMBERVARIABLE DID NUMBERVARIABLE INST_NUMBER numberVARIABLE 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) percentFROM (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;
=====
from web
define 1='64umkzksavzym'
define 2='Y'
ttitle off
btitle off
col s_diag_pack new_value s_diag_pack noprint
col s_sql_id new_value s_sql_id noprint
var v_sql_id varchar2(13)
set feed off term off
select '&1' s_sql_id from dual;
set term on
whenever sqlerror exit 128
begin
:v_sql_id := '&s_sql_id';
if
length(:v_sql_id) < 1
or
:v_sql_id is null
then
raise value_error;
end if;
end;
/
whenever sqlerror continue
set feed on
set feed off term off
select decode(upper('&2'),'Y','','--') s_diag_pack from dual;
set feed on term on
set pagesize 100 linesize 300 trimspool on
col partition_start format a6 head 'PSTART'
col sql_id format a13
col partition_stop format a6 head 'PSTOP'
col owner format a20
col table_name format a30
col index_name format a40
col phv format a43 wrap
col last_analyzed format a19
col stale_stats format a11 head 'stale_stats'
col num_rows format 99,999,999,999
col blocks format 9,99,999,999
col partition_position format 999999 head 'PP'
col phv for a15
break on sql_id skip 1
--spool stats-sqlid.txt
with objects as (
-- extra inline view is to eliminate duplicates in listagg()
select
sql_id
, listagg(phv,',') within group(order by phv) phv
, object_owner
, object_name
, object_type
, partition_start
, partition_stop
from (
select distinct
sql_id
, phv
, object_owner
, object_name
, object_type
, partition_start
, partition_stop
from (
select
sql_id
, plan_hash_value phv
, object_owner
, object_name
, object_type
, case partition_start
when 'ROW LOCATION' then 'ROWID'
else partition_start
end partition_start
, case partition_stop
when 'ROW LOCATION' then 'ROWID'
else partition_stop
end partition_stop
from v$sql_plan
where sql_id = :v_sql_id
and object_owner is not null
and object_type in ('TABLE','INDEX','INDEX (UNIQUE)','INDEX (CLUSTER)','CLUSTER','TABLE (FIXED)')
&s_diag_pack union all
&s_diag_pack select
&s_diag_pack sql_id
&s_diag_pack , plan_hash_value phv
&s_diag_pack , object_owner
&s_diag_pack , object_name
&s_diag_pack , object_type
&s_diag_pack , case partition_start
&s_diag_pack when 'ROW LOCATION' then 'ROWID'
&s_diag_pack else partition_start
&s_diag_pack end partition_start
&s_diag_pack , case partition_stop
&s_diag_pack when 'ROW LOCATION' then 'ROWID'
&s_diag_pack else partition_stop
&s_diag_pack end partition_stop
&s_diag_pack from dba_hist_sql_plan
&s_diag_pack where sql_id = :v_sql_id
&s_diag_pack and object_owner is not null
&s_diag_pack and object_type in ('TABLE','INDEX','INDEX (UNIQUE)','INDEX (CLUSTER)','CLUSTER','TABLE (FIXED)')
)
)
group by
sql_id
, object_owner
, object_name
, object_type
, partition_start
, partition_stop
),
indexes as (
select * from objects where object_type in ('INDEX','INDEX (UNIQUE)','INDEX (CLUSTER)')
),
tables as (
select * from objects where object_type in ('TABLE','CLUSTER','TABLE (FIXED)')
)
select
sql_id
, phv
, owner
, table_name
|| decode(s.partition_name, null,'','.' || s.partition_name)
as table_name
, null index_name
, partition_position
, t.partition_start
, t.partition_stop
, num_rows
, blocks
, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_analyzed
, stale_stats
from dba_tab_statistics s
join tables t on t.object_owner = s.owner
and t.object_name = s.table_name
union all
select
sql_id
, phv
, owner
, table_name
, index_name
|| decode(s.partition_name, null,'','.' || s.partition_name)
as index_name
, partition_position
, i.partition_start
, i.partition_stop
, num_rows
, leaf_blocks blocks
, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_analyzed
, stale_stats
from dba_ind_statistics s
join indexes i on i.object_owner = s.owner
and i.object_name = s.index_name
order by sql_id
, owner
, table_name
, index_name nulls first
, partition_position nulls first
/
define 1= 'c33dcq0rbj6gg' set feedback off heading on timi on pages 200 lines 500 echo off VERIFY OFF col INST for 9999 col EXECS for 99999999 col CHILD for 99999 col BIND_SENSE for a10 col BIND_AWARE for a10 col SHAREABLE for a10 col USE_FEEDBACK_STATS for a18 col OPTIMIZER_STATS for a16 col BIND_EQ_FAILURE for a16 col Reason for a40 col SQL_PLAN_BASELINE for a30 col SQL_PATCH for a30 col SQL_PROFILE for a64 col IS_OBSOLETE for a11 col FLASHBACK_CURSOR for a16 col IS_SHAREABLE for a12 SELECT INST, sql_id, child_number, is_obsolete as "IS_OBSOLETE", flashback_cursor as "FLASHBACK_CURSOR", is_shareable as "IS_SHAREABLE", LISTAGG(Reason, '; ') WITHIN GROUP (ORDER BY to_number(rid)) AS Reason FROM (select sc.sql_id, sc.child_number, sc.child_address, sc.inst_id as INST, is_obsolete, flashback_cursor, is_shareable, xt.rid || ' |' || xt.Reasons || ' |' || xt.Details as Reason, xt.rid from gv$sql_shared_cursor sc, gv$sql s, xmltable('/Reasonz/ChildNode' passing xmltype('<Reasonz>'||sc.reason||'</Reasonz>') columns RID varchar2(60) path 'ID', Reasons varchar2(60) path 'reason', Details varchar2(60) path 'details') xt where dbms_lob.substr(reason, 256) <> ' ' and '&1' = sc.sql_id and s.sql_id = sc.sql_id and s.child_address = sc.child_address and 1 = sc.inst_id) GROUP BY INST, sql_id, is_obsolete, flashback_cursor, is_shareable, child_number, child_address /INST SQL_ID CHILD_NUMBER IS_OBSOLETE FLASHBACK_CURSOR IS_SHAREABLE REASON ----- ------------- ------------ ----------- ---------------- ------------ ---------------------------------------- 1 c33dcq0rbj6gg 0 N N Y 39 |Bind mismatch(33) |High SQL Version Counts - Script to determine reason(s) (Doc ID 438755.1) select * from table(version_rpt('c33dcq0rbj6gg'));===========================================sql text search !!!!!!!!!!!!!!!!!!!!!!!!!!http://kerryosborne.oracle-guy.com/2009/04/16/hidden-sql-why-cant-i-find-my-sql-text/ set pagesize 300 select CON_ID,SQL_ID,count(*) from v$open_cursor group by CON_ID,SQL_ID --having count(*) >3000 order by count(*); CON_ID SQL_ID COUNT(*) ---------- ------------- ---------- 3 7qmnt62p9kkxu 64703 define sql_text='' define sql_id='9zg9qd9bm4spu' set verify off set pagesize 999 col username format a13 col prog format a22 col sql_text format a50 col sid format 999 col child_number format 99999 heading CHILD col ocategory format a10 col avg_etime format 9,999,999.99 col etime format 9,999,999.99 set linesize 400 select sql_id, child_number, plan_hash_value plan_hash, executions execs, elapsed_time/1000000 etime, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, u.username, sql_text from gv$sql s, dba_users u where 1=1 -- and upper(sql_text) like upper(nvl('&sql_text',sql_text)) and sql_text not like '%from v$sql where sql_text like nvl(%' and sql_id like nvl('&sql_id',sql_id) and u.user_id = s.parsing_user_id / set lines 300 col sql_text for a70 select sql_text from gv$sqltext where 1=1 -- sql_text like nvl('&sql_text','%') and sql_id like nvl('&sql_id',sql_id) order by piece /=============================================table_4_9_fbd5_0_0_0table_4_9_fbd5_0_0_0 Enter value for hex_value: fbd5 select owner, object_name, object_type from dba_objects where object_id = (select to_number('&hex_value','XXXXXX') from dual); Enter value for hex_value: fbd5 select distinct * from v$open_cursor where rownum < 10 and sql_id like '%fat%'; select username, sid, serial#, status, sql_id, prev_sql_id, event, seconds_in_wait from v$session where 1=1 -- username like nvl('&username',username) and sid like nvl('&sid',sid) and sql_id='7vtb7h3kpfat0' order by username, sid, serial#; set pagesize 300 select CON_ID,SQL_ID,count(*) from v$open_cursor group by CON_ID,SQL_ID having count(*) >3000 order by count(*); define sql_id='5dqz0hqtp9fru' select inst_id,kgllkuse, kgllksnm, user_name, kglhdpar, kglnahsh, kgllksqlid, kglnaobj, kgllkest, decode(kgllkexc, 0, to_number(NULL), kgllkexc), kgllkctp from x$kgllk where kglhdnsp = 0 and kglhdpar != kgllkhdl and kgllksqlid like nvl('&sql_id',kgllksqlid);https://github.com/tanelpoder/tpt-oracle/blob/master/kgllk.sql set linesize 500 col hold_mode head HOLD_MODE for a10 col req_mode head REQ_MODE for a10 col object_owner head OBJECT_OWNER for a15 col namespace for a30 word_wrap col kgllk_state head 0xSTATE for A8 col sid for 99999999999 SELECT * FROM ( SELECT s.sid , KGLLKSNM rsid -- , KGLLKADR -- , KGLLKUSE -- , KGLLKSES , decode(l.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', to_char(l.kgllkmod)) hold_mode , decode(l.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', to_char(l.kgllkreq)) req_mode -- , LPAD('0x'||TRIM(TO_CHAR(l.kgllkflg,'XXXXX')),8) kgllk_state -- , decode(l.kgllkflg, 0, 1, 'BROKEN', 2, 'BREAKABLE', l.kgllkflg) kgllk_state -- 11g stuff -- , kgllkest -- , kgllkexc -- , KGLLKFLG -- , KGLLKSPN -- , KGLLKHTB , KGLNAHSH , KGLLKSQLID -- , KGLHDPAR -- , KGLHDNSP -- , n.kglsttyp , n.kglstdsc namespace , USER_NAME object_owner , KGLNAOBJ object_name , TO_CHAR(l.kgllkflg,'XXXXX') kgllk_state , KGLLKHDL , KGLLKPNC , KGLLKPNS , KGLLKCNT -- , KGLLKCTP -- cursor type FROM x$kgllk l , v$session s , x$kglst n WHERE s.saddr(+) = l.kgllkuse AND l.kglhdnsp = n.indx --AND kgllkhdl = hextoraw(upper(lpad('&1',vsize(l.kgllkhdl)*2,'0'))) ) WHERE 1=1 and KGLLKSQLID='cgk2h2fsy3zsk' /define sql_id='XXXXXXX' SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 1000 PAGESIZE 0 TRIM ON TRIMSPOOL ON ECHO OFF FEEDBACK OFF --SPOOL /u04/reports/report_sql_monitor.txt; SELECT DBMS_SQLTUNE.report_sql_monitor( sql_id => '&&sql_id', type => 'TEXT', report_level => 'ALL') AS report FROM dual; SET lines 300 pagesize 100 col PLAN FOR a200 SELECT RPAD('(' || p.ID || ' ' || NVL(p.parent_id,'0') || ')',8) || '|' || RPAD(LPAD (' ', 2*p.DEPTH) || p.operation || ' ' || p.options,40,'.') || NVL2(p.object_owner||p.object_name, '(' || p.object_owner|| '.' || p.object_name || ') ', '') || 'Cost:' || p.COST || ' ' || NVL2(p.bytes||p.CARDINALITY,'(' || p.bytes || ' bytes, ' || p.CARDINALITY || ' rows)','') || ' ' || NVL2(p.partition_id || p.partition_start || p.partition_stop,'PId:' || p.partition_id || ' PStart:' || p.partition_start || ' PStop:' || p.partition_stop,'') || 'io cost=' || p.io_cost || ',cpu_cost=' || p.cpu_cost AS PLAN FROM dba_hist_sql_plan p WHERE p.sql_id='&&sql_id' -- AND plan_hash_value='554605205' ORDER BY p.id, p.parent_id;-- sql hint set linesize 300 pagesize 300 col HINT for a100 define sql_id='7ycnuk1r48000' define child_no=0 select extractvalue(value(t),'.') hint from table( select xmlsequence( extract(xmltype(other_xml),'/other_xml/outline_data/hint') ) from gv$sql_plan where sql_id = '&sql_id' and child_number = &child_no and other_xml is not null ) t / col OUTLINE_HINTS for a100 define sql_id='cp1g5w1dgc000' select distinct extractvalue(value(d), '/hint') as outline_hints from xmltable('/*/outline_data/hint' passing ( select xmltype(other_xml) as xmlval from dba_hist_sql_plan where 1=1 and sql_id = '&sql_id' --and plan_hash_value = &plan_hash_value and other_xml is not null and rownum<2 ) ) d;define sql_id='94qn6y14kw01g' define child_number=0 set echo off feed off head off BEGIN DBMS_SQLDIAG.DUMP_TRACE(p_sql_id => '&&sql_id', p_child_number => &&child_number, p_component => 'Compiler', p_file_id => 'Optimizer_Trace' ); END; / undef sql_id undef child_number col VALUE for a100 select value from v$diag_info where name = 'Default Trace File'; to check location set linesize 400 col ALERT_LOG for a100 col PATH for a100 select di.value ||'/'|| 'alert_' || i.instance_name || '.log' ALERT_LOG from v$diag_info di, v$instance i where di.name = 'Diag Trace';http://anuj-singh.blogspot.com/2018/01/tracing-sql-statement-execution.htmlselect * from table(dbms_xplan.display_awr('60aq76cdwUUU',124844,FORMAT => 'TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE')); select * from table(dbms_xplan.display_awr('60aq76cdwUUU',7311153,FORMAT => 'TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));Sql plan change !!https://anuj-singh.blogspot.com/2024/
