查出全表掃描的相關SQL語句

1.按指定的應用程式查

Select T.Sql_Text, T.Disk_Reads, T.First_Load_Time, T.Module, U.Username, T.Hash_Value
From V$sqlarea T, V$sql_Plan P, All_Users U
Where T.Hash_Value = P.Hash_Value And P.Operation = 'TABLE ACCESS' And U.User_Id = T.Parsing_User_Id And
      P.Options = 'FULL' And T.Module = 'xxx.exe' And Disk_Reads <> 0
Order By Disk_Reads Desc, Sql_Text

Select Sql_Text From V$sqltext Where Hash_Value = 190441126 Order By Piece

2.顯示讀取的資料量(行和塊數)
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 ('ZLHIS')
Order By 1, 2;

3.顯示讀取的資料量(位元組數)
select to_char(sysdate,'yyyymm') as tjyf,a.object_owner, a.object_name,c.BYTES/1024/1024,sum(b.EXECUTIONS)  
from  
     (select object_owner,object_name,HASH_VALUE  
 from v$sql_plan  
  where object_owner not in ('SYS', 'SYSTEM','DBSNMP','OUTLN','PERFSTAT','PUBLIC','SQLAB','WMSYS') and OPTIONS = 'FULL' 
  group by object_owner,object_name,HASH_VALUE) a,  
     v$sqlarea b, dba_segments c  
  where a.HASH_VALUE = b.HASH_VALUE  
        and a.OBJECT_OWNER=c.owner  
        and a.object_name=c.segment_name  
       and c.segment_type='TABLE' 
 group by to_char(sysdate,'yyyymm'),a.object_owner, a.object_name,c.BYTES/1024/1024 
   order by sum(b.EXECUTIONS);