查詢最佔資源、CPU、記憶體、和執行最長的SQL語句

--當前執行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;