查詢oracle比較慢的session和SQL[轉]

一、如何找到消耗資源大的Oracle的session及其執行的SQL語句
    HP-UX可以用glance,top、IBM- AIX可以用topas、另外可以使用PS命令檢視程式。
    透過這些程式我們可以找到用系統資源特別大的這些程式的程式號，我們就可以透過以下的sql語句發現這個pid正在執行哪個sql，這個sql最好在pl/sql developer,toad等軟體中執行, 把<>中的spid換成你的spid就可以了。 

SELECT a.username,
       a.machine,
       a.program,
       a.sid,
       a.serial#,
       a.status,
       c.piece,
       c.sql_text
  FROM v$session a,
       v$process b,
       v$sqltext c
WHERE b.spid='&spid'
   AND b.addr=a.paddr
   AND a.sql_address=c.address(+)
ORDER BY c.piece  

 

    
    我們就可以把得到的這個sql分析一下，看一下它的執行計劃是否走索引，對其最佳化避免全表掃描，以減少IO等待，從而加快語句的執行速度。
    提示：我在做最佳化sql時，經常碰到使用in的語句，這時我們一定要用exists把它給換掉，因為Oracle在處理In時是按Or的方式做的，即使使用了索引也會很慢。
比如：
SELECT  col1,col2,col3 FROM table1 a
WHERE a.col1 not in (SELECT  col1 FROM table2)
可以換成：
SELECT  col1,col2,col3 FROM table1 a
WHERE not exists
(SELECT  'x'  FROM table2 b
WHERE  a.col1=b.col1)

二、另一個有用的指令碼：查詢前十條效能差的sql (disk_reads  最多)
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 ;

三、迅速發現Oracle Server的效能問題的成因，我們可以求助於v$session_wait這個檢視，看系統的這些session在等什麼，使用了多少的IO。
參考指令碼：
--指令碼說明：檢視佔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

對檢索出的結果的幾點說明：
1、按每個正在等待的session已經發生的物理讀排的序，因為它與實際的IO相關。
2、可以看一下這些等待的程式都在忙什麼，語句是否合理？
  Select sql_address from v$session where sid=;
  Select * from v$sqltext where address=;

 或者：

 select sql_text,sid,serial# ,username from v$session a,v$sqlarea b where a.sql_address=b.address and sid='&sid'；

執行以上兩個語句便可以得到這個session的語句。
你也以用alter system kill session 'sid,serial#';把這個session殺掉。
3、應觀注一下event這列，這是我們調優的關鍵一列，下面對常出現的event做以簡要的說明：
A、buffer busy waits，free buffer waits這兩個引數所標識是dbwr是否夠用的問題，與IO很大相關的，當v$session_wait中的free buffer wait的條目很小或沒有的時侯，說明你的系統的dbwr程式決對夠用，不用調整；free buffer wait的條目很多，你的系統感覺起來一定很慢，這時說明你的dbwr已經不夠用了，它產生的wio已經成為你的資料庫效能的瓶頸，這時的解決辦法如下：
a.1、增加寫程式，同時要調整db_block_lru_latches引數
示例：修改或新增如下兩個引數
  db_writer_processes=4
  db_block_lru_latches=8
a.2、開非同步IO，IBM這方面簡單得多，hp則麻煩一些，可以與Hp工程師聯絡。

B、db file sequential read，指的是順序讀，即全表掃描，這也是我們應該儘量減少的部分，解決方法就是使用索引、sql調優，同時可以增大db_file_multiblock_read_count這個引數。

C、db file scattered read,這個引數指的是透過索引來讀取，同樣可以透過增加db_file_multiblock_read_count這個引數來提高效能。

D、latch free,與栓相關的了，需要專門調節。

E、其他引數可以不特別觀注。