• 查出全表扫描的相关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); 

  • 相关阅读:
    数据库架构的演变
    一个简单的跨库事务问题
    一个优美的架构需要考虑的几个问题
    铁道部新客票系统设计
    详细介绍软件架构设计的三个维度
    单代号网络图
    分库分表带来的完整性和一致性问题
    软件架构设计箴言理解
    设计高并发的电子商店
    mysql之索引补充
  • 原文地址:https://www.cnblogs.com/zyk/p/1592059.html
Copyright © 2020-2023  润新知