oracle DBA (三)

找到与所连接的会话有关的当前等待事件：

select SW.Sid,S.Username,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAIT

from v$session S,v$session_wait SW where S.Username is not null and SW.Sid=S.Sid

and SW.event not like '%SQL*Net%' order by SW.Wait_Time Desc;

 

Oracle所有回滚段状态的检查：

SQL>select  segment_name,owner,tablespace_name,initial_extent,next_extent,dba_rollback_segs.status from dba_rollback_segs,v$datafile where file_id=file#;

 

Oracle回滚段扩展信息的检查:

col name format a10

set linesize 140        

select substr(name,1,40) name,extents,rssize,optsize,aveactive,extends,wraps,shrinks,hwmsize

from v$rollname rn,v$rollstat rs where (rn.usn=rs.usn);
 

extents:回滚段中的盘区数量。

Rssize:以字节为单位的回滚段的尺寸。

optsize：为optimal参数设定的值。

Aveactive:从回滚段中删除盘区时释放的以字节为单位的平均空间的大小。

Extends:系统为回滚段增加的盘区的次数。

Shrinks:系统从回滚段中清除盘区（即回滚段收缩）的次数。回滚段每次清除盘区时，系统可能会从这个回滚段中消除一个或多个盘区。

Hwmsize:回滚段尺寸的上限，即回滚段曾经达到的最大尺寸。

(如果回滚段平均尺寸接近OPTIMAL的值，那么说明OPTIMAL的值设置正确，如果回滚段动态增长次数或收缩次数很高，那么需要提高OPTIMAL的值)

 

查看回滚段的使用情况，哪个用户正在使用回滚段的资源:

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';

检查buffer pool HIT_RATIO执行

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执行

select sum(waits)* 100 /sum(gets) "Ratio", sum(waits)

   "Waits", sum(gets) "Gets" from v$rollstat;

检查count/value执行:

select class,count from v$waitstat where class like '%undo%';

select value from v$sysstat where name='consistent gets';

(两者的value值相除)

 

检查average_wait执行:

select event,total_waits,time_waited,average_wait from v$system_event

where event like '%undo%';

 

检查RBS header get ratio执行:

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%)

 

杀会话的脚本:

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)'); 

7、查看数据库库对象:

select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

 

8、查看数据库的版本:　

Select * from v$version;

 

9、查看数据库的创建日期和归档方式:

Select Created, Log_Mode, Log_Mode From V$Database;

 

10、捕捉运行很久的SQL:

column username format a12

column opname format a16

column progress format a8

select username,sid,opname,round(sofar*100 / totalwork,0) || '%' as progress,time_remaining,sql_text from v$session_longops , v$sql where time_remaining <> 0 and sql_address=address and sql_hash_value = hash_value