Oracle how to :查找最消耗資源的sql及session

   方法1

        在業務最忙的時間段生成一個15分鐘的statspack報告或者awr，檢視 裡面的top sql

   方法2 

      使用top工具，檢視目前最消耗資源的OS程序，并記住相應的程序ID（PID)，該PID對應到Oracle

      的v$process中的addr，$session中的paddr 

          執行如下查詢來找到問題sql   （假設對應的pid為4529）

       SELECT p.pid,p.spid,p.username,s.sid,s.serial#,c.hash_value,c.sql_text

       FROM v$process p,v$session s,v$sqltext c

       WHERE a.addr=s.paddr

       AND s.sql_hash_value=c.hash_value

       and  p.spid=4529

     解決方法

       找到問題會話後，通常使用一下指令kill 程序

alter system kill session 'sid,serial#';     ---這裡的sid和serial#是通過上面的查詢語句獲得

但是此指令釋放資源極為緩慢，為了更快速的釋放資源，我們通常按照如下步驟kill程序

1.在作業系統級kill 程序

         kill -9 4529

2.在資料庫内部kill session 并指定immediate參數

                     alter system kill session 'sid,serial#' immediate；

       示例

       假如在資料庫層面kill session長時間無效果

      SQL> select sid,serial#,status from v$session;

       SID    SERIAL# STATUS

---------- ---------- --------

.....

       142       1124 KILLED

.......

首先找到該session對應的OS的程序ID 

SQL> select 'kill -9 ' || spid from v$process where addr=(select paddr from v$session where sid=&sid);

Enter value for sid: 142

old   1: select 'kill -9 ' || spid from v$process where addr=(select paddr from v$session where sid=&sid)

new   1: select 'kill -9 ' || spid from v$process where addr=(select paddr from v$session where sid=142)

KILL-9'||SPID

--------------------

kill -9 4529

在作業系統級别kill 掉該程序

    kill -9 4529

    ps -ef | grep 4529

然後在Oracle層面kill session，并加上 immediate參數

SQL> alter system kill session '142,1124' immediate;

SQL> select sid,serial# ,status from v$session;

補充

1. 通過OS 程序ID 找到對應的資料庫session sid

SQL> select sid,serial# from v$session where paddr=(select addr from v$process where spid=&spid);

2  檢視目前session對應的sid，serial#

SQL> select sid,serial# from v$session where sid=(select sid from v$mystat where rownum=1);

3.通過session的sid找到對應的OS 的程序ID 

SQL> select spid from v$process where addr=(select paddr from v$session where sid=&sid);