Home » RDBMS Server » Performance Tuning » Delete statement taking long time in Test database (Oracle 9i)
Delete statement taking long time in Test database [message #405189] Tue, 26 May 2009 20:54 Go to next message
ashwin_1202
Messages: 3
Registered: May 2009
Junior Member
Hi,

I am running a procedure in which a delete statement is executing for enormous amount of time in Test. I don't understand the problem as the same procedure takes less than 30 min in production.

below is the delete statement in the procedure that is taking long time.


DELETE FROM audit_report ar
WHERE audit_rpt_sys_id LIKE '6%'
AND NOT EXISTS (SELECT 1
FROM v_ez_submission s, v_ez_financial_audit fa
WHERE s.submission_id = fa.submission_id
AND ar.acn = fa.fac_acn
AND s.peps_flag IS NULL
AND s.status = 1
AND s.type IN (1, 5, 6)
AND s.submission_date IS NOT NULL)
AND NOT EXISTS (SELECT 1
FROM v_ez_submission s, v_ez_financial_audit fa
WHERE s.submission_id = fa.submission_id
AND ar.acn = fa.acn
AND s.peps_flag IS NULL
AND s.status = 1
AND s.type IN (1, 5, 6)
AND s.submission_date IS NOT NULL)

I understand that there might be any memory settings which is different from production and is the reason why it is working in production.

I am attaching the explain plan screen shot. Please guide me if I need to do anything to fix the problem
Re: Delete statement taking long time in Test database [message #405190 is a reply to message #405189] Tue, 26 May 2009 21:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Your plan shows that all of the objects are remote (on another database). Is that the same when you run it in Production? Or are they local when run in Prod?

Post the Prod Explain Plan as well.

Ross Leishman
Re: Delete statement taking long time in Test database [message #405365 is a reply to message #405190] Wed, 27 May 2009 07:39 Go to previous messageGo to next message
ashwin_1202
Messages: 3
Registered: May 2009
Junior Member
It is the same procedure that runs in Production and the delete statement takes less than 30 seconds to finish. I am attaching you the explain plan in production for that delete statement.

Re: Delete statement taking long time in Test database [message #405415 is a reply to message #405365] Wed, 27 May 2009 10:26 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Try to analyze your table.

Babu
Re: Delete statement taking long time in Test database [message #405442 is a reply to message #405415] Wed, 27 May 2009 15:36 Go to previous messageGo to next message
ashwin_1202
Messages: 3
Registered: May 2009
Junior Member
I analyzed the whole schema. But it still the same time.
Re: Delete statement taking long time in Test database [message #405453 is a reply to message #405442] Wed, 27 May 2009 21:53 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Answer each of the following for BOTH test and production:
- How many rows in audit_report with audit_rpt_sys_id LIKE '6%' (ie. matching rows BEFORE the subqueries are applied)
- How many rows in v_ez_submission?
- How many rows in v_ez_financial_audit?
- How many rows will be DELETED?


Also, what are v_ez_submission and v_ez_financial_audit? Are they remote tables, local views over remote tables, or remote views?

Ross Leishman
Previous Topic: Database Performance (merged2) 10.2
Next Topic: Need help on Query Tuning
Goto Forum:
  


Current Time: Tue Dec 06 04:28:31 CST 2016

Total time taken to generate the page: 0.06580 seconds