--查看集群等待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
WHEReva lue>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大占用系统内存语句，总的内存使用量