Query to check Table Full Scan

Query:


spool large_table_scans.txt
–Find Large Table Scans 
set linesize 132
SELECT substr(table_owner,1,10) Owner,
       substr(table_name,1,15) Table_Name,
       size_kb, statement_count, reference_count,
       substr(executions,1,4) Exec, 
       substr(executions * reference_count,1,8) tot_scans
FROM (SELECT a.object_owner table_owner,
             a.object_name table_name,
             b.segment_type table_type,
             b.bytes / 1024 size_kb,
             SUM(c.executions ) executions,
             COUNT( DISTINCT a.hash_value ) statement_count,
             COUNT( * ) reference_count
      FROM sys.v_$sql_plan a, sys.dba_segments b, sys.v_$sql c
      WHERE a.object_owner (+) = b.owner
        AND a.object_name (+) = b.segment_name
        AND b.segment_type IN (‘TABLE’, ‘TABLE PARTITION’)
        AND a.operation LIKE ‘%TABLE%’
        AND a.options = ‘FULL’
        AND a.hash_value = c.hash_value
        AND b.bytes / 1024 > 1024
        AND a.object_owner != ‘SYS’
      GROUP BY a.object_owner, a.object_name, a.operation, b.bytes/1024, b.segment_type
      ORDER BY 4 DESC, 1, 2 );
spool off 

  • December 31, 2018 | 17 views
  • Comments