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

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

****原文：blog.csdn.net/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",

       ((b1.value/b2.value)*10) "avg block rec time(ms)"

      

FROM gv$sysstat b1,gv$sysstat b2

WHERE b1.name='gc cr block receive time'

AND  b2.name='gc cr blcoks recevied'

AND b1.inst_id=b2.INST_ID

AND b2.value <>0;

 

--查看进程，sga资源

SELECT resource_name,current_utilization cu,max_utilization mu,

        a.INITIAL_ALLOCATION,limit_value lv

FROM gv$resource_limit a

WHERE max_utilization >0;

 

--查看shared_pool_size资源

SELECT *

FROM V$SGASTAT

WHERENAMELIKE'g%';

 

--查看oracle并行进程

SELECT inst_id,statistic,value

FROM gv$pq_sysstat

WHEREvalue>0

orderby1,2;

 

--查看回归段正在进行的事务

select a.name,b.xacts,c.sid,c.SERIAL#,c.USERNAME,d.SQL_TEXT

from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e

where a.usn=b.usn

and b.usn=e.xidusn

and c.taddr=e.addr

and c.sql_address=d.address

and c.sql_hash_value=d.hash_value;

 

--查看unod事务表

select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarecfrom v$transaction;

 

--查看操作系统oracle进程

SELECT a.username,a.COMMAND,a.program,spid,sid,a.serial#

FROM v$session a,v$process b

WHERE a.paddr=b.addr

and spid='5816';

 

 

--查看操作系统进程对应的sql语句

select b.username,a.sql_text

from v$sql a,v$session b

where b.sql_address=a.address

and b.sql_hash_value=a.hash_value

and b.sid='5816';

 

--查看cpu数量

selectname,valuefrom v$parameter wherename='cpu_count';

 

--库缓存命中率

selectsum(pins)/(sum(pins)+sum(reloads))*100 "hit ratio"

from v$librarycache;

 

--数据字典命中率

select (1-sum(getmisses)/sum(gets))*100 "hit ratio"

from v$rowcache;

--PGA内存排序命中率

select a.value "Disk Sorts",b.value "Memroy sorts",

      round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2) "Pct memory sorts"

from v$sysstat a,v$sysstat b

where a.name='sorts (disk)'

and b.name='sorts (memory)';

 

--缓存区，缓存命中率

select (1-sum(decode(name,'physical reads',value,0))/

       (sum(decode(name,'db block gets',value,0))+

        sum(decode(name,'consistent gets',value,0))

       )

       )*100 "hit radio"

from v$sysstat;

 

--10大占用系统内存语句，总的内存使用量

selectsum(pct_bufgets) "percent"

from (selectrank()over (orderby buffer_getsdesc)as rank_bufgets,

       to_char(100* ratio_to_report(buffer_gets)over (),'999.99') pct_bufgets

       from v$sqlarea)

where rank_bufgets <11;

--查看脏块

select objd,count(*)

from v$bh

where dirty='Y'

groupby objdorderbycount(*);

--查看oracle操作系统进程

select *from v$processwhere backgroundisnull;

--查看操作系统进程对应的sql语句

select sql_textfrom v$sql

where sql_idin

 (select sql_idfrom gv$session

  where paddrin (select addrfrom gv$processwhere program ='ORACLE.EXE (SHAD)'));

 

--查看热点对象

--查看每个session占用cpu情况

select ss.sid,se.SERIAL#,se.command,ss.valueCPU ,se.username,se.program

from v$sesstatss, v$session se

where ss.statistic#in

(select statistic#

 from v$statname

 wherename ='CPU used by this session')

 and se.sid=ss.sid

 and ss.sid>6

 orderbyCPUdesc;

 --查看pid对应的sql语句

select s.sid,w.event, w.wait_time, w.seq#, q.sql_text

from v$session_wait w, v$session s, v$process p, v$sqlarea q

where s.paddr=p.addr

--and s.sid=146

and s.SERIAL#=6488

and s.sql_address=q.address;