11.回滚段信息

1）信息1

col segment_name format a20 col tablespace_name format a20 select segment_name,owner,tablespace_name, dba_rollback_segs.status from dba_rollback_segs,v$Datafile where file_id=file#;

     2）信息2 select segment_name,initial_extent,next_extent,min_extents, owner,dba_rollback_segs.status status,optsize from dba_rollback_segs,v$rollstat where dba_rollback_segs.segment_id=v$rollstat.usn;

 

3）信息3

col Rollback_Name for a16 select substr(V$rollname.NAME,1,20) "Rollback_Name",         substr(V$rollstat.EXTENTS,1,6) "EXTENT",         v$rollstat.RSSIZE, v$rollstat.WRITES,         substr(v$rollstat.XACTS,1,6) "XACTS",         v$rollstat.GETS,         substr(v$rollstat.WAITS,1,6) "WAITS",         v$rollstat.HWMSIZE, v$rollstat.SHRINKS,         substr(v$rollstat.WRAPS,1,6) "WRAPS",         substr(v$rollstat.EXTENDS,1,6) "EXTEND",         v$rollstat.AVESHRINK,         v$rollstat.AVEACTIVE from v$rollname, v$rollstat where v$rollname.USN = v$rollstat.USN order by v$rollname.USN;

4）信息4

select  r.name Rollback_Name,        p.pid Oracle_PID,          p.spid OS_PID,          nvl(p.username,'NO TRANSACTION') Transaction,          p.terminal Terminal  from v$lock l, v$process p, v$rollname r  where   l.addr = p.addr(+)          and trunc(l.id1(+)/65536)=r.usn        and l.type(+) = 'TX'          and l.lmode(+) = 6  order by r.name;

5）回滚段的争用情况

select name ,waits ,gets ,waits/gets "Ratio" from v$rollstat a ,v$rollname b where a.usn=b.usn;

6）rollback信息

select  substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#",          substr(sys.dba_segments.OWNER,1,8) "Owner",          substr(sys.dba_segments.TABLESPACE_NAME,1,17) "Tablespace Name",          substr(sys.dba_segments.SEGMENT_NAME,1,12) "Rollback Name",          substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent",         substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts",         substr(sys.dba_segments.MIN_EXTENTS,1,5) "MinEx",         substr(sys.dba_segments.MAX_EXTENTS,1,5) "MaxEx",         substr(sys.dba_segments.PCT_INCREASE,1,5) "%Incr",          substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)",          substr(sys.dba_segments.EXTENTS,1,6) "Extent#",          substr(sys.dba_rollback_segs.STATUS,1,10) "Status"  from sys.dba_segments, sys.dba_rollback_segs  where sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and        sys.dba_segments.segment_type = 'ROLLBACK'  order by sys.dba_rollback_segs.segment_id;

 

12.Redo log信息检查

1）Redo Log 文件状态

   col member for a56 select f.member "member",        f.group# "group",        l.bytes/1024/1024 "size",        l.status from v$logfile f, v$log l where f.group#=l.group# order by f.group#,f.member;

2）LogGroup信息

SELECT group#, sequence#, bytes, members, status from v$log;

3）关于log_buffer

        select name,value from v$sysstat where name in('redo entries','redo buffer allocation retries');

4）查询LOG大小及频率

set linesize 300 set pages 100 column d1 form a20 heading "Date" column sw_cnt form 99999 heading 'Number|of|Switches' column Mb form 999,999 heading "Redo Size" column redoMbytes form 999,999,9999 heading "Redo Log File Size (Mb)"

break on report

compute sum of sw_cnt on report compute sum of Mb on report

var redoMbytes number;

begin    select max(bytes)/1024/1024 into :redoMbytes from v$log; end; /

print redoMbytes

select trunc(first_time) d1

       , count(*) sw_cnt        , count(*) * :redoMbytes Mb from v$log_history group by trunc(first_time) /

 

13.IO情况检查

   col file_name for a46 select df.name file_name, fs.phyrds reads, fs.phywrts writes, (fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) readtime, (fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) writetime from  v$datafile df,v$filestat fs where df.file#=fs.file# order by df.name;

select count(*) from v$session;

14.命中率相关检查

1）Shared Pool Size 命中率    select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %" from v$librarycache where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');

2）数据字典命中率

select round((1-sum(getmisses)/sum(gets))*100,1) "data dictionary hit ratio %" from v$rowcache;

3）锁竞争

select  substr(ln.name,1,25) Name,          l.gets, l.misses,          100*(l.misses/l.gets) "% Ratio (STAY UNDER 1%)"  from v$latch l, v$latchname ln  where ln.name in ('cache buffers lru chain')  and ln.latch# = l.latch#;

4）排序命中率

select a.value "Sort(Disk)", b.value "Sort(Memory)", round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)"   from v$sysstat a, v$sysstat b where a.name = 'sorts (disk)' and b.name = 'sorts (memory)';

5）数据缓冲区命中率

select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratio  from v$sysstat phy,v$sysstat cur,v$sysstat con where phy.name='physical reads' and cur.name='db block gets' and con.name='consistent gets';

6）Miss LRU Hit命中率

column "Miss LRU Hit%" format 99.9999999; col name format a40  select name, (sleeps/gets) "Miss LRU Hit%" from v$latch where name ='cache buffers lru chain';

7）检查内存排序性能

select a.name, to_char(value) from v$statname a, v$sysstat where a.statistic# = v$sysstat.statistic# and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)');

8）redo log buffer retry ratio

select to_char(r.value/e.value) "redo log buffer retry ratio" from v$sysstat r,v$sysstat e where r.name='redo buffer allocation retries' and e.name='redo entries';

9）wait等待检查

select count(*) total_in_wait from v$session_wait where event='log buffer space';

select event,total_waits,time_waited,average_wait

from v$system_event where event like '%undo%';

select sid,seq#,event,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%'

and event not like 'rdbms%';

15、查询lock锁

   SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0) ORDER BY id1, request;