Re: shrinking rbs?

A copy of this was sent to Doug O'Leary <dkoleary_at_mediaone.net> (if that email address didn't require changing) On Fri, 01 Jun 2001 02:02:58 GMT, you wrote:

>In article <thd2nb7jerf09a_at_beta-news.demon.nl>, postbus_at_sybrandb.demon.nl
>says...
>
>
> He is partly correct, but mostly incorrect.
> You are indeed risking incurring 1555, but not because they are in use, but
> because you make them too small. Selects are no transactions and do not
> extend the rollback segment.
> His resolution to alter the rollback segment offline and online, would,
> AFAIK, not change anything.
>
>I didn't think that sounded right; however, I'm not sure I understand
>your reason for the "snapshot too old" risk either. Let me outline what
>I believe our situation was and what I think you're saying and you can
>correct where I'm having the problem:
>
>1. Our rollback segments started with two extents each and grew from
>there. Since optsize wasn't set, the rollback segments never shrunk back
>down and eventually reached their maxextents of 121 (thus, the ora-1628
>errors).
>

well, i don't see how shrinking the rbs is going to ultimately resolve the 1628. I mean -- that was in effect saying "I've allocated N extents and I need MORE to satisfy the current workload".

You seem doomed to get the ora1628 again in the future unless you

• make the rbs's have bigger extents
• allow the rbs's to have more extents.

>2. When I manually shrunk the rollback segments, they went back down
>to two extents each. Now, I take it, from what you and the our resident
>dba are telling me, that if there was an active transaction happening at
>that specific time and if someone else were running long winded queries
>against the database, then I'd be at risk for the snapshot too old
>errors. This is because there's a strong possibility that the rollback
>segment extents the transaction was using just got shrunk out of
>existance. Do I got that right?
>

you don't need ANY active transactions (modifying the database)

you just need a query. That query MIGHT (might not) need some of the data in the 119 or so extents you just released -- that don't exist anymore. If it found that it did (and bear in mind, a query never KNOWS ahead of time what rbs data it needs -- it discovers this as it hits blocks), it would get the ora-1555.

You need no WRITE transactions.

You only need one query (and it doesn't have to be long running really, it only took you a couple of seconds to shrink them, the query just needs to be a little longer than that)

>If that is right, I have a bone to pick with Oracle (gee, I'm sure
>they'll get right on that!). If you're shrinking rollback segments,
>wouldn't it make sense to *not* remove extents that are actively being
>used?
>

yes it would, yes it does, and no we don't.

The problem is a query doesn't "USE" rbs. It might need the contents of rbs at some point to provide a read consistent view of the data -- but it is not using rbs. A "write" transaction will use RBS and if it was using it -- we would not shrink it.

>Assuming again that #2 is correct - is there a correct method to manually
>shrink rollback segments without risking the snapshot errors?
>

no, i hate shrinking rollback segments.

The probability of an ORA-1555 is in direct proportion to the size of your SMALLEST rollback segment. You don't want them to shrink, you'd really like to have N rollback segments each of the same exact size permanently allocated.

>I appreciate your time and patience, Sybrand. I know you end up
>answering a lot of questions. I appreaciate it.
>
>Doug

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp