增强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 统计表... 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服务器的用户名.

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 的当前等待

动态性能视图v$session_waits中存储了所有用户的当前等待, 这里我们只关注跟IO和buffer space, latch 相关的几个常见的等待事件.

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的执行过程cursor及其全部sql语句. 比如session当前正在执行(active session)或者最后一次执行的sql语句(inactive session)

2.1 游标

V$open_cursor中存储有给定session的所有子游标及其执行过程. 但v$open_cursor视图中给定的sql_text不完整. 所以如果需要查看完整的执行语句, 我们需要跟v$sql_text进行关联.

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正在执行的sql语句(对于inactive session来说是最后一次执行的sql文本). 对应于 active session, 我们可以使用sql_hash_value和sql_address关联v$sql_text得到我们需要的结果, 但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 统计表

统计表是从v$sess_events视图中查询得到的session的资源利用情况. 由于一些等待事件只有在session完成后才会更新其汇总数据, 所以这里得到的结果可能会跟实际情况有些偏差, 明细的结果参照logoff trigger跟踪得到的结果信息.

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 解析等待事件明细

我们要定位到当前等待事件正在跟踪的数据库对象的话需要查询dba_extents动态性能视图.但Dba_extents视图的查询效果非常差. 使用这个视图定位对象的时间花销较大. 我们有两种方式解决这个问题.

首先, 我们可以使用v$bh代替dba_extents执行查询. V$bh中存储当前data buffer中的所有数据对象. 但这个查询方式存在的问题在于, 我们需要等待查询的对象进入缓冲区之后才能得到查询结果, 而对于那些db file sequential read和db file scattered read查询来说, 有可能在我们执行查询时对应仍然未在缓冲区中.

另外, 我们可以通过建立dba_extents的映像表来加速这个查询过程, 比如, 针对我们的BI系统. 晚间的ETL执行过程完成之后, 基本不会再修改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;

下述代码描述了怎么根据v$session_wait视图查询出来的等待事件参数p1,p2,p3得到实际等待的内容.

由于过程中使用到了x$底层性能表, 所以需要一点额外的操作.

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

create public synonym v$ktsso for sys.v_$ktsso;  --这样将导致所有的用户都可以访问这里的数据.


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

create public synonym v$kglob for sys.v_$kglob;  --这样将导致所有的用户都可以访问这里的数据.

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

create public synonym v$kglpn for sys.v_$kglpn;  --这样将导致所有的用户都可以访问这里的数据.

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)
##关联v$session_wait中event为library cache pin的P1RAW，再关联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