Home » SQL & PL/SQL » SQL & PL/SQL » Performance issue post parallelism change
Performance issue post parallelism change [message #249261] Tue, 03 July 2007 17:11 Go to next message
concorde800
Messages: 52
Registered: May 2007
Member
Hello,

I have the below query that runs normally as a part of batch job for 15 minutes. The tables SLS_ORDR_FACT_ID and SO_FACT_INC_KEYS_BASIC are large (15 million rows each) and have parallelism degrees of 2 (sls_ordr_fact_id) and 8 (so_fact_inc_keys_basic).


The problem occured when parallelism on table sls_ordr_fact_id was removed and made noparallel by dba team. Now, the batch job runs for 130 minutes. Is

Assuming that the parallelism on table cannot be set back. How do we get the query to run optimally again (run again for 15 minutes).

Am I correct in assuming that having one table parallelised and other running serially is causing this to run longer?

The code executed is below

DELETE 
  FROM ops_stg.sls_ordr_fact PARTITION (EMEA) SO
WHERE SO.KEY_SLS_ORDR_FACT_ID
  IN (SELECT  
       INC.key_sls_ordr_fact_id FROM DM_COMMON.SO_FACT_INC_KEYS_BASIC INC
       WHERE INC.key_sls_ordr_fact_ID = SO.KEY_SLS_ORDR_FACT_ID AND
              INC.rcrd_delete_flag = 'Y') and mod (trunc(key_sls_ordr_fact_id/10000000000),1) = 0
Re: Performance issue post parallelism change [message #249262 is a reply to message #249261] Tue, 03 July 2007 17:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Might the following help?
ALTER SESSION ENABLE PARALLEL DML;
Re: Performance issue post parallelism change [message #249263 is a reply to message #249262] Tue, 03 July 2007 17:21 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
REPLACE IN WITH EXIST..
Re: Performance issue post parallelism change [message #249264 is a reply to message #249262] Tue, 03 July 2007 17:27 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
I monitored this job using SQL Navigator. When this job runs for 130 minutes, it gets 8 of 8 threads on the INNER Select query. Once the inner query is completed the Delete (outer query) happens in no time and the job finishes immediately.

This would lead me to assume that the issue is not about this query not getting enough threads, but something else?

It appears to me that the Select operation would need further optimization.
Re: Performance issue post parallelism change [message #249716 is a reply to message #249262] Thu, 05 July 2007 11:32 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Thanks for tip !!! The ALTER SESSION ENABLE PARALLEL would override the serialized DML execution of the DELETE statement.

My only issue would be that DELETE DML gets loaded thru a PROCEDURE in a PACKAGE BODY.

Would this procedure would have to be executed in parallel (by including parallel_enable in create procedure syntax in package spec and package body) as well?

OR, a simple ALTER SESSION ENABLE PARALLEL DML in the package body should do it?

Thank you.
Re: Performance issue post parallelism change [message #249717 is a reply to message #249261] Thu, 05 July 2007 11:38 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>OR, a simple ALTER SESSION ENABLE PARALLEL DML in the package body should do it?
Why would you depend upon getting an answer from a total stranger rather simply testing for YOURSELF?
Re: Performance issue post parallelism change [message #249718 is a reply to message #249716] Thu, 05 July 2007 11:39 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The later.
Remember you can enable parallel dml only at the beginning of a transaction otherwise you get the error:
ORA-12841: Cannot alter the session parallel DML state within a transaction

Remember after a parallel DML you can't do anything of your table until you commit.
If you try you get the error:
ORA-12838: cannot read/modify an object after modifying it in parallel

Regards
Michel
Previous Topic: date field error
Next Topic: Large query with multiple conditional joins
Goto Forum:
  


Current Time: Fri Dec 09 15:50:57 CST 2016

Total time taken to generate the page: 0.22552 seconds