Oracle Scratchpad

May 11, 2011

dbms_xplan reprise

Filed under: dbms_xplan,lateral view,Oracle — Jonathan Lewis @ 5:28 pm BST May 11,2011

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.

        distinct sql_id /* hash_value */ , child_number
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:

        plan_table_output  -- (the column of the pipelined function)
from    (
                        distinct sql_id, child_number
                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.


  1. I added a filter to the inline view on “object_owner”. This way I only get queries that I care about, and not all the queries on the system.

    Comment by Jimmy — May 11, 2011 @ 6:55 pm BST May 11,2011 | Reply

  2. Smart stuff. It saves the effort of first finding the sql_id and then looking for other details like text and the execution plan.

    By the way, the most common approach that I have seen being being used by people to find FTS is “Repeatedly querying v$session_longops, finding SID and then the other details.”

    A small spelling mistake at “…which alluws us to…”

    Comment by Amardeep Sidhu — May 12, 2011 @ 4:56 am BST May 12,2011 | Reply

  3. Great. Now, I am building “intelligent” scripts to identify SQLs that do FTSs, Nested Loops, Sort-Merge Joins, Hash Joins.
    And hammering the Library Cache !

    Comment by Hemant K Chitale — May 12, 2011 @ 6:25 am BST May 12,2011 | Reply

  4. […] you can embed it into a query that calls dbms_xplan.display_cursor() – as I demontrated about 10 years ago – or dbms_sql_monitor.report_sql_monitor() if you’re suitably […]

    Pingback by Finding SQL | Oracle Scratchpad — August 5, 2021 @ 6:24 pm BST Aug 5,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

Website Powered by

%d bloggers like this: