oracle优化4

查看回滚段的使用情况，哪个用户正在使用回滚段的资源 :
SELECT S.USERNAME, U.NAME FROM V$TRANSACTION T,V$ROLLSTAT R,
V$ROLLNAME U,V$SESSION S WHERE S.TADDR=T.ADDR AND
T.XIDUSN=R.USN AND R.USN=U.USN ORDER BY S.USERNAME;

如何查看一下某个 shared_server 正在忙什么 :
SELECT A.USERNAME,A.MACHINE,A.PROGRAM,A.SID,
A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT
FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C
WHERE B.SPID= 13161 AND B.ADDR=A.PADDR
AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;

数据库共享池性能检查 :
SELECT NAMESPACE,GETS,GETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONS FROM V$LIBRARYCACHE WHERE NAMESPACE IN ( 'SQLAREA' , 'TABLE/PROCEDURE' , 'BODY' , 'TRIGGER' );

检查数据重载比率 :
SELECT SUM (RELOADS)/SUM (PINS)* 100 "RELOAD RATIO" FROM
V$LIBRARYCACHE;

检查数据字典的命中率 :
SELECT 1 -SUM (GETMISSES)/SUM (GETS) "DATA DICTIONARY HIT
RATIO" FROM V$ROWCACHE;
( 对于 library cache, gethitratio 和 pinhitratio 应该大于 90%, 对于数据重载比率 ,reload ratio 应该小于 1%, 对于数据字典的命中率 ,data dictionary hit ratio 应该大于 85%)

 

检查共享内存的剩余情况 :
SELECT REQUEST_MISSES, REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED;
( 对于共享内存的剩余情况 , request_misses 和 request_failures 应该接近 0)

数据高速缓冲区性能检查 :
SELECT 1 -P.VALUE /(B.VALUE +C.VALUE ) "DB BUFFER CACHE HIT RATIO" FROM V$SYSSTAT P,V$SYSSTAT B,V$SYSSTAT C WHERE P.NAME = 'PHYSICAL READS' AND B.NAME = 'DB BLOCK GETS' AND C.NAME = 'CONSISTENT GETS' ;