增強(qiáng)plsql developer的session工具... 1

一, 過(guò)濾器... 1

1.1 all session. 2

1.2 user sessions. 2

1.3 active sessions. 3

1.4 所有session 的當(dāng)前等待... 3

二, 詳細(xì)資料... 4

2.1 游標(biāo)... 4

2.2 sql文本... 5

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

2.4 鎖. 5

2.5 解析等待事件明細(xì)... 5

增強(qiáng)plsql developer的session工具 一, 過(guò)濾器

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

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后臺(tái)session和用戶session. 并額外給出了所有session的當(dāng)前等待事件. 包括正在空閑等待用戶輸入的session.

注意, 給定的sql語(yǔ)句后面不能加分號(hào).

1.2 user sessions

這里使用到了pl sql 的全局變量user, 這個(gè)值為當(dāng)前使用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

使用過(guò)濾條件status=’ACTIVE’得到所有活動(dòng)的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 的當(dāng)前等待

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

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')

二, 詳細(xì)資料

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

2.1 游標(biāo)

V$open_cursor中存儲(chǔ)有給定session的所有子游標(biāo)及其執(zhí)行過(guò)程. 但v$open_cursor視圖中給定的sql_text不完整. 所以如果需要查看完整的執(zhí)行語(yǔ)句, 我們需要跟v$sql_text進(jìn)行關(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語(yǔ)句(對(duì)于inactive session來(lái)說(shuō)是最后一次執(zhí)行的sql文本). 對(duì)應(yīng)于 active session, 我們可以使用sql_hash_value和sql_address關(guān)聯(lián)v$sql_text得到我們需要的結(jié)果, 但inactive session的sql_hash_value為0, 這時(shí)就需要使用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)計(jì)表

統(tǒng)計(jì)表是從v$sess_events視圖中查詢得到的session的資源利用情況. 由于一些等待事件只有在session完成后才會(huì)更新其匯總數(shù)據(jù), 所以這里得到的結(jié)果可能會(huì)跟實(shí)際情況有些偏差, 明細(xì)的結(jié)果參照l(shuí)ogoff 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 鎖.

默認(rèn)的查詢語(yǔ)句效率不是一般的差. 稍作修改如下.

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 解析等待事件明細(xì)

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

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

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

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視圖查詢出來(lái)的等待事件參數(shù)p1,p2,p3得到實(shí)際等待的內(nèi)容.

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

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

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


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

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

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

create public synonym v$kglpn for sys.v_$kglpn;? --這樣將導(dǎo)致所有的用戶都可以訪問這里的數(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
##主要用來(lái)處理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

?