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

  • 相关阅读:
    Maven学习
    Android屏幕适配
    Java多线程中的死锁问题[转]
    数据结构基本概念和算法分析
    AsyncTask(异步任务)
    Android自定义滑动开关按钮
    记录一些我记不住的技术
    2017携程Web前端实习生招聘笔试题总结
    JavaScript ES5面向对象实现一个todolist
    原生JavaScript实现一个简单的todo-list
  • 原文地址:https://www.cnblogs.com/zyk/p/1592059.html
Copyright © 2020-2023  润新知