Home » RDBMS Server » Performance Tuning » Deletion script doesnt completes
Deletion script doesnt completes [message #291797] Sun, 06 January 2008 23:15 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Experts,

I am deleting 100.000 records from a table.

I executed one script that doesn't completed from 1 day.
I hope that should execute in 1 hour.

I checked UNDOTBS1 tablespace and find that it is 99% used.
My guess is because of this my deletion script is not working.

Please tell me how i can reduce size of the UNDOTBS1?

Or please tell me any other option so that my deletion script will work.

Thanks in advance.

[Updated on: Sun, 06 January 2008 23:15]

Report message to a moderator

Re: Deletion script doesnt completes [message #291812 is a reply to message #291797] Sun, 06 January 2008 23:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I hope that should execute in 1 hour.

Based on what?

Quote:

My guess is because of this my deletion script is not working.

No comment to give as you didn't post it.

Quote:

Or please tell me any other option so that my deletion script will work.

Post it.
And don't forget to use code tags.

Regards
Michel
Re: Deletion script doesnt completes [message #291825 is a reply to message #291812] Mon, 07 January 2008 00:17 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
My script is

DECLARE
i NUMBER:=0;
BEGIN
FOR cid IN (
SELECT a.id FROM test a WHERE a.database_id =10136 
)
LOOP
i:=i+1;
DELETE FROM test WHERE id = cid.id;
IF (MOD(i, 1000) = 0) THEN
COMMIT;
END IF;
END LOOP;
commit;
END;


select cursor query results 100.000 records.
Previously this script takes 1 hour to complete.


And when i seen the UNDO tablespace it is 99% complete.
I cannot increase size of the tablespace because of space problem.

Please tell me how can i reduce the size of it?

Thanks in advance.

[Updated on: Mon, 07 January 2008 00:18]

Report message to a moderator

Re: Deletion script doesnt completes [message #291851 is a reply to message #291825] Mon, 07 January 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
UNDO tablespace fill to 99% is normal, it means Oracle uses efficiently the space you allocated.

Committing every 1000 rows leads to wait and wait and wait not only you but everybody.

Just use delete test where a.database_id=10136;

Regards
Michel



Re: Deletion script doesnt completes [message #291868 is a reply to message #291797] Mon, 07 January 2008 01:58 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:

Committing every 1000 rows leads to wait and wait and wait not only you but everybody

Commit releases locks, it prevent waits.
Isn't it so?
Re: Deletion script doesnt completes [message #291888 is a reply to message #291868] Mon, 07 January 2008 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Commit locks internal structures that is used by everyone and so generates waits.

Regards
Michel

[Updated on: Mon, 07 January 2008 02:30]

Report message to a moderator

Re: Deletion script doesnt completes [message #291895 is a reply to message #291888] Mon, 07 January 2008 02:44 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Currently i added 500MB in the undo tablespace.
Now 90% used space in undo tablespace.

And i started to delete records manually only 1000 records.
Then say commit;

So i think that there should not increase in the UNDO tablespace.

But the undo tablespace is incresing.

So i want to know that suppose again undo tablespace reaches to 99% will it cause problem while deleting?

Thanks in advance.
Re: Deletion script doesnt completes [message #291898 is a reply to message #291895] Mon, 07 January 2008 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Concepts
Part II Oracle Database Architecture
Chapter 2 Data Blocks, Extents, and Segments
Section Overview of Segments
Subsection Introduction to Automatic Undo Management

First read this. You missed the basic knowledge.
Then read:
Database Administrator's Guide
Chapter 10 Managing the Undo Tablespace

Regards
Michel
Re: Deletion script doesnt completes [message #291908 is a reply to message #291898] Mon, 07 January 2008 03:38 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks Michel,

I am agree with you that i am weak in DBA because i am new to DBA. I will learn the topics which you have mentioned.

But for the time being please just tell will my delete statemets run even undo tablespace will 99%.
Thanks.
Re: Deletion script doesnt completes [message #291909 is a reply to message #291908] Mon, 07 January 2008 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 07 January 2008 08:25

UNDO tablespace fill to 99% is normal, it means Oracle uses efficiently the space you allocated.

I will not repeat the documentation.
So assume it is normal and don't care about it.

Regards
Michel

Re: Deletion script doesnt completes [message #291910 is a reply to message #291909] Mon, 07 January 2008 03:46 Go to previous message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks a lot.
Previous Topic: SGA - Buffer cache size
Next Topic: Where to Start the Performance Tuning?
Goto Forum:
  


Current Time: Wed Dec 07 20:10:51 CST 2016

Total time taken to generate the page: 0.12973 seconds