Quick note to myself. If I am tuning a query by its SQL_ID I use these scripts:
From AWR:
findsql.sql – finds SQL_ID of queries that match certain strings
sqlstat.sql – shows execution history of SQL_ID
getplans.sql – shows all plans for SQL_ID
From V$ views:
vfindsqlid.sql – finds SQL_ID of queries that match certain strings
vsqlarea.sql – shows current executions of SQL_ID
extractplansqlid.sql – shows current plan of SQL_ID
I mainly mine the history from the AWR but I have been looking at V$ view information more recently, especially after putting in a fix to a slow query plan. I updated this on 6/18/21 to include the find scripts. The six scripts together are a pretty good toolkit for finding a SQL_ID and getting information about it. The AWR gives you history (we keep 6 weeks of hourly snapshots). The V$ scripts give you current information.
Bobby
I created this recently to help one group find/tune full table scans and another group to find full table scans to help plan for data purging.
Tim…
**********
with my$view as
(
select
snap.snap_id
,snap.begin_interval_time
,snap.end_interval_time
,stat.parsing_schema_name
,stat.module
,plan.*
from
v$database d
join dba_hist_snapshot snap on ( snap.dbid = d.dbid )
join dba_hist_sqlstat stat on (
( stat.snap_id = snap.snap_id )
and ( stat.dbid = snap.dbid )
and ( stat.instance_number = snap.instance_number )
)
join dba_hist_sql_plan plan on
(
( plan.dbid = snap.dbid )
and ( plan.sql_id = stat.sql_id )
and ( plan.plan_hash_value = stat.plan_hash_value )
and ( plan.con_dbid = stat.con_dbid )
)
)
,my$result as
(
select distinct t.object_owner ,t.object_name ,t.sql_id ,t.plan_hash_value ,t.dbid
from my$view t
where ( 0 = 0 )
and ( t.begin_interval_time > ( sysdate – 32 ) ) — optional, limit history lookback
and ( t.parsing_schema_name in ( ‘MYSCHEMA1’ ) ) — optional, query executing username filter
and ( t.module not in ( ‘DBMS_SCHEDULER’ ) ) — optional, noise filter
and ( t.object_owner in ( MYSCHEMA1′ ) ) — recommended, schemaowner list
and ( t.operation = ‘TABLE ACCESS’ and t.options = ‘FULL’ ) — object access method
and ( t.object_name in ( ‘MYTABLE1′ ,’MYTABLE2’ ) ) — optional, list of interesting tables/indexes
order by
t.object_owner
,t.object_name
,t.sql_id
,t.plan_hash_value
)
select ‘ set heading off’ dml from dual union
select ‘ set linesize 250’ from dual union
select ‘ set pagesize 0’ from dual union
select ‘ set trimspool on’ from dual union
select
‘select * from table( DBMS_XPLAN.DISPLAY_AWR( sql_id => ”’ || sql_id || ”’ ,plan_hash_value => ‘ || plan_hash_value || ‘ ) );’
from my$result;
Thanks for sharing this Tim!
Bobby