ORACLE SQL History ....
define sql_id='7jycxu86n60qh' 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 rows_per_exec HEA 'Rows Per Exec' col et_secs_per_exec HEA 'Elap Secs|Per Exec' col cpu_secs_per_exec HEA 'CPU Secs|Per Exec' col io_secs_per_exec HEA 'IO Secs|Per Exec' col cl_secs_per_exec HEA 'Clus Secs|Per Exec' col ap_secs_per_exec HEA 'App Secs|Per Exec' col cc_secs_per_exec HEA 'Conc Secs|Per Exec' col pl_secs_per_exec HEA 'PLSQL Secs|Per Exec' col ja_secs_per_exec HEA 'Java Secs|Per Exec' col executions_total FOR 999,999 HEA 'Execs|Total' 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 ;
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'
;
SET lin 200 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 HEA 'Execs|Total'
COL rows_per_exec HEA 'Rows Per Exec'
COL et_secs_per_exec HEA 'Elap Secs|Per Exec'
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec'
COL io_secs_per_exec HEA 'IO Secs|Per Exec'
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec'
COL ap_secs_per_exec HEA 'App Secs|Per Exec'
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec'
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec'
COL ja_secs_per_exec HEA 'Java Secs|Per Exec'
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), '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
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 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'
;
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
---
-- 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
-- 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
/
==============
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.
====
-- 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'
;
==============
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%';
===
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'));
Comments
Post a Comment