Home » RDBMS Server » Performance Tuning » Query taking 20 minutes in Production (ORACLE 11g)
Query taking 20 minutes in Production [message #666617] Fri, 17 November 2017 04:58 Go to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
Hi Sir,

The below query attached is doing a Left Outer Join Multiple times on the same because of which it is taking 20 minutes to execute in Production Environment
WITH TMP_ACT AS												
(												
SELECT * FROM SPETLB21.TMP_ACTIVITY A )												
												
	SELECT             											
							 e.Company_Id AS Company_Id					
                            , Pol_Num												
                            , Proposal_Num												
                            , c.Holding_Id      AS Holding_Id												
                            , e.Policy_Id       AS Policy_Id												
							, e.orig_eff_dt		AS Original_Eff_Dt			
							, d.coverage_id     AS Coverage_Id					
                            , d.cov_num    AS Coverage_Num  --added as per Des to investigate												
							, d.rider_num  AS rider_num   					
							, f.life_participant_id AS life_participant_id					
							, f.party_seq_num as Life_num					
							, f.Party_ID as PARTY_ID					
							, f.Vitality_Status	as Vitality_Ind				
                            , q.LOCAL_LOOKUP_CD AS coverage_cd												
                            , j.LOCAL_LOOKUP_CD AS STATCODE												
                            , u.LOCAL_LOOKUP_CD AS PREM_STAT_CD												
                            , l.LOCAL_LOOKUP_CD AS CNTTYPE												
                            , m.LOCAL_LOOKUP_CD AS Payment_Mode												
                            , n.LOCAL_LOOKUP_CD AS Payment_Method												
                            , Total_Prem_Amt												
                            , Single_Prem_Amt												
                            , r.LOCAL_LOOKUP_CD        AS Source_of_Business												
                            , t.LOCAL_LOOKUP_LONG_DESC AS LONGDESC												
                            , o.LOCAL_LOOKUP_CD        AS Currency_Cd												
							, 'null'				       AS Report_Code 	
							, 'null'					   AS Report_Desc 
                            ,												
                              (												
                              CASE												
                                        WHEN k.LOCAL_LOOKUP_CD='2'												
                                        THEN 'SGP'												
                                        ELSE 'BRN'												
                              END												
                              )												
                              AS COUNTRY_Cd												
                            , i.Start_Dt as Ptr_Eff_Dt												
							, i.transaction_dt as Status_Dt /*Added Status Change Date from PTRNPF for tracking latest change in Penders*/					
							, i.Activity_Cd as Tran_Num					
							, i.batch_tran_cd as batch_tran_cd					
                            , h.Curr_From												
                            , AGNTNUM												
                            , AGENCYNUM												
							, CONTRACT_START_DATE					
                            , agnt_agncy_rel.District_cd												
                            , Area_Cd												
                            , clusterNm												
                            , SDOA												
                            , Subchannel1												
                            , Subchannel2												
                            , Subchannel3 												
												
                    FROM												
												
							SPETLB21.TMP_HOLDING	c				
												
                              INNER JOIN SPETLB21.TMP_POLICY_CURR e												
								  ON				
									(			
									  c.Holding_id    =e.Holding_id			
									  /* AND c.company_id=e.company_id */			
									)			
												
                              INNER JOIN SPETLB21.TMP_COVERAGE_CURR d												
								  ON				
									(			
									  d.Policy_Id     =e.Policy_Id			
									 /*  AND d.company_id=e.company_id */			
									)			
												
                              INNER JOIN SPETLB21.TMP_LIFEP_CURR f												
								  ON				
									(			
									 d.coverage_id=f.coverage_id			
									)			
												
                              INNER JOIN SPETLB21.TMP_POLSTATUS_CURR h												
								  ON				
									(			
									 e.policy_id=h.policy_id			
									)			
							  INNER JOIN 					
							  (SELECT A.* 					
							  FROM SPETLB21.TMP_ACT A 					
							  INNER JOIN 					
								(       				
								SELECT				
									LOCAL_LOOKUP_CD			
								FROM				
									SPETLB21.MV_SalesMI_Adam_Grp_Code_Rltn			
								WHERE				
									(MASTER_LKP_TYPE_DESC)='BUSDPF' and substr(LOCAL_LOOKUP_CD,1,4) <= to_char(sysdate,'yyyy')			
								) c				
							ON 					
							(					
								(				
								SUBSTR(c.LOCAL_LOOKUP_CD,5,2)       ='12'				
								AND TO_CHAR(a.TRANSACTION_DT,'YYYYMM') >= SUBSTR(c.LOCAL_LOOKUP_CD,1,4)||'11' /*use transaction date as discussed */				
								)				
								OR				
								(				
								SUBSTR(c.LOCAL_LOOKUP_CD,5,2)      <>'12'				
								AND TO_CHAR(a.TRANSACTION_DT,'YYYYMM') >=CAST(SUBSTR(c.LOCAL_LOOKUP_CD,1,4) AS SMALLINT)-1 ||'11'/*use transaction date as discussed */				
								) 				
							)    					
							where  					
								(batch_tran_cd  in ('T607','T71A','T46A','T642','T646','TN32'))				
												
							UNION ALL					
							SELECT * 					
							FROM SPETLB21.TMP_ACT A 					
							where   					
							(batch_tran_cd in ('T1JA','T6A0') or (batch_tran_cd='T600' and activity_cd=1)) /*Extract all Outstanding Penders and Submission for reinstatement*/   					
							  ) i ON					
									(			
										e.policy_id=i.policy_id		
									)			
												
                              LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn j												
								  ON				
									(			
									  j.CODE_RELATIONSHIP_ID    =h.policy_status			
									  AND (j.MASTER_LKP_TYPE_DESC)='CONTRACT RISK STATUS'			
									)			
                              LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn l												
									ON			
                                        (												
                                                  l.CODE_RELATIONSHIP_ID =e.product_cd												
                                                  /* AND (l.MASTER_LKP_TYPE_DESC)='CONTRACT PROCESSING RULES' */												
                                        )												
                              LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn k												
                              ON												
                                        (												
                                                  k.CODE_RELATIONSHIP_ID    =e.Company_Id												
                                                  AND (k.MASTER_LKP_TYPE_DESC)='COMPANY CODE'												
                                        )												
                              LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn m												
                              ON												
                                        (												
                                                  m.CODE_RELATIONSHIP_ID    =e.Payment_Mode												
                                                  --AND m.MASTER_LKP_TYPE_DESC='Payment Mode' --Vamsi: Condition changed in ETL Spec.												
												  AND (m.MASTER_LKP_TYPE_DESC)='FREQUENCIES'
                                        )												
                              LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn n												
                              ON												
                                        (												
                                                  n.CODE_RELATIONSHIP_ID    =e.Payment_Method												
                                                 -- AND n.MASTER_LKP_TYPE_DESC='Payment Method' --Vamsi: Condition changed in ETL Spec.												
												  AND (n.MASTER_LKP_TYPE_DESC)='BILLING CHANNELS/METHOD OF PAY'
                                        ) 												
												
                             LEFT OUTER  JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn o ON												
                                       (												
                                                  o.CODE_RELATIONSHIP_ID=c.Currency_Cd												
                                                  --AND o.MASTER_LKP_TYPE_DESC='Currency' --Vamsi: Condition changed in ETL Spec.												
												  AND (o.MASTER_LKP_TYPE_DESC)='CURRENCY CODE DETAILS'
                                       )												
                            												
							LEFT OUTER  JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn q ON					
							(					
									  q.CODE_RELATIONSHIP_ID=f.Coverage_Cd			
									  AND			
									  (q.MASTER_LKP_TYPE_DESC)='GENERAL COVERAGE/RIDER DETAILS'			
							) --Map Coverage_Cd column from Adam Life Participant once the Agreement changes from Adam Coverage to Adam Life Participant is  deployed					
							LEFT OUTER  JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn r ON					
							(					
									  --r.CODE_RELATIONSHIP_ID=f.Source_of_Business-- Vamsi : Condition Changed			
									  r.CODE_RELATIONSHIP_ID=e.Source_of_Business			
									  AND			
									  (r.MASTER_LKP_TYPE_DESC)='SOURCES OF BUSINESS'			
							) 			
							LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn t ON					
							(					
									  t.local_lookup_cd=r.local_lookup_cd			
									  AND			
									  (t.MASTER_LKP_TYPE_DESC)='DETAIL CHANNEL SALES MI'			
									  and k.LOCAL_LOOKUP_CD = t.local_lookup_company_id			
							)					
												
                              LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn u												
								  ON				
									(			
									  u.CODE_RELATIONSHIP_ID    =h.premium_status			
									  AND (u.MASTER_LKP_TYPE_DESC)='COVERAGE/RIDER PREMIUM STATUS'			
									)			
							LEFT OUTER JOIN spetlb21.MV_SalesMI_Sub_Channel agnt_agncy_rel ON agnt_agncy_rel.holding_id=c.holding_id					



The attached is the execution Plan of the below query

Appreciate your help on the above to tune the above query.

We can use PARALLEL(4) Hint but still it taking the same time after adding PARALLEL(4) Hint.

Appreciate your help to tune the query in less than 10 minutes.


Re: Query taking 20 minutes in Production [message #667214 is a reply to message #666617] Fri, 15 December 2017 02:02 Go to previous messageGo to next message
JohnMax
Messages: 3
Registered: December 2017
Junior Member
Could you paste all the scripts to create tables & indexes and describe how many rows each table has?

If yes, that will help me to think out a better solution for you.
Re: Query taking 20 minutes in Production [message #667215 is a reply to message #666617] Fri, 15 December 2017 02:26 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Suggestions:

First, use sensible table aliases. All those single letter aliases make it impossible to read the code. What are "a", "o", "c" and so on meant to mean? I don't know.

Second, format your code in a way that makes it readable. Why all those spaces? It is so spaced out you need a screen the width of the Atlantic to see it.

Third, generate the exec plan with EXPLAIN PLAN and DBMS_XPLAN.DISPLAY, the default FORMAT will do to start with. Use SQL*Plus, ad copy/paste it all here. That screen shot is useless.
Re: Query taking 20 minutes in Production [message #667216 is a reply to message #667215] Fri, 15 December 2017 03:26 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also the subject of aliases - make sure all columns have a table alias in front of them, not just some of them.

A with clause that is solely - select * from table - is a waste of time.
Previous Topic: AWR Interpretation and analysis
Next Topic: Consuming a more time (low performance)
Goto Forum:
  


Current Time: Fri Mar 29 05:29:13 CDT 2024