Hi all,
I did a test and my conclusions are:
- You can shrink your rollback segment down to (next + initial + a bit of
header (not so sure the size))
not down to initial * minextents
this will cause ora 1555 for sure
What I did for test "ALTER ROLLBACK SEGMENT rbs SHRINK TO 1M; to get
minimal shrink.
Oracle will shrink "as smaller size as possible".
- You must set
if next > initial then
Optimal size > next * minextents
elseif initial > next then
Optimal size > initial * minextents
else
Oracle recommend initial = next
endif
Note: Sorry I'm not log my sql command during test
This is trial and error type of test
Supporting Document from Oracle:
Set an Optimal Number of Extents for Each Rollback Segment
You should carefully assess the kind of transactions the system runs when
setting the OPTIMAL parameter for each rollback segment. For a system that
executes long-running transactions frequently, OPTIMAL should be large so
that Oracle does not have to shrink and allocate extents frequently. Also,
for a system that executes long queries on active data, OPTIMAL should be
large to avoid "snapshot too old" errors. OPTIMAL should be smaller for a
system that mainly executes short transactions and queries so that the
rollback segments remain small enough to be cached in memory, thus improving
system performance.
Sinardy
-----Original Message-----
Carmichael
Sent: 31 January 2002 21:05
To: Multiple recipients of list ORACLE-L
very strange.... got the same results you did. But when I did my own
testing I didn't shrink below initial*min_extents
Now, I had done one thing you didn't, which was to run a delete that
would force the rollback segment to extend before I tried the shrink.
Strangeness abounds
Anyone know someone in Oracle who can go in and read the code? <G>
- Rachel Carmichael <wisernet100_at_yahoo.com> wrote:
> doesn't sound like you are doing anything stupid, and I ran on
> 8.1.6.0
> and it said it would shrink it but didn't. I'll use your test case
> against my databases in the morning
>
>
> --- "Johnston, Tim" <TJohnston_at_quallaby.com> wrote:
> > Ok... I've ran this test case on both a 8.1.6.0 and a 8.1.7.3
> > database...
> > I've ran it with both public and private rollback segments (just in
> > case)...
> > I've looked at rssize in v$rollstat and sum(bytes) from
> > dba_segments...
> > And, in all cases, it allows me to shrink the segment to less then
> > both
> > optimal and min_extents*extent_size even though
> > optimal=min_extents*extent_size... Is there something wrong in the
> > test
> > case? Has anyone ran this on one of their test databases? I'm
> > confused
> > since the test case seems straight forward... The way I read, it
> > allows me
> > to perform the shrink and the resulting segment is actually the
> size
> > I
> > specify... Am I doing something stupid in the test case?
> >
> > Thanks
> > Tim
> >
> > PS - Here is the test case again...
> >
> > column segment_name format a30
> > create public rollback segment tim tablespace rbs storage ( initial
> > 1M next
> > 1M minextents 20 maxextents 100 optimal 20M );
> > alter rollback segment tim online;
> > select segment_name, min_extents, initial_extent, next_extent
> > from dba_segments
> > where segment_name = 'TIM';
> > select rs.extents, rs.rssize/1048576 , rs.optsize/1048576
> > from dba_rollback_segs drs,
> > v$rollstat rs
> > where drs.segment_name = 'TIM'
> > and drs.segment_id = rs.usn;
> > alter rollback segment TIM shrink to 10M;
> > select segment_name, min_extents, initial_extent, next_extent
> > from dba_segments
> > where segment_name = 'TIM';
> > select rs.extents, rs.rssize, rs.optsize
> > from dba_rollback_segs drs,
> > v$rollstat rs
> > where drs.segment_name = 'TIM'
> > and drs.segment_id = rs.usn;
> > alter rollback segment tim offline;
> > drop rollback segment tim;
> >
> > -----Original Message-----
> > Sent: Wednesday, January 30, 2002 2:31 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Um... Must be version differences... Here is a test case I ran on
> > my
> > 8.1.7.3 machine...
> >
> > SQL> create public rollback segment tim tablespace rbs storage (
> > initial 1M
> > next 1M minextents 20 maxextents 100 optimal 20M );
> >
> > Rollback segment created.
> >
> > SQL> alter rollback segment tim online;
> >
> > Rollback segment altered.
> >
> > SQL> select segment_name, min_extents, initial_extent, next_extent
> > 2 from dba_segments
> > 3 where segment_name = 'TIM';
> >
> > SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT
> NEXT_EXTENT
> > ------------------------------ ----------- --------------
> -----------
> > TIM 20 1048576
> 1048576
> >
> > SQL> select rs.extents, rs.rssize/1048576 , rs.optsize/1048576
> > 2 from dba_rollback_segs drs,
> > 3 v$rollstat rs
> > 4 where drs.segment_name = 'TIM'
> > 5 and drs.segment_id = rs.usn;
> >
> > EXTENTS RS.RSSIZE/1048576 RS.OPTSIZE/1048576
> > ---------- ----------------- ------------------
> > 20 20.296875 20
> >
> > SQL> alter rollback segment TIM shrink to 10M;
> >
> > Rollback segment altered.
> >
> > SQL> select segment_name, min_extents, initial_extent, next_extent
> > 2 from dba_segments
> > 3 where segment_name = 'TIM';
> >
> > SEGMENT_NAME MIN_EXTENTS INITIAL_EXTENT
> NEXT_EXTENT
> > ------------------------------ ----------- --------------
> -----------
> > TIM 20 1048576
> 1048576
> >
> > SQL> select rs.extents, rs.rssize, rs.optsize
> > 2 from dba_rollback_segs drs,
> > 3 v$rollstat rs
> > 4 where drs.segment_name = 'TIM'
> > 5 and drs.segment_id = rs.usn;
> >
> > EXTENTS RSSIZE OPTSIZE
> > ---------- ---------- ----------
> > 10 10633216 20971520
> >
> > SQL> alter rollback segment tim offline;
> >
> > Rollback segment altered.
> >
> > SQL> drop rollback segment tim;
> >
> > Rollback segment dropped.
> >
> > SQL>
> >
> > What version is your test running on?
> >
> > Tim
> >
> > PS - Here is my test case if you want to run it...
> >
> > column segment_name format a30
> > create public rollback segment tim tablespace rbs storage ( initial
> > 1M next
> > 1M minextents 20 maxextents 100 optimal 20M );
> > alter rollback segment tim online;
> > select segment_name, min_extents, initial_extent, next_extent
> > from dba_segments
> > where segment_name = 'TIM';
> > select rs.extents, rs.rssize/1048576 , rs.optsize/1048576
> > from dba_rollback_segs drs,
> > v$rollstat rs
> > where drs.segment_name = 'TIM'
> > and drs.segment_id = rs.usn;
> > alter rollback segment TIM shrink to 10M;
> > select segment_name, min_extents, initial_extent, next_extent
> > from dba_segments
> > where segment_name = 'TIM';
> > select rs.extents, rs.rssize, rs.optsize
> > from dba_rollback_segs drs,
> > v$rollstat rs
> > where drs.segment_name = 'TIM'
> > and drs.segment_id = rs.usn;
> > alter rollback segment tim offline;
> > drop rollback segment tim;
> >
> > -----Original Message-----
> > Sent: Wednesday, January 30, 2002 2:17 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > testing....
> >
> > if the initial extent*min_extents is less than the optimal, yes you
> > can
> > shrink below optimal.
> >
> > if initial*minextents = optimal, you can execute an alter rollback
> > segment statement that lists a shrink size that is less than
> optimal
> > AND less than initial*minextents and it will not fail. But if you
> > LOOK
> > at the size it shrinks to, it's initial*minextents.
> >
> > so you can "kinda" shrink below optimal, the statement won't fail,
> > but
> > Oracle will ignore the number you give it.
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Johnston, Tim
> > INET: TJohnston_at_quallaby.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing
> > Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like
> subscribing).
>
=== message truncated ===
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions!
http://auctions.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sinard Xing
INET: sinardyxing_at_bcs-ach.com.sg
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Feb 01 2002 - 04:21:34 CST
