程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 【ORACLE】分析oracle會話及性能語句23條

【ORACLE】分析oracle會話及性能語句23條

編輯:Oracle教程

【ORACLE】分析oracle會話及性能語句23條


--查看集群等待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;

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved