I supported a database with 300+ GB of data and it has a couple of
tables with 2 Million rows, but I never have a needed to use more than
200MB size of rollback segment. The Oracle rule of thumb is if the
table has a lot of small insert/update transaction then you needed more
smaller rollback segments (say 0.5 ~ 10 MB - depended on how wide of the
table - for each rollaback seg. and you probably needed 5 or 6 of them).
For a very large transaction, for example: import a 2 million rows table,
you needed a very large rollback seg (300 MB). In addition, set the OPTIMAL
size = the size of (Initial extent + Next Extent) + 10%, this will prevent
the rollback segment keep expanding & shrink back so many time, thus reduce
the Oracle's performance.
Doug Cowles wrote in message <374C25B7.E2DC840C_at_bigfoot.com>...
>Ok.... You're probably right, it is probably a snapshot too old error, but
I
>have
>trouble understanding why. The code consists of 3 loops, all updating
exactly
>the
>same table, committing after every record. The error occurs on the third
loop.
>There is a completely unnecessary select for update in a cursor for each of
the
>loops.
>The update part is unnecessary. Does a select for update cause rollback
entries?
>
>Even if it did, they are not really updated, so I am confused. Code looks
>similar
>to the following, keeping in mind that page_fact is about a million rows.
>
>cursor a is select akey from atable for update;
>cursor b is select bkey from btable for update;
>cursor c is select ckey from ctable for update;
>begin
>for i in a loop
> update page_fact
> set pf_key = akey
> where x=y;
> commit;
>end loop
>
>for j in b loop
> update page_fact
> set pr_key = bkey
> where x=y;
> commit;
>end loop
>
>for k in c loop
> update page_fact
> set ps_key = c_key
> where x=y;
> commit;
>end loop
>
>Supposedly, the c loop is where the code bombs. I'm going to re-run it
over
>night to be sure -
>but I don't see why this would cause a snapshot too old error (if all the
>assumptions
>are correct) Maybe someone else would.
>
>As to the rollback sizes - you are right, they are not really 1.5 GB, they
are
>really 60MB
>optimal 70MB with unlimited extents. But they have the ability to extend
to 1.5
>GB, which
>would eliminate the possibility of them not being big enough no? Since you
said
>
>that snapshot too old is not a space error, I am sort of encouraged, but
curious
>as to why
>the above would cause it. If anyone can suggest futher reading, good
threads
>etc., on
>snapshot too old I would be much obliged....
>
>- Dc.
>
>John Higgins wrote:
>
>> You say that you have created 3 1.5GB rollback segments but the HWM never
>> get above 300M.
>>
>> I think you have NOT created the rollback segments at 1.5GB.
>>
>> If, for example, you specify Initial as 100MB, next ad 100MB and min
extents
>> as 2, then you have created a 200MB rollback segment. It may be inside a
>> 1.5GB tablespace, but only big transactions cause a rollback segment to
>> extend. Otherwise, Oracle just wraps around and around the current size.
>>
>> Also, what about OPTIMAL? If it is set to a size less than 1.5GB, Oracle
>> will shrink the rollback segment back to that size.
>>
>> I suspect that you are getting the ORA-01555 snapshot too old; rollback
too
>> small type error. This is not an out-of space error. This means a query
>> needs to find old data in the rollback (for read consistency) but the
>> updates have wrapped around and overwritten the old data. Curiously, if
the
>> updates had not committed as often, you might avoid this error! (But
then,
>> the updates might extend the rollback segments past the size of the
>> tablespace!)
>>
>> This
>>
>> Doug Cowles wrote:
>>
>> > I know the rule of thumb is, lots of connections and users, OLTP, lots
>> > of little and
>> > medium sized rollback segments, big batch jobs, fewer, and larger
>> > rollback segments.
>> >
>> > But..
>> >
>> > We have someone doing some preliminary datawarehouse loads, and have
>> > provided
>> > 3 1.5G rollback segments, combined equalling 4.5G which is bigger than
>> > the database
>> > itself (I know that's ridiculous) , but our user is getting rollback
too
>> > small, and the high
>> > water marks on these three 1.5G rollback segments never get above 300M.
>> > Still
>> > PLENTY of room, at far as I know.
>> >
>> > So..
>> > I'm guessing that there are certain situations in Oracle, even with one
>> > user and one session,
>> > where Oracle will ask for another rollback segment, rather than extend
>> > one where there
>> > is plenty of room. Can anyone either tell me I'm wrong, or tell me
>> > what kind of circumstances would cause this?
>> >
>> > As to the errors, I have not seen specifically what it is. User says
>> > it's rollback too small,
>> > but for all I know it could be need more rollbacks or snapshot too old.
>> > Regardless..any
>> > feedback would be appreciated.
>> >
>> > - Dc.
>
>
>
Received on Mon May 31 1999 - 10:40:09 CDT
