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
manoj12
Messages: 196
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.


Previous Topic: Undo tablespace(monitoring and avoiding ORA-01555
Next Topic: Consuming a more time (low performance)
Goto Forum:
  


Current Time: Thu Dec 14 13:17:31 CST 2017

Total time taken to generate the page: 0.02655 seconds