增強plsql developer的session monitor工具

增強plsql developer的session工具... 1

一, 過濾器... 1

1.1 all session. 2

1.2 user sessions. 2

1.3 active sessions. 3

1.4 所有session 的當前等待... 3

二, 詳細資料... 4

2.1 游標... 4

2.2 sql文本... 5

2.3 統(tǒng)計表... 5

2.4 鎖. 5

2.5 解析等待事件明細... 5

增強plsql developer的session工具 一, 過濾器

Pl/sql developer工具默認為session工具提供三種過濾器: all sessions. User session, active sessions. 這里, 作者將新增一種過濾器: 所有正在等待的鏈接.

1.1 all session.

select

b.SADDR,b.SID,b.SERIAL#,b.AUDSID,b.PADDR,b.USER#,b.USERNAME,b.COMMAND,b.OWNERID,b.TADDR,

b.LOCKWAIT,b.STATUS,b.SERVER,b.SCHEMA#,b.SCHEMANAME,b.OSUSER,b.PROCESS,b.MACHINE,b.TERMINAL,b.PROGRAM,

b.TYPE,b.SQL_ADDRESS,b.SQL_HASH_VALUE,b.PREV_SQL_ADDR,b.PREV_HASH_VALUE,b.MODULE,b.MODULE_HASH,b.ACTION,

b.CLIENT_INFO,b.FIXED_TABLE_SEQUENCE,b.ROW_WAIT_OBJ#,b.ROW_WAIT_FILE#,b.ROW_WAIT_BLOCK#,b.ROW_WAIT_ROW#,

b.LOGON_TIME ,a.EVENT,a.P1TEXT,a.P1,a.P1RAW,a.p2text,a.p2,a.p2raw,a.p3text,

?a.p3, a.p3raw,a.wait_time,a.seconds_in_wait,a.state??????????

from v$session_wait a,v$session b

where a.sid = b.sid

and b.username is not null

order by logon_time desc, sid

這里給出了所有的session, 包括oracle后臺session和用戶session. 并額外給出了所有session的當前等待事件. 包括正在空閑等待用戶輸入的session.

注意, 給定的sql語句后面不能加分號.

1.2 user sessions

這里使用到了pl sql 的全局變量user, 這個值為當前使用pl/sql developer登錄到oracle服務(wù)器的用戶名.

select

b.SADDR,b.SID,b.SERIAL#,b.AUDSID,b.PADDR,b.USER#,b.USERNAME,b.COMMAND,b.OWNERID,b.TADDR,

b.LOCKWAIT,b.STATUS,b.SERVER,b.SCHEMA#,b.SCHEMANAME,b.OSUSER,b.PROCESS,b.MACHINE,b.TERMINAL,b.PROGRAM,

b.TYPE,b.SQL_ADDRESS,b.SQL_HASH_VALUE,b.PREV_SQL_ADDR,b.PREV_HASH_VALUE,b.MODULE,b.MODULE_HASH,b.ACTION,

b.CLIENT_INFO,b.FIXED_TABLE_SEQUENCE,b.ROW_WAIT_OBJ#,b.ROW_WAIT_FILE#,b.ROW_WAIT_BLOCK#,b.ROW_WAIT_ROW#,

b.LOGON_TIME ,a.EVENT,a.P1TEXT,a.P1,a.P1RAW,a.p2text,a.p2,a.p2raw,a.p3text,

?a.p3, a.p3raw,a.wait_time,a.seconds_in_wait,a.state??????????

from v$session_wait a,v$session b

where a.sid = b.sid

and b.username = user

order by logon_time desc, sid

1.3 active sessions

使用過濾條件status=’ACTIVE’得到所有活動的session.

select

b.SADDR,b.SID,b.SERIAL#,b.AUDSID,b.PADDR,b.USER#,b.USERNAME,b.COMMAND,b.OWNERID,b.TADDR,

b.LOCKWAIT,b.STATUS,b.SERVER,b.SCHEMA#,b.SCHEMANAME,b.OSUSER,b.PROCESS,b.MACHINE,b.TERMINAL,b.PROGRAM,

b.TYPE,b.SQL_ADDRESS,b.SQL_HASH_VALUE,b.PREV_SQL_ADDR,b.PREV_HASH_VALUE,b.MODULE,b.MODULE_HASH,b.ACTION,

b.CLIENT_INFO,b.FIXED_TABLE_SEQUENCE,b.ROW_WAIT_OBJ#,b.ROW_WAIT_FILE#,b.ROW_WAIT_BLOCK#,b.ROW_WAIT_ROW#,

b.LOGON_TIME ,a.EVENT,a.P1TEXT,a.P1,a.P1RAW,a.p2text,a.p2,a.p2raw,a.p3text,

?a.p3, a.p3raw,a.wait_time,a.seconds_in_wait,a.state??????????

from v$session_wait a,v$session b

where a.sid = b.sid

and b.username is not null

and b.status = 'ACTIVE'

order by logon_time desc, sid

1.4 所有session 的當前等待

動態(tài)性能視圖v$session_waits中存儲了所有用戶的當前等待, 這里我們只關(guān)注跟IO和buffer space, latch 相關(guān)的幾個常見的等待事件.

select a.EVENT,a.P1TEXT,a.P1,a.P1RAW,a.p2text,a.p2,a.p2raw,a.p3text,

?a.p3, a.p3raw,a.wait_time,a.seconds_in_wait,a.state,

b.SADDR,b.SID,b.SERIAL#,b.AUDSID,b.PADDR,b.USER#,b.USERNAME,b.COMMAND,b.OWNERID,b.TADDR,

b.LOCKWAIT,b.STATUS,b.SERVER,b.SCHEMA#,b.SCHEMANAME,b.OSUSER,b.PROCESS,b.MACHINE,b.TERMINAL,b.PROGRAM,

b.TYPE,b.SQL_ADDRESS,b.SQL_HASH_VALUE,b.PREV_SQL_ADDR,b.PREV_HASH_VALUE,b.MODULE,b.MODULE_HASH,b.ACTION,

b.CLIENT_INFO,b.FIXED_TABLE_SEQUENCE,b.ROW_WAIT_OBJ#,b.ROW_WAIT_FILE#,b.ROW_WAIT_BLOCK#,b.ROW_WAIT_ROW#,

b.LOGON_TIME???????????

from v$session_wait a,v$session b

where a.sid = b.sid

and b.username is not null

and b.type <> 'BACKGROUND'

and a.event in ('db file sequential read', 'db file scattered read',

??????????????? 'latch free', 'direct path read', 'direct path write',

???????????????? 'enqueue', 'library cache pin', 'library cache load lock',

????????????????? 'buffer busy waits', 'free buffer waits')

二, 詳細資料

過濾器給出的是對連接session的篩選過程. 詳細資料部分給出了指定(在過濾文本中選中)的session的詳細信息. 比如指定session的執(zhí)行過程cursor及其全部sql語句. 比如session當前正在執(zhí)行(active session)或者最后一次執(zhí)行的sql語句(inactive session)

2.1 游標

V$open_cursor中存儲有給定session的所有子游標及其執(zhí)行過程. 但v$open_cursor視圖中給定的sql_text不完整. 所以如果需要查看完整的執(zhí)行語句, 我們需要跟v$sql_text進行關(guān)聯(lián).

select a.TYPE,b.ADDRESS,b.HASH_VALUE,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.SID = :sid

? order by b.ADDRESS,b.HASH_VALUE,c.PIECE

? /*concatenate*/

2.2 sql文本

這里給出的是session正在執(zhí)行的sql語句(對于inactive session來說是最后一次執(zhí)行的sql文本). 對應(yīng)于 active session, 我們可以使用sql_hash_value和sql_address關(guān)聯(lián)v$sql_text得到我們需要的結(jié)果, 但inactive session的sql_hash_value為0, 這時就需要使用prev_sql_addr和prev_hash_value得到我們希望的值.

select sql_text from v$sqltext_with_newlines

where address = hextoraw(decode(:sql_hash_value,0,:PREV_SQL_ADDR,:sql_address))

and hash_value = decode(:sql_hash_value,0,:prev_hash_value,:sql_hash_value)

order by piece

/* concatenate */

2.3 統(tǒng)計表

統(tǒng)計表是從v$sess_events視圖中查詢得到的session的資源利用情況. 由于一些等待事件只有在session完成后才會更新其匯總數(shù)據(jù), 所以這里得到的結(jié)果可能會跟實際情況有些偏差, 明細的結(jié)果參照logoff trigger跟蹤得到的結(jié)果信息.

select names.name, stats.statistic#, stats.value

from v$sesstat stats, v$statname names

where stats.sid = :sid

and names.Statistic# = stats.Statistic#

and stats.VALUE > 0

order by stats.VALUE desc,stats.statistic#

2.4 鎖.

默認的查詢語句效率不是一般的差. 稍作修改如下.

select /*+ ordered use_hash(o b)*/

?????? b.*,

?????? o.owner object_owner,

?????? o.object_name

?from? v$lock b, dba_objects o

?? where b.sid = :sid

???? and o.object_id = b.ID1

???? and b.id1 = :p2

???? and b.id2 = :p3

???? and b.BLOCK = 1

2.5 解析等待事件明細

我們要定位到當前等待事件正在跟蹤的數(shù)據(jù)庫對象的話需要查詢dba_extents動態(tài)性能視圖.但Dba_extents視圖的查詢效果非常差. 使用這個視圖定位對象的時間花銷較大. 我們有兩種方式解決這個問題.

首先, 我們可以使用v$bh代替dba_extents執(zhí)行查詢. V$bh中存儲當前data buffer中的所有數(shù)據(jù)對象. 但這個查詢方式存在的問題在于, 我們需要等待查詢的對象進入緩沖區(qū)之后才能得到查詢結(jié)果, 而對于那些db file sequential read和db file scattered read查詢來說, 有可能在我們執(zhí)行查詢時對應(yīng)仍然未在緩沖區(qū)中.

另外, 我們可以通過建立dba_extents的映像表來加速這個查詢過程, 比如, 針對我們的BI系統(tǒng). 晚間的ETL執(zhí)行過程完成之后, 基本不會再修改dba_extents表, 這時如果我們維護一個dba_extents的映像表代替dba_extents來完成我們的查詢過程, 將是一個非常高效的替代方案.

create table perfstat.dba_extent_his
as
select * from dba_extents;

create index perfstat.ind_dba_extent_his on perfstat.dba_extent_his(block_id,blocks);

?

truncate table dba_extent_his;

insert into dba_extent_his
select * from dba_extents;

下述代碼描述了怎么根據(jù)v$session_wait視圖查詢出來的等待事件參數(shù)p1,p2,p3得到實際等待的內(nèi)容.

由于過程中使用到了x$底層性能表, 所以需要一點額外的操作.

create view sys.v_$ktsso
as
select * from sys.x$ktsso;

create public synonym v$ktsso for sys.v_$ktsso;? --這樣將導致所有的用戶都可以訪問這里的數(shù)據(jù).


create view sys.v_$kglob
as
select * from sys.x$kglob;

create public synonym v$kglob for sys.v_$kglob;? --這樣將導致所有的用戶都可以訪問這里的數(shù)據(jù).

create view sys.v_$kglpn
as
select * from sys.x$kglpn;

create public synonym v$kglpn for sys.v_$kglpn;? --這樣將導致所有的用戶都可以訪問這里的數(shù)據(jù).

?

?

?

select de.owner || '.' || de.segment_name || '----' || de.partition_name object_name,

?????? de.segment_type object_type

? from perfstat.dba_extent_his de

?where de.file_id = :p1

?? and :p2 between de.block_id and (de.block_id + de.blocks - 1)

?? and :event in ('db file sequential read', 'db file scattered read')

union all

select (select segment_name || '-' || partition_name

????????? from perfstat.dba_extent_his de

???????? where de.file_id = :p1

?????????? and :p2 between de.block_id and (de.block_id + de.blocks - 1)

??????? ) obj_name,

?????? (select obj_type from

?????????? (select decode(ktssosegt,

?????????????????????????????? 1,

?????????????????????????????? 'SORT',

?????????????????????????????? 2,

?????????????????????????????? 'HASH',

???? ??????????????????????????3,

?????????????????????????????? 'DATA',

?????????????????????????????? 4,

?????????????????????????????? 'INDEX',

?????????????????????????????? 5,

?????????????????????????????? 'LOB_DATA',

?????????????????????????????? 6,

?? ????????????????????????????'LOB_INDEX',

?????????????????????????????? 'UNDEFINED') obj_type

????????? from v$ktsso

???????? where inst_id = userenv('instance')

?????????? and ktssoses = :saddr

?????????? and ktssosno = :serial#)

?????????? where rownum < 2) obj_type

? from dual

? where :event in ('direct path read','direct path write')

union all

select name obj_name, null obj_type

? from v$latchname

?where latch# = :p2

?? and :event = 'latch free'

union all

select object_name||'---'||subobject_name object_name,

?chr(bitand(:p1,-16777216)/16777215) ||

?chr(bitand(:p1,16711680)/65535) ||'----'|| mod(:p1,16) object_type

from dba_objects

?where object_id = :row_wait_obj#

?? and :event = 'equeue'

union all

select (

select segment_name||'----'||partition_name

? from dba_extents

?where :P2 between block_id and (block_id + blocks - 1)

??? and file_id = :p1) object_name,

??? (

????? select segment_type ||

??????? case when header_block = :p2 then ' header block '

???????????? when? freelist_groups > 1

??????????? ???and :p2 between header_block + 1 and (header_block + freelist_groups) then ' freelist group block'

???????????? else? ' data block' end? obj_type

????? from

??????? dba_segments s

????? where s.header_file = :p1

??? ) object_type

from dual

where :event = 'buffer busy waits'

union all

select kglnaobj obj_name,null obj_type

from?? x$kglob

where? inst_id? = userenv('instance')

and??? kglhdadr = :P1RAW

and :event = 'library cache pin'

?

2.6 library cache pin 的阻塞者

select a.sid,

?????? a.serial#,

?????? a.username,

?????? a.paddr,

?????? a.logon_time,

?????? a.sql_hash_value,

?????? b.kglpnmod

? from v$session a, sys.x$kglpn b

?where a.saddr = b.kglpnuse

?and b.inst_id = userenv('instance')

?? and b.kglpnreq = 0

?? and b.kglpnmod not in (0, 1)?

?? and b.kglpnhdl = :P1RAW

x$kglpn——[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s

?????? desc x$kglpn
##主要用來處理library cache pin holder

Name? ?? ? ? ? Null?? ? Type
?----------------------------------------------------- -------- ------------
?ADDR? ? ? ? ? RAW(4)
?INDX? ? ? ? ?NUMBER
?INST_ID? ? ? NUMBER
?KGLPNADR? ? ?RAW(4)
?KGLPNUSE? ? ? RAW(4)
?KGLPNSES? ? ?RAW(4)
?KGLPNHDL? ? ? RAW(4)
##關(guān)聯(lián)v$session_wait中event為library cache pin的P1RAW，再關(guān)聯(lián)v$session，可以查出sid和serial#

KGLPNLCK? ? ? ? ?RAW(4)

KGLPNCNT? ? ? NUMBER

KGLPNMOD? ? ? ? NUMBER

##如果值為3，表示為library cache pin的holder；如果值為0，表示為waiter

KGLPNREQ? ? ? NUMBER
##如果值為0，表示為library cache pin的holder；如果值為2，表示為waiter

?