Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure / Deletion Conflict (Oracle 12C)
Stored Procedure / Deletion Conflict [message #670411] Tue, 03 July 2018 11:39 Go to next message
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 #670412 is a reply to message #670411] Tue, 03 July 2018 11:42 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
Missing indexes on foreign key columns, perhaps?
Re: Stored Procedure / Deletion Conflict [message #670413 is a reply to message #670411] Tue, 03 July 2018 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
dba_vijay wrote on Tue, 03 July 2018 09:39
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
post SQL & results that show why session "hangs".

What is session doing during this "hang" time?

Consider to enable SQL_TRACE to get some visibility into what is happening inside the DB engine during the "hang"
Re: Stored Procedure / Deletion Conflict [message #670414 is a reply to message #670413] Tue, 03 July 2018 11:49 Go to previous messageGo to next message
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 #670415 is a reply to message #670414] Tue, 03 July 2018 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
What is OS name & version?
What is Oracle version to 4 decimal places?

If you don't know where & why time is being spent, then you don't know where to apply the fix.

You have a mystery & give us no clues.
Re: Stored Procedure / Deletion Conflict [message #670416 is a reply to message #670415] Tue, 03 July 2018 12:13 Go to previous messageGo to next message
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 #670417 is a reply to message #670416] Tue, 03 July 2018 12:24 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
>I was able to reproduce it, If that stored procedure is called in parallel by more than once process it hangs for 45 + minutes.
how, exactly, is stored procedure called in parallel?

BTW why are numbers (35) enclosed in single quote marks?
what datatype is "dd.doc_status"?
Re: Stored Procedure / Deletion Conflict [message #670418 is a reply to message #670417] Tue, 03 July 2018 12:32 Go to previous messageGo to next message
dba_vijay
Messages: 11
Registered: February 2009
Junior Member
Parallel means. if 2 Java process call the procedure at the same time.
for e.g. we process files, so if two files are kicked at the same time.

doc_status is varchar2, and we have alphanumeric status for documents.
Re: Stored Procedure / Deletion Conflict [message #670423 is a reply to message #670418] Wed, 04 July 2018 03:07 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 #670424 is a reply to message #670423] Wed, 04 July 2018 03:08 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Any why are you using dynamic SQL for this?
Re: Stored Procedure / Deletion Conflict [message #670438 is a reply to message #670424] Wed, 04 July 2018 11:50 Go to previous messageGo to next message
dba_vijay
Messages: 11
Registered: February 2009
Junior Member
Can I do a delete statement in a Stored Procedure?
Re: Stored Procedure / Deletion Conflict [message #670439 is a reply to message #670423] Wed, 04 July 2018 11:53 Go to previous messageGo to next message
dba_vijay
Messages: 11
Registered: February 2009
Junior Member
All 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
Re: Stored Procedure / Deletion Conflict [message #670460 is a reply to message #670438] Thu, 05 July 2018 03:39 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
dba_vijay wrote on Wed, 04 July 2018 17:50
Can I do a delete statement in a Stored Procedure?
Well you currently are, and the answer should obviously be yes anyway, so I'm not sure why you are asking?
Re: Stored Procedure / Deletion Conflict [message #670461 is a reply to message #670439] Thu, 05 July 2018 03:40 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
dba_vijay wrote on Wed, 04 July 2018 17:53
All 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?
Previous Topic: Help required on ranking logic
Next Topic: problem with concatenation and group by functions
Goto Forum:
  


Current Time: Sun Sep 20 11:22:52 CDT 2020