AWR and V$ Scripts for a SQL_ID

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to AWR and V$ Scripts for a SQL_ID

  1. Tim N. says:

    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;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.