OraFAQ Forum: Performance Tuning » delete statement taking long time

Home » RDBMS Server » Performance Tuning » delete statement taking long time

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

delete statement taking long time [message #272812]

Sun, 07 October 2007 15:11

el33t
Messages: 29
Registered: October 2007

Junior Member

I'm deleting some 5 lakh records from a table and it has been running for more than 4 hours now.

OS: RHEL 4 kernel 2.6.9-55.0.6 ELsmp

Database: Oracle 10G(10.2.0.1.0)

Server config: AMD Opteron Dual CPU @ 2.0 Ghz, 8GB RAM, SCSI in RAID.

I can't seem to find the progress of the query from "V$session_longops" either.

Checking output of command:

# sar -P ALL 2 100

03:12:44 PM       CPU     %user     %nice   %system   %iowait     %idle
03:12:46 PM       all     26.43      0.00      3.87      0.12     69.58
03:12:46 PM         0      2.50      0.00      4.00      0.00     93.50
03:12:46 PM         1      2.50      0.00      6.00      0.00     91.50
03:12:46 PM         2      2.50      0.00      4.50      0.00     93.00
03:12:46 PM         3     98.51      0.00      1.49      0.00      0.00

03:12:46 PM       CPU     %user     %nice   %system   %iowait     %idle
03:12:48 PM       all     25.88      0.00      0.25      0.00     73.88
03:12:48 PM         0      3.00      0.00      0.50      0.00     96.50
03:12:48 PM         1      0.50      0.00      0.00      0.00     99.50
03:12:48 PM         2      0.50      0.00      0.00      0.00     99.50
03:12:48 PM         3     99.50      0.00      0.50      0.00      0.00

03:12:48 PM       CPU     %user     %nice   %system   %iowait     %idle
03:12:50 PM       all     25.47      0.00      0.75      0.00     73.78
03:12:50 PM         0      2.49      0.00      1.49      0.50     95.52
03:12:50 PM         1      0.50      0.00      0.00      0.00     99.50
03:12:50 PM         2      0.00      0.00      0.00      0.00    100.00
03:12:50 PM         3     98.51      0.00      1.49      0.00      0.00

Looking at iowait i don't think anythings happening, right?

Please help with some pointers. I have restarted the process thrice now.

Thank You.

[Updated on: Sun, 07 October 2007 15:15]

Report message to a moderator

Re: delete statement taking long time [message #272813 is a reply to message #272812]

Sun, 07 October 2007 15:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/84315/74940/

There could be many different reasons why you think a problem exists.

Not sufficient details have been provided for anyone to make any meaningful conclusion and only wild guesses can be offered.

Has THIS delete worked much faster in the past? If so, what changed?

The bottom line is that YOU need to determine what actually is or is not happening.

Report message to a moderator

Re: delete statement taking long time [message #272815 is a reply to message #272813]

Sun, 07 October 2007 15:28

el33t
Messages: 29
Registered: October 2007

Junior Member

hi,

thanks for reply...i'm reading the link you have provided..

this is the first time i'm deleting so many records on this database. I tried passing rownum <=10000 and it completes in 15 minutes.

as a quick thing may you please tell me how do i monitor the progress of DML statement?

thanks!

Report message to a moderator

Re: delete statement taking long time [message #272816 is a reply to message #272812]

Sun, 07 October 2007 15:32

el33t
Messages: 29
Registered: October 2007

Junior Member

no changes to the database but some recent changes i made to Linux are

elevator=deadline

vm.pagecache = 1 15 30

Report message to a moderator

Re: delete statement taking long time [message #272817 is a reply to message #272812]

Sun, 07 October 2007 15:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Determine the SID if the session doing the delete.

SELECT * FROM V$SESS_IO WHERE SID = <delete_sid>;
do this SELECT periodically.
the I/O counts should be increasing if/when rows are being deleted.

Report message to a moderator

Re: delete statement taking long time [message #272818 is a reply to message #272817]

Sun, 07 October 2007 15:43

el33t
Messages: 29
Registered: October 2007

Junior Member

Hello,

Thanks for the input.

Output of the query:

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES
---------- ---------- --------------- -------------- -------------
CONSISTENT_CHANGES
------------------
       421  563474190       261165091          21822       1979465
                 0


SQL> SELECT * FROM V$SESS_IO WHERE SID = 421;

       SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES
---------- ---------- --------------- -------------- -------------
CONSISTENT_CHANGES
------------------
       421  564172508       261490308          21857       1981928
                 0


SQL> SELECT * FROM V$SESS_IO WHERE SID = 421;

       SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES
---------- ---------- --------------- -------------- -------------
CONSISTENT_CHANGES
------------------
       421  565453641       262087215          21912       1986440
                 0

Report message to a moderator

Re: delete statement taking long time [message #272821 is a reply to message #272818]

Sun, 07 October 2007 22:13

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

And how long did you wait between executions? 1 second? 1 minute? 1 hour?

Report message to a moderator

Re: delete statement taking long time [message #272822 is a reply to message #272812]

Sun, 07 October 2007 22:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> SET TIME ON

Report message to a moderator

Re: delete statement taking long time [message #272963 is a reply to message #272812]

Mon, 08 October 2007 06:42

el33t
Messages: 29
Registered: October 2007

Junior Member

hi anacedent

thanks a million for all your help. The task got finally over and lasted for 508 mins to delete 4.64 lacs records Embarassed

now i'm setting up another database server for a developer on RHEL 4 so any performance and setup tips you would like to share Smile

yes i will read the sticky threads also

The machine config is Intel core 2 duo e6750 + Intel DG33FBC mobo + 2 GB RAM + 160 GB SATA

thanks again!

Report message to a moderator

Previous Topic:	What are the keep tables and indexes in Oracle HRMS, Oracle Financials and Oracle Payroll
Next Topic:	Takes long time on sorting.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 04 22:17:15 CST 2024