dbms_xplan reprise

One of the questions that pops up on the internet from time to time is the one about finding SQL that’s doing full tablescans.

Since the appearance of the dynamic performance view v$sql_plan in Oracle 9i this has been quite easy – provided you can check memory soon enough. A query like the following will give you the sql_id (hash_value, if you’re still on 9i) and child_number of any query that has a full tablescan (or index fast full scan) in its execution path.

select
        distinct sql_id /* hash_value */ , child_number
from
        v$sql_plan
where   (operation = 'TABLE ACCESS' and options = 'FULL')
or      (operation = 'INDEX' and options = 'FAST FULL SCAN')
;

Note – just because there is a full tablescan in the plan this doesn’t mean that it will definitely happen, and you don’t necessarily know how often it might happen each time the query executes: so finding the SQL isn’t the whole story.

Once you have the sql_id (hash_value) and child_number you can always check v$sql (or v$sqlstats in 10g) for the SQL text and any other details you might want. If you’re on 10g, though, there’s an interesting variation on this theme. It’s easy (though resource-intensive on big busy systems) to use dbms_xplan to print the SQL text and execution plans for the suspect queries.

The function dbms_xplan.display_cursor() is a “pipelined function”, which means that it can behave like a table if you apply the table() operator to it; and table() operators can appear in lateral joins in Oracle. This means we can take the previous query, put it into an inline view, and (laterally) join it to dbms_xplan.display_cursor() as follows:

select
        plan_table_output  -- (the column of the pipelined function)
from    (
                select
                        distinct sql_id, child_number
                from
                        v$sql_plan
                where   (operation = 'TABLE ACCESS' and options = 'FULL')
                or      (operation = 'INDEX' and options = 'FAST FULL SCAN')
        ) v,
        table(dbms_xplan.display_cursor(v.sql_id, v.child_number))
;

Notice that the table() operator has to appear after the view that the pipelined function is referencing, which allows us to pass columns from the view into the pipelined function. This query gives us the dbms_xplan.display_cursor() output – with all the variations that allows – for every query that’s still in memory that has done a tablescan or index fast full scan.

You may find this a useful thing to run occasionally. But do be cautious – it WILL hammer the library cache for some time, especially if you have a large shared pool and a busy system.