| Delete statement taking long time in Test database [message #405189] |
Tue, 26 May 2009 20:54  |
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 #405453 is a reply to message #405442] |
Wed, 27 May 2009 21:53  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
