Oracle:查詢誰Lock住Process, sid number ?做了什麼事？

ORACLE Tips: 查詢誰Lock住Process, sid number ?

Oracle:10G

select b.object_name obj_name,
       d.ctime time,
       to_char(d.lmode) l,
       to_char(d.request) r,
       a.os_user_name os_user,
       c.machine machine,
       c.program program,
       c.serial# s_serial,
       a.oracle_username ora_user,
       a.object_id obj_id,
       a.process pid,
       a.session_id s_id,
       p.spid
from v$locked_object a, all_objects b, v$session c, v$lock d,V$process p
where a.object_id=b.object_id
and c.Paddr = p.Addr
and a.session_id=c.sid
and a.session_id=d.sid
and d.type='TX'
order by d.ctime desc, p.spid;

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

#查詢Session History(首先要知道Session_id，通常em發送訊息過來，就會指名session_id，
這樣會比較好找出是那一個Session做什麼事):

select sample_time,session_id,sql_id,session_state,blocking_session_status,
event,wait_class,time_waited,program,module from v$active_session_history
where blocking_session_status!='NOT IN WAIT';

#記住sql_id，再到v$sqltext查詢是執行那一個sql(piece是執行順序)：

select * from v$sqltext where sql_id='f2fnfj93dwwvh' order by piece;

上面的動作都是Oracle 10G的ASH的功能：

ASH (Active Session History) 體系結構


在Oracle10g之前，當前會話記錄保存在v$session中；處於等待狀態的會話會被復制一份放在
v$session_wait中。當該連接斷開後，其原來的連接信息在v$session和v$session_wait中就
會被刪除。沒有視圖能提供有關session在歷史上的每個時間點上都在做什麼，以及在等待
什麼資源。原來的v$session及v$session_wait只是顯示當前session正在執行什麼SQL及等待
什麼資源。


從Oracle10g開始，Oracle提供了Active Session History (ASH)來解決這個問題。 每隔1秒鐘
ASH都會將當前活動的session的信息記錄在SGA的一個緩衝區(循環使用)中。在ASH中，這個過
程稱為采樣(Sampling)。ASH缺省每一秒收集一下v$session中活動會話的情況，記錄會話等待
的事件，不活動的會話不會被采樣, 間隔時間由 _ash_sampling_interval 參數確定 .


在10g中新出現了一個視圖：v$session_wait_history。這個視圖保存了每個活動session在
v$session_wait中最近10次的等待事件.  但這對於一段時期內的數據性能狀況的監測是遠遠不夠
的，為了解決這個問題，在10g中還新添加了一個視圖：v$active_session_history。這就是ASH
（active session history）。


ASH采用的策略 ---

典型的情況下,為了診斷當前數據庫的狀態，需要最近的五到十分鐘的詳細信息。然而，由於記錄
session的活動信息是很費時間和空間的，ASH采用的策略是:  保存處於等待狀態的活動session的
信息，每秒從v$session_wait及v$session中采樣一次，並將采樣信息保存在內存中(註意：ASH的
采樣數據是保存在內存中)。

 ASH的工作原理 ---

對於Active Session的采樣(每秒收集相關視圖中的信息)數據存放在SGA中，SGA中分配給ASH的大小
可以從v$sgastat中查詢(Shared Pool下ASH buffers)，該空間可以循環使用，如果需要，以前的信
息可以被新的信息覆蓋。要把所有session的所有活動記錄下來是非常消耗資源的。因此ASH只能從
V$SESSION 等少數相關視圖中獲取那些活動的session的信息。ASH每隔1秒收集session信息時，不是
通過SQL語句完成的，而是采用直接訪問內存的方式，相對更高效。


因為每秒需要采樣數據，所以ASH緩存裏數據量非常大，將他們全部刷新到磁盤上的話，會非常消
耗磁盤空間，因此在將ASH緩存中的數據刷新到AWR相關表中的時候， 采取以下策略：

1.  MMON 默認每隔60分鐘 (可以調整) 將ash buffers 中的數據全部flush到磁盤 。
2.  MMNL 默認當ash buffers 滿66%的時候將ash buffers 中的1/10的數據寫入磁盤 (具體1/10是哪些數據，遵循FIFO原則) 。
3.  MMNL 寫入的采用數據百分比 10%  表示的是寫入磁盤的數據占 ash buffers 中采樣數據量的百分比 (而不是占ash buffers 總大小的比例)
4.  為了節省空間，AWR中采集的數據默認在7天後自動清除。

http://tw.myblog.yahoo.com/shin-chuan/article?mid=536&prev=-1&next=531