查询最占资源、CPU、内存、和执行最长的SQL语句

花前月下人 2019-06-27 13:47:21  1155  收藏 分类专栏： oracle   oracle专栏收录该内容 1 篇文章0 订阅 订阅专栏

--当前执行sql语句
select a.SID,
       a.SERIAL#,
       a.USERNAME,
       b.PARSE_CALLS,
       b.PARSING_SCHEMA_NAME,
       b.CPU_TIME/1000000,
       b.ELAPSED_TIME/1000000,
       b.DISK_READS,
       b.DIRECT_WRITES,
       b.BUFFER_GETS,
       a.event,
        b.sql_text,
        b.SQL_FULLTEXT
  from v$session a inner join v$sqlarea b

 on a.SQL_HASH_VALUE = b. hash_value and b.PARSING_SCHEMA_NAME=upper('smsdb')

--物理读最高sql语句

select a.USERNAME,                                                     
       a. USER_ID,
       b.PARSE_CALLS,
       b.PARSING_SCHEMA_NAME,
       b.CPU_TIME/1000000,
       b.ELAPSED_TIME/1000000,
       b.DISK_READS,
       b.DIRECT_WRITES,
       b.BUFFER_GETS,
        b.sql_text,
        b.SQL_FULLTEXT
  from dba_users a inner join v$sqlarea b

 on a.USER_ID = b.PARSING_USER_ID and b.PARSING_SCHEMA_NAME=upper('smsdb') and disk_reads>1000000


--查询前10名执行最多次数SQL语句
select sql_text "SQL语句", executions "执行次数"
  from (select sql_text,
               executions,
               rank() over
               
               (order by executions desc) exec_rank
          from v$sqlarea)
 where exec_rank <= 10;
 
 
 --查询前10名占用CPU最高的SQL语句  
 select sql_text "SQL语句",
                       c_t "SQL执行时间（秒）",executions "执行次数",cs "每次执行时间（秒）"　from (select sql_text,
                       cpu_time /1000000 c_t,executions,ceil(executions/(cpu_time/1000000))cs,
                       rank() over(order by cpu_time desc) top_time
                  from v$sqlarea) where  top_time <= 10

--查询前10名执行时间最长SQL语句                  
select sql_text "SQL语句",
                       c_t "处理时间(秒)",executions "执行次数",cs "每次执行时间（秒）"
                from (select sql_text,
                       ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,
                       rank() over(order by ELAPSED_TIME desc) top_time
                  from v$sqlarea) where  top_time <= 10

--查询前10名最耗资源SQL语句                  
select sql_text "SQL语句",
                       DISK_READS "物理读次数",cs "每次执行时间（秒）"
                from (select sql_text,
                       ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,DISK_READS,
                       rank() over(order by DISK_READS desc) top_disk
                  from v$sqlarea) where  top_disk <= 10

 
--查询前10名最耗内存SQL语句                 
select sql_text "SQL语句",
                       BUFFER_GETS "内存读次数",cs "每次执行时间（秒）"
                from (select sql_text,
                       ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,BUFFER_GETS,
                       rank() over(order by BUFFER_GETS desc) top_mem
                  from v$sqlarea) where  top_mem <= 10

--查看锁表语句

 Select
c.sid,
c.serial#,
d.name,
b.object_name,
c.username,
c.program,
c.osuser
from gv$Locked_object a, All_objects b, gv$session c, audit_actions d
where a.object_id = b.object_id
and a.inst_id = c.inst_id(+)
and a.session_id = c.sid(+)
and c.command = d.action;