四, 监控使用系统资源较多的用户信息
下述语句监控当前活动用户中, 使用cpu,逻辑读, 物理读, 解析次数较多的session的相关信息. 报表结果包括, session id, 系统pid, session状态, 正在执行的sql语句等.
select se.r1,
se.sid,
se.spid,
se.status,
se.name,
se.value,
sq.sql_text,
sq.piece,
se.status,
se.prog,
se.terminal,
se.osuser
from (select rank() over(partition by e.name order by value desc) r1,
a.sid,
b.spid,
e.name,
value,
a.sql_address,
a.STATUS,
substr(a.program, 1, 40) prog,
a.terminal,
osuser
from v$session a, v$process b, v$sesstat c, v$statname e
where c.sid = a.sid
and a.paddr = b.addr
and c.STATISTIC# = e.STATISTIC#
and e.NAME in( 'db block changes',
'CPU used by this session',
'parse count (hard)',
'parse count (total)',
'physical reads',
'physical reads direct',
'session logical reads'
)
and a.status = 'ACTIVE'
and value >0
) se,
v$sqltext sq
where se.sql_address = sq.ADDRESS(+)
and se.r1 <= 3
order by se.status,se.name,se.r1, sq.PIECE;
4.1 V$SESSTAT
根据oracleonlinebook中的描述, v$sesstat存储session从login到logout的详细资源使用统计.
V$sysstat和v$sesstat差别如下:
l V$sesstat只保存session数据, 而v$sysstat保存所有sessions的累计值.
l V$sesstat只暂存数据, session退出后数据即清空. V$sysstat则是累积的, 只有当实例被shutdown才会清空.
l V$sesstat不包括统计项名称, 如果要获得统计项名称则必须与v$sysstat或v$statname连接查询获得.
V$sesstat可被用于找出如下类型session
l 高资源占用
l 高平均资源占用(登陆后资源使用率)
l 默认资源占用比(两快照之间)
V$sesstat常用列说明
Note: Statistics numbers are not guaranteed to remain constant from one release to another. Therefore, you should rely on the statistics name rather than its number in your applications. | ||
表二, 监控项说明
CLASS | NAME | desc |
1 | CPU used by this session | 所有session的cpu占用量, 不包含后台进程. 这项统计的单位是毫秒. |
8 | db block changes | 造成SGA中数据块变化的insert,update或delete操作数. |
64 | parse count (hard) | 在shared pool中解析调用的未命中次数. 当sql语句执行并且该语句不在shared pool或虽然在shared pool但因为两者存在部分差异而不能被使用时产生硬解析. 硬解析会带来cpu和资源使用的高昂开销, 因为它需要oracle在shared pool中重新分配内存, 然后再确定执行计划, 最终语句才会被执行. |
64 | parse count (total) | 解析调用总数, 包括软解析和硬解析. 当session执行了一条sql语句, 该语句已经存在于shared pool并且可以被使用则产生软解析. |
64 | parse time cpu | 总cpu解析时间(单位 10ms).包括硬解析和软解析 |
64 | parse time elapsed | 完成解析调用的总时间花销 |
8 | physical reads | OS blocks read数. 包括插入到SGA缓存区的物理读以及PGA中的直读. 这项统计并非IO请求数. |
8 | physical reads direct |
|
1 | session logical reads | 逻辑读请求数 |
64 | sorts (disk) | 在硬盘上执行的排序次数 |
64 | sorts (memory) | 在内存中执行的排序次数 |
64 | sorts (rows) | 列排序总数. |
64 | table scan blocks gotten | 全表扫描中读取的总块数 |