一 v$session_wait 当前session中的等待情况
select * from v$session_wait where wait_class#=8
这个8从哪里来.看第二个视图
二 v$event_name
select wait_class#,wait_class,count(*) from v$event_name group by wait_class#,wait_class order by 1
在11G里分了12类等侍事件,可以看出8是user I/O等待
0 Other 736
1 Application 17
2 Configuration 24
3 Administrative 55
4 Concurrency 33
5 Commit 2
6 Idle 95
7 Network 35
8 User I/O 48
9 System I/O 30
10 Scheduler 8
11 Cluster 50
12 Queueing 9
查看具体某个等待事件的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
1011061 723 8
1060212 1239 8
7996004 517 8
11121084 1436 8
这里把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
0 logical reads YES
1 buffer busy waits NO
2 gc buffer busy NO
3 db block changes YES
4 physical reads NO
5 physical writes NO
6 physical read requests NO
7 physical write requests NO
8 physical reads direct NO
9 physical writes direct NO
11 optimized physical reads NO
12 gc cr blocks received NO
13 gc current blocks received NO
14 ITL waits NO
15 row lock waits NO
17 space used NO
18 space allocated NO
20 segment 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周期性执行的检查点,日志切换,管理员检查点命令