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