|
s.waits "Waits",
(waits/gets ) * 100 "% Ratio"
from v$rollstat s, v$rollname r
where s.usn = r.usn
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 9.0 Investigation
prompt If the radio is above 2% , create more rollback segment.
prompt The guideline is :
prompt Transaction Number * Rollback Seg. Num
prompt < 16 * 4
prompt >= 16 & < 32 * 8
prompt >= 32 * Min(50,Transaction Numbers/4)
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 9.1 Rollback Segment Contention +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** Rollback Segment Contention ***" Skip 1
select class,count
from v$waitstat
where class like '%undo%'
Union
select name,value
from v$sysstat
where name = 'consistent gets'
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 9.1 Investigation
prompt the ratio of waits for any class should be less than 1% of the
prompt total number of requests
prompt If the ratio is greater than 1% , consider creating more rollback seg
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt How to estimate the transaction size
prompt before execute : select usn,writes from v$rollstat
prompt after execute : select usn,writes from v$rollstat
prompt compare the result
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt
ttitle off
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 10.0 Free Space Coalesced +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt
ttitle left "*** Free Space Coalesced ***" Skip 1
select Tablespace_name , percent_blocks_coalesced
from dba_free_space_coalesced
order by percent_blocks_coalesced
/
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
上一页 [1] [2] [3] [4] [5] [6] 下一页 | 
