换休在家总结的sql，主要是对latch 的查询

--每个用户使用的资源
SELECT A.USERNAME,B.BLOCK_GETS,B.CONSISTENT_GETS,
B.PHYSICAL_READS,B.BLOCK_CHANGES,B.CONSISTENT_CHANGES
FROM V$SESSION A,V$SESS_IO B
WHERE A.SID=B.SID
ORDER BY USERNAME;
----用户访问的对象
select a.sid,b.owner,b.object,b.type
from v$session a,v$access b
where a.sid=b.sid;

------freelist
select ((a.count/(b.value-c.value))*100) pct
from v$waitstat a,v$sysstat b,v$sysstat c
where a.class='free list'
and b.statistic#=(select statistic# from v$statname
where name='db block gets')
and c.statistic#=(select statistic# from v$statname
where name='consistent gets');
--------根据用户名进行授权的对象级特权
select b.owner||'.'||b.table_name obj,
b.privilege what_granted,b.grantable,a.username
from dba_users a,dba_tab_privs b
where a.username=b.grantee
order by 1,2,3;

---------共享池拴锁的效率
SELECT NAME,
ROUND(MISSES/DECODE(GETS,0,1,GETS),2)*100||'%' AS "WILLTOWAITMISSRATIO",
ROUND(IMMEDIATE_MISSES/DECODE(IMMEDIATE_GETS,0,1,IMMEDIATE_GETS),2)*100||'%' "IMMMISSRATIO",
SLEEPS
FROM V$LATCH
WHERE NAME IN('library cache','row cache objects','shared pool','shared java pool');

----------缓存
select o.*,d_o.owner,d_o.object_name,object_type,o.buffer,o.avg_touches
from (select obj object,count(1) buffer,avg(tch) avg_touches
from x$bh
group by obj) o,
dba_objects d_o
where o.object=d_o.DATA_OBJECT_ID
order by owner,object_name
-------------latch wait for cache buffers chains
select /*+orderde*/
de.owner||'.'||de.segment_name segment_name,
de.segment_type segment_type,
de.extent_id extent#,
bh.dbablk-de.block_id+1 block#,
bh.lru_flag,
bh.tch,
lc.child#
from (select max(sleeps)maxsleeps from v$latch_children
where name='cache buffers chains') max_sleeps,
v$latch_children lc,
x$bh bh,dba_extents de
where lc.name='cache buffers chains'
and lc.sleeps>(0.8*maxsleeps)
and bh.hladdr=lc.addr
and de.file_id=bh.file#
and bh.dbablk between de.block_id
and de.block_id+de.blocks-1
order by bh.tch;

--------------- wait buffer busy
select /*+ordered*/
sid,event,owner,segment_name,segment_type,p1,p2,p3
from v$session_wait sw,dba_extents de
where de.FILE_ID=sw.P1
and sw.P2 between de.BLOCK_ID and de.BLOCK_ID+de.BLOCKS-1
and (event='buffer busy waits' or event='write complete waits')
and p1 is not null
order by event,sid;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6456/viewspace-112208/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6456/viewspace-112208/