監(jiān)控當前數(shù)據(jù)庫的活動session

六, 監(jiān)控當前數(shù)據(jù)庫的活動session 6.1 監(jiān)控session的執(zhí)行語句

6.1.1 通過動態(tài)性能視圖查找活動session的執(zhí)行語句

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通過動態(tài)性能視圖查找所有session的執(zhí)行語句

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 通過操作系統(tǒng)查找相關(guān)session信息

1, 找出最消耗cpu的操作系統(tǒng)進程

# 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, 找出給定操作系統(tǒng)pid的session的執(zhí)行sql

V$open_cursor視圖列出session打開的所有cursor, 很多時候都將被用到, 比如: 你可以通過這個視圖查看各個session打開的cursor數(shù).

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

V$sqlarea中的統(tǒng)計項在語句完全執(zhí)行后被更新(并且從v$session.sql_hash_value中消失). 因此, 我們無法通過v$sqlarea跟v$session直接關(guān)聯(lián)找到session, 除非語句被再次執(zhí)行. 不過如果session的cursor仍然打開著, 用戶就可以通過v$open_cursor來找出執(zhí)行這個語句的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 通過動態(tài)性能視圖查找相關(guān)session信息

利用V_$SQLAREA視圖提供了執(zhí)行的細節(jié)。（執(zhí)行、讀取磁盤和讀取緩沖區(qū)的次數(shù)）

? 數(shù)據(jù)列

EXECUTIONS：執(zhí)行次數(shù)

DISK_READS：讀盤次數(shù)

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

OPTIMIZER_MODE：優(yōu)化方式

SQL_TEXT：Sql語句

SHARABLE_MEM：占用shared pool的內(nèi)存多少

BUFFER_GETS：讀取緩沖區(qū)的次數(shù)

? 用途

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

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

3、幫忙分析是否需要索引或改善聯(lián)接

求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;

查看語句占用的內(nèi)存情況

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 通過操作系統(tǒng)查找相關(guān)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級別的等待事件, 通過查詢這兩個視圖用戶可以得到當前數(shù)據(jù)庫的一些操作到底在等待什么, 是磁盤IO, 緩沖區(qū)忙還是插鎖等.

V$SESSION_WAIT中的常用列

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

附注：
1.State字段有四種含義﹕
Waiting：SESSION正等待這個事件。
Waited unknown time：由于設(shè)置了timed_statistics值為false，導致不能得到時間信息。表示發(fā)生了等待，但時間很短。
Wait short time：表示發(fā)生了等待，但由于時間非常短不超過一個時間單位，所以沒有記錄。
Waited knnow time：如果session等待然后得到了所需資源，那么將從waiting進入本狀態(tài)。

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

3.Wait_time和Second_in_wait字段值與state相關(guān):
如果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 '等待完成, 統(tǒng)計時間未置為可用'
??????????? 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視圖的列代表的緩沖區(qū)忙等待事件如下：

P1—與等待相關(guān)的數(shù)據(jù)文件的全部文件數(shù)量。

P2—P1中的數(shù)據(jù)文件的塊數(shù)量。

P3—描述等待產(chǎn)生原因的代碼。

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

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

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

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 塊被讀入緩沖區(qū)。
100 我們想要NEW(創(chuàng)建)一個塊，但這一塊當前被另一session讀入。
110 我們想將當前塊設(shè)為共享，但這一塊被另一session讀入，所以我們必須等待read()結(jié)束。
120 我們想獲得當前的塊，但其他人已經(jīng)將這一塊讀入緩沖區(qū)，所以我們只能等待他人的讀入結(jié)束。
130 塊被另一session讀入，而且沒有找到其它協(xié)調(diào)的塊，所以我們必須等待讀的結(jié)束。緩沖區(qū)死鎖后這種情況也有可能產(chǎn)生。所以必須讀入塊的CR。
200 我們想新創(chuàng)建一個block，但其他人在使用，所以我們只好等待他人使用結(jié)束。
210 Session想讀入SCUR或XCUR中的塊，如果塊交換或者session處于非連續(xù)的TX模式，所以等待可能需要很長的時間。
220 在緩沖區(qū)查詢一個塊的當前版本，但有人以不合法的模式使用這一塊，所以我們只能等待。
230 以CR/CRX方式獲得一個塊，但塊中的更改開始并且沒有結(jié)束。
231 CR/CRX掃描找到當前塊，但塊中的更改開始并且沒有結(jié)束。

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

?

Copyright (c) 1982, 2006, Oracle.? All Rights Reserved.

?

SQL> conn / as sysdba

Connected.

SQL> oradebug setospid 487432

Oracle pid: 12, Unix process pid: 487432, image: oracle@i2db (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

Copyright (c) 1982, 2005, Oracle.? All rights reserved.