SQL Report .... / SQL info ...
ITL wait
https://anuj-singh.blogspot.com/search?q=ITL
SET SERVEROUTPUT ON VARIABLE sqlid VARCHAR2(13); VARIABLE sql_id VARCHAR2(13); BEGIN :sql_id := '&sql_id'; :sqlid := '&&sql_id'; END; / PRINT sql_id; PRINT sqlid;define sql_id='f8xb39xkyc000'define sql_id='&sql_id'
=====================
>= 19.28
sql diag report
set feedback on
var report clob
exec :report := dbms_sqldiag.report_sql('9djzz14xarx5j');
--exec :report :=DBMS_SQLDIAG.Report_SQL(SQL_ID => '9djzz14xarx5j', Level => 'ALL')
set trimspool on trim on pagesize 0 linesize 32767 long 1000000 longchunksize 1000000
spool diagsql1.html
select :report report from dual;
spool off
==== var sqlid VARCHAR2(13); begin :sqlid := 'g0bggfqrddc4w'; end; ----- change sql id /
set long 50000 pagesize 500 linesize 300
col frm heading from
select * from (select 'gv$sql' frm , sql_fulltext from gv$sql where sql_id=:sqlid
union all
select 'dba_hist', sql_text from dba_hist_sqltext where sql_id=:sqlid
);
SET LONG 100000
SET LONGCHUNKSIZE 100000
SET PAGESIZE 50000
SET LINESIZE 300
COL sql_id FORMAT a15
COL sql_fulltext FORMAT a200 WORD_WRAPPED
SELECT con_id,sql_id, sql_fulltext
FROM (
SELECT
con_id,
sql_id, sql_fulltext,
ROW_NUMBER() OVER (PARTITION BY sql_id ORDER BY child_number) AS rn
FROM v$sql
WHERE sql_id IN (
'&sql_id'
)
)
WHERE rn = 1;
col plan_table_output for a150 select plan_table_output from table(dbms_xplan.display_cursor(:sql_id, null, 'BASIC')) union all select * from table(dbms_xplan.display_awr(:sql_id, null, null, 'ALL'))
col plan_table_output for a150
select plan_table_output
from table(dbms_xplan.display_cursor('&sql_id', null, 'BASIC'))
union all
select * from table(dbms_xplan.display_awr('&sql_id', null, null, 'ALL'))
set linesize 300 pagesize 300
col sql_id for a20
col last_active_time for a26
col last_load_time for a20
col instance for a16
col instance_name for a20
col status for a20
col PLAN_TABLE_OUTPUT for a70
select a.sql_id, a.child_number
, to_char(a.last_active_time, 'DD-MON-YYYY HH24:MI:SS') last_active_time
, a.loaded_versions, a.open_versions, a.users_opening
from gv$sql a
where sql_id=:sqlid;
col last_active_time for a16
col last_load_time for a20
select a.sql_id, a.child_number
, (select instance_name
from gv$instance
where instance_number=a.inst_id) instance_name
, a.hash_value, a.old_hash_value, a.plan_hash_value
from gv$sql a
where a.sql_id=:sqlid;
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 1000
column SQL_TEXT for a100 word_wrap
col kill for a15
col event for a20
break on sql_text skip 1
SELECT DISTINCT ''''||S.sid ||','|| S.serial#||',@'||S.inst_id ||'''' kill, s.last_call_et,
s.sql_id,
s.username,
s.status,
s.blocking_session,
s.BLOCKING_INSTANCE,
sq.LAST_ACTIVE_TIME,
s.event,
dbms_lob.substr(sq.sql_fulltext,4000,1) ||dbms_lob.substr(sq.sql_fulltext,4000,4001) SQL_TEXT
FROM gv$session s, gv$sql sq
WHERE sq.address = s.sql_address
AND sq.hash_value = s.SQL_HASH_VALUE
AND sq.sql_id = s.sql_id
--AND s.status = 'ACTIVE'
-- AND s.last_call_et >= 500
--AND s.sql_id IS NOT NULL
-- AND SQ.SQL_TEXT NOT LIKE '%$%'
and sq.sql_id= :sqlid
;
COLUMN owner FORMAT a10
COLUMN name FORMAT a25
COLUMN namespace FORMAT a20
COLUMN type FORMAT a15
COLUMN sql_id FORMAT a15
SET LINESIZE 120 PAGESIZE 50
select odep.to_owner as owner,
odep.to_name as name,
occh.namespace,
occh.type,
sqla.sql_id
from v$sqlarea sqla,
v$object_dependency odep,
v$db_object_cache occh
where 1=1
and sqla.sql_id = '&sql_id'
and odep.from_hash = sqla.hash_value
and occh.hash_value (+) = odep.to_hash
-- and odep.to_owner='ANUJ'
-- and rownum<5
;
select
odep.to_name
from v$sqlarea sqla,
v$object_dependency odep,
v$db_object_cache occh
where 1=1
and sqla.sql_id = 'bw7jtfcudw0jj3'
and odep.from_hash = sqla.hash_value
and occh.hash_value (+) = odep.to_hash
--and odep.to_owner='ANUJ'
-- and rownum<5
;
alter session set NLS_DATE_FORMAT = 'DD-MM-yyyy HH24:MI:SS';
set linesize 300 pagesize 300
col TABLE_NAME for a26
col last_analyzed for a27
col stale_stats for a12
select table_name,stale_stats,last_analyzed
from dba_tab_statistics
where 1=1
-- and owner='U'
and table_name in (select
odep.to_name
from v$sqlarea sqla,
v$object_dependency odep,
v$db_object_cache occh
where 1=1
and sqla.sql_id = 'bw7jtfcudw003'
and odep.from_hash = sqla.hash_value
and occh.hash_value (+) = odep.to_hash
-- and odep.to_owner='ANUJ'
-- and rownum<5
)
union all
select table_name,stale_stats,last_analyzed
from dba_tab_statistics
where 1=1
and table_name in (select
odep.to_name
from v$sqlarea sqla,
v$object_dependency odep,
v$db_object_cache occh
where 1=1
and sqla.sql_id = 'bw7jtfcudw003'
and odep.from_hash = sqla.hash_value
and occh.hash_value (+) = odep.to_hash
)
;
set pagesize 100
set linesize 150
set trims off
set tab off
set verify off
column table_name format a50
column index_name format a50
column object_type format a40
column owner format a40
accept sql_id prompt 'Enter the SQL ID: '
PROMPT ==========
PROMPT Tables
PROMPT ==========
with plan_tables as (
select distinct object_name,object_owner, object_type
from v$sql_plan
where object_type like 'TABLE%'
and sql_id = '&sql_id')
select t.object_owner owner,
t.object_name table_name,
t.object_type object_type,
decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness
from dba_tab_statistics s,
plan_tables t
where s.table_name = t.object_name
and s.owner = t.object_owner
and s.partition_name is null
and s.subpartition_name is null
order by t.object_owner, t.object_name;
PROMPT ==========
PROMPT Indexes
PROMPT ==========
with plan_indexes as (
select distinct object_name,object_owner, object_type
from v$sql_plan
where object_type like 'INDEX%'
and sql_id = '&sql_id')
select i.object_owner owner,
i.object_name index_name,
i.object_type object_type,
decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness
from dba_ind_statistics s,
plan_indexes i
where s.index_name = i.object_name
and s.owner = i.object_owner
and s.partition_name is null
and s.subpartition_name is null
order by i.object_owner, i.object_name;
define 1='6jfvj3ydcdyh3'
--define 2='USER' -- table name
DEFINE 3="timestamp'2026-07-15 00:00:00'"
DEFINE 4="timestamp'2026-07-15 06:00:00'"
COL asqlmon_operation HEAD Plan_Operation FOR a72
COL asqlmon_predicates HEAD PREDICATES FOR a100 word_wrap
COL obj_alias_qbc_name FOR a40
COL options FOR a30
COL asqlmon_plan_hash_value HEAD PLAN_HASH_VALUE PRINT
COL asqlmon_sql_id HEAD SQL_ID NOPRINT
COL asqlmon_sql_child HEAD "CHILD" PRINT
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 "ID" FOR 9999
COL asqlmon_parent_id HEAD "PID" FOR 9999
BREAK ON asqlmon_sql_id SKIP 1 ON asqlmon_sql_child SKIP 1 ON asqlmon_plan_hash_value SKIP 1 ON asqlmon_sample_time SKIP 1 DUP ON asqlmon_operation
PROMPT
PROMPT -- ASQLMon v1.1 - by Tanel Poder ( https://tanelpoder.com ) - Display SQL execution plan line level activity breakdown from ASH
SET LINESIZE 999 PAGESIZE 5000 TRIMOUT ON TRIMSPOOL ON
WITH sample_times AS (
select * from dual
),
sq AS (
SELECT
count(*) samples
, ash.sql_id
, ash.sql_child_number
, ash.sql_plan_hash_value
, ash.sql_full_plan_hash_value
, NVL(ash.sql_plan_line_id,1) sql_plan_line_id -- this is because simple "planless" operations like single-row insert
, ash.sql_plan_operation
, ash.sql_plan_options
, ash.session_state
, ash.event
-- , AVG(ash.p3) avg_p3 -- p3 is sometimes useful for listing block counts for IO wait events
FROM
v$active_session_history ash
WHERE
1=1
AND ash.sql_id LIKE '&1'
--AND ash.sql_child_number LIKE '&2'
AND ash.sample_time BETWEEN &3 AND &4
GROUP BY
ash.sql_id
, ash.sql_child_number
, ash.sql_plan_hash_value
, ash.sql_full_plan_hash_value
, NVL(ash.sql_plan_line_id,1)
, ash.sql_plan_operation
, ash.sql_plan_options
, ash.session_state
, ash.event
)
SELECT
plan.sql_id asqlmon_sql_id
, plan.child_number asqlmon_sql_child
, plan.full_plan_hash_value asqlmon_full_plan_hash_value
, 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
--, 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
, plan.parent_id asqlmon_parent_id
, LPAD(' ', depth, ' ') || plan.operation ||' '|| plan.options || NVL2(plan.object_name, ' ['||plan.object_name ||']', null) asqlmon_operation
, sq.session_state
, sq.event
-- , sq.avg_p3
, plan.object_alias || CASE WHEN plan.qblock_name IS NOT NULL THEN ' ['|| plan.qblock_name || ']' END obj_alias_qbc_name
-- , CASE WHEN plan.access_predicates IS NOT NULL THEN '[A:] '|| SUBSTR(plan.access_predicates,1,1990) END || CASE WHEN plan.filter_predicates IS NOT NULL THEN ' [F:] ' || SUBSTR(plan.filter_predicates,1,1990) END asqlmon_predicates
-- , plan.projection
FROM
gv$sql_plan plan
, sq
WHERE
1=1
AND sq.sql_id(+) = plan.sql_id
AND sq.sql_child_number(+) = plan.child_number
AND sq.sql_plan_line_id(+) = plan.id
AND sq.sql_full_plan_hash_value(+) = plan.full_plan_hash_value
--AND sq.sql_plan_hash_value(+) = plan.plan_hash_value
AND plan.sql_id LIKE '&1'
--AND plan.child_number LIKE '&2'
ORDER BY
plan.child_number
, plan.plan_hash_value
, plan.id
/
===========================================================================
define 1='6jfvj3ydcdyh3'
define 2='USER' -- table name
DEFINE 3="timestamp'2026-07-15 00:00:00'"
DEFINE 4="timestamp'2026-07-15 06:00:00'"
SET LINESIZE 999 PAGESIZE 5000 TRIMOUT ON TRIMSPOOL ON
COL accessed_table HEAD Accessed_Table FOR a40
COL aindex_operation HEAD Plan_Operation FOR a70
COL aindex_predicates HEAD PREDICATES FOR a100 truncate
COL obj_alias_qbc_name FOR a40
COL options FOR a30
COL AAS FOR 9999.9
COL cpu_pct HEAD CPU FOR A5
COL wait_pct HEAD WAIT FOR A5
COL ela_sec_exec HEAD "ELA_SEC/EXEC" FOR 9999990.999
COL aindex_plan_hash_value HEAD PLAN_HASH_VALUE PRINT
COL aindex_sql_id HEAD SQL_ID NOPRINT
COL aindex_sql_child HEAD "CHILD" PRINT
COL aindex_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 aindex_id HEAD "ID" FOR 9999
COL aindex_parent_id HEAD "PID" FOR 9999
PROMPT
PROMPT -- Santa's Little (Index) Helper BETA v0.5 - by Tanel Poder ( https://tanelpoder.com )
WITH
tab AS (SELECT /*+ NO_MERGE */ owner, table_name, num_rows
FROM dba_tables
WHERE UPPER(table_name) LIKE '%&2%'
),
ind AS (SELECT /*+ NO_MERGE */ owner, index_name, table_owner, table_name
FROM dba_indexes
WHERE (table_owner, table_name) IN (SELECT owner, table_name FROM tab)),
sample_times AS (
select * from dual
),
sq AS (
SELECT
count(*) samples
, ash.sql_id
, ash.sql_child_number
, ash.sql_plan_hash_value
, NVL(ash.sql_plan_line_id,1) sql_plan_line_id -- this is because simple "planless" operations like single-row insert
, ash.sql_plan_operation
, ash.sql_plan_options
, ash.session_state
, ash.wait_class
, ash.event
FROM
v$active_session_history ash
WHERE
1=1
AND ash.sql_plan_operation IN ('TABLE ACCESS', 'INDEX')
AND ash.sql_id LIKE '&1'
AND ash.sample_time BETWEEN &3 AND &4
GROUP BY
ash.sql_id
, ash.sql_child_number
, ash.sql_plan_hash_value
, NVL(ash.sql_plan_line_id,1)
, ash.sql_plan_operation
, ash.sql_plan_options
, ash.session_state
, ash.wait_class
, ash.event
),
ash_and_plan AS (
SELECT
plan.sql_id
, plan.child_number
, plan.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
--, LPAD(plan.id,4)||CASE WHEN parent_id IS NULL THEN ' ' ELSE ' <- ' END||LPAD(plan.parent_id,4) aindex_plan_id
, plan.id aindex_id
, plan.parent_id aindex_parent_id
, plan.operation ||' '|| plan.options || NVL2(plan.object_name, ' ['||plan.object_owner||'.'||plan.object_name ||']', null) aindex_operation
, plan.object_owner
, plan.object_name
, plan.object_type
, plan.cardinality
, stat.executions
, stat.elapsed_time
, sq.session_state
, sq.wait_class
, sq.event
, plan.object_alias || CASE WHEN plan.qblock_name IS NOT NULL THEN ' ['|| plan.qblock_name || ']' END obj_alias_qbc_name
, CASE WHEN plan.access_predicates IS NOT NULL THEN '[A:] '|| SUBSTR(plan.access_predicates,1,1994) END || CASE WHEN plan.filter_predicates IS NOT NULL THEN ' [F:] ' || SUBSTR(plan.filter_predicates,1,1994) END aindex_predicates
-- , plan.projection
FROM
v$sql_plan plan
, v$sqlstats_plan_hash stat
, sq
WHERE
1=1
AND sq.sql_id(+) = plan.sql_id
AND sq.sql_child_number(+) = plan.child_number
AND sq.sql_plan_line_id(+) = plan.id
AND sq.sql_plan_hash_value(+) = plan.plan_hash_value
--
AND sq.sql_id(+) = stat.sql_id
AND sq.sql_plan_hash_value(+) = stat.plan_hash_value
AND stat.sql_id = plan.sql_id
AND stat.plan_hash_value = plan.plan_hash_value
--
AND plan.sql_id LIKE '&1'
----
)
SELECT * FROM (
SELECT
SUM(ap.seconds) seconds
, ROUND(SUM(ap.seconds) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
, LPAD(TO_CHAR(ROUND(SUM(CASE WHEN ap.session_state = 'ON CPU' THEN ap.seconds ELSE 0 END) / SUM(ap.seconds) * 100))||'%',4) cpu_pct
, LPAD(TO_CHAR(ROUND(SUM(CASE WHEN ap.session_state = 'WAITING' THEN ap.seconds ELSE 0 END) / SUM(ap.seconds) * 100))||'%',4) wait_pct
--, SUM(CASE WHEN ap.wait_class = 'User I/O' THEN ap.seconds ELSE 0 END) iowait_sec
, t.owner||'.'||table_name accessed_table
, ap.aindex_operation
, ap.cardinality plan_card
, t.num_rows table_rows
, ap.cardinality / NULLIF(t.num_rows,0) * 100 filter_pct
, ap.executions sql_execs
, ROUND(ap.elapsed_time / NULLIF(ap.executions,0) / 1000000,3) ela_sec_exec
, ap.aindex_predicates
, COUNT(DISTINCT ap.sql_id) dist_sqlids
, COUNT(DISTINCT ap.plan_hash_value) dist_plans
, MIN(ap.sql_id)
, MAX(ap.sql_id)
-- , ap.projection
FROM
ash_and_plan ap
, (SELECT tab.*, 'TABLE' object_type, tab.owner object_owner, tab.table_name object_name FROM tab
UNION ALL
SELECT tab.*, 'INDEX', ind.owner object_owner, ind.index_name object_name
FROM tab, ind
WHERE tab.owner = ind.table_owner AND tab.table_name = ind.table_name
) t
WHERE
ap.object_owner = t.object_owner AND ap.object_name = t.object_name AND SUBSTR(ap.object_type,1,5) = t.object_type
AND ap.seconds > 0
GROUP BY
t.owner
, t.table_name
, ap.aindex_operation
, t.num_rows
, ap.cardinality
, ap.executions
, ap.elapsed_time
, ap.aindex_predicates
-- , ap.projection
ORDER BY
seconds DESC
)
WHERE rownum <= 30
/
*******************************************************************************
spool hist_sql_c0sv8y71hhhcy.lst
undefine sql_id
define sql_id='c0sv8y71hhhcy'
set null null
set lines 320
set pages 99
set trimspool on
col snap_beg format a12
col iowait_delta format 99999999.99 heading io|wait|delta|(ms)
col iowait_total format 99999999.99 heading io|wait|total|(ms)
col ELAPSED_TIME_DELTA format 99999999.99 heading elapsd|time|delta|(ms)
col CPU_TIME_DELTA format 99999999.99 heading cpu|time|delta|(ms)
col PLAN_HASH_VALUE heading plan_hash|value
col CONCURRENCY_WAIT_delta format 99999999.99 heading conc|wait|delta|(ms)
col CLUSTER_WAIT_DELTA format 99999999.99 heading clust|wait|delta|(ms)
col PX_SERVERS_EXECS_DELTA format 99999 heading PXServ|Exec|delta
col APWAIT_DELTA format 99999 heading appl|wait|time|delta(micro)
col PLSEXEC_TIME_DELTA format 99999 heading plsql|exec|time|delta(micro)
col JAVAEXEC_TIME_DELTA format 99999 heading java|exec|time|delta(micro)
col optimizer_cost format 9999 heading opt|cost
col optimizer_mode format a10 heading optim|mode
col kept_versions format 999 heading kept|vers
col invalidations_delta format 999 heading inv|alid|dlt
col parse_calls_delta format 99999 heading parse|calls|delta
col executions_delta format 999999 heading exec|delta
col fetches_delta format 9999999 heading fetches|delta
col end_of_fetch_count_delta format 99999 heading end|of|fetch|call|delta
col buffer_gets_delta format 99999999999 heading buffer|gets|delta
col disk_reads_delta format 9999999999 heading disk|reads|delta
col DIRECT_WRITES_DELTA format 99999999 heading direct|writes|delta
col rows_processed_delta format 999999999 heading rows|processed|delta
col rows_ex format 99999999 heading rows|exec
col snap_id format 99999 heading snap|id
col ela_ex format 99999999.99 heading elapsed|per|execution
col cwt_ex format 99999999.99 heading cwt|per|execution
col cc_ex format 99999999.99 heading cc|per|execution
col io_ex format 99999999.99 heading io|per|execution
col instance_number format 99 heading in|ID
select dba_hist_sqlstat.instance_number, sql_id, plan_hash_value,
dba_hist_sqlstat.snap_id,
to_char(dba_hist_snapshot.BEGIN_INTERVAL_TIME,'dd-mm hh24:mi') snap_beg,
invalidations_delta,
parse_calls_delta,
executions_delta,
px_servers_execs_delta,
fetches_delta,
buffer_gets_delta,
disk_reads_delta,
direct_writes_delta,
rows_processed_delta,
elapsed_time_delta/1000 elapsed_time_delta,
cpu_time_delta/1000 cpu_time_delta,
iowait_delta/1000 iowait_delta,
clwait_delta/1000 cluster_wait_delta,
ccwait_delta/1000 concurrency_wait_delta,
substr(optimizer_mode,1,3) opt,
case when executions_delta = 0 then NULL
when cpu_time_delta = 0 then NULL
else
(cpu_time_delta/executions_delta)/1000
end cpu_ex,
case when executions_delta = 0 then NULL
when elapsed_time_delta = 0 then NULL
else
(elapsed_time_delta/executions_delta)/1000
end ela_ex
,substr(SQL_PROFILE,1,32) sql_profile
from dba_hist_sqlstat, dba_hist_snapshot
where sql_id='&&sql_id'
and dba_hist_sqlstat.snap_id=dba_hist_snapshot.snap_id
and dba_hist_sqlstat.instance_number=dba_hist_snapshot.instance_number
order by dba_hist_sqlstat.instance_number, plan_hash_value, dba_hist_sqlstat.snap_id
/
select dba_hist_sqlstat.instance_number, sql_id, plan_hash_value,
dba_hist_sqlstat.snap_id,
to_char(dba_hist_snapshot.BEGIN_INTERVAL_TIME,'dd-mm hh24:mi') snap_beg,
invalidations_delta,
parse_calls_delta,
executions_delta,
elapsed_time_delta/1000 elapsed_time_delta,
cpu_time_delta/1000 cpu_time_delta,
iowait_delta/1000 iowait_delta,
clwait_delta/1000 cluster_wait_delta,
ccwait_delta/1000 concurrency_wait_delta,
substr(optimizer_mode,1,3) opt,
case when executions_delta = 0 then NULL
when rows_processed_delta = 0 then NULL
else
(rows_processed_delta/executions_delta)
end rows_ex,
case when executions_delta = 0 then NULL
when iowait_delta = 0 then NULL
else
(iowait_delta/executions_delta)/1000
end io_ex,
case when executions_delta = 0 then NULL
when clwait_delta = 0 then NULL
else
(clwait_delta/executions_delta)/1000
end cwt_ex,
case when executions_delta = 0 then NULL
when ccwait_delta = 0 then NULL
else
(ccwait_delta/executions_delta)/1000
end cc_ex,
case when executions_delta = 0 then NULL
when cpu_time_delta = 0 then NULL
else
(cpu_time_delta/executions_delta)/1000
end cpu_ex,
case when executions_delta = 0 then NULL
when elapsed_time_delta = 0 then NULL
else
(elapsed_time_delta/executions_delta)/1000
end ela_ex
from dba_hist_sqlstat, dba_hist_snapshot
where sql_id='&&sql_id'
and dba_hist_sqlstat.snap_id=dba_hist_snapshot.snap_id
and dba_hist_sqlstat.instance_number=dba_hist_snapshot.instance_number
order by dba_hist_sqlstat.instance_number, plan_hash_value, dba_hist_sqlstat.snap_id
/
select plan_table_output from table (dbms_xplan.display_awr('&&sql_id',null, null, 'ADVANCED +PEEKED_BINDS'));
select plan_table_output from table (dbms_xplan.display_cursor('&&sql_id', null, 'ADVANCED +PEEKED_BINDS'));
spool off
**************************************************
sql text !!!!!!!!!!!!!!!!!!!
define SQL_ID='asp0p496wn0hk'
set serveroutput on size unlimited
set verify off
whenever sqlerror continue;
DECLARE
myReport CLOB;
l_sql_id VARCHAR2(30) := '&SQL_ID';
--------------------------------------------------
-- Procedure to chunk CLOB printing safely
--------------------------------------------------
PROCEDURE printCLOB (p_clob IN OUT NOCOPY CLOB) IS
i_offset NUMBER := 1;
i_amount INTEGER;
i_clob_length NUMBER := NVL(DBMS_LOB.GETLENGTH(p_clob), 0);
lc_buffer VARCHAR2(8000);
BEGIN
IF i_clob_length = 0 THEN
RETURN;
END IF;
IF (DBMS_LOB.ISOPEN(p_clob) != 1) THEN
DBMS_LOB.OPEN(p_clob, 0);
END IF;
WHILE i_offset <= i_clob_length LOOP
i_amount := 8000;
DBMS_LOB.READ(
lob_loc => p_clob,
amount => i_amount,
offset => i_offset,
buffer => lc_buffer
);
DBMS_OUTPUT.PUT(lc_buffer);
i_offset := i_offset + i_amount;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
IF (DBMS_LOB.ISOPEN(p_clob) = 1) THEN
DBMS_LOB.CLOSE(p_clob);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in printCLOB: ' || SQLERRM);
IF (DBMS_LOB.ISOPEN(p_clob) = 1) THEN
DBMS_LOB.CLOSE(p_clob);
END IF;
END printCLOB;
--------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Looking for SQL text for ' || l_sql_id || '...' || CHR(10));
-- 1. Check Cursor Cache (SGA)
BEGIN
SELECT sql_fulltext
INTO myReport
FROM gv$sqlarea
WHERE sql_id = l_sql_id
AND ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Found ' || l_sql_id || ' in Cursor Cache (gv$sqlarea).');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found in Cursor Cache.');
END;
-- 2. Check AWR Repository if not found in Cache
IF NVL(DBMS_LOB.GETLENGTH(myReport), 0) = 0 THEN
BEGIN
SELECT sql_text
INTO myReport
FROM dba_hist_sqltext
WHERE sql_id = l_sql_id
AND ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Found ' || l_sql_id || ' in AWR Repository (dba_hist_sqltext).');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found in AWR Repository.');
END;
END IF;
-- 3. Print Output
IF NVL(DBMS_LOB.GETLENGTH(myReport), 0) > 0 THEN
DBMS_OUTPUT.PUT_LINE('======================================================');
printCLOB(myReport);
DBMS_OUTPUT.PUT_LINE('======================================================');
ELSE
DBMS_OUTPUT.PUT_LINE('The SQL text for ' || l_sql_id || ' could not be found anywhere.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unhandled Error: ' || SUBSTR(SQLERRM, 1, 255));
END;
/
=====================
-- define sql_id='7jycxu86n60qh' set linesize 300 col begin_interval_time for a28 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, (disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio, (rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows, (CPU_TIME_DELTA/decode(nvl(CPU_TIME_DELTA,0),0,1,executions_delta))/1000000 avg_cpu_wait, (IOWAIT_DELTA/decode(nvl(IOWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_user_io_wait, (CLWAIT_DELTA/decode(nvl(CLWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_clu_wait, (APWAIT_DELTA/decode(nvl(APWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_app_wait, (CCWAIT_DELTA/decode(nvl(CCWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_concurrent_wait from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = :sql_id --sql_id and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3 ;
col inst for 99999999
col sid for 9990
col serial# for 999990
col username for a12
col osuser for a16
col program for a10 trunc
col Locked for a6
col status for a1 trunc print
col "hh:mm:ss" for a8
col SQL_ID for a15
col seq# for 99990
col event heading 'Current/LastEvent' for a25 trunc
col state head 'State (sec)' for a14
col kill for a15
select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, username,
ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser,
substr(program,instr(program,'/',-1)+1,
decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')-1),instr(program,'@')-1)) program, decode(lockwait,NULL,' ','L') locked, status,
to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss",
SQL_ID, seq# , event,
decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6),'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state,substr(module,1,25) module, substr(action,1,20) action
from GV$SESSION
where type = 'USER'
and audsid != 0 -- to exclude internal processess
and sql_id= :sqlid
order by inst_id, status, last_call_et desc, sid
/
set pagesize 200 linesize 300
col spid for a10
col PROGRAM for a10
col action for a10
col logon_time for a16
col module for a13
col cli_process for a7
col cli_mach for a15
col status for a10
col SCHEMANAME for a10
col last_call_et_Hrs for 9999.99
col sql_hash_value for 9999999999999
col username for a10
col "Last SQL" for a60
col elapsed_time for 999999999999
col kill for a15
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,p.spid,s.SCHEMANAME, s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,s.sql_id,PREV_SQL_ID,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and p.addr=s.paddr
--and s.status='INACTIVE'
-- and s.last_call_et > (3600)
--and s.last_call_et > ('&TIME_HRS' * 3600)
--and s.program='JDBC Thin Client'
and s.sql_id= :sqlid
order by last_call_et;
----------------------------
set long 50000
col sql_fulltext for a100 wrap
select sql_fulltext from gv$sql
where sql_id=:sqlid;
--and child_number=&childnr
;
set long 30000 pagesize 500 linesize 300
col frm heading from
select * from (select 'gv$sql' frm , sql_fulltext sql_text from gv$sql where sql_id=:sqlid
union all
select 'dba_hist', sql_text from dba_hist_sqltext where sql_id=:sqlid
)
;
-------
set pagesize 300 linesize 300
SELECT t.* FROM gv$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t
WHERE 1=1
-- and sql_text LIKE '%select id2_65%'
and sql_id =:sqlid
;
set linesize 300 pagesize 1000
column SQL_TEXT format a100 word_wrap
break on sql_text skip 1
SELECT DISTINCT sq.sql_id,dbms_lob.substr(sq.sql_fulltext,4000,1) ||dbms_lob.substr(sq.sql_fulltext,4000,4001) SQL_TEXT from gv$sql sq
where 1=1
and sq.sql_id=:sqlid;
col sql_text for a100 wrap
select *
from sys.WRH$_SQLTEXT
where sql_id=:sqlid;
col sql_id for a20
col plan_name for a35
col created for a30
col FORCE_MATCHING_SIGNATURE for 9999999999999999999
select sql.sql_id, sql.child_number, sql.inst_id, sql.force_matching_signature, sql.plan_hash_value, bl.plan_name, bl.enabled, bl.accepted, bl.fixed, bl.optimizer_cost
from gv$sql sql
, dba_sql_plan_baselines bl
where sql.sql_id=:sqlid
--and sql.child_number=&childnr
and sql.force_matching_signature=bl.SIGNATURE;
col SQL_HANDLE for a30
col origin for a16
col last_modified for a30
col last_verified for a30
select sql_handle, plan_name, origin, created, last_modified, last_verified
from dba_sql_plan_baselines
where signature in (select force_matching_signature
from gv$sql
where sql_id=:sqlid
--and child_number=&childnr
);
col name for a30
col task_exec_name for a16
col category for a10
select sql.sql_id, prof.name, prof.category, prof.created, prof.task_exec_name, prof.status
from DBA_SQL_PROFILES prof
, gv$sql sql
where sql.sql_id=:sqlid
-- and sql.child_number=&childnr
and sql.force_matching_signature=prof.SIGNATURE;
-- set heading off
col outline_hints for a132
select extractvalue(value(d), '/hint') as outline_hints
from xmltable('/*/outline_data/hint' passing
(select xmltype(other_xml) as xmlval
from gv$sql_plan
where sql_id=:sqlid
-- and child_number=childnr
and other_xml is not null
)
) d;
col sql_profile for a30
col sql_patch for a30
col sql_plan_baseline for a35
select sql_id, child_number, inst_id, sql_profile, sql_plan_baseline, sql_patch from gv$sql
where sql_id=:sqlid
--and child_number=&childnr
order by sql_id, inst_id, child_number;
select executions, parse_calls, loads, rows_processed, sorts from gv$sql
where sql_id=:sqlid
--and child_number=&childnr
;
select trunc(elapsed_time/1000000,1) elapsed_time, trunc(application_wait_time/1000000,1) applic_wait_time, trunc(cpu_time/1000000,1) cpu_time,
trunc(user_io_wait_time/1000000,1) user_io_wait_time, trunc(concurrency_wait_time/1000000,1) concurr_time
from gv$sql
where sql_id=:sqlid
--and child_number=&childnr
;
select trunc(elapsed_time/1000000,1) elapsed_time, trunc(application_wait_time/1000000,1) applic_wait_time, trunc(cpu_time/1000000,1) cpu_time,
trunc(user_io_wait_time/1000000,1) user_io_wait_time, trunc(concurrency_wait_time/1000000,1) concurr_time
from gv$sql
where sql_id=:sqlid
--and child_number=&childnr
;
col username for a30
col module for a40
col action for a30
col service for a30
col PARSING_SCHEMA_NAME for a30
select u.username, s.PARSING_SCHEMA_NAME, s.SERVICE, s.MODULE, s.ACTION
from gv$sql s , dba_users u
where s.sql_id=:sqlid
--and s.child_number=&childnr
and u.user_id=s.PARSING_USER_ID;
COLUMN id FORMAT 99
COLUMN operation FORMAT a18
COLUMN options FORMAT a11
COLUMN actual_time FORMAT 99.999 HEADING "Actual|Time"
COLUMN object_name FORMAT a17 HEADING "Object|Name"
COLUMN last_starts FORMAT 9999999 HEADING "Last|Starts"
COLUMN actual_rows FORMAT 9999999 HEADING "Actual|Rows"
SELECT id
,LPAD (' ', DEPTH) || operation operation
,options
,last_elapsed_time / 1000000 actual_time
,object_name
,last_starts
,last_output_rows actual_rows
FROM gv$sql_plan_statistics_all
WHERE sql_id = :sqlid
ORDER BY id;---=========
/* V$SQL_CS_HISTOGRAM – Distributes the frequency (three-bucket histogram) at which Oracle used to decide if a SQL statement was bind-sensitive, including how many times a particular child cursor has been executed. V$SQL_CS_SELECTIVITY – Contains information about the relative selectivity of a SQL statement’s predicates, including the predicates themselves, and their high and low value ranges. V$SQL_CS_STATISTICS – Lists the statistics of whether and/or how often an Adaptive Cursor has been shared. The column PEEKED = Y, then the bind set has been used to build the adaptive cursor. */
col IS_OBSOLETE for a11 col IS_BIND_SENSITIVE for a17 col IS_BIND_AWARE for a13 col IS_SHAREABLE for a12 select IS_OBSOLETE, IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE from gv$sql where sql_id=:sqlid --and child_number=&childnr ; col PREDICATE for a30 select inst_id, sql_id, child_number, predicate,range_id, low, high from GV$SQL_CS_SELECTIVITY where sql_id = :sqlid order by inst_id, child_number; select * from GV$SQL_CS_HISTOGRAM where sql_id = :sqlid order by inst_id, child_number;
col is_bind_sensitive for a20 col is_bind_aware like is_bind_sensitive col is_shareable like is_bind_sensitive select sql_id,child_number, parse_calls,executions, buffer_gets, rows_processed, plan_hash_value, is_bind_sensitive, is_bind_aware, is_shareable from gv$sql where sql_id=:sqlid select sql_id, child_number,predicate, range_id, low, high from gv$sql_cs_selectivity where sql_id in (select sql_id from gv$sql where sql_id=':sqlid') select sql_id,child_number,bind_set_hash_value, peeked, executions, rows_processed, buffer_gets, cpu_time from gv$sql_cs_statistics where sql_id in (select sql_id from gv$sql where sql_id=:sqlid); select * from gv$sql_cs_histogram where sql_id in (select sql_id from gv$sql where sql_id=:sqlid);
--- =================
select buffer_gets, disk_reads, (sharable_mem+persistent_mem+runtime_mem) sql_area_used from gv$sql
where sql_id=:sqlid
--and child_number=&childnr
;
select *
from
xmltable( 'for $a at $i in /ROWSET/ROW
,$r in $a/*
return element ROW{
element ROW_NUM{$i}
,element COL_NAME{$r/name()}
,element COL_VALUE{$r/text()}
}'
passing xmltype(cursor( select *
from gv$sql_shared_cursor
where sql_id=:sqlid
-- and child_number=&childnr
))
columns
row_num int
,col_name varchar2(30)
,col_value varchar2(100)
);
col name for a10
col value_string for a50
col datatype_string for a50
select child_number, name, position
, case datatype
when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
when 12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
else value_string
end value_string
, datatype_string
from gv$sql_bind_capture
where sql_id=:sqlid
-- and child_number=&childnr
order by position;
select 'variable '||replace(name, ':', 'BIND_')||' '||decode(datatype_string, 'TIMESTAMP', 'VARCHAR2(128)', datatype_string) datatype_string
from gv$sql_bind_capture
where sql_id=:sqlid
-- and child_number=&childnr
order by position;
prompt
select 'exec '||replace(name, ':', ':BIND_')||' := '||
case datatype
when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
when 12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
else value_string
end value_string
from gv$sql_bind_capture
where sql_id=:sqlid
--and child_number=&childnr
and datatype_string NOT LIKE '%CHAR%'
and datatype_string NOT IN ('DATE', 'CLOB', 'TIMESTAMP')
order by position;
col exec for a100 wrap
select 'exec '||replace(name, ':', ':BIND_')||' := '''||value_string||'''' exec
from (
select name
, case datatype
when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
when 12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
else value_string
end value_string
, position
from gv$sql_bind_capture
where 1=1
and sql_id=:sqlid
-- and child_number=&childnr
and (datatype_string LIKE '%CHAR%'
OR datatype_string IN ('DATE', 'CLOB', 'TIMESTAMP'))
)
order by position;
col name for a10
col value_string for a50
col datatype_string for a50
select child_number, name, position
, case datatype
when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
when 12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
else value_string
end value_string
, datatype_string
from gv$sql_bind_capture
where 1=1
and sql_id=:sqlid
-- and child_number <> &childnr
order by child_number, position;
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'));
column v_sql_handle new_value l_sql_handle noprint
select distinct plan.SQL_HANDLE v_sql_handle
from dba_sql_plan_baselines plan
, gv$sql sql
where sql.sql_id=:sqlid
--and sql.child_number=&childnr
and sql.force_matching_signature=plan.SIGNATURE;
SELECT * FROM TABLE(dbms_xplan.display_sql_plan_baseline(sql_handle=>'&l_sql_handle'));
select distinct 'select sys.dbms_spm.DROP_SQL_PLAN_BASELINE('''||sql_handle||''') from dual;' "Based on SQL_HANDLE"
from dba_sql_plan_baselines
where signature in (select force_matching_signature
from gv$sql
where sql_id=:sqlid
-- and child_number=&childnr
);
select 'declare v_pls PLS_INTEGER; BEGIN v_pls :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'''||sql_handle||''', plan_name=>'''||plan_name||'''); END;'
from dba_sql_plan_baselines
where signature in (select force_matching_signature
from gv$sql
where sql_id=:sqlid
-- and child_number=&childnr
);
set heading on
SELECT owner, table_name, last_analyzed, sample_size, num_rows, avg_row_len, blocks, partitioned, global_stats
FROM dba_tables
WHERE table_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%TABLE ACCESS%'
)
ORDER BY owner, table_name
/
SELECT ob.owner, ob.object_name, ob.subobject_name, ob.object_type, to_char(savtime, 'DD-MON-YY HH24:MI:SS') savtime, rowcnt, blkcnt, avgrln ,samplesize, analyzetime
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE object_type in ('TABLE')
AND object_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%TABLE ACCESS%'
)
and object_id=obj#
order by ob.owner, ob.object_name, analyzetime asc
;
SELECT owner, index_name, table_name, last_analyzed, sample_size, num_rows, partitioned, global_stats FROM dba_indexes
WHERE index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid,null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
ORDER BY owner, table_name, index_name
/
SELECT table_owner, table_name, partition_name, subpartition_count, last_analyzed, sample_size, num_rows, avg_row_len
FROM dba_tab_partitions
WHERE table_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid,null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%TABLE ACCESS%'
)
ORDER BY table_owner, table_name, partition_name
/
SELECT index_owner, index_name, partition_name, subpartition_count, last_analyzed, sample_size, num_rows
FROM dba_ind_partitions
WHERE index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
ORDER BY index_owner, index_name, partition_name
/
SELECT ob.owner, ob.object_name, ob.subobject_name, ob.object_type,to_char(savtime, 'DD-MON-YY HH24:MI:SS') savtime
, rowcnt, BLEVEL , LEAFCNT, DISTKEY, CLUFAC, samplesize, analyzetime
FROM sys.WRI$_OPTSTAT_IND_HISTORY, dba_objects ob
WHERE object_type in ('INDEX')
AND object_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
and object_id=obj#
order by ob.owner, ob.object_name, analyzetime asc
;
SELECT ic.index_owner, ic.index_name, ic.table_name, ic.column_name, ic.column_position col_pos, tc.last_analyzed, tc. sample_size, tc.num_distinct, tc.num_nulls, tc.density, tc.histogram, tc.num_buckets
FROM dba_ind_columns ic
, dba_tab_columns tc
WHERE ic.index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
AND ic.table_owner=tc.owner
AND ic.table_name=tc.table_name
AND ic.column_name=tc.column_name
ORDER BY ic.table_owner, ic.table_name, ic.index_name, ic.column_position
/
select distinct 'exec sys.dbms_shared_pool.purge('''||address||', '||hash_value||''', ''c'')'
from gv$sql
where sql_id = :sqlid
--and child_number=&childnr
;
-- C or c cursor --'P' or 'p' is stands for procedure --'T' or 't' is stands for type --'R' or 'r' is stands for trigger --'Q' or 'q' is stands for sequence @coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
SELECT ob.owner, ob.object_name, col.COLUMN_NAME, to_char(his.savtime, 'DD-MON-YY HH24:MI:SS') savtime
, his.NULL_CNT, his.DISTCNT, his.DENSITY, his.SAMPLE_DISTCNT, his.sample_size, his.TIMESTAMP#
FROM sys.WRI$_OPTSTAT_HISTHEAD_HISTORY his
, dba_objects ob
, dba_tab_columns col
, dba_ind_columns ic
WHERE ic.index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr(:sqlid, null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
AND ic.table_owner=col.owner
AND ic.table_name=col.table_name
AND ic.column_name=col.column_name
and ob.object_type in ('TABLE')
and ob.object_id=his.obj#
and col.COLUMN_ID=his.INTCOL#
and ob.object_name=col.TABLE_NAME
and ob.owner=col.owner
order by col.owner, col.table_name, ic.column_position, col.column_name, savtime asc
;
alter session set NLS_TIMESTAMP_FORMAT = 'DD-MM-YYYY HH24:MI:SS.FF';
set headin on
col BEGIN_INTERVAL_TIME for a40
col FORCE_MATCHING_SIGNATURE for 9999999999999999999999
select a.BEGIN_INTERVAL_TIME
, a.INSTANCE_NUMBER
, b.PLAN_HASH_VALUE
, b.FORCE_MATCHING_SIGNATURE F_MATCHING_SIGN
, b.SQL_PROFILE
, b.executions_delta EXEC_DELTA
, round(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000,2) "Elapsed time (sec.)"
, b.PX_SERVERS_EXECS_TOTAL PX_SERV_TOT
, b.PX_SERVERS_EXECS_DELTA PX_SERV_DELTA
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
order by a.BEGIN_INTERVAL_TIME
/
select a.BEGIN_INTERVAL_TIME
,b.sql_id
, a.INSTANCE_NUMBER
, b.PLAN_HASH_VALUE
, b.OPTIMIZER_COST
, b.LOADED_VERSIONS
, b.VERSION_COUNT
, b.MODULE
, b.ACTION
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
order by a.BEGIN_INTERVAL_TIME
/
col DATATYPE_STRING for a30
col VALUE_STRING for a30
select a.BEGIN_INTERVAL_TIME
, a.INSTANCE_NUMBER
, b.PLAN_HASH_VALUE
, b.INVALIDATIONS_TOTAL
, c.POSITION
, c.DATATYPE_STRING
, case c.datatype
when 180 then to_char(anydata.accesstimestamp(c.value_anydata),'DD-MON-YYYY HH24:MI:SS')
when 12 then to_char(anydata.accessdate(c.value_anydata),'DD-MON-YYYY HH24:MI:SS')
else c.value_string
end value_string
from dba_hist_snapshot a,
dba_hist_sqlstat b,
table(dbms_sqltune.extract_binds(b.bind_data)) c
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
and b.bind_data is not null
order by a.BEGIN_INTERVAL_TIME, c.POSITION
/
select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, round(max(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "MAX (sec)"
, round(min(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "MIN (sec)"
, round(avg(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "AVG (sec)"
, round(stddev(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "STDDEV"
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
/
select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, max(OPTIMIZER_COST) "MAX COST"
, min(OPTIMIZER_COST) "MIN COST"
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
/
select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, b.MODULE
, b.ACTION
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE, b.MODULE, b.ACTION
/
select a.INSTANCE_NUMBER,b.sql_id, b.PLAN_HASH_VALUE
, round(sum(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "ELAPSED (sec)"
, round(sum(b.CPU_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "CPU (sec)"
, round(sum(b.IOWAIT_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "IO WAIT (sec)"
, round(sum(b.APWAIT_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "APP WAIT (sec)"
, round(sum(b.CLWAIT_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "CLUSTER WAIT (sec)"
, round(sum(b.CCWAIT_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "CONCUR WAIT (sec)"
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a, dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER,b.sql_id, b.PLAN_HASH_VALUE
/
select a.INSTANCE_NUMBER,b.sql_id, b.PLAN_HASH_VALUE
, b.FORCE_MATCHING_SIGNATURE
, b.SQL_PROFILE
, sum(b.SHARABLE_MEM) "SHARABLE MEM"
, sum(b.PARSE_CALLS_DELTA) "TOT PARSE"
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER,b.sql_id, b.PLAN_HASH_VALUE, b.FORCE_MATCHING_SIGNATURE, b.SQL_PROFILE
order by a.INSTANCE_NUMBER
/
select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, sum(b.BUFFER_GETS_DELTA) "BUFFER GETS"
, sum(b.DISK_READS_DELTA) "DISK READS"
, sum(b.DIRECT_WRITES_DELTA) "DIRECT WRITES"
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a, dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
/
select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, sum(b.ROWS_PROCESSED_DELTA) "ROWS_PROCESSED"
, sum(b.SORTS_DELTA) "SORTS"
, sum(b.INVALIDATIONS_DELTA) "INVALIDATIONS"
, sum(b.PX_SERVERS_EXECS_DELTA) "PX_SERVERS_EXECS"
, sum(b.executions_delta) "TOT EXECS"
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and b.sql_id=:sqlid
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
order by a.INSTANCE_NUMBER
/
set heading off
SELECT 'UNBOUND_CURSOR: '||SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))),
'SQL_TYPE_MISMATCH: '||SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))),
'OPTIMIZER_MISMATCH: '||SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))),
'OUTLINE_MISMATCH: '||SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))),
'STATS_ROW_MISMATCH: '||SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))),
'LITERAL_MISMATCH: '||SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))),
'FORCE_HARD_PARSE: '||SUM(TO_NUMBER(DECODE(force_hard_parse,'Y',1,'N','0'))),
'EXPLAIN_PLAN_CURSOR: '||SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))),
'BUFFERED_DML_MISMATCH: '||SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))),
'PDML_ENV_MISMATCH: '||SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))),
'INST_DRTLD_MISMATCH: '||SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))),
'SLAVE_QC_MISMATCH: '||SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))),
'TYPECHECK_MISMATCH: '||SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))),
'AUTH_CHECK_MISMATCH: '||SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))),
'BIND_MISMATCH: '||SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))),
'DESCRIBE_MISMATCH: '||SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))),
'LANGUAGE_MISMATCH: '||SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))),
'TRANSLATION_MISMATCH: '||SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))),
'BIND_EQUIV_FAILURE: '||SUM(TO_NUMBER(DECODE(bind_equiv_failure,'Y',1,'N','0'))),
'INSUFF_PRIVS: '||SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))),
'INSUFF_PRIVS_REM: '||SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))),
'REMOTE_TRANS_MISMATCH: '||SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))),
'LOGMINER_SESSION_MISMATCH: '||SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))) ,
'INCOMP_LTRL_MISMATCH: '||SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))),
'OVERLAP_TIME_MISMATCH: '||SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))),
'EDITION_MISMATCH: '||SUM(TO_NUMBER(DECODE(edition_mismatch,'Y',1,'N','0'))),
'MV_QUERY_GEN_MISMATCH: '||SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))),
'USER_BIND_PEEK_MISMATCH: '||SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))),
'TYPCHK_DEP_MISMATCH: '||SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))),
'NO_TRIGGER_MISMATCH: '||SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))),
'FLASHBACK_CURSOR: '||SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))),
'ANYDATA_TRANSFORMATION: '||SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))),
-- 'INCOMPLETE_CURSOR: '||SUM(TO_NUMBER(DECODE(incomplete_cursor,'Y',1,'N','0'))),
'TOP_LEVEL_RPI_CURSOR: '||SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))),
'DIFFERENT_LONG_LENGTH: '||SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))),
'LOGICAL_STANDBY_APPLY: '||SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))),
'DIFF_CALL_DURN: '||SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))),
'BIND_UACS_DIFF: '||SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))),
'PLSQL_CMP_SWITCHS_DIFF: '||SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))),
'CURSOR_PARTS_MISMATCH: '||SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))),
'STB_OBJECT_MISMATCH: '||SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))),
'CROSSEDITION_TRIGGER_MISMATCH : '||SUM(TO_NUMBER(DECODE(crossedition_trigger_mismatch,'Y',1,'N','0'))),
'PQ_SLAVE_MISMATCH: '||SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))),
'TOP_LEVEL_DDL_MISMATCH: '||SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))),
'MULTI_PX_MISMATCH: '||SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))),
'BIND_PEEKED_PQ_MISMATCH: '||SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))),
'MV_REWRITE_MISMATCH: '||SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))),
'ROLL_INVALID_MISMATCH: '||SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))),
'OPTIMIZER_MODE_MISMATCH: '||SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))),
'PX_MISMATCH: '||SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))),
'MV_STALEOBJ_MISMATCH: '||SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))),
'FLASHBACK_TABLE_MISMATCH: '||SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))),
'LITREP_COMP_MISMATCH: '||SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))),
'PLSQL_DEBUG: '||SUM(TO_NUMBER(DECODE(plsql_debug,'Y',1,'N','0'))),
'LOAD_OPTIMIZER_STATS: '||SUM(TO_NUMBER(DECODE(load_optimizer_stats,'Y',1,'N','0'))),
'ACL_MISMATCH: '||SUM(TO_NUMBER(DECODE(acl_mismatch,'Y',1,'N','0'))),
'FLASHBACK_ARCHIVE_MISMATCH: '||SUM(TO_NUMBER(DECODE(flashback_archive_mismatch,'Y',1,'N','0'))),
'LOCK_USER_SCHEMA_FAILED: '||SUM(TO_NUMBER(DECODE(lock_user_schema_failed,'Y',1,'N','0'))),
'REMOTE_MAPPING_MISMATCH: '||SUM(TO_NUMBER(DECODE(remote_mapping_mismatch,'Y',1,'N','0'))),
'LOAD_RUNTIME_HEAP_FAILED: '||SUM(TO_NUMBER(DECODE(load_runtime_heap_failed,'Y',1,'N','0'))),
'HASH_MATCH_FAILED: '||SUM(TO_NUMBER(DECODE(hash_match_failed,'Y',1,'N','0'))),
'PURGED_CURSOR: '||SUM(TO_NUMBER(DECODE(purged_cursor,'Y',1,'N','0'))),
'BIND_LENGTH_UPGRADEABLE: '||SUM(TO_NUMBER(DECODE(bind_length_upgradeable,'Y',1,'N','0')))
FROM gv$sql_shared_cursor
WHERE address IN (SELECT address
FROM gv$sqlarea
WHERE sql_id = :sqlid)
/
===
column 01 format a2 column 02 format a2 column 03 format a2 column 04 format a2 column 05 format a2 column 06 format a2 column 07 format a2 column 08 format a2 column 09 format a2 column 10 format a2 column 11 format a2 column 12 format a2 column 13 format a2 column 14 format a2 column 15 format a2 column 16 format a2 column 17 format a2 column 18 format a2 column 19 format a2 column 20 format a2 column 21 format a2 column 22 format a2 column 23 format a2 column 24 format a2 column 25 format a2 column 26 format a2 column 27 format a2 column 28 format a2 column 29 format a2 column 30 format a2 column 31 format a2 column 32 format a2 column 33 format a2 column 34 format a2 column 35 format a2 column 36 format a2 column 37 format a2 column 38 format a2 column 39 format a2 column 40 format a2 column 41 format a2 column 42 format a2 column 43 format a2 column 44 format a2 column 45 format a2 column 46 format a2 column 47 format a2 column 48 format a2 column 49 format a2 column 50 format a2 column 51 format a2 column 52 format a2 column 53 format a2 column 54 format a2 column 55 format a2 column 56 format a2 column 57 format a2 column 58 format a2 column 59 format a2 column 60 format a2 column 61 format a2 column 62 format a2 column 63 format a2 select sql_id ,child_number , CON_ID ,sql_type_mismatch "01" ,optimizer_mismatch "02" ,outline_mismatch "03" ,stats_row_mismatch "04" ,literal_mismatch "05" ,force_hard_parse "06" ,explain_plan_cursor "07" ,buffered_dml_mismatch "08" ,pdml_env_mismatch "09" ,inst_drtld_mismatch "10" ,slave_qc_mismatch "11" ,typecheck_mismatch "12" ,auth_check_mismatch "13" ,bind_mismatch "14" ,describe_mismatch "15" ,language_mismatch "16" ,translation_mismatch "17" ,bind_equiv_failure "18" ,insuff_privs "19" ,insuff_privs_rem "20" ,remote_trans_mismatch "21" ,logminer_session_mismatch "22" ,incomp_ltrl_mismatch "23" ,overlap_time_mismatch "24" ,edition_mismatch "25" ,mv_query_gen_mismatch "26" ,user_bind_peek_mismatch "27" ,typchk_dep_mismatch "28" ,no_trigger_mismatch "29" ,flashback_cursor "30" ,anydata_transformation "31" ,pddl_env_mismatch "32" ,top_level_rpi_cursor "33" ,different_long_length "34" ,logical_standby_apply "35" ,diff_call_durn "36" ,bind_uacs_diff "37" ,plsql_cmp_switchs_diff "38" ,cursor_parts_mismatch "39" ,stb_object_mismatch "40" ,crossedition_trigger_mismatch "41" ,pq_slave_mismatch "42" ,top_level_ddl_mismatch "43" ,multi_px_mismatch "44" ,bind_peeked_pq_mismatch "45" ,mv_rewrite_mismatch "46" ,roll_invalid_mismatch "47" ,optimizer_mode_mismatch "48" ,px_mismatch "49" ,mv_staleobj_mismatch "50" ,flashback_table_mismatch "51" ,litrep_comp_mismatch "52" ,plsql_debug "53" ,load_optimizer_stats "54" ,acl_mismatch "55" ,flashback_archive_mismatch "56" ,lock_user_schema_failed "57" ,remote_mapping_mismatch "58" ,load_runtime_heap_failed "59" ,hash_match_failed "60" ,purged_cursor "61" ,bind_length_upgradeable "62" ,use_feedback_stats "63" from gv$sql_shared_cursor where 1=1 -- sql_id = '&sql_id' and CHILD_NUMBER>10 order by child_number ; SQL_ID CHILD_NUMBER CON_ID 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 ------------- ------------ ---------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 3wrrjm9qtr2my 11 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N 3y9bat7nwcjy5 11 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N Y N N N N N N N N N N N N N N N N 6fjdakmfd1d9f 11 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N Y N N N N N N N N N N N N N N N N
set pagesize 400
col SQL_TEXT for a50 wrap
col REASON for a40
col PARSING_SCHEMA_NAME for a20
set lines 180
select sql_id,sc.address,version_count,parsing_schema_name,reason,lpad(' ',10,'-')||replace(sql_text,(13)) sql_text from (
select
sql_id, address, '** reason => '
||decode(max(UNBOUND_CURSOR),'Y','UNBOUND_CURSOR '||': '||count(*)||' | ')
||decode(max(SQL_TYPE_MISMATCH),'Y','SQL_TYPE_MISMATCH '||': '||count(*)||' | ')
||decode(max(OPTIMIZER_MISMATCH),'Y','OPTIMIZER_MISMATCH '||': '||count(*)||' | ')
||decode(max(OUTLINE_MISMATCH),'Y','OUTLINE_MISMATCH '||': '||count(*)||' | ')
||decode(max(STATS_ROW_MISMATCH),'Y','STATS_ROW_MISMATCH '||': '||count(*)||' | ')
||decode(max(LITERAL_MISMATCH),'Y','LITERAL_MISMATCH '||': '||count(*)||' | ')
||decode(max(FORCE_HARD_PARSE),'Y','FORCE_HARD_PARSE '||': '||count(*)||' | ')
||decode(max(EXPLAIN_PLAN_CURSOR),'Y','EXPLAIN_PLAN_CURSOR '||': '||count(*)||' | ')
||decode(max(BUFFERED_DML_MISMATCH),'Y','BUFFERED_DML_MISMATCH '||': '||count(*)||' | ')
||decode(max(PDML_ENV_MISMATCH),'Y','PDML_ENV_MISMATCH '||': '||count(*)||' | ')
||decode(max(INST_DRTLD_MISMATCH),'Y','INST_DRTLD_MISMATCH '||': '||count(*)||' | ')
||decode(max(SLAVE_QC_MISMATCH),'Y','SLAVE_QC_MISMATCH '||': '||count(*)||' | ')
||decode(max(TYPECHECK_MISMATCH),'Y','TYPECHECK_MISMATCH '||': '||count(*)||' | ')
||decode(max(AUTH_CHECK_MISMATCH),'Y','AUTH_CHECK_MISMATCH '||': '||count(*)||' | ')
||decode(max(BIND_MISMATCH),'Y','BIND_MISMATCH '||': '||count(*)||' | ')
||decode(max(DESCRIBE_MISMATCH),'Y','DESCRIBE_MISMATCH '||': '||count(*)||' | ')
||decode(max(LANGUAGE_MISMATCH),'Y','LANGUAGE_MISMATCH '||': '||count(*)||' | ')
||decode(max(TRANSLATION_MISMATCH),'Y','TRANSLATION_MISMATCH '||': '||count(*)||' | ')
||decode(max(BIND_EQUIV_FAILURE),'Y','BIND_EQUIV_FAILURE '||': '||count(*)||' | ')
||decode(max(INSUFF_PRIVS),'Y','INSUFF_PRIVS '||': '||count(*)||' | ')
||decode(max(INSUFF_PRIVS_REM),'Y','INSUFF_PRIVS_REM '||': '||count(*)||' | ')
||decode(max(REMOTE_TRANS_MISMATCH),'Y','REMOTE_TRANS_MISMATCH '||': '||count(*)||' | ')
||decode(max(LOGMINER_SESSION_MISMATCH),'Y','LOGMINER_SESSION_MISMATCH '||': '||count(*)||' | ')
||decode(max(INCOMP_LTRL_MISMATCH),'Y','INCOMP_LTRL_MISMATCH '||': '||count(*)||' | ')
||decode(max(OVERLAP_TIME_MISMATCH),'Y','OVERLAP_TIME_MISMATCH '||': '||count(*)||' | ')
||decode(max(EDITION_MISMATCH),'Y','EDITION_MISMATCH '||': '||count(*)||' | ')
||decode(max(MV_QUERY_GEN_MISMATCH),'Y','MV_QUERY_GEN_MISMATCH '||': '||count(*)||' | ')
||decode(max(USER_BIND_PEEK_MISMATCH),'Y','USER_BIND_PEEK_MISMATCH '||': '||count(*)||' | ')
||decode(max(TYPCHK_DEP_MISMATCH),'Y','TYPCHK_DEP_MISMATCH '||': '||count(*)||' | ')
||decode(max(NO_TRIGGER_MISMATCH),'Y','NO_TRIGGER_MISMATCH '||': '||count(*)||' | ')
||decode(max(FLASHBACK_CURSOR),'Y','FLASHBACK_CURSOR '||': '||count(*)||' | ')
||decode(max(ANYDATA_TRANSFORMATION),'Y','ANYDATA_TRANSFORMATION '||': '||count(*)||' | ')
||decode(max(PDDL_ENV_MISMATCH),'Y','PDDL_ENV_MISMATCH '||': '||count(*)||' | ')
||decode(max(TOP_LEVEL_RPI_CURSOR),'Y','TOP_LEVEL_RPI_CURSOR '||': '||count(*)||' | ')
||decode(max(DIFFERENT_LONG_LENGTH),'Y','DIFFERENT_LONG_LENGTH '||': '||count(*)||' | ')
||decode(max(LOGICAL_STANDBY_APPLY),'Y','LOGICAL_STANDBY_APPLY '||': '||count(*)||' | ')
||decode(max(DIFF_CALL_DURN),'Y','DIFF_CALL_DURN '||': '||count(*)||' | ')
||decode(max(BIND_UACS_DIFF),'Y','BIND_UACS_DIFF '||': '||count(*)||' | ')
||decode(max(PLSQL_CMP_SWITCHS_DIFF),'Y','PLSQL_CMP_SWITCHS_DIFF '||': '||count(*)||' | ')
||decode(max(CURSOR_PARTS_MISMATCH),'Y','CURSOR_PARTS_MISMATCH '||': '||count(*)||' | ')
||decode(max(STB_OBJECT_MISMATCH),'Y','STB_OBJECT_MISMATCH '||': '||count(*)||' | ')
||decode(max(CROSSEDITION_TRIGGER_MISMATCH),'Y','CROSSEDITION_TRIGGER_MISMATCH '||': '||count(*)||' | ')
||decode(max(PQ_SLAVE_MISMATCH),'Y','PQ_SLAVE_MISMATCH '||': '||count(*)||' | ')
||decode(max(TOP_LEVEL_DDL_MISMATCH),'Y','TOP_LEVEL_DDL_MISMATCH '||': '||count(*)||' | ')
||decode(max(MULTI_PX_MISMATCH),'Y','MULTI_PX_MISMATCH '||': '||count(*)||' | ')
||decode(max(BIND_PEEKED_PQ_MISMATCH),'Y','BIND_PEEKED_PQ_MISMATCH '||': '||count(*)||' | ')
||decode(max(MV_REWRITE_MISMATCH),'Y','MV_REWRITE_MISMATCH '||': '||count(*)||' | ')
||decode(max(ROLL_INVALID_MISMATCH),'Y','ROLL_INVALID_MISMATCH '||': '||count(*)||' | ')
||decode(max(OPTIMIZER_MODE_MISMATCH),'Y','OPTIMIZER_MODE_MISMATCH '||': '||count(*)||' | ')
||decode(max(PX_MISMATCH),'Y','PX_MISMATCH '||': '||count(*)||' | ')
||decode(max(MV_STALEOBJ_MISMATCH),'Y','MV_STALEOBJ_MISMATCH '||': '||count(*)||' | ')
||decode(max(FLASHBACK_TABLE_MISMATCH),'Y','FLASHBACK_TABLE_MISMATCH '||': '||count(*)||' | ')
||decode(max(LITREP_COMP_MISMATCH),'Y','LITREP_COMP_MISMATCH '||': '||count(*)||' | ')
||decode(max(PLSQL_DEBUG),'Y','PLSQL_DEBUG '||': '||count(*)||' | ')
||decode(max(LOAD_OPTIMIZER_STATS),'Y','LOAD_OPTIMIZER_STATS '||': '||count(*)||' | ')
||decode(max(ACL_MISMATCH),'Y','ACL_MISMATCH '||': '||count(*)||' | ')
||decode(max(FLASHBACK_ARCHIVE_MISMATCH),'Y','FLASHBACK_ARCHIVE_MISMATCH '||': '||count(*)||' | ')
||decode(max(LOCK_USER_SCHEMA_FAILED),'Y','LOCK_USER_SCHEMA_FAILED '||': '||count(*)||' | ')
||decode(max(REMOTE_MAPPING_MISMATCH),'Y','REMOTE_MAPPING_MISMATCH '||': '||count(*)||' | ')
||decode(max(LOAD_RUNTIME_HEAP_FAILED),'Y','LOAD_RUNTIME_HEAP_FAILED '||': '||count(*)||' | ')
||decode(max(HASH_MATCH_FAILED),'Y','HASH_MATCH_FAILED '||': '||count(*)||' | ')
||decode(max(PURGED_CURSOR),'Y','PURGED_CURSOR '||': '||count(*)||' | ')
||decode(max(BIND_LENGTH_UPGRADEABLE),'Y','BIND_LENGTH_UPGRADEABLE '||': '||count(*)||' | ')
||decode(max(USE_FEEDBACK_STATS),'Y','USE_FEEDBACK_STATS '||': '||count(*)||' | ')
reason
from
v$sql_shared_cursor
group by
sql_id, address
) sc join v$sqlarea sq using(sql_id)
where version_count > 200
and parsing_schema_name not in ('SYS')
order by sql_id, version_count
;
=== set heading on
ref
https://github.com/Marcel-Jan/oracle_performance/blob/master/sqlperf.sql
=================================
set linesize 500
col "Total time" for a15
col "Average time" for a15
col "Average time PX" for a15
col "Avg time/1M rows" for a15
col "Avg time/row" for a15
-- tab=Executions (recent)
select sql_id
,trunc(last_active_time) as "Run date"
, to_char(min(last_active_time),'HH24:MI:SS') as "First"
, to_char(max(last_active_time),'HH24:MI:SS') as "Last"
, plan_hash_value as "Plan hash"
, sum(executions_calc) as "Times called"
, sum(end_of_fetch_count) as "Times completed"
, least(100, round(100 * sum(end_of_fetch_count) / sum(executions_calc),1)) as "Success %"
, cast(numtodsinterval(sum(elapsed_time)/1e6,'SECOND') as interval day(1) to second(2)) as "Total time"
-- , round(sum(elapsed_time)/1e6) as "Total seconds"
, round(sum(elapsed_time)/1e6 / nvl(sum(executions_calc),1),1) as "Average (s)"
, round(sum(elapsed_time)/1e6 / nvl(sum(executions_calc),1)/nvl(nullif(sum(px_servers_executions),0),1)) as "Average (s) PX"
, cast(numtodsinterval(sum(elapsed_time)/1e6 / nvl(sum(executions_calc),1),'SECOND') as interval day(1) to second(1)) as "Average time"
, cast(numtodsinterval(sum(elapsed_time)/1e6 / nvl(sum(executions_calc),1) /nvl(nullif(sum(px_servers_executions),0),1),'SECOND') as interval day(1) to second(1)) as "Average time PX"
-- , sum(buffer_gets) as "Buffer gets"
, round(sum(buffer_gets)/sum(executions_calc)) as "Buffer gets/exec"
, round(sum(buffer_gets)/nullif(sum(rows_processed),0)) as "Buffer gets/row"
, sum(rows_processed) as "Rows"
, round(sum(rows_processed) / sum(executions_calc)) as "Rows/exec"
, cast(numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0)/1e6,'SECOND') as interval day(1) to second(3)) as "Avg time/row"
, cast
( case
when sum(elapsed_time)/nullif(sum(rows_processed),0) < 2147483647 then -- 2**31 -1, limit for 32 bit integers
numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0),'SECOND')
else numtodsinterval(sum(elapsed_time/3600)/nullif(sum(rows_processed),0),'HOUR')
end -- Avoid "ORA-01873: the leading precision of the interval is too small" for large values
as interval day(5) to second(0)) as "Avg time/1M rows"
, sum(px_servers_executions) as "PX server executions"
from ( select s.sql_id
, s.last_active_time
, plan_hash_value
--, executions
, case executions when 0 then 1 else executions end as executions_calc -- to use in expressions without NULLIF
, px_servers_executions
, elapsed_time
, buffer_gets
, rows_processed
, end_of_fetch_count
from gv$sqlstats s )
where sql_id = trim('&sql_id')
group by trunc(last_active_time), plan_hash_value,sql_id
having sum(executions_calc) > 0
order by trunc(last_active_time), plan_hash_value;
select run_date as "Run date"
, "First"
, "Last"
, sql_id
, plan_hash_value as "Plan hash"
, executions as "Times called"
, end_of_fetch_count as "Times completed"
, success_rate as "Success %"
, elapsed_time as "Total time"
, avg_time as "Avg time"
, avg_s as "Avg seconds"
, round(avg_s * (sum(avg_rows) over() / greatest(sum(avg_s) over(),1))) as "Avg s scaled to rows" -- for charting time vs rows
, avg_rows as "Avg rows"
, avg_bg as "Avg Buffer gets"
, bg_per_row as "Buffer gets/row"
, avg_time_per_row as "Time/row"
, px_servers_execs as "PX server executions"
from
( select trunc(cast(t.begin_interval_time as date)) as run_date
,s.sql_id
, plan_hash_value
, to_char(min(cast(t.begin_interval_time as date)),'HH24:MI:SS') as "First"
, to_char(max(cast(t.end_interval_time as date)),'HH24:MI:SS') as "Last"
, sum(s.executions_delta) as executions
, sum(s.end_of_fetch_count_delta) as end_of_fetch_count
, max(s.executions_total) as executions_total
, max(s.end_of_fetch_count_total) as end_of_fetch_count_total
, least(100, round(100 * max(s.end_of_fetch_count_total) / nullif(max(s.executions_total),0),1)) as success_rate
, cast(numtodsinterval(max(s.elapsed_time_total)/1e6,'SECOND') as interval day(1) to second(2)) as elapsed_time
, cast(numtodsinterval(max(s.elapsed_time_total)/1e6 / nvl(nullif(max(s.executions_total),0),1),'SECOND') as interval day(1) to second(1)) as avg_time
, round(max(s.elapsed_time_total)/1e6 / nvl(nullif(max(s.executions_total),0),1),1) as avg_s
, round(max(s.buffer_gets_total)/nullif(max(s.executions_total),0)) as avg_bg
, round(max(s.buffer_gets_total)/nullif(max(s.rows_processed_total),0)) as bg_per_row
, max(s.rows_processed_total) as rows_processed
, round(max(s.rows_processed_total) / nullif(max(s.executions_total),0)) as avg_rows
, cast(numtodsinterval(max(s.elapsed_time_total)/nullif(max(s.rows_processed_total),0)/1e6,'SECOND') as interval day(1) to second(3)) as avg_time_per_row
, max(s.elapsed_time_total)/nullif(max(s.rows_processed_total),0)/1e6 as avg_s_per_row
, max(s.px_servers_execs_total) as px_servers_execs
from dba_hist_sqlstat s
join dba_hist_snapshot t on t.snap_id = s.snap_id and t.dbid = s.dbid and t.instance_number = s.instance_number
where s.sql_id = trim('&sql_id')
group by trunc(cast(t.begin_interval_time as date)), s.plan_hash_value,s.sql_id )
order by 1, 2, plan_hash_value;
======
set linesize 500 pagesize 300
col "Avg time/1M rows" for a15
col "Avg time/row" for a15
col "Total time" for a15
col "Average seconds" for 999999999999
col "Average time" for a15
select
sql_id ,
plan_hash_value as "Plan hash"
, sum(executions_calc) as "Times called"
, sum(end_of_fetch_count) as "Times completed"
, round(100 * sum(end_of_fetch_count) / sum(executions_calc),1) as "Success %"
, cast(numtodsinterval(sum(elapsed_time)/1E6,'SECOND') as interval day(1) to second(2)) as "Total time"
-- , round(sum(elapsed_time)/1e6) as "Total seconds"
, round(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1),1) as "Average seconds"
, cast(numtodsinterval(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1),'SECOND') as interval day(1) to second(1)) as "Average time"
-- , sum(buffer_gets) as "Buffer gets"
, round(sum(buffer_gets)/sum(executions_calc)) as "Buffer gets/exec"
, round(sum(buffer_gets)/nullif(sum(rows_processed),0)) as "Buffer gets/row"
, sum(rows_processed) as "Rows"
, round(sum(rows_processed) / sum(executions_calc)) as "Rows/exec"
, cast(numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0)/1E6,'SECOND') as interval day(1) to second(3)) as "Avg time/row"
, cast
( case
when sum(elapsed_time)/nullif(sum(rows_processed),0) < 2147483647 then -- 2**31 -1, limit for 32 bit integers and a
numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0),'SECOND')
else numtodsinterval(sum(elapsed_time/3600)/nullif(sum(rows_processed),0),'HOUR')
end -- Avoid "ORA-01873: the leading precision of the interval is too small" for large values
as interval day(5) to second(0)) as "Avg time/1M rows"
, sum(px_servers_executions) as "PX server executions"
from ( select s.sql_id sql_id
, trunc(s.last_active_time) as exec_date
, plan_hash_value
--, executions
, case executions when 0 then 1 else executions end as executions_calc -- to use in expressions without NULLIF
, px_servers_executions
, elapsed_time
, buffer_gets
, rows_processed
, end_of_fetch_count
from gv$sqlstats s
union
select s.sql_id
, trunc(cast(h.begin_interval_time as date)) as exec_date
, plan_hash_value
--, executions_delta executions
, nullif(executions_delta,0) executions_calc
, px_servers_execs_delta as px_servers_executions
, elapsed_time_delta as elapsed_time
, buffer_gets_delta as buffer_gets
, rows_processed_delta as rows_processed
, end_of_fetch_count_delta as end_of_fetch_count
from dba_hist_sqlstat s
join dba_hist_snapshot h on h.snap_id = s.snap_id and h.dbid = s.dbid and h.instance_number = s.instance_number
)
where 1=1
--and sql_id = trim('&sql_id')
group by sql_id,plan_hash_value
having sum(executions_calc) > 0
order by plan_hash_value;
set linesize 80
======
set linesize 500
col sql_text for a50
define 1='3s1yukp05bzg6'
select * from (select st.inst_id ,
st.con_id,
st.executions as execs,
st.sql_id,
st.plan_hash_value as plan,
round(st.elapsed_time/decode(st.executions,0,1,st.executions)) as ela_per_exec,
round(st.cpu_time/decode(st.executions,0,1,st.executions)) as cpu_per_exec,
round(st.buffer_gets/decode(st.executions,0,1,st.executions)) as gets_per_exec,
round(st.concurrency_wait_time/decode(st.executions,0,1,st.executions)) as conc_per_exec,
round(st.cluster_wait_time/decode(st.executions,0,1,st.executions)) as clu_per_exec,
round(st.user_io_wait_time/decode(st.executions,0,1,st.executions)) as uio_per_exec,
round(st.physical_read_bytes/decode(st.executions,0,1,st.executions)/1024/1024) as read_mb_per_exec,
round(st.physical_read_requests/decode(st.executions,0,1,st.executions)) as reads_per_exec,
round(st.disk_reads/decode(st.executions,0,1,st.executions)/1024/1024) as disk_reads_per_exec,
round(st.physical_write_bytes/decode(st.executions,0,1,st.executions)/1024/1024) as writes_mb_per_exec,
round(st.physical_write_requests/decode(st.executions,0,1,st.executions)) as writes_per_exec,
round(st.direct_writes/decode(st.executions,0,1,st.executions)) as direct_writes_per_exec,
round(st.rows_processed/decode(st.executions,0,1,st.executions)) as rows_per_exec,
round(st.px_servers_executions/decode(st.executions,0,1,st.executions)) as px_per_exec,
substr(sql_text,1,50) sql_text
from gv$sqlstats st
where 1=1
-- and st.con_id!=0
-- and sql_id in ('&&1')
-- order by ela_per_exec
-- order by cpu_per_exec
-- order by gets_per_exec
-- order by conc_per_exec
-- order by clu_per_exec
-- order by read_mb_per_exec
-- order by reads_per_exec
-- order by disk_reads_per_exec
-- order by writes_mb_per_exec
-- order by writes_per_exec
-- order by direct_writes_per_exec
order by rows_per_exec
)
where 1=1
and rownum <51
/=====
SQL Monitor reports
col SQL_EXEC_ID for a20
col SQL_EXEC_START for a27
col SQL_ID for a20
SELECT * from ( select report_id,
EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_id') sql_id,
EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_exec_id') sql_exec_id,
EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_exec_start') sql_exec_start
FROM dba_hist_reports
WHERE component_name = 'sqlmonitor'
and report_id > ( select max(report_id) -10 from dba_hist_reports)
)
where 1=1
--and to_date(SQL_EXEC_START,'mm/dd/yyyy') >sysdate -1
;
REPORT_ID SQL_ID SQL_EXEC_ID SQL_EXEC_START
---------- -------------------- -------------------- ---------------------------
11202388 0nkzfyf2zgmn4 16777216 12/16/2023 11:22:22
11202123 ghzqum97067kv 16777486 12/16/2023 10:30:56
11202204 f254uv2p53y7j 16778741 12/16/2023 10:47:38
11202205 4pzfupdrwqxud 16778793 12/16/2023 10:47:38
11202486 0rkyj8cvvf3w2 16809402 12/16/2023 11:51:49
11202491 3uc5umkn5dm2w 16777470 12/16/2023 11:53:15
11202512 99k1tpada3gsk 16777216 12/16/2023 11:57:43
set long 10000000 longchunksize 10000000 pages 0 linesize 300
select dbms_auto_report.report_repository_detail(RID => 727796, TYPE => 'text') FROM dual;
=====
set linesize 400 pagesize 300
define sql_id='f705bwx3q0ydq'
col KEY1 for a15
col KEY2 for a15
col KEY3 for a15
col KEY4 for a15
col REPORT_PARAMETERS for a20
col REPORT_SUMMARY for a50 wrap
col COMPONENT_NAME for a20
col REPORT_NAME for a20
col report_id new_v report_id
select * from
(
select * from DBA_HIST_REPORTS
where 1=1
and key1 = '&&sql_id'
and COMPONENT_NAME = 'sqlmonitor'
order by generation_time desc
)
where rownum = 1;
====
set linesize 500 pagesize 300
col report_summary for a50
col strt for a22
col plan_hash for a17
col dop for a15
col username for a15
col txt for a15
col ins for a15
col dop_reqs for a15
col dop_alloc for a15
col act for a15
col mod for a15
col sql_exec_id for a17
col sql_id for a17
col sql_text for a20 wrap
SELECT /*+ NO_XML_QUERY_REWRITE */
t.report_id rid,
to_number(user_fetch_count) fetches,
X1.sql_exec_start strt,
x1.sql_id,
x1.plan_hash,
X1.dop,
to_number(x1.duration) elp,
round(x1.elapsed_time / 1000000) Etot,
round(x1.cpu_time / 1000000) cpu_time,
round(x1.user_io_wait_time / 1000000) io_time,
round(to_number(x1.read_bytes) / 1024 / 1024/1024) GBr,
round(to_number(x1.write_bytes) / 1024 / 1024/1024) GBw,
to_char(substr(x1.sql_text,1,4000)) txt,
inst_id ins,
t.session_id sid,
t.SESSION_SERIAL# ser,
x1.action act,
x1.module mod,
x1.username,
x1.sql_text,
round(application_wait_time / 1000000) app,
round(concurrency_wait_time / 1000000) con,
round(cluster_wait_time / 1000000) clustert,
round(plsql_exec_time / 1000000) plsql,
round(other_wait_time / 1000000) other,
round(sum(x1.elapsed_time) over(partition by t.snap_id) / 1000000) elp_snap,
t.snap_id snap,
x1.px_servers_requested dop_reqs,
x1.px_servers_allocated dop_alloc,
x1.sql_exec_id
,t.report_summary
--to_char(substr(b.sql_text,1,4000))
FROM dba_hist_reports t,
--dba_hist_sqltext b ,
xmltable('/report_repository_summary/sql' PASSING
xmlparse(document t.report_summary) COLUMNS sql_id path
'@sql_id',
sql_exec_start path '@sql_exec_start',
sql_exec_id path '@sql_exec_id',
status path 'status',
sql_text path 'sql_text',
first_refresh_time path 'first_refresh_time',
last_refresh_time path 'last_refresh_time',
refresh_count path 'refresh_count',
inst_id path 'inst_id',
session_id path 'session_id',
session_serial path 'session_serial',
user_id path 'user_id',
username path 'user',
con_id path 'con_id',
con_name path 'con_name',
module path 'module',
action path 'action',
service path 'service',
program path 'program',
plan_hash path 'plan_hash',
is_cross_instance path 'is_cross_instance',
dop path 'dop',
instances path 'instances',
px_servers_requested path 'px_servers_requested',
px_servers_allocated path 'px_servers_allocated',
duration path 'stats/stat[@name="duration"]',
elapsed_time path 'stats/stat[@name="elapsed_time"]',
cpu_time path 'stats/stat[@name="cpu_time"]',
user_io_wait_time path
'stats/stat[@name="user_io_wait_time"]',
user_fetch_count path
'stats/stat[@name="user_fetch_count"]',
application_wait_time path
'stats/stat[@name="application_wait_time"]',
concurrency_wait_time path
'stats/stat[@name="concurrency_wait_time"]',
cluster_wait_time path
'stats/stat[@name="cluster_wait_time"]',
plsql_exec_time path 'stats/stat[@name="plsql_exec_time"]',
other_wait_time path 'stats/stat[@name="other_wait_time"]',
buffer_gets path 'stats/stat[@name="buffer_gets"]',
read_reqs path 'stats/stat[@name="read_reqs"]',
read_bytes path 'stats/stat[@name="read_bytes"]',
write_bytes path 'stats/stat[@name="write_bytes"]'
) x1,
dba_hist_snapshot C
where 1=1
--and x1.elapsed_time / 1000000 > 200
and t.COMPONENT_NAME = 'sqlmonitor'
and C.SNAP_ID = t.snap_id
and c.instance_number = inst_id
and upper( x1.action) like '%%'
--and to_char(c.BEGIN_INTERVAL_TIME, 'HH24') in (23,0,1,2)--- and 8
--and X1.dop > 0
--and t.snap_id between 42620 and 42626
--and t.key1 in (/*'8r3pnzg9ncard','6judcbbtju567',*/'3n2s2zpkqu016')
--and t.snap_id=43994
--and c.BEGIN_INTERVAL_TIME > sysdate-50
--and c.BEGIN_INTERVAL_TIME < sysdate - 1
--and c.BEGIN_INTERVAL_TIME > sysdate - 15
and c.BEGIN_INTERVAL_TIME > sysdate - interval '60' minute
order by abs( to_number(x1.duration) ) desc
;
========================
from https://www.williamrobertson.net/code/performance_by_sqlid_PLSQLDEV.sql
SET PAGESIZE 100
SET LINESIZE 250
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET VERIFY OFF
COLUMN "Plan hash" FORMAT 99999999999
COLUMN "Times called" FORMAT 999,999,990
COLUMN "Times completed" FORMAT 999,999,990
COLUMN "Success %" FORMAT 990.0
COLUMN "Total time" FORMAT A16
COLUMN "Average seconds" FORMAT 999,990.0
COLUMN "Average time" FORMAT A16
COLUMN "Average time PX" FORMAT A16
COLUMN "Buffer gets/exec" FORMAT 999,999,990
COLUMN "Buffer gets/row" FORMAT 999,999,990
COLUMN "Rows" FORMAT 999,999,990
COLUMN "Rows/exec" FORMAT 999,999,990
COLUMN "Avg time/row" FORMAT A18
COLUMN "Avg time/1M rows" FORMAT A20
COLUMN "PX server executions" FORMAT 999,999,990
COLUMN "Run date" FORMAT A10
COLUMN "First" FORMAT A8
COLUMN "Last" FORMAT A8
COLUMN "Avg time" FORMAT A16
COLUMN "Avg seconds" FORMAT 999,990.0
COLUMN "Avg rows" FORMAT 999,999,990
COLUMN "Avg Buffer gets" FORMAT 999,999,990
COLUMN "Time/row" FORMAT A18
COLUMN sql_id FORMAT A13
COLUMN sql_exec_start FORMAT A19
COLUMN elapsed FORMAT A12
COLUMN top_level_call FORMAT A30 TRUNCATED
COLUMN current_procedure FORMAT A35 TRUNCATED
COLUMN wait_object FORMAT A35 TRUNCATED
COLUMN username FORMAT A15
COLUMN source FORMAT A35 TRUNCATED
COLUMN command_type FORMAT A15
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A35 TRUNCATED
COLUMN operation FORMAT A30 TRUNCATED
COLUMN name FORMAT A20
COLUMN value_string FORMAT A40 TRUNCATED
COLUMN datatype_string FORMAT A15
COLUMN sql_text FORMAT A100 WRAPPED
COLUMN object_owner FORMAT A15
COLUMN object_type FORMAT A15
COLUMN object_name FORMAT A30 TRUNCATED
COLUMN "Stale?" FORMAT A6
COLUMN operation FORMAT A25 TRUNCATED
COLUMN cardinality FORMAT 999,999,990
COLUMN "Rows (global)" FORMAT 999,999,990
COLUMN blocks FORMAT 999,999,990
COLUMN last_analyzed FORMAT A10
COLUMN partition_start FORMAT A15 TRUNCATED
COLUMN partition_stop FORMAT A15 TRUNCATED
define sql_id='7fpm6cqb98088'
PROMPT =======================================================================
PROMPT 1. PLANS SUMMARY (v$sqlstats + AWR)
PROMPT =======================================================================
SELECT 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 / 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"
, 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
NUMTODSINTERVAL(SUM(elapsed_time) / NULLIF(SUM(rows_processed), 0), 'SECOND')
ELSE NUMTODSINTERVAL(SUM(elapsed_time / 3600) / NULLIF(SUM(rows_processed), 0), 'HOUR')
END 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
, TRUNC(s.last_active_time) AS exec_date
, plan_hash_value
, CASE executions WHEN 0 THEN 1 ELSE executions END AS executions_calc
, px_servers_executions
, elapsed_time
, buffer_gets
, rows_processed
, end_of_fetch_count
FROM v$sqlstats s
UNION ALL
SELECT s.sql_id
, TRUNC(CAST(h.begin_interval_time AS DATE)) AS exec_date
, plan_hash_value
, NULLIF(executions_delta, 0) AS 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 sql_id = TRIM('&&sql_id')
GROUP BY plan_hash_value
HAVING SUM(executions_calc) > 0
ORDER BY plan_hash_value;
PROMPT
PROMPT =======================================================================
PROMPT 2. RECENT EXECUTIONS (v$sqlstats)
PROMPT =======================================================================
SELECT TO_CHAR(TRUNC(last_active_time), 'YYYY-MM-DD') 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 / 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"
, 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"
, SUM(px_servers_executions) AS "PX server executions"
FROM ( SELECT s.sql_id
, s.last_active_time
, plan_hash_value
, CASE executions WHEN 0 THEN 1 ELSE executions END AS executions_calc
, px_servers_executions
, elapsed_time
, buffer_gets
, rows_processed
, end_of_fetch_count
FROM v$sqlstats s )
WHERE sql_id = TRIM('&&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;
PROMPT
PROMPT =======================================================================
PROMPT 3. HISTORICAL EXECUTIONS (AWR)
PROMPT =======================================================================
SELECT TO_CHAR(run_date, 'YYYY-MM-DD') AS "Run date"
, "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"
, avg_rows AS "Avg rows"
, avg_bg AS "Avg Buffer gets"
, bg_per_row AS "Buffer gets/row"
, avg_time_per_row AS "Time/row"
, px_servers_execs AS "PX server executions"
FROM ( SELECT TRUNC(CAST(t.begin_interval_time AS DATE)) AS run_date
, 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
, LEAST(100, ROUND(100 * SUM(s.end_of_fetch_count_delta) / NULLIF(SUM(s.executions_delta), 0), 1)) AS success_rate
, CAST(NUMTODSINTERVAL(SUM(s.elapsed_time_delta) / 1e6, 'SECOND') AS INTERVAL DAY(1) TO SECOND(2)) AS elapsed_time
, CAST(NUMTODSINTERVAL(SUM(s.elapsed_time_delta) / 1e6 / NVL(NULLIF(SUM(s.executions_delta), 0), 1), 'SECOND') AS INTERVAL DAY(1) TO SECOND(1)) AS avg_time
, ROUND(SUM(s.elapsed_time_delta) / 1e6 / NVL(NULLIF(SUM(s.executions_delta), 0), 1), 1) AS avg_s
, ROUND(SUM(s.buffer_gets_delta) / NULLIF(SUM(s.executions_delta), 0)) AS avg_bg
, ROUND(SUM(s.buffer_gets_delta) / NULLIF(SUM(s.rows_processed_delta), 0)) AS bg_per_row
, SUM(s.rows_processed_delta) AS rows_processed
, ROUND(SUM(s.rows_processed_delta) / NULLIF(SUM(s.executions_delta), 0)) AS avg_rows
, CAST(NUMTODSINTERVAL(SUM(s.elapsed_time_delta) / NULLIF(SUM(s.rows_processed_delta), 0) / 1e6, 'SECOND') AS INTERVAL DAY(1) TO SECOND(3)) AS avg_time_per_row
, MAX(s.px_servers_execs_delta) AS px_servers_execs
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot t ON t.snap_id = s.snap_id AND t.dbid = s.dbid AND t.instance_number = s.instance_number
WHERE s.sql_id = TRIM('&&sql_id')
GROUP BY TRUNC(CAST(t.begin_interval_time AS DATE)), s.plan_hash_value )
ORDER BY 1, 2, plan_hash_value;
PROMPT
PROMPT =======================================================================
PROMPT 4. RECENT ASH HISTORY
PROMPT =======================================================================
SELECT ash.sql_id, TO_CHAR(ash.sql_exec_start, 'YYYY-MM-DD HH24:MI:SS') AS 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#
, tls.sql_text AS top_level_call
, RTRIM(p.owner || '.' || p.object_name || '.' || p.procedure_name, '.') AS current_procedure
FROM v$active_session_history ash
LEFT JOIN dba_procedures p ON p.object_id = ash.plsql_object_id AND NVL(p.subprogram_id, .5) = NVL(ash.plsql_subprogram_id, .5)
LEFT JOIN v$sqlstats tls ON tls.sql_id = ash.top_level_sql_id
WHERE ash.sql_id = TRIM('&&sql_id')
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;
PROMPT
PROMPT =======================================================================
PROMPT 5. RECENT ASH HISTORY WITH WAIT OBJECTS
PROMPT =======================================================================
SELECT ash.sql_id, TO_CHAR(ash.sql_exec_start, 'YYYY-MM-DD HH24:MI:SS') AS 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 v$active_session_history ash
JOIN dba_users u ON u.user_id = ash.user_id
WHERE ash.sql_id = TRIM('&&sql_id')
AND ash.sql_exec_id IS NOT NULL
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;
PROMPT
PROMPT =======================================================================
PROMPT 6. V$SQL STATS
PROMPT =======================================================================
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
, TO_CHAR(last_active_time, 'YYYY-MM-DD HH24:MI:SS') AS 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) AS "CPU%"
, ROUND(100 * user_io_wait_time / NULLIF(elapsed_time, 0), 1) AS "IO%"
, ROUND(100 * concurrency_wait_time / NULLIF(elapsed_time, 0), 1) AS "CONCURRENCY%"
, ROUND(100 * application_wait_time / NULLIF(elapsed_time, 0), 1) AS "APPLICATION%"
, ROUND(100 * plsql_exec_time / NULLIF(elapsed_time, 0), 1) AS "PL/SQL%"
, buffer_gets AS 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 AS version_count
, invalidations
, px_servers_executions
, cluster_wait_time
FROM v$sql s
LEFT JOIN dba_objects o ON o.object_id = s.program_id
WHERE s.sql_id = TRIM('&&sql_id');
PROMPT
PROMPT =======================================================================
PROMPT 7. OBJECT STATS
PROMPT =======================================================================
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 v$sql_plan_statistics_all p
WHERE p.sql_id = TRIM('&&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 p.sql_id = TRIM('&&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 ALL
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.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, '(')
);
PROMPT
PROMPT =======================================================================
PROMPT 8. DBMS_XPLAN (Current Cursor)
PROMPT =======================================================================
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(TRIM('&&sql_id'), NULL, 'ADVANCED'));
PROMPT
PROMPT =======================================================================
PROMPT 9. DBMS_XPLAN (AWR)
PROMPT =======================================================================
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(TRIM('&&sql_id'), NULL, NULL, 'ADVANCED'));
PROMPT
PROMPT =======================================================================
PROMPT 10. LAST CAPTURED BINDS
PROMPT =======================================================================
SELECT name, value_string, datatype_string, last_captured
FROM ( SELECT DISTINCT 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 b.sql_id = TRIM('&&sql_id')
AND b.was_captured = 'YES' )
WHERE capture_seq = 1
ORDER BY LPAD(LTRIM(name, ':B'), 30);
PROMPT
PROMPT =======================================================================
PROMPT 11. SQL TEXT
PROMPT =======================================================================
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 ALL SELECT 189, 'MERGE' FROM dual ) a ON a.action = s.command_type
WHERE s.sql_id = TRIM('&&sql_id');
UNDEFINE sql_id
************************
--define sql_id
--undefine sql_id
-- spool perf_sqlcost.&&sql_id..out append
prompt SQL Full Text
set lines 250 pages 200 verify off
col sql_id for a15
col profile for a7
col patch for a5
col baseline for a8
col is_bind_sensitive for a17
col is_bind_aware for a13
col is_shareable for a12
col inst_id for 9999
col sql_profile for a10 truncate
col PARSING_SCHEMA_NAME for a12 truncate
col "SQL_ID (CHILD)" for a18 truncate
set long 1000000
define sql_id='grjjta7c6avch'
set long 200000
col sql_text for a199
select sql_text from dbA_hist_sqltext where sql_id = '&&sql_id' AND rownum=1;
col cost for 999,999,999,999
col cpu_cost for 999,999,999,999
col io_cost for 999,999,999,999
col timestamp for a25
prompt Get some execution history dba_hist_sqlstat
SELECT TO_CHAR(snap.end_interval_time,'YYYY-MM-DD HH24:MI:SS') end_interval_time
,dhs.instance_number
,snap.snap_id
,snap.con_id
,dhs.dbid
,dhs.sql_id
,dhs.plan_hash_value
,dhs.optimizer_cost
,dhs.sql_profile
,dhs.parsing_schema_name
,dhs.executions_delta
,dhs.invalidations_delta
,dhs.rows_processed_delta
,dhs.elapsed_time_delta
,ROUND(dhs.elapsed_time_delta/NULLIF(dhs.executions_delta,0)/NULLIF(dhs.rows_processed_delta,0)) elapsed_per_exec_per_row_micro
-- ,dhs.io_offload_elig_bytes_delta
-- ,dhs.io_interconnect_bytes_delta
FROM dba_hist_sqlstat dhs JOIN dba_hist_snapshot snap on (dhs.snap_id = snap.snap_id AND dhs.instance_number = snap.instance_number)
JOIN v$pdbs pdbs ON (pdbs.dbid = dhs.dbid)
WHERE sql_id = '&&sql_id'
AND snap.con_id = sys_context('userenv','con_id')
AND dhs.executions_delta > 0
ORDER BY snap.end_interval_time,dhs.dbid,snap.snap_id;
prompt Plan and cost from DBA_HIST_SQL_PLAN for &&sql_id
SELECT sql_id
,plan_hash_value
,cost
,cpu_cost
,io_cost
,to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') timestamp
FROM dba_hist_sql_plan
WHERE sql_id = '&&sql_id'
AND other_xml is not null
ORDER BY cost,sql_id,timestamp;
prompt Plans and basic info from DBA_HIST_ACTIVE_SESS_HISTORY
SELECT instance_number inst_id
,sql_id||' ('||sql_child_number||')' "SQL_ID (CHILD)"
,sql_plan_hash_value
,to_char(sample_time,'YYYY-MM-DD HH24:MI') sample_time_minute_group
,ROUND(MAX(pga_allocated)/1024/1024) max_pga_allocated_mb
,ROUND(MAX(temp_space_allocated)/1024/1024) max_temp_space_allocated_mb
,count(*) ash_count
FROM dba_hist_active_sess_history
WHERE 1=1
-- sql_id = '&&sql_id'
GROUP BY instance_number,sql_id||' ('||sql_child_number||')',sql_plan_hash_value,to_char(sample_time,'YYYY-MM-DD HH24:MI')
ORDER BY 1,2,4,3
/
col sample_time_minute_group for a25
prompt Plans and basic info from GV$ACTIVE_SESSION_HISTORY
SELECT inst_id
,sql_id||' ('||sql_child_number||')' "SQL_ID (CHILD)"
,sql_plan_hash_value
,to_char(sample_time,'YYYY-MM-DD HH24:MI') sample_time_minute_group
,ROUND(MAX(pga_allocated)/1024/1024) max_pga_allocated_mb
,ROUND(MAX(temp_space_allocated)/1024/1024) max_temp_space_allocated_mb
,count(*) ash_count
FROM gv$active_session_history
WHERE sql_id = '&&sql_id'
GROUP BY inst_id,sql_id||' ('||sql_child_number||')',sql_plan_hash_value,to_char(sample_time,'YYYY-MM-DD HH24:MI')
ORDER BY 1,2,4,3
/
col duration for a30
col start_time_approx for a30
col end_time_approx for a30
prompt Plans and basic info from GV$ACTIVE_SESSION_HISTORY EVERY RUN
SELECT inst_id
,sql_id||' ('||sql_child_number||')' "SQL_ID (CHILD)"
,sql_plan_hash_value
,sql_exec_id
,sql_exec_start
,MIN(sample_time) start_time_approx
,MIN(sample_time) end_time_approx
,(MAX(sample_time)-MIN(sample_time)) duration
,ROUND(MAX(pga_allocated)/1024/1024) max_pga_allocated_mb
,ROUND(MAX(temp_space_allocated)/1024/1024) max_temp_space_allocated_mb
,count(*) ash_count
FROM gv$active_session_history
WHERE sql_id = '&&sql_id'
AND sql_exec_id IS NOT NULL
GROUP BY inst_id,sql_exec_id,sql_exec_start,sql_id||' ('||sql_child_number||')',sql_plan_hash_value
ORDER BY 1,2,4,3
/
prompt Plans and speed in GV$SQL for &&sql_id - total per child, per row and per execution
SELECT inst_id
,sql_id||' ('||child_number||')' "SQL_ID (CHILD)"
,plan_hash_value
,parsing_schema_name
,DECODE(sql_profile,null,'N','Y') profile
,DECODE(sql_patch,null,'N','Y') patch
,DECODE(sql_plan_baseline,null,'N','Y') baseline
,is_bind_sensitive
,is_bind_aware
,is_shareable
,executions
,rows_processed
,ROUND(rows_processed/NULLIF(executions,0))rows_per_exec
,ROUND(elapsed_time/1000,1) elapsed_time_ms
,ROUND(elapsed_time/NULLIF(rows_processed,0)/1000,1) elapsed_per_row_ms
,ROUND(elapsed_time/NULLIF(executions,0)/1000,1) elapsed_per_exec_ms
FROM gv$sql
WHERE sql_id = '&&sql_id'
ORDER BY inst_id,sql_id,child_number,plan_hash_value
/
col IO_CELL_OFFLOAD_ELIGIBLE_MB for 999,999,999,999
col IO_CELL_OFFLOAD_RETURNED_MB for 999,999,999,999
col IO_CELL_OFFLOAD_ELIGIBLE_MB for 999,999,999,999
col IO_CELL_UNCOMPRESSED_MB for 999,999,999,999
col PHYSICAL_READ_MB for 999,999,999,999
col IO_CELL_OFFLOAD_ELIGIBLE_MB for 999,999,999,999
col OPTIMIZED_PHY_READ_REQUESTS for 999,999,999,999
--PHYSICAL_READ_BYTES — number of bytes read by the SQL
--PHYSICAL_READ_REQUESTS — number of read requests issued by the SQL
--PHYSICAL_WRITE_BYTES — number of bytes written by the SQL
--PHYSICAL_WRITE_REQUESTS — number of write requests issued by the SQL
--IO_CELL_OFFLOAD_ELIGIBLE_BYTES — number of bytes eligible for predicate offload to Exadata storage server
--IO_CELL_OFFLOAD_RETURNED_BYTES — number of bytes returned by smart scans
--IO_INTERCONNECT_BYTES — number of bytes exchanged between the database and the storage servers
--IO_CELL_UNCOMPRESSED_BYTES — number of uncompressed bytes read, where uncompressed bytes is the size after decompression
--OPTIMIZED_PHY_READ_REQUESTS — number of read requests satisfied from Exadata Smart Flash Cache, or read requests avoided due to storage index or columnar cache
prompt Offloading
prompt hard to be conclusive for multiple executions...
prompt could have been 1 good and 2 bad, etc...
SELECT sql_id
,child_number
,plan_hash_value phv
,executions execs
,ROUND(IO_CELL_OFFLOAD_ELIGIBLE_BYTES /1024/1024) IO_CELL_OFFLOAD_ELIGIBLE_MB
,ROUND(IO_CELL_OFFLOAD_RETURNED_BYTES /1024/1024) IO_CELL_OFFLOAD_RETURNED_MB
,ROUND(IO_CELL_UNCOMPRESSED_BYTES /1024/1024) IO_CELL_UNCOMPRESSED_MB
,ROUND(IO_INTERCONNECT_BYTES/1024/1024) IO_INTERCONNECT_MB
,ROUND(PHYSICAL_READ_BYTES/1024/1024) PHYSICAL_READ_MB
,ROUND(OPTIMIZED_PHY_READ_REQUESTS) OPTIMIZED_PHY_READ_REQUESTS
,100 - (100*(IO_INTERCONNECT_BYTES) / NULLIF(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0)) PCT_OFFLOADED
from gv$sql s
where executions > 0
AND sql_id = '&&sql_id'
/
from web
set pages 200 lines 250col OWNER for a30col TABLE_NAME for a30col OBJECT_OWNER for a30col COLUMN_NAME for a30col PARTITION_NAME for a15col SUBPARTITION_NAME for a30col STALE_STATS for a5col SAMPLE for a5col OBJECTS for a80col plan_hash_values for a80 wrapcol object_type for a10col density for 0.999999999999999col num_distinct for 999999999999999col num_nulls for 999999999999999define sql_idundefine sql_idbreak on index_nameprompt Enter the sql_id of the query to pull all objects out of the execution plansprompt &&sql_idspool perf_sqlstats.&&sql_id..out appendpromptprompt What objects are in the execution plans:prompt all objects may not appear in all executions plansprompt - different indexes can be selectedprompt - objects can be transformed out of plansprompt - covering indexes can eleminate table accessWITH objects as ( SELECT distinct dhsp.object_type||': '||dhsp.object_owner||'.'||dhsp.object_name objects ,dhsp.sql_id ,dhsp.plan_hash_value FROM dba_hist_sql_plan dhsp WHERE 1=1 AND ( dhsp.object_type like '%TABLE%' OR dhsp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' UNION SELECT distinct gvp.object_type||': '||gvp.object_owner||'.'||gvp.object_name objects ,gvp.sql_id ,gvp.plan_hash_value FROM gv$sql_plan gvp WHERE 1=1 AND ( gvp.object_type like '%TABLE%' OR gvp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' ORDER BY 1)SELECT objects,sql_id,LISTAGG(plan_hash_value,' ' ON OVERFLOW TRUNCATE) WITHIN GROUP (order by plan_hash_value) plan_hash_values FROM objects GROUP BY objects,sql_id ORDER BY objects,sql_id/
promptprompt object_stats for table and indexes used in the queryprompt if you are using a covering index on a partitioned table, partition stats will not showWITH objects AS ( SELECT dhsp.object_type ,dhsp.object_owner ,dhsp.object_name ,dhsp.sql_id FROM dba_hist_sql_plan dhsp WHERE 1=1 AND ( dhsp.object_type like '%TABLE%' OR dhsp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' UNION SELECT gvp.object_type ,gvp.object_owner ,gvp.object_name ,gvp.sql_id FROM gv$sql_plan gvp WHERE 1=1 AND ( gvp.object_type like '%TABLE%' OR gvp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' ORDER BY 1)SELECT objects.object_type ,NVL(dts.owner,dis.owner) owner ,NVL(dts.table_name,dis.table_name) table_name ,dis.index_name ,NVL(dts.partition_name,dis.partition_name) partition_name ,NVL(dts.subpartition_name,dis.subpartition_name) subpartition_name ,NVL(dts.global_stats,dis.global_stats) global_stats ,NVL(dts.num_rows,dis.num_rows) num_rows ,NVL(dts.blocks,dis.leaf_blocks) blocks ,NVL(dts.last_analyzed,dis.last_analyzed) last_analyzed ,NVL(dts.stale_stats,dis.stale_stats) stale_stats ,NVL(dts.stattype_locked,dis.stattype_locked) locked ,dts.avg_row_len ,dis.clustering_factor -- DECODE to prevent divide by 0 error ,TO_CHAR(ROUND((NVL(dts.sample_size,dis.sample_size)/NVL(DECODE(dts.num_rows,0,1,dts.num_rows),DECODE(dis.num_rows,0,1,dis.num_rows)))*100,2))||'%' sample FROM objects LEFT OUTER JOIN dba_tab_statistics dts ON (dts.owner = objects.object_owner AND dts.table_name=objects.object_name) LEFT OUTER JOIN dba_ind_statistics dis ON (dis.owner = objects.object_owner AND dis.index_name=objects.object_name)WHERE objects.sql_id = '&&sql_id'ORDER BY dts.owner,dts.table_name,dts.partition_name NULLS FIRST,dts.subpartition_name NULLS FIRST,dis.index_name/col index_owner for a30col table_owner for a30col is_used_in_plan for a16promptprompt now show every index definition on the relevant tables so we can see if there could be an alternativeprompt you will need to look at the predicates / FILTER conditions in the execution plan to determineprompt if other indexes could be suitable for the query-- get all tables and indexes from the plan-- pull all table names associated with those objects-- and show indexes related to the table namesWITH feed AS ( SELECT dhsp.object_type ,dhsp.object_owner ,dhsp.object_name ,dhsp.sql_id FROM dba_hist_sql_plan dhsp WHERE 1=1 AND ( dhsp.object_type like '%TABLE%' OR dhsp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' UNION SELECT gvp.object_type ,gvp.object_owner ,gvp.object_name ,gvp.sql_id FROM gv$sql_plan gvp WHERE 1=1 AND ( gvp.object_type like '%TABLE%' OR gvp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' ORDER BY 1), -- now we need to add all of the tables for the indexes mentioned above in case of covering index objects AS ( SELECT ind.table_owner, ind.table_name, ind.index_name FROM dba_indexes ind JOIN feed obj ON (obj.object_owner=ind.table_owner AND (obj.object_name = ind.table_name OR obj.object_name = ind.index_name) ) )SELECT dic.table_owner ,dic.table_name ,dic.index_owner ,dic.index_name ,dic.column_name ,dic.column_position ,dic.descend ,(select 'YES' from feed WHERE dic.index_name IN (SELECT feed.object_name FROM feed) AND rownum = 1) is_used_in_plan FROM dba_ind_columns dic JOIN objects ob ON (dic.table_owner=ob.table_owner AND (dic.table_name = ob.table_name OR dic.index_name = ob.index_name)) GROUP BY dic.table_owner,dic.table_name,dic.index_owner,dic.index_name,dic.column_name,dic.column_position,dic.descend ORDER BY dic.table_owner,dic.table_name,is_used_in_plan desc NULLS LAST,dic.index_owner,dic.index_name,dic.column_position/col column_name for a30col data_type for a10 truncatecol nullable for a4col histogram for a30prompt and lets have a look at those columns, just in the table (global stats if partitioned)WITH feed AS ( SELECT dhsp.object_type ,dhsp.object_owner ,dhsp.object_name ,dhsp.sql_id FROM dba_hist_sql_plan dhsp WHERE 1=1 AND ( dhsp.object_type like '%TABLE%' OR dhsp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' UNION SELECT gvp.object_type ,gvp.object_owner ,gvp.object_name ,gvp.sql_id FROM gv$sql_plan gvp WHERE 1=1 AND ( gvp.object_type like '%TABLE%' OR gvp.object_type like '%INDEX%' ) AND sql_id = '&&sql_id' ORDER BY 1), -- now we need to add all of the tables for the indexes mentioned above in case of covering index objects AS ( SELECT ind.table_owner, ind.table_name, ind.index_name FROM dba_indexes ind JOIN feed obj ON (obj.object_owner=ind.table_owner AND (obj.object_name = ind.table_name OR obj.object_name = ind.index_name) ) )SELECT dtc.owner ,dtc.table_name ,dtc.column_name ,dtc.data_type ,dtc.nullable ,dtc.density ,dtc.num_distinct ,dtc.num_nulls ,dtc.histogram ,dtc.num_buckets FROM dba_tab_columns dtc JOIN objects ON (dtc.owner=objects.table_owner AND dtc.table_name = objects.table_name) GROUP BY dtc.owner ,dtc.table_name ,dtc.column_name ,dtc.data_type ,dtc.nullable ,dtc.density ,dtc.num_distinct ,dtc.num_nulls ,dtc.histogram,dtc.num_buckets,dtc.column_id ORDER BY dtc.owner,dtc.table_name,dtc.column_id/
set lines 250 pages 200col sql_id for a15col profile for a7col patch for a5col baseline for a8col is_bind_sensitive for a17col is_bind_aware for a13col is_shareable for a12col inst_id for 9999define sql_idundefine sql_id--spool perf_sqlplan.&&sql_id..out appendprompt Plans and speed in GV$SQL for &&sql_id
SELECT inst_id ,sql_id ,child_number ,plan_hash_value ,DECODE(sql_profile,null,'N','Y') profile ,DECODE(sql_patch,null,'N','Y') patch ,DECODE(sql_plan_baseline,null,'N','Y') baseline ,is_bind_sensitive ,is_bind_aware ,is_shareable ,executions ,rows_processed ,ROUND(rows_processed/decode(executions,0,1,executions)) rows_per_exec ,ROUND(elapsed_time/1000,1) elapsed_time_ms ,ROUND(elapsed_time/decode(executions,0,1,executions)/1000,1) elapsed_per_exec_ms FROM gv$sql WHERE sql_id = '&&sql_id' ORDER BY inst_id,sql_id,child_number,plan_hash_value;
prompt get all current plans in GV$SQL for sql_id &&sql_idSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&&sql_id',null,'TYPICAL +ALIAS +NOTE +PEEKED_BINDS +OUTLINE +HINT_REPORT +ADAPTIVE'));
prompt get all plans in AWR for sql_id &&sql_idpause [p] Press enter to continue or CTRL+C to stop
-- display_awr is deprecated--SELECT * FROM TABLE(dbms_xplan.display_awr('&&sql_id',null,null,'TYPICAL +ALIAS +NOTE +PEEKED_BINDS +OUTLINE +HINT_REPORT +ADAPTIVE'));SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY('&&sql_id',null,'TYPICAL +ALIAS +NOTE +PEEKED_BINDS +OUTLINE +HINT_REPORT +ADAPTIVE'));prompt Sometimes the AWR SQL Plan does not extract.prompt This is some manual code to extract from DBA_HIST_SQL_PLANprompt Fruther SQL will extract XML info such as outline and notes from the other_xml CLOB.pause [p] Press enter to continue or CTRL+C to stopcol operation for a30col object for a40col partition_start_stop for a20col mb for 999,999,999.00col temp_space_mb for 999,999,999.00select sql_id ,plan_hash_value ,position ,lpad(' ',depth)||operation operation ,object_owner||'.'||object_name||' ('||object_type||')' AS object ,parent_id ,cost ,bytes/1024/1024 mb ,cpu_cost ,io_cost ,temp_space/1024/1024 temp_space_mb ,partition_id||' ('||partition_start||'-'||partition_stop||')' partition_start_stop FROM dba_hist_sql_plan WHERE sql_id = '&&sql_id' ORDER BY sql_id,plan_hash_value,parent_id NULLS FIRST, position/col bindval for a50 wrapcol hint for a150 wrapprompt InfoSELECT sql_id,plan_hash_value,tag, typ, valFROM dba_hist_sql_plan dhsp, XMLTABLE( '/other_xml/*' PASSING XMLTYPE( dhsp.other_xml ) COLUMNS tag VARCHAR2(20) PATH 'name()', typ VARCHAR2(20) PATH '@type', val VARCHAR2(20) PATH 'text()' )WHERE sql_id = '&&sql_id' AND other_xml is not null/
prompt bindsSELECT sql_id,plan_hash_value,bind,bindnam,bindpos,binddty,bindcsi,bindfrm,bindmxl,bindvalFROM dba_hist_sql_plan dhsp, XMLTABLE( '/other_xml/peeked_binds/*' PASSING XMLTYPE( dhsp.other_xml ) COLUMNS bind VARCHAR2(20) PATH 'name()', bindnam VARCHAR2(20) PATH '@nam', bindpos VARCHAR2(20) PATH '@pos', binddty VARCHAR2(20) PATH '@dty', bindcsi VARCHAR2(20) PATH '@csi', bindfrm VARCHAR2(20) PATH '@frm', bindmxl VARCHAR2(20) PATH '@mxl', bindval VARCHAR2(300) PATH 'text()' )WHERE sql_id = '&&sql_id' AND other_xml is not nullORDER BY 1,2,3,4/
prompt outline hints to recreate planSELECT sql_id,plan_hash_value,decode(outline,'hint','outline_hint',outline) outline,hintFROM dba_hist_sql_plan dhsp, XMLTABLE( '/other_xml/outline_data/*' PASSING XMLTYPE( dhsp.other_xml ) COLUMNS outline VARCHAR2(20) PATH 'name()', hint VARCHAR2(300) PATH 'text()' )WHERE sql_id = '&&sql_id' AND other_xml is not nullORDER BY 1,2,3/
prompt hint_usageSELECT sql_id,plan_hash_value,tab,origin,status,f,n,t,x,hFROM dba_hist_sql_plan dhsp, XMLTABLE( '/other_xml/hint_usage/q/*' PASSING XMLTYPE( dhsp.other_xml ) COLUMNS tab VARCHAR2(30) PATH 'text()', origin VARCHAR2(10) PATH './h/@o', status VARCHAR2(10) PATH './h/@st', f VARCHAR2(20) PATH './f/text()', n VARCHAR2(20) PATH './n/text()', t VARCHAR2(20) PATH './t/text()', x VARCHAR2(20) PATH './x/text()', h VARCHAR2(20) PATH './h/x/text()' )WHERE sql_id = '&&sql_id' AND other_xml is not null/
SET LINESIZE 300 PAGESIZE 300
-- Format individual columns
COLUMN inst_id FORMAT 999
COLUMN con_id FORMAT 999
COLUMN child_number FORMAT 99999
COLUMN plan_hash_value FORMAT 9999999999
COLUMN sql_plan_baseline FORMAT a30
COLUMN executions FORMAT 999999
COLUMN parsing_schema_name FORMAT a15
COLUMN optimizer_env_hash_value FORMAT 9999999999
COLUMN is_bind_sensitive FORMAT a14
COLUMN is_bind_aware FORMAT a14
COLUMN first_load_time FORMAT a20
COLUMN last_load_time FORMAT a20
SELECT inst_id, sql_id,con_id, child_number, plan_hash_value,
sql_plan_baseline, executions, parsing_schema_name,
optimizer_env_hash_value, is_bind_sensitive, is_bind_aware,
first_load_time, last_load_time
FROM gv$sql
WHERE sql_id = '&sql_id'
AND con_id = TO_NUMBER(SYS_CONTEXT('USERENV', 'CON_ID'))
ORDER BY inst_id, child_number;
*****************************************************
define sql_id='7fpm6cqb98088'
SET SERVEROUTPUT ON
WHENEVER SQLERROR CONTINUE SQL.SQLCODE;
DECLARE
myReport CLOB;
l_offset NUMBER := 1;
PROCEDURE printCLOB (p_clob IN OUT NOCOPY CLOB) IS
i_offset NUMBER := 1;
i_amount INTEGER;
i_clob_length NUMBER := DBMS_LOB.getlength(p_clob);
lc_buffer VARCHAR2(32767);
BEGIN
IF (DBMS_LOB.isopen(p_clob) != 1) THEN
DBMS_LOB.open(p_clob, 0);
END IF;
DBMS_OUTPUT.ENABLE (buffer_size => NULL);
LOOP
BEGIN
i_amount := 32767;
DBMS_LOB.READ (
lob_loc => p_clob,
amount => i_amount,
offset => i_offset,
buffer => lc_buffer
);
DBMS_OUTPUT.put_line(lc_buffer);
i_offset := i_offset + i_amount;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
DBMS_LOB.close(p_clob);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error : ' || SQLERRM);
END printCLOB;
BEGIN
DBMS_OUTPUT.put_line(CHR(10) || CHR(10) || 'Looking for sql text for &SQL_ID : ' || CHR(10) || CHR(10));
-- 1. Check Cursor Cache
BEGIN
SELECT sql_fulltext
INTO myReport
FROM gv$sqlarea
WHERE sql_id = '&SQL_ID'
AND ROWNUM = 1;
IF myReport IS NOT NULL THEN
DBMS_OUTPUT.put_line('Found &SQL_ID in cursor cache.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('Not found in Cursor Cache');
END;
-- 2. Check AWR if not found in cache
IF (myReport IS NULL OR DBMS_LOB.getlength(myReport) = 0) THEN
BEGIN
SELECT sql_text
INTO myReport
FROM dba_hist_sqltext
WHERE sql_id = '&SQL_ID'
AND ROWNUM = 1;
IF myReport IS NOT NULL THEN
DBMS_OUTPUT.put_line('Found &SQL_ID in AWR.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('Not found in AWR');
END;
END IF;
-- 3. Output results
IF (myReport IS NOT NULL AND DBMS_LOB.getlength(myReport) > 0) THEN
DBMS_OUTPUT.PUT_LINE('>>');
printCLOB(myReport);
DBMS_OUTPUT.PUT_LINE('>>');
ELSE
DBMS_OUTPUT.put_line('The sql text for &SQL_ID could not be found');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 255));
RAISE;
END;
/
