0Other736
1Application17
2Configuration24
3Administrative55
4Concurrency33
5Commit2
6Idle95
7Network35
8UserI/O48
9System I/O30
10Scheduler8
11Cluster50
12Queueing9
查看具体某个等待事件的p1,p2,p3参数
select name,parameter1,parameter2,parameter3 from v$event_name where wait_class#=8 and name='db file sequential read'
db file sequential read file# block# blocks
那如何统计一个当前会话中的每个session的累计等待事件,那看第三个视图
三 v$session_event v$system_event
select total_waits,sid,wait_class# from v$session_event where wait_class#=8 and total_waits>1000000 order by 1
10110617238
106021212398
79960045178
1112108414368
这里把sid取出来,再去v$session,v$sql里相应的sql即可.
select sql_id,sql_hash_value,prev_hash_value,machine,username,status from v$session where sid='1436'
select * from v$sqltext where hash_value='3889181855' order by 5
v$system_event记录了实例自启动以来的全部等待事件.这里用来看整个数据库的瓶紧.
select * from v$system_event where wait_class# not in (6,7) order by total_waits desc
四 v$segment_statistics
例如我们发现一个表有很大的User I/O等待事件,那么我们就要分析这个表,oracle提供了v$segment_statistics收集对象的统计信息:
select * from v$segment_statistics where object_name='PUB_HX_WORK_RELA'
--查询读较大的表
select * from v$segment_statistics where statistic_name='physical reads direct' and value>1057155967
oracle 11g收信的统计信息有18类:
select * from v$segstat_name
0logical reads YES
1buffer busy waits NO
2gc buffer busy NO
3db block changes YES
4physical reads NO
5physical writes NO
6physicalreadrequests NO
7physical write requests NO
8physical reads direct NO
9physical writes direct NO
11optimized physical reads NO
12gc cr blocks received NO
13gccurrentblocks received NO
14ITL waits NO
15row lock waits NO
17spaceused NO
18spaceallocated NO
20segment scans NO
这里顺便说一个比较有趣的视图
select * from v$osstat
五 看下oracle 的读命中率
select 1-
(select value from v$sysstat where name='physical reads')/
((select value from v$sysstat where name='db block gets')+(select value from v$sysstat where name='consistent gets'))
from dual
六 查询过往时间等待事件
select event,p1,p1text,p2,p2text,p3,p3text,wait_time_micro from v$session_wait_history a,v$event_name b where a.event#=b.event#
and b.wait_class# not in (6,7) order by wait_time_micro desc
七 v$latch,x$bh
select b.addr,b.ts#,b.file#,a.gets,a.misses,a.sleeps,b.dbarfil,b.dbablk from x$bh b,v$latch_children a where b.hladdr=a.addr and a.name='cache buffers chains' and a.gets>4000000;
取出dbablk,然后根据dba_extents 算出相应段信息
select c.owner,c.segment_name,c.bytes,c.blocks from dba_extents c where block_id<=535308 and block_id+blocks>535308;
--这里只对cache buffers chains 有效
对事件cache buffers lru chain.
获取cache buffers lru chain的情况如下:
需要装载块到内存的进程,需要查询lru获取空闲区时
DBWR进程在将lruw上的脏块写入文件后,需要将相应缓冲区放入lru时,包括如下情况:
需要装载块到内存的进程,在查询lru列时,查询超过特定块数还没有获取时,向DBWR请求写dirty block
parallel query时,或truncate,drop,tablespace backup时,向DBWR请求写相关对象的dirty block
检查点导致的脏块写入,包括:为保障FAST_START_MTTR_TARGET/LOG_CHECKPOINT_TIMEOUT周期性执行的检查点,日志切换,管理员检查点命令
posted on 2012-03-29 09:51 snowhill 阅读(243) 评论(0) 编辑 收藏 引用 所属分类: 数据库-oracle