增强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