MarkP28665 wrote:
>
> >> Please point me to a manual that covers this or explain how to
> determine rollback segment fragmentation. <<
>
> The Concepts manual and Database Administrator's Guide explain everything
> pretty well. If you use the optimize parameter on your rollback segments
> the segments are probably not in unnecessary extents. Dropping and
> recreateing the rollback segments is more likely done to eliminate
> tablespace fragmentation. If the rollback segments does have unnecessary
> extents then with Oracle ver. 7.3+ the command 'alter rollback segment
> seg_name skrink;' can be used to force the segment to give up the extra
> extents. Normally Oracle only optimizes a rollback segment when looking
> for another extent within the segment and it determines that the segment
> is larger than the optimal size and unused extents exist.
> So a segment will hang onto extents created to support a large job until
> some other job/transaction fills an extent within the segment and tries to
> get another one. Kevin Loney's Oracle DBA Handbook gives a good, detailed
> explanation of rollback segment interanal handling.
>
> Look at the v$rollstat and sys.dba_rollback_segs tables for current
> information on your segments.
>
> Mark Powell -- The only advise that counts is the advise that you follow
> so follow your own advise
--
You had to drop and recreate the rollback segments only in Version 6.
With
the optimal parameter (in your create rollback segment statements) you
don't
need to drop the rollback segments. If the parameter is not set
correctly (too
low) you'll have increasing number of shrinks (in v$rollstat) and you
can get
"Snapshot too old" errors. If it is too large, then you will get lower
cache hit
ratios on the segments.
Regards,
Virag
----------------------------------------------------------------------------
Virag Saksena
vsaksena_at_us.oracle.com
Senior Principal Consultant
650.506.5087
System Performance Group
Oracle Services
----------------------------------------------------------------------------
Received on Mon Aug 18 1997 - 00:00:00 CDT
