OraFAQ Forum: Performance Tuning » Deletion script doesnt completes

Home » RDBMS Server » Performance Tuning » Deletion script doesnt completes

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

Deletion script doesnt completes [message #291797]

Sun, 06 January 2008 23:15

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

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: Deletion script doesnt completes [message #291825 is a reply to message #291812]

Mon, 07 January 2008 00:17

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

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: Deletion script doesnt completes [message #291868 is a reply to message #291797]

Mon, 07 January 2008 01:58

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?

Report message to a moderator

Re: Deletion script doesnt completes [message #291888 is a reply to message #291868]

Mon, 07 January 2008 02:30

Michel Cadot
Messages: 68665
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

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.

Report message to a moderator

Re: Deletion script doesnt completes [message #291898 is a reply to message #291895]

Mon, 07 January 2008 02:51

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: Deletion script doesnt completes [message #291908 is a reply to message #291898]

Mon, 07 January 2008 03:38

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.

Report message to a moderator

Re: Deletion script doesnt completes [message #291909 is a reply to message #291908]

Mon, 07 January 2008 03:44

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: Deletion script doesnt completes [message #291910 is a reply to message #291909]

Mon, 07 January 2008 03:46

ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai

Senior Member

Thanks a lot.

Report message to a moderator

Previous Topic:	SGA - Buffer cache size
Next Topic:	Where to Start the Performance Tuning?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 06:08:18 CDT 2024