OraFAQ Forum: Server Administration » Performance degrade while deleting 1 million row

Home » RDBMS Server » Server Administration » Performance degrade while deleting 1 million row (Oracle 9.2.0.8)

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

Performance degrade while deleting 1 million row [message #439026]

Wed, 13 January 2010 12:08

manish_edu
Messages: 2
Registered: January 2010

Junior Member

Hi ,
I have to delete 1 million row from my table as the data was incorrectly inserted..

It is production system and deleting 1 million row will make the database die and user will have to stuck their operation for long..

Please guide me the best suitable way to delete these rows..

Report message to a moderator

Re: Performance degrade while deleting 1 million row [message #439028 is a reply to message #439026]

Wed, 13 January 2010 12:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have to delete 1 million row from my table as the data was incorrectly inserted..

about how many rows exist in table now, before delete?

>It is production system and deleting 1 million row will make the database die and user will have to stuck their operation for long..

Not necessarily

What tables/operations are dependent upon this table.

do DELETE at 2AM

Report message to a moderator

Re: Performance degrade while deleting 1 million row [message #439040 is a reply to message #439028]

Wed, 13 January 2010 12:48

manish_edu
Messages: 2
Registered: January 2010

Junior Member

It was approx 10 million rows.. we used to update the table with billing information of the user at night .. In the day time users are connected with this table to generate report..

Report message to a moderator

Re: Performance degrade while deleting 1 million row [message #439041 is a reply to message #439040]

Wed, 13 January 2010 12:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

With Oracle WRITERS do not block READERS & READERS do not block WRITERS.

Without knowing more details, a single DELETE statement should have little to no impact on generating reports.

Report message to a moderator

Re: Performance degrade while deleting 1 million row [message #439048 is a reply to message #439041]

Wed, 13 January 2010 13:44

palazzi
Messages: 11
Registered: June 2009
Location: Toluca

Junior Member

Hi

Probably your database "died" because of an undo problem.

Check if you have enough undo space or autoextend activated.

Saludos.

Report message to a moderator

Re: Performance degrade while deleting 1 million row [message #439049 is a reply to message #439048]

Wed, 13 January 2010 13:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Yes, UNDO of DELETE are the largest, because the UNDO is a complete row INSERT.

When compared to UNDO of INSERT is a small DELETE ROWID

Report message to a moderator

Re: Performance degrade while deleting 1 million row [message #439064 is a reply to message #439026]

Wed, 13 January 2010 22:48

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

you are SURE you want to DELETE

then

Step 1 :- alter table <Table Name> nologging;
Step 2 :- Set AutoCommit <Say 5000>
Step 23:- delete from <Table name>

Step 1 :- No-logging can be used to minimize the amount of redo generated.

Step 2 :- Keep on Commiting data so Roll back segment is not filled and possibly you avoid 1555 error. so you are not back to square 1 Smile

Hope it helps.

Report message to a moderator

Re: Performance degrade while deleting 1 million row [message #439075 is a reply to message #439064]

Thu, 14 January 2010 01:38

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

Senior Member
Account Moderator

1/ NOLOGGING works only on direct path operation which is not the case of a DELETE. (In addition, it implies a backup after the operation.)

2/ Autocommit SQL*Plus feature auto commits after x DML statements not after x modified/deleted rows, so it does nothing in a single DELETE

3/ Committing inside a loop does not avoid ORA-1555 it INCREASES the likelyhood to get it.

Time to review your basics. Smile

Regards
Michel

[Updated on: Thu, 14 January 2010 01:39]

Report message to a moderator

Re: Performance degrade while deleting 1 million row [message #439083 is a reply to message #439028]

Thu, 14 January 2010 02:29

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

BlackSwan wrote on Wed, 13 January 2010 19:12

>
about how many rows exist in table now, before delete?

And the answer is ?

How many indexes does the table have ?

Report message to a moderator

Re: Performance degrade while deleting 1 million row [message #439089 is a reply to message #439026]

Thu, 14 January 2010 03:22

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

Quote:

Time to review your basics.

Sure but some other time Michel.

manish_edu

Check following Link.

Performing Massive DELETEs as Bulk INSERT

Report message to a moderator

Re: Performance degrade while deleting 1 million row [message #439092 is a reply to message #439089]

Thu, 14 January 2010 03:49

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

Senior Member
Account Moderator

To know if alternative way might be the more efficient way, we will have to wait for OP to answer BlakcSwan's question. And the one you pointed to might not be the most efficient one.

In addition, you can't do it in while others are working, and for the moment OP wants to do it without stopping the application (as his sentence "user will have to stuck their operation for long" seems to imply).

Regards
Michel

Report message to a moderator

Previous Topic:	About REDO LOG FILE
Next Topic:	Oracle 11g Installation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 11:47:41 CST 2024