Oracle-檢視sql運作狀況

檢視占io較大的正在運作的session

SELECT se.sid,
       se.serial#,
       pr.SPID,
       se.username,
       se.status,
       se.terminal,
       se.program,
       se.MODULE,
       se.sql_address,
       st.event,
       st. p1text,
       si.physical_reads,
       si.block_changes
  FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
 WHERE st.sid = se.sid
   AND st. sid = si.sid
   AND se.PADDR = pr.ADDR
   AND se.sid > 6
   AND st. wait_time = 0
   AND st.event NOT LIKE '%SQL%'
 ORDER BY physical_reads DESC;      

查找前十條性能差的sql

SELECT *
  FROM (select PARSING_USER_ID,
               EXECUTIONS,
               SORTS,
               COMMAND_TYPE,
               DISK_READS,
               sql_text
          FROM v$sqlarea
         order BY disk_reads DESC)
 where ROWNUM < 10;      

從V$SQLAREA中查詢最占用資源的查詢

select b.username username,a.disk_reads reads,a.BUFFER_GETS as buffer,
    a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
    a.sql_text Statement
from  v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
and a.disk_reads > 100000
order by a.disk_reads desc;      

列出使用頻率最高的5個查詢

select sql_text,executions
from (select sql_text,executions,
   rank() over
    (order by executions desc) exec_rank
   from v$sql)
where exec_rank <=5;      

消耗磁盤讀取最多的sql top5

select disk_reads,sql_text
from (select sql_text,disk_reads,
   dense_rank() over
     (order by disk_reads desc) disk_reads_rank
   from v$sql)
where disk_reads_rank <=5;      

找出需要大量緩沖讀取（邏輯讀）操作的查詢

select buffer_gets,sql_text
from (select sql_text,buffer_gets,
   dense_rank() over
     (order by buffer_gets desc) buffer_gets_rank
   from v$sql)
where buffer_gets_rank<=5;      

人們永遠沒有足夠的時間把它做好，但永遠有足夠的時間重新來過。

可是，因為并不是總有機會重做一遍，你必須做得更好，換句話說，

人們永遠沒有足夠的時間去考慮到底是不是想要它，但永遠有足夠的時間去為之後悔。

★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★

淺掘千口井，不如深挖一口井！當知識支撐不了野心時，那就靜下心來學習吧！運維技術交流QQ群：618354452

個人微信公衆号，定期釋出技術文章和運維感悟。歡迎大家關注交流。