oracle 等待,oracle 中的等待事件

青旅半醒 2023-01-18 06:39 170阅读 0赞

![cbef093dcc044b2793832001e2365e43.png][]0Other736![cbef093dcc044b2793832001e2365e43.png][]1Application17![cbef093dcc044b2793832001e2365e43.png][]2Configuration24![cbef093dcc044b2793832001e2365e43.png][]3Administrative55![cbef093dcc044b2793832001e2365e43.png][]4Concurrency33![cbef093dcc044b2793832001e2365e43.png][]5Commit2![cbef093dcc044b2793832001e2365e43.png][]6Idle95![cbef093dcc044b2793832001e2365e43.png][]7Network35![cbef093dcc044b2793832001e2365e43.png][]8UserI/O48![cbef093dcc044b2793832001e2365e43.png][]9System I/O30![cbef093dcc044b2793832001e2365e43.png][]10Scheduler8![cbef093dcc044b2793832001e2365e43.png][]11Cluster50![cbef093dcc044b2793832001e2365e43.png][]12Queueing9![cbef093dcc044b2793832001e2365e43.png][]查看具体某个等待事件的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

![cbef093dcc044b2793832001e2365e43.png][]10110617238![cbef093dcc044b2793832001e2365e43.png][]106021212398![cbef093dcc044b2793832001e2365e43.png][]79960045178![cbef093dcc044b2793832001e2365e43.png][]1112108414368![cbef093dcc044b2793832001e2365e43.png][]这里把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

![cbef093dcc044b2793832001e2365e43.png][]0logical reads    YES

![cbef093dcc044b2793832001e2365e43.png][]1buffer busy waits    NO

![cbef093dcc044b2793832001e2365e43.png][]2gc buffer busy    NO

![cbef093dcc044b2793832001e2365e43.png][]3db block changes    YES

![cbef093dcc044b2793832001e2365e43.png][]4physical reads    NO

![cbef093dcc044b2793832001e2365e43.png][]5physical writes    NO

![cbef093dcc044b2793832001e2365e43.png][]6physicalreadrequests    NO

![cbef093dcc044b2793832001e2365e43.png][]7physical write requests    NO

![cbef093dcc044b2793832001e2365e43.png][]8physical reads direct    NO

![cbef093dcc044b2793832001e2365e43.png][]9physical writes direct    NO

![cbef093dcc044b2793832001e2365e43.png][]11optimized physical reads    NO

![cbef093dcc044b2793832001e2365e43.png][]12gc cr blocks received    NO

![cbef093dcc044b2793832001e2365e43.png][]13gccurrentblocks received    NO

![cbef093dcc044b2793832001e2365e43.png][]14ITL waits    NO

![cbef093dcc044b2793832001e2365e43.png][]15row lock waits    NO

![cbef093dcc044b2793832001e2365e43.png][]17spaceused    NO

![cbef093dcc044b2793832001e2365e43.png][]18spaceallocated    NO

![cbef093dcc044b2793832001e2365e43.png][]20segment scans    NO

![cbef093dcc044b2793832001e2365e43.png][]

这里顺便说一个比较有趣的视图

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

[cbef093dcc044b2793832001e2365e43.png]: /images/20221021/fa0738bca3e74e1180fc770fa3c873f3.png