【ORACLE】分析oracle会话及性能语句23条

************************************************************************

****原文：blog.youkuaiyun.com/clark_xu 徐长亮的专栏

************************************************************************

--查看集群等待cache busy--等待资源  buffer busy --数据块一致读等待

SELECT inst_id,event,p1 file_number,p2 block_number,wait_time

FROM gv$session_wait

WHERE eventIN ('global cache busy','buffer busy global cache','buffer busy global cr');

 

--全局锁的会话

SELECT

d1.INST_ID inst_id,s.sid,p.spid,

d1.RESOURCE_NAME1 resource_name,

s.sid,sw.EVENT,sw.WAIT_TIME

FROM gv$ges_enqueue d1,gv$process p,gv$session s,gv$session_wait sw

WHERE blocker=1

AND (d1.INST_ID=p.INST_IDand d1.pid=p.spid)

AND (p.inst_id=s.INST_IDand p.addr=s.paddr)

AND (s.INST_ID=sw.INST_IDand s.sid=sw.sid)

ORDERBY sw.WAIT_TIMEdesc;

 

--查询缓存融合写操作的比率

SELECT A.INST_ID "Instance",

       A.VALUE/B.value "Cache fusion writes ratio"

FROM GV$SYSSTAT A,GV$SYSSTAT B

WHERE A.name='DBWR fusion writes'

AND  B.name='physical writes'

AND  B.INST_ID=A.inst_id

GROUPBY A.INST_ID, A.VALUE/B.VALUE;

 

 

--查询网络地址

SELECT *FROM GV$CLUSTER_INTERCONNECTS;

 

--一致性数据块请求时间

SELECT b1.INST_ID,b2.VALUE "blocks recevied",

       b1.VALUE "block recevied time",