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.
--
Regards,
Frank van Bortel
Received on Thu Feb 03 2005 - 13:01:19 CST
