oracle 等待,oracle 中的等待事件

0Other7361Application172Configuration243Administrative554Concurrency335Commit26Idle957Network358UserI/O489System I/O3010Scheduler811Cluster5012Queueing9查看具体某个等待事件的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

10110617238106021212398799600451781112108414368这里把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