jcon.no: Oracle blog

From time to time it can be smart checking your database for unwanted table scans or index fast full scans. An table scan or index full scan is not necessarily evil, but can sometimes be an indication that you are not hitting an optimal execution plan. This other day I read a note from Jonathan Lewis showing howto gather all executions plans from memory and search for such execution plans including such scans.

Warning! This SQL will really hit your shared pool while it´s running (library cache), especially if you´re having a large shared pool and a database with a relativly high workload.

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))
;

The dbms_xplan.display_cursor() is a “pipelined function”, which means it can be treated as a table using the table() operator. Spool to a file, and then walk through the different execution plans looking for odeties.