Wednesday, July 14, 2010

Find Full Table Scan Queries


This query provides a list of queries (1,000 characters only) involved in full table scans. The total number of rows and blocks for each table is also displayed so that you can determine whether the full scan is degrading performance.
SELECT   sp.object_owner, sp.object_name,
         (SELECT sql_text
            FROM v$sqlarea sa
           WHERE sa.address = sp.address
             AND sa.hash_value = sp.hash_value) sqltext,
         (SELECT executions
            FROM v$sqlarea sa
           WHERE sa.address = sp.address
             AND sa.hash_value = sp.hash_value) no_of_full_scans,
         (SELECT    LPAD (NVL (TRIM (TO_CHAR (num_rows)), ' '),
                          15,
                          ' '
                         )
                 || ' | '
                 || LPAD (NVL (TRIM (TO_CHAR (blocks)), ' '), 15, ' ')
                 || ' | '
                 || BUFFER_POOL
            FROM dba_tables
           WHERE table_name = sp.object_name AND owner = sp.object_owner)
                                                           "rows|blocks|pool"
    FROM v$sql_plan sp
   WHERE operation = 'TABLE ACCESS'
     AND options = 'FULL'
     AND object_owner IN ('USERNAME')
ORDER BY 1, 2;

Or the following query finds recently FULL TABLE Scan queries; means, index are missing:
SELECT *
FROM dba_hist_active_sess_history
WHERE sql_plan_options = 'FULL'
ORDER BY sample_time DESC

SELECT sql_text, parsing_schema_name, module
FROM v$sql
WHERE sql_id = 'drtn4c1accwrw'


1 comment:

Joe said...

Thanks Ferdous, this is a great post.

Also see:

SQL Full table scans