Home » RDBMS Server » Performance Tuning » delete statement taking long time
delete statement taking long time [message #272812] Sun, 07 October 2007 15:11 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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.
Re: delete statement taking long time [message #272815 is a reply to message #272813] Sun, 07 October 2007 15:28 Go to previous messageGo to next message
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!
Re: delete statement taking long time [message #272816 is a reply to message #272812] Sun, 07 October 2007 15:32 Go to previous messageGo to next message
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

Re: delete statement taking long time [message #272817 is a reply to message #272812] Sun, 07 October 2007 15:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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.
Re: delete statement taking long time [message #272818 is a reply to message #272817] Sun, 07 October 2007 15:43 Go to previous messageGo to next message
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
Re: delete statement taking long time [message #272821 is a reply to message #272818] Sun, 07 October 2007 22:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
And how long did you wait between executions? 1 second? 1 minute? 1 hour?
Re: delete statement taking long time [message #272822 is a reply to message #272812] Sun, 07 October 2007 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SQL> SET TIME ON
Re: delete statement taking long time [message #272963 is a reply to message #272812] Mon, 08 October 2007 06:42 Go to previous message
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!
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:
  


Current Time: Sat Dec 10 14:45:25 CST 2016

Total time taken to generate the page: 0.07200 seconds