監控當前資料庫的活動session
6.1 監控session的執行語句
6.1.1 透過動態效能檢視查詢活動session的執行語句

select a.SID,a.USERNAME,a.machine,a.TERMINAL,b.PIECE,b.SQL_TEXT
from v$session a,
v$sqltext b
where b.ADDRESS = decode(a.SQL_HASH_VALUE,0,a.PREV_SQL_ADDR,a.SQL_ADDRESS)
and a.status = 'ACTIVE'
and user# >0
order by a.SQL_ADDRESS,b.PIECE;

6.1.2透過動態效能檢視查詢所有session的執行語句

select a.SID,b.ADDRESS,b.HASH_VALUE,a.USERNAME,a.machine,b.SADDR,c.PIECE,c.SQL_TEXT
from v$session a,
v$open_cursor b,
v$sqltext c
where a.SID = b.SID
and b.ADDRESS = c.ADDRESS
and b.HASH_VALUE = c.HASH_VALUE
and a.status = 'ACTIVE'
and user# >0
order by a.SID,b.ADDRESS,b.HASH_VALUE,a.USERNAME,a.machine,b.SADDR,c.PIECE;

6.1.3 透過作業系統查詢相關session資訊

1, 找出最消耗cpu的作業系統程式

# ps aux| grep -v grep | grep ora| head -10

oracle 876648 1.9 1.0 57832 82156 - A 16:22:35 7:59 oracleSISDB2 (LO

oracle 594138 1.9 1.0 58808 83132 - A 15:22:46 16:48 oracleSISDB2 (LO

oracle 495712 0.9 1.0 56628 80952 - A 17:04:47 0:43 oracleSISDB2 (LO

oracle 712946 0.5 1.0 55716 80040 - A 17:11:33 0:07 oracleSISDB2 (LO

oracle 966862 0.1 1.0 55144 79468 - A Jul 08 153:01 oracleSISDB2 (LO

oracle 442494 0.1 1.0 58984 83308 - A Feb 16 1751:47 ora_lms1_SISDB2

oracle 581808 0.1 1.0 59140 83464 - A Feb 16 1747:01 ora_lms0_SISDB2

oracle 811254 0.1 1.0 55228 79552 - A 15:51:29 0:31 oracleSISDB2 (LO

oracle 573582 0.0 1.0 57680 82004 - A Feb 16 149:17 ora_lmon_SISDB2

oracle 651300 0.0 1.0 57204 81528 - A Feb 16 125:13 ora_diag_SISDB2

2, 找出給定作業系統pid的session的執行sql

V$open_cursor檢視列出session開啟的所有cursor, 很多時候都將被用到, 比如: 你可以透過這個檢視檢視各個session開啟的cursor數.

當診斷系統資源佔用時, v$open_cursor檢視常被用來連線v$sqlarea和v$sql查詢出特定SQL(高邏輯或物理IO). 然後, 下一步就是找出源頭.

V$sqlarea中的統計項在語句完全執行後被更新(並且從v$session.sql_hash_value中消失). 因此, 我們無法透過v$sqlarea跟v$session直接關聯找到session, 除非語句被再次執行. 不過如果session的cursor仍然開啟著, 使用者就可以透過v$open_cursor來找出執行這個語句的session.

SELECT   /*+ ORDERED */
         address,piece,sql_text
    FROM v$sqltext a
   WHERE (a.hash_value, a.address) IN (
            SELECT d.HASH_VALUE,d.ADDRESS
              FROM v$session b,v$open_cursor d
              where b.SID = d.SID
               and b.paddr = (SELECT addr
                                FROM v$process c
                               WHERE c.spid = '&pid'))
ORDER BY address,piece;

6.2 session的資源佔用
6.2.1 透過動態效能檢視查詢相關session資訊

利用V_$SQLAREA檢視提供了執行的細節。（執行、讀取磁碟和讀取緩衝區的次數）

? 資料列

EXECUTIONS：執行次數

DISK_READS：讀盤次數

COMMAND_TYPE：命令型別（3:select,2:insert;6:update;7delete;47:pl/sql程式單元）

OPTIMIZER_MODE：最佳化方式

SQL_TEXT：Sql語句

SHARABLE_MEM：佔用shared pool的記憶體多少

BUFFER_GETS：讀取緩衝區的次數

? 用途

1、幫忙找出效能較差的SQL語句

2、幫忙找出最高頻率的SQL

3、幫忙分析是否需要索引或改善聯接

求DISK READ較多的SQL
select st.ADDRESS,st.PIECE,st.sql_text
from v$sql s, v$sqltext st
where s.address = st.address
and s.hash_value = st.hash_value
and s.disk_reads > 300
order by st.address, st.piece ;

求DISK SORT嚴重的SQL
select sess.username, sql.sql_text, sort1.blocks
from v$session sess, v$sqlarea sql, v$sort_usage sort1
where sess.serial# = sort1.session_num
   and sort1.sqladdr = sql.address
   and sort1.sqlhash = sql.hash_value
   and sort1.blocks > 200;

檢視語句佔用的記憶體情況

select username, sum(sharable_mem), sum(persistent_mem), sum(runtime_mem)
from sys.v_$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
group by username;

6.2.2 透過作業系統查詢相關session資訊

# ps aux|head -1; ps aux|sort -nr +2 |head -10

USER PID %CPU %MEM SZ RSS TTY STAT STIME TIME COMMAND

root 73764 6.1 0.0 384 384 - A Jan 10 130144:34 wait

root 57372 6.1 0.0 384 384 - A Jan 10 132116:52 wait

root 65568 6.0 0.0 384 384 - A Jan 10 129411:36 wait

# ps aux |head -1; ps aux |sort -nr +3 | head -10

USER PID %CPU %MEM SZ RSS TTY STAT STIME TIME COMMAND

oracle 974978 2.2 1.0 57992 82316 - A 14:05:06 2:41 oracleSISDB2 (LO

oracle 966862 0.1 1.0 55144 79468 - A Jul 08 80:49 oracleSISDB2 (LO

oracle 942332 0.0 1.0 59112 83436 - A Feb 16 2:24 ora_arc0_SISDB2

oracle 909346 1.4 1.0 58364 82688 - A 13:49:28 3:22 oracleSISDB2 (LO

SELECT   /*+ ORDERED */
         address,piece,sql_text
    FROM v$sqltext a
   WHERE (a.hash_value, a.address) IN (
            SELECT d.HASH_VALUE,d.ADDRESS
              FROM v$session b,v$open_cursor d
              where b.SID = d.SID
               and b.paddr = (SELECT addr
                                FROM v$process c
                               WHERE c.spid = '&pid'))
ORDER BY address,piece;

6.3 session的等待事件
V$session_event, v$session_wait兩個檢視中記錄的是session級別的等待事件, 透過查詢這兩個檢視使用者可以得到當前資料庫的一些操作到底在等待什麼, 是磁碟IO, 緩衝區忙還是插鎖等.

V$SESSION_WAIT中的常用列

SID: session標識
EVENT: session當前等待的事件，或者最後一次等待事件。
WAIT_TIME: session等待事件的時間(單位，百分之一秒)如果本列為0，說明session當前session還未有任何等待。
SEQ#: session等待事件將觸發其值自增長
P1, P2, P3: 等待事件中等待的詳細資料
P1TEXT, P2TEXT, P3TEXT: 解釋說明p1,p2,p3事件

附註：
1.State欄位有四種含義﹕
Waiting：SESSION正等待這個事件。
Waited unknown time：由於設定了timed_statistics值為false，導致不能得到時間資訊。表示發生了等待，但時間很短。
Wait short time：表示發生了等待，但由於時間非常短不超過一個時間單位，所以沒有記錄。
Waited knnow time：如果session等待然後得到了所需資源，那麼將從waiting進入本狀態。

Wait_time值也有四種含義：
值>0：最後一次等待時間(單位：10ms)，當前未在等待狀態。
值=0：session正在等待當前的事件。
值=-1：最後一次等待時間小於1個統計單位，當前未在等待狀態。
值=-2：時間統計狀態未置為可用，當前未在等待狀態。

3.Wait_time和Second_in_wait欄位值與state相關:
如果state值為Waiting，那麼wait_time值無用。Second_in_wait值是實際的等待時間(單位：秒)。
如果state值為Wait unknow time，那麼wait_time值和Second_in_wait值都無用。
如果state值為Wait short time，那麼wait_time值和Second_in_wait值都無用。
如果state值為Waiting known time，那麼wait_time值就是實際等待時間(單位：秒)，Second_in_wait值無用。

Select s.SID,
       s.username,
       s.program,
       s.status,
       se.event,
       se.total_waits,
       se.total_timeouts,
       se.time_waited,
       se.average_wait
from v$session s, v$session_event se
Where s.sid = se.sid
   And se.event not like 'SQl*Net%'
   And s.status = 'ACTIVE'
   And s.username is not null;

Select s.SID,
       s.username,
       s.program,
       s.status,
       sw.EVENT,
       sw.STATE,
       case when sw.STATE = 'WAITING' then '正在等待...'
            when sw.state = 'WAITED UNKNOWN TIME' then '等待完成, 但時間很短'
            when sw.state = 'WAITED SHORT TIME' THEN '等待完成, 但時間更短'
            when sw.state = 'WAITED KNOWN TIME' then '等待完成,等待時間(單位10ms)'||sw.wait_time end state_memo,
       case when sw.STATE = 'WAITING' then sw.SECONDS_IN_WAIT else 0 end seconds_in_wait,
       sw.WAIT_TIME,
       case when sw.WAIT_TIME = -1 then '等待完成, 最後一次等待時間小於10ms...'
            when sw.WAIT_TIME = -2 then '等待完成, 統計時間未置為可用'
            when sw.WAIT_TIME > 0 then '等待完成, 最後一次等待時間(單位10ms)'||sw.WAIT_TIME
            when sw.WAIT_TIME = 0 then '正在等待' end wait_time_memo,
       st.PIECE,
       st.SQL_TEXT,
       sw.P1TEXT,sw.p1, sw.P2TEXT,sw.p2, sw.P3TEXT, sw.P3
from v$session s, v$session_wait sw, v$sqltext st
Where s.sid = sw.sid
   and s.sql_address = st.address(+)
   And sw.event not like 'SQl*Net%'
   And s.status = 'ACTIVE'
   And s.username is not null
order by sw.state,s.sid,st.PIECE;

v$session_wait檢視的列代表的緩衝區忙等待事件如下：

P1—與等待相關的資料檔案的全部檔案數量。

P2—P1中的資料檔案的塊數量。

P3—描述等待產生原因的程式碼。

例：select p1 "File #", p2 "Block #", p3 "Reason Code"

from v$session_wait
where event = 'buffer busy waits';

如果以上查詢的結果顯示一個塊在忙等待，以下的查詢將顯示這一塊的名稱和型別：

select owner, segment_name, segment_type

from dba_extents

where file_id = &P1　and &P2 between block_id and block_id + blocks -1;

我們也可以查詢dba_data_files以確定等待的檔案的file_name，方法是使用v$session_wait中的P1。

從v$session_wait中查詢P3(原因編碼)的值可以知道session等待的原因。原因編碼的範圍從0到300，下列為部分編碼所代表的事項：
0 塊被讀入緩衝區。
100 我們想要NEW(建立)一個塊，但這一塊當前被另一session讀入。
110 我們想將當前塊設為共享，但這一塊被另一session讀入，所以我們必須等待read()結束。
120 我們想獲得當前的塊，但其他人已經將這一塊讀入緩衝區，所以我們只能等待他人的讀入結束。
130 塊被另一session讀入，而且沒有找到其它協調的塊，所以我們必須等待讀的結束。緩衝區死鎖後這種情況也有可能產生。所以必須讀入塊的CR。
200 我們想新建立一個block，但其他人在使用，所以我們只好等待他人使用結束。
210 Session想讀入SCUR或XCUR中的塊，如果塊交換或者session處於非連續的TX模式，所以等待可能需要很長的時間。
220 在緩衝區查詢一個塊的當前版本，但有人以不合法的模式使用這一塊，所以我們只能等待。
230 以CR/CRX方式獲得一個塊，但塊中的更改開始並且沒有結束。
231 CR/CRX掃描找到當前塊，但塊中的更改開始並且沒有結束。

6.4 跟蹤長時間執行session的10046事件
1, 使用sql_trace跟蹤當前session的10046事件

SQL> alter session set sql_trace = true;

Session altered

SQL> select 1 from dual;

1

SQL> alter session set sql_trace = false;

Session altered

2, 使用set events跟蹤當前session的10046事件

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

SQL> select 2 from dual;

2

----------

2

SQL> alter session set events '10046 trace name context off';

Session altered

3, 使用oradebug跟蹤當前session的10046事件

例如我們檢視PID = 487432的程式, 可以使用下面的方法.

# su - oracle

[YOU HAVE NEW MAIL]

$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 14 17:24:42 2009

SQL> conn / as sysdba

Connected.

SQL> oradebug setospid 487432

Oracle pid: 12, Unix process pid: 487432, image: (MMNL)

SQL> oradebug event 10046 trace name context forever,level 8

Statement processed.

SQL> oradebug tracefile_name

/oracle/admin/arpdb/bdump/arpdb_mmnl_487432.trc

SQL> oradebug event 10046 trace name context off

Statement processed.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

$ tkprof /oracle/admin/arpdb/bdump/arpdb_mmnl_487432.trc

output = arpdb_mm1.txt

TKPROF: Release 10.2.0.3.0 - Production on Tue Jul 14 17:31:29 2009

監控當前資料庫的活動session

相關文章