OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Archive logs (Oracle 10.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Archive logs [message #282290]

Wed, 21 November 2007 06:52

wojtyla
Messages: 17
Registered: March 2005
Location: USA

Junior Member

HI,

i m facing a peculiar problem

when i load data into a table (using batch inserts & updates)
the tables space increases by 50 MB but the archive size increases bt 5 GB

The operation done is as follows

there is a table with two columns

Initialy the table is empty
then i load (batch insert 2 million records) in the first column
the archive logs increase by 2 Gb

now i batch update the table to load data into the second column
the archive log size goes to 9.5 GB

Can anyone suggest a way why this is happening & how i can reduce the archive log size .

it is noted that the oracle server takes up 100% cpu during these operation

Report message to a moderator

Re: Archive logs [message #282294 is a reply to message #282290]

Wed, 21 November 2007 06:55

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

First empty with truncate and not delete.
Then directly load the final data with just an insert and don't use update.

Regards
Michel

Report message to a moderator

Re: Archive logs [message #282303 is a reply to message #282294]

Wed, 21 November 2007 07:10

wojtyla
Messages: 17
Registered: March 2005
Location: USA

Junior Member

i have to insert first
& then update

the insert & update can have any time difference it is not one after the other immedialtely

the actual scenario is is even bigger
there are actually 26 columns

so evertime truncate & inseart for millions of records is very time consuming

Report message to a moderator

Re: Archive logs [message #282306 is a reply to message #282303]

Wed, 21 November 2007 07:38

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You ask how to increase performances and decrease log generation.
I said what you have to do.
If you say, I don't want to change what I do, I answer then you stay with your current log generation.

Regards
Michel

Report message to a moderator

Re: Archive logs [message #282310 is a reply to message #282306]

Wed, 21 November 2007 08:00

wojtyla
Messages: 17
Registered: March 2005
Location: USA

Junior Member

My question is why the logs are generated at such a high rate?

Doing this & that will improve the performance & reduce log genration is ok

What does the oracle process do for updates that generates such huge logs

I am sorry if i hurt your feelings but i am looking at the problem why it is happening so & ways to reduce logs for such updates

Report message to a moderator