数据库共享池性能检查:
SQL>Select namespace,gets,gethitratio,pins,pinhitratio,reloads,
Invalidations from v$librarycache where namespace in
('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');
检查数据重载比率:
SQL>select sum(reloads)/sum(pins)*100 "reload ratio" from
v$librarycache;
检查数据字典的命中率:
SQL>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%)
检查共享内存的剩余情况:
SQL>select request_misses, request_failures from v$shared_pool_reserved;
(对于共享内存的剩余情况, request_misses 和request_failures应该接近0)
数据高速缓冲区性能检查:
SQL>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';
检查buffer pool HIT_RATIO执行
SQL>select name, (physical_reads/(db_block_gets+consistent_gets))
"MISS_HIT_RATIO" FROM v$buffer_pool_statistics WHERE (db_block_gets+ consistent_gets)> 0;
(正常时db buffer cache hit ratio 应该大于90%,正常时buffer pool MISS_HIT_RATIO 应该小于10%)
数据库回滚段性能检查:
检查Ratio执行
SQL>select sum(waits)* 100 /sum(gets) "Ratio", sum(waits)
   "Waits", sum(gets) "Gets" from v$rollstat;
检查count/value执行:
SQL>select class,count from v$waitstat where class like '%undo%';
SQL>select value from v$sysstat where name='consistent gets';
(两者的value值相除)
检查average_wait执行:
SQL>select event,total_waits,time_waited,average_wait from v$system_event
where event like '%undo%';
检查RBS header get ratio执行:
SQL>select n.name,s.usn,s.wraps, decode(s.waits,0,1,1- s.waits/s.gets)"RBS
header get ratio" from v$rollstat s,v$rollname n where s.usn=n.usn;
(正常时Ratio应该小于1%, count/value应该小于0.01%,average_wait最好为0，该值越小越好,RBS header get ratio应该大于95%)
杀会话的脚本:
SQL>select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS,
'orakill '||sid||' '||spid HOST_COMMAND,
'alter system kill session '''||A.sid||','||A.SERIAL#||'''' SQL_COMMAND
from v$session A,V$PROCESS B where A.PADDR=B.ADDR AND SID>6;
查看排序段的性能:
SQL>SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
找出消耗CPU最高的进程对应的SQL语句：
SQL>set line 240
SQL>set verify off
SQL>column sid format 999
SQL>column pid format 999
SQL>column S_# format 999
SQL>column username format A9 heading "ORA User"
SQL>column program  format a29
SQL>column SQL      format a60
SQL>COLUMN OSname format a9 Heading "OS User"
SQL>SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
S.osuser osname,P.serial# S_#,P.terminal,P.program  program,
P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80))  SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr
AND S.sql_address = a.address (+)  AND P.spid LIKE '%&1%';
Enter value for 1: PID（这里输入占用CPU最高的进程对应的PID）
以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句：
SQL>col owner for a12
SQL>col object_name for a16
SQL>select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id;
SQL>select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
SQL>Select sql_address from v$session where sid=;
SQL>Select * from v$sqltext where address=;
SQL>select COMMAND_TYPE,PIECE,sql_text from v$sqltext where address=(select sql_address from v$session a where sid=18);