Re: Do I really need more than 1 rollback segment?

Well, I've been out at the UK Oracle User Group conference for the last few days, so I haven't been keeping an eye on the newsgroup - but you might like to experiment along the following lines:

column name format a50

select
 phywrts, phyblkwrt, name
from
 v$filestat fs,
 v$datafile df
where
 df.file# = fs.file#
;

column name format a20

select
 name, writes, gets, extents, rssize
from
 v$rollstat rs,
 sys.undo$ un
where
 un.us# = rs.usn
and un.name != 'SYSTEM'
;

begin
 for i in 1..100 loop
  log_package.log_message('log.txt',i);
  insert into rollback_demo
  select rownum, rpad('x',1000)
  from all_objects
  where rownum <= 20;
  commit;
  delete from rollback_demo;
  commit;
 end loop;
end;
/

column name format a50

select
 phywrts, phyblkwrt, name
from
 v$filestat fs,
 v$datafile df
where
 df.file# = fs.file#
;

column name format a20

select
 name, writes, gets, extents, rssize
from
 v$rollstat rs,
 sys.undo$ un
where
 un.us# = rs.usn
and un.name != 'SYSTEM'
;

Set up a system with one rollback segment other than the system rollback segment, and run the above code a couple of times with variations in the size of the rollback segment.

Typical results for 4K block size, 1024 blocks in the buffer and:

        3 x 32K extents in RBS
or     5 x 1MB extents in RBS

Stats from v$rollstat - virtually no difference. Stats from v$filestat -

    Small rbs 50 blocks written
    Large rbs 686 blocks written.

If your rollback segments are small enough they will be recycled before Oracle has time to dump them to disc - and this can make a dramatic difference on the total I/O - which will affect the way DBWR can work even if the RBS is on a totally separate set of discs.

Having said that, I also point out in the seminar that whilst small is good, you have to size your total rollback to support your longest running read-consistent event.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Howard J. Rogers wrote in message
<3c166064$0$559$afc38c87_at_news.optusnet.com.au>...


>We could have a battle of the giants on our hand here.  Steve Adams says

>make 'em big, and who cares about a bit of wasted space: these things are

>supposed to be on their own hard disk anyway.  (I am paraphrasing like

>crazy, natch).

>

>Personally, I go for the Steve Adams school of thought on this one.  I

can't


>see any drawbacks (though I'm sure Jonathan will elaborate on the

>'additional I/O' idea) of large segments.  It's the NUMBER of them that's

>the worry, to avoid contention issues.

>