dbms_xplan.display_cursor管道函式

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.

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:

Notice that the table() operator has to appear after the view that the pipelilned function is referencing, which alluws 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.