| Stored Procedure / Deletion Conflict [message #670411] |
Tue, 03 July 2018 11:39  |
dba_vijay
Messages: 11
Registered: February 2009
|
Junior Member |
|
|
I need some help with a problem, which may be simple for many but have me confused a lot.
I have a stored procedure which is called by a Java Program. Which has a portion that deletes 6 tables (1-6), one by one, on the basis of a same select statement from table A,B.
Sometimes we have noticed is that this stored procedures hangs at the delete part.
I was able to reproduce it, If that stored procedure is called in parallel by more than once process it hangs for 45 + minutes.
All the deletes are independent for both calls.
As deletes does not put any lock on the tables, we should be able to delete different rows without any lock and resource issues.
Do you have any suggestions or pointer on getting around with this.
thanks
|
|
|
|
|
|
|
|
| Re: Stored Procedure / Deletion Conflict [message #670414 is a reply to message #670413] |
Tue, 03 July 2018 11:49  |
dba_vijay
Messages: 11
Registered: February 2009
|
Junior Member |
|
|
It just stucks at the deletion, as i dont have much DBA access to the database I dont have much insight into whats happening.
below is the delete part. Which slows down sometimes. I am working on enabling SQL trace in the meantime.
if p_document_status = '35' then
l_sql_text := 'DELETE FROM GROUP_RULE_MICR WHERE copy_id in (
SELECT copy_id from document_copy dc inner join document_decision dd on dc.doc_id = dd.doc_id
INNER JOIN ' || l_temp_table || ' temp on dd.doc_id = temp.doc_id WHERE dd.doc_status = ''35'')';
execute immediate l_sql_text;
l_sql_text := 'DELETE FROM GROUP_RULE_OUTPUT WHERE copy_id in (
SELECT copy_id from document_copy dc inner join document_decision dd on dc.doc_id = dd.doc_id
INNER JOIN ' || l_temp_table || ' temp on dd.doc_id = temp.doc_id WHERE dd.doc_status = ''35'')';
execute immediate l_sql_text;
l_sql_text := 'DELETE FROM GROUP_RULE_OVERLAY WHERE copy_id in (
SELECT copy_id from document_copy dc inner join document_decision dd on dc.doc_id = dd.doc_id
INNER JOIN ' || l_temp_table || ' temp on dd.doc_id = temp.doc_id WHERE dd.doc_status = ''35'')';
execute immediate l_sql_text;
l_sql_text := 'DELETE FROM Document_Extra_Insert WHERE copy_id in (
SELECT copy_id from document_copy dc inner join document_decision dd on dc.doc_id = dd.doc_id
INNER JOIN ' || l_temp_table || ' temp on dd.doc_id = temp.doc_id WHERE dd.doc_status = ''35'')';
execute immediate l_sql_text;
l_sql_text := 'DELETE FROM Document_Form WHERE copy_id in (
SELECT copy_id from document_copy dc inner join document_decision dd on dc.doc_id = dd.doc_id
INNER JOIN ' || l_temp_table || ' temp on dd.doc_id = temp.doc_id WHERE dd.doc_status = ''35'')';
execute immediate l_sql_text;
l_sql_text := 'DELETE FROM Document_Address Where Address_Id in (
SELECT dcam.address_id from doc_copy_address_map dcam inner join document_copy dc on dcam.copy_id = dc.copy_id
inner join document_decision dd on dc.doc_id = dd.doc_id
INNER JOIN ' || l_temp_table || ' temp on dd.doc_id = temp.doc_id WHERE dd.doc_status = ''35'')';
execute immediate l_sql_text;
l_sql_text := 'DELETE FROM Doc_Copy_Address_Map where copy_id in (
SELECT copy_id from document_copy dc inner join document_decision dd on dc.doc_id = dd.doc_id
INNER JOIN ' || l_temp_table || ' temp on dd.doc_id = temp.doc_id WHERE dd.doc_status = ''35'')';
execute immediate l_sql_text;
l_sql_text := 'DELETE FROM Document_Copy WHERE copy_id in (
SELECT copy_id from document_copy dc inner join document_decision dd on dc.doc_id = dd.doc_id
INNER JOIN ' || l_temp_table || ' temp on dd.doc_id = temp.doc_id WHERE dd.doc_status = ''35'')';
execute immediate l_sql_text;
end if;
|
|
|
|
|
|
| Re: Stored Procedure / Deletion Conflict [message #670416 is a reply to message #670415] |
Tue, 03 July 2018 12:13 |
dba_vijay
Messages: 11
Registered: February 2009
|
Junior Member |
|
|
I thought version etc wont matter in this case,
I am looking for pointers where to start, rather then running stored procedure again and again.
I have seen some blocks on the tables being deleted.
Linux x86 64 Bit
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
|
|
|
|
|
|
|
|
| Re: Stored Procedure / Deletion Conflict [message #670423 is a reply to message #670418] |
Wed, 04 July 2018 03:07 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Instrument the code so it logs the start and end of each delete statement, then you can see which deletes are taking the most time.
Trace the session - that'll show you exactly where the time is being spent.
Ask yourself if you can get in the situation where two java processes end up trying to delete the same row in any of the tables - if that happens then the 2nd session will hang on the relevant delete until the first session commits.
Check for unindexed foregin keys on any child tables to the ones you are deleting, if you find any add an index on all the columns in the foreign key.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Stored Procedure / Deletion Conflict [message #670461 is a reply to message #670439] |
Thu, 05 July 2018 03:40 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
dba_vijay wrote on Wed, 04 July 2018 17:53All deletes are independent rows.They should not conflict with each other.
I rewrote the delete statements and also worked on Indexes, made sure there are
indexes being used.
till now it is working,
thanks a lot
Does that mean it's fixed now?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
