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