Home » RDBMS Server » Server Administration » Archive logs (Oracle 10.2)
Archive logs [message #282290] Wed, 21 November 2007 06:52 Go to next message
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

Re: Archive logs [message #282294 is a reply to message #282290] Wed, 21 November 2007 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Archive logs [message #282303 is a reply to message #282294] Wed, 21 November 2007 07:10 Go to previous messageGo to next message
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
Re: Archive logs [message #282306 is a reply to message #282303] Wed, 21 November 2007 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Archive logs [message #282310 is a reply to message #282306] Wed, 21 November 2007 08:00 Go to previous messageGo to next message
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

Re: Archive logs [message #282319 is a reply to message #282310] Wed, 21 November 2007 08:30 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

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

Because you are doing work.

Quote:

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

Question
Explain your sentence.

Quote:

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

It saves your operations and associated data in order to undo or redo them.

Quote:

ways to reduce logs for such updates

No way if you don't change what you do.

Regards
Michel
Previous Topic: Database in not respondin
Next Topic: Making Oracle password automatically windows password
Goto Forum:
  


Current Time: Sat Dec 03 22:07:05 CST 2016

Total time taken to generate the page: 0.09939 seconds