Re: rollback space

On Thu, 03 Feb 2005 20:01:19 +0100, Frank van Bortel <fvanbortel_at_netscape.net> wrote:

>Tom Dyess wrote:
>> "Frank van Bortel" <fvanbortel_at_netscape.net> wrote in message
>> news:cttbon$rb3$1_at_news3.zwoll1.ov.home.nl...
>>
>>>Tom Dyess wrote:
>>>
>>>><manish.nevrekar_at_gmail.com> wrote in message
>>>>news:1107369583.318529.264990_at_z14g2000cwz.googlegroups.com...
>>>>
>>>>
>>>>>1) Each row has 10 columns. Approximately 70 B each row.
>>>>>2) No lobs
>>>>>3)No other transactions are running, this is a batch load, any other
>>>>>transaction if running at all is select.
>>>>>4) there are no on isert triggers on the table.
>>>>>
>>>>>I beg to differ on commiting after every 'x' rows. I always prefer to
>>>>>commit once at the end of the transaction. Commiting often is
>>>>>definitely more work plus there is always the chance of the dreaded
>>>>>ORA-01555 "Snapshot too old" error.
>>>>>In my ETL procedures every insert and update is time stamped and hence
>>>>>it can be identified.
>>>>>Thanks
>>>>>Manish
>>>>>
>>>>
>>>>
>>>>Here's a pretty good article on the "Snapshot too Old." I committing
>>>>every x rows shouldn't cause a problem. Concerning it being more work, it
>>>>shouldn't take too long, or at least you'll make up for it by having your
>>>>insertion work as desired. Have you tried Oracle's direct data loading?
>>>>I've used DOA for Delphi and it utilizes this capability.
>>>>
>>>>http://www.oraclepower.com/WebPortal/webportal?aid=sr&id=11337
>>>>
>>>
>>>Did you *read* your own recommendation? It says (about committing,
>>>also called fetch across commit): "so a "snapshot too old" error becomes a
>>>real possibility."
>>>
>>>I dare to go further: it's a receipe for a snapshot too old!
>>>
>>>Commit once is a sound advice.
>>>--
>>>Regards,
>>>Frank van Bortel
>>
>>
>> Oh, you mean that italic part about 1/2 way down. Lol. Yes, I noticed it. I
>> also noticed the next paragraph:
>>
>>
>>>As discussed earlier, increasing the number and size of your rollback
>>>segments also helps avoid the "snapshot too old" error message by reducing
>>>the >likelihood that a committed pre-image is overwritten, but it's
>>>difficult to predict how big they will need to be, and you may not want to
>>>create your rollback >segments that big anyway.
>>
>>
>> It seems his issue is his rollback segmets are huge and he isn't getting it
>> to finish.
>>
>>
>>>I dare to go further: it's a receipe for a snapshot too old!
>>
>>
>> You're very daring, Frank. It's not a zero-sum game. I'm not talking about
>> every single row, but 2 commits over the scope of the loading would be
>> something to try. What's your solution?
>>
>> ---
>> Tom Dyess
>> OraclePower.com
>>
>>
>2 commits? where? There was no mention of 2 commits.
>And I got some wide scsi 10k rpm disks lying around,
>so getting your rollback segments to 20GB should not
>be a problem.
>
>What baffles me (too), is how do you get so much rollback
>generated when inserting 10MB (150k rows of 70 byte)
>of data? Must be something else - I'd like to see the
>procedure.

Could it be lots of recursive SQL, from inserting into heavily indexed tables?
Thanks.

• Ed Stevens

Cohn's Law: The more time you spend in reporting on what you are doing, the less time you have to do anything. Stability is achieved when you spend all your time doing nothing but reporting on the nothing you are doing. Received on Thu Feb 03 2005 - 14:49:32 CST