Forum Stats

  • 3,827,592 Users
  • 2,260,797 Discussions
  • 7,897,311 Comments

Discussions

Help with performance tuning of query with multiple left joins

user8170666
user8170666 Member Posts: 6 Red Ribbon
edited Feb 15, 2022 8:52AM in SQL & PL/SQL

Below query cost is very high. Unable to identify which part requires tuning based on attached query execution plan.

Even if 'Order by' it is not there, cost remains same.

Appreciate any help from members.

Query plan is attached for reference.

select count(*) from policy_register; -- 8 million

select count(*) from ENTITY_ADDRESS_INFO; --13 million

select count(*) from FLAGS_REGISTER; --10 million

select count(*) from ENTITY_REGISTER; --11 million

select count(*) from SEGMENTATION_MASTER; --11 records smallest table



Query:

SELECT CPLX_742.LEGAL_STATUS,

CPLX_742.POLICY_TERM_ID

FROM (POLICY_REGISTER CPLX_742

LEFT JOIN FLAGS_REGISTER CPLX_747

ON ( ( CPLX_742.POLICY_TERM_ID=CPLX_747.POLICY_TERM_ID ) )

LEFT JOIN (POLICY_ENTITY_REGISTER CPLX_748

LEFT JOIN (ENTITY_REGISTER CPLX_749

LEFT JOIN ENTITY_ADDRESS_INFO CPLX_750

ON ( ( CPLX_749.SYSTEM_ENTITY_CODE=CPLX_750.SYSTEM_ENTITY_CODE

AND CPLX_750.PRIMARY_ADDRESS_YN  ='Y' ) ) )

ON ( ( CPLX_748.SYSTEM_ENTITY_CODE=CPLX_749.SYSTEM_ENTITY_CODE ) ) )

ON ( ( CPLX_742.POLICY_TERM_ID  =CPLX_748.POLICY_TERM_ID ) )

LEFT JOIN (ENTITY_REGISTER CPLX_743

LEFT JOIN ENTITY_ADDRESS_INFO CPLX_752

ON ( ( CPLX_743.SYSTEM_ENTITY_CODE=CPLX_752.SYSTEM_ENTITY_CODE

AND CPLX_752.PRIMARY_ADDRESS_YN  ='Y' ) )

LEFT JOIN (POLICY_REGISTER CPLX_753

LEFT JOIN FLAGS_REGISTER CPLX_754

ON ( ( CPLX_753.POLICY_TERM_ID     =CPLX_754.POLICY_TERM_ID ) ) )

ON ( ( CPLX_743.GOVERNING_POLICY_TERM_ID=CPLX_753.POLICY_TERM_ID ) )

LEFT JOIN FLAGS_REGISTER CPLX_755

ON ( ( CPLX_743.SYSTEM_ENTITY_CODE =CPLX_755.SYSTEM_ENTITY_CODE ) ) )

ON ( ( CPLX_742.ACCOUNT_SYSTEM_CODE=CPLX_743.SYSTEM_ENTITY_CODE )

AND ( CPLX_743.ENTITY_TYPE     ='ACCOUNT' ) )

LEFT JOIN (ENTITY_REGISTER CPLX_745

LEFT JOIN ENTITY_ADDRESS_INFO CPLX_757

ON ( ( CPLX_745.SYSTEM_ENTITY_CODE=CPLX_757.SYSTEM_ENTITY_CODE

AND CPLX_757.PRIMARY_ADDRESS_YN  ='Y' ) ) )

ON ( ( CPLX_742.BROKER_SYSTEM_CODE=CPLX_745.SYSTEM_ENTITY_CODE )

AND ( CPLX_745.ENTITY_TYPE    ='BROKER' ) )

LEFT JOIN (ENTITY_REGISTER CPLX_744

LEFT JOIN ENTITY_ADDRESS_INFO CPLX_756

ON ( ( CPLX_744.SYSTEM_ENTITY_CODE =CPLX_756.SYSTEM_ENTITY_CODE

AND CPLX_756.PRIMARY_ADDRESS_YN  ='Y' ) ) )

ON ( ( CPLX_742.INSURED_SYSTEM_CODE=CPLX_744.SYSTEM_ENTITY_CODE )

AND ( CPLX_744.ENTITY_TYPE     ='INSURED' ) )

LEFT JOIN (ENTITY_REGISTER CPLX_746

LEFT JOIN ENTITY_ADDRESS_INFO CPLX_758

ON ( ( CPLX_746.SYSTEM_ENTITY_CODE =CPLX_758.SYSTEM_ENTITY_CODE

AND CPLX_758.PRIMARY_ADDRESS_YN   ='Y' ) ) )

ON ( ( CPLX_742.CUSTOMER_SYSTEM_CODE=CPLX_746.SYSTEM_ENTITY_CODE ) ) 

)

WHERE 

( CPLX_742.DATA_SEGMENT   IN

 (SELECT polic751.DATA_SEGMENT

 FROM SEGMENTATION_MASTER polic751

 WHERE (( CPLX_742.BROKER_SYSTEM_CODE = polic751.BROKER_SYSTEM_CODE

 OR polic751.AGENT_NO         = 'ALL')

 AND ( CPLX_742.BILL_TYPE       = polic751.BILL_TYPE

 OR polic751.BILL_TYPE        = 'ALL')

 AND ( CPLX_742.PRODUCT_CODE     = polic751.PRODUCT_CODE

 OR polic751.PRODUCT_CODE       = 'ALL')

 AND ( CPLX_742.STATE_CODE      = polic751.STATE

 OR polic751.STATE          = 'ALL')

 AND ( CPLX_742.OPERATING_COMPANY   = polic751.OPERATING_COMPANY

 OR polic751.OPERATING_COMPANY    = 'ALL'))

 AND ( polic751.DISPLAY_USER_ID    ='admin'

 OR 1                 =1 )

 ) )

AND ( ( CPLX_743.SOURCE_SYSTEM_ENTITY_CODE= 'ACC123' )

AND CPLX_748.ENTITY_TYPE         = 'ACCOUNT' )

ORDER BY CPLX_756.FULL_NAME ASC ,

 CPLX_742.POLICY_NO ASC ,

 CPLX_742.POLICY_EFFECTIVE_DATE DESC ,

 CPLX_742.POLICY_TERM_ID DESC;

Tagged:

Answers

  • Paulzip
    Paulzip Member Posts: 8,694 Blue Diamond

    Post plan in thread, most volunteers won't open a *.zip file from a random source.

  • user8170666
    user8170666 Member Posts: 6 Red Ribbon

    Large Query Plan in attached text file.

  • user8170666
    user8170666 Member Posts: 6 Red Ribbon

     OPERATION OBJECT_NAME CARDINALITY COST 


     SELECT STATEMENT 

       1 11861 

      

     SORT 

       1 11861 

         

     FILTER 

            

            

     Filter Predicates 

               

     EXISTS (SELECT 0 FROM SEGMENTATION_MASTER POLIC751 WHERE (POLIC751.BILL_TYPE=:B1 OR POLIC751.BILL_TYPE=U'ALL') AND (POLIC751.OPERATING_COMPANY=:B2 OR POLIC751.OPERATING_COMPANY=U'ALL') AND (POLIC751.PRODUCT_CODE=:B3 OR POLIC751.PRODUCT_CODE=U'ALL') AND (POLIC751.STATE=:B4 OR POLIC751.STATE=U'ALL') AND POLIC751.DATA_SEGMENT=:B5 AND (POLIC751.AGENT_NO=U'ALL' OR POLIC751.BROKER_SYSTEM_CODE=:B6)) 

            

     NESTED LOOPS 

       291 38 

               

     NESTED LOOPS 

       1 33 

                  

     NESTED LOOPS 

       1 29 

                     

     NESTED LOOPS 

       1 25 

                        

     NESTED LOOPS 

       1 20 

                           

     HASH JOIN 

       1 16 

                              

     Access Predicates 

                                 

     CPLX_742.POLICY_TERM_ID=CPLX_747.POLICY_TERM_ID(+) 

                              

     NESTED LOOPS 

       1 16 

                                 

     STATISTICS COLLECTOR 

            

                                    

     NESTED LOOPS 

       1 14 

                                       

     NESTED LOOPS 

       1 12 

                                          

     HASH JOIN 

       1 8 

                                             

     Access Predicates 

                                                

     CPLX_743.SYSTEM_ENTITY_CODE=CPLX_755.SYSTEM_ENTITY_CODE(+) 

                                             

     NESTED LOOPS 

       1 8 

                                                

     STATISTICS COLLECTOR 

            

                                                   

     HASH JOIN 

       1 6 

                                                      

     Access Predicates 

                                                         

     CPLX_743.SYSTEM_ENTITY_CODE=CPLX_752.SYSTEM_ENTITY_CODE(+) 

                                                      

     NESTED LOOPS 

       1 6 

                                                         

     STATISTICS COLLECTOR 

            

                                                            

     TABLE ACCESS 

     ENTITY_REGISTER 1 4 

                                                               

     INDEX 

     IDX_ENTITY_REG_PT_3 1 3 

                                                                  

     Access Predicates 

                                                                     

     AND 

                                                                        

     CPLX_743.ENTITY_TYPE=U'ACCOUNT' 

                                                                        

     CPLX_743.SOURCE_SYSTEM_ENTITY_CODE=U'ACCRT001-1-1' 

                                                         

     INDEX 

     ENTITY_ADDRESS_INFO_PTI2 1 2 

                                                            

     Access Predicates 

                                                               

     AND 

                                                                  

     CPLX_743.SYSTEM_ENTITY_CODE=CPLX_752.SYSTEM_ENTITY_CODE(+) 

                                                                  

     CPLX_752.PRIMARY_ADDRESS_YN(+)=U'Y' 

                                                      

     INDEX 

     ENTITY_ADDRESS_INFO_PTI2 1 2 

                                                         

     Filter Predicates 

                                                            

     CPLX_752.PRIMARY_ADDRESS_YN(+)=U'Y' 

                                                

     INDEX 

     IND_FR_SYSTEM_ENTITY_CODE 1 2 

                                                   

     Access Predicates 

                                                      

     CPLX_743.SYSTEM_ENTITY_CODE=CPLX_755.SYSTEM_ENTITY_CODE(+) 

                                                   

     Filter Predicates 

                                                      

     CPLX_755.SYSTEM_ENTITY_CODE(+) IS NOT NULL 

                                             

     INDEX 

     IND_FR_SYSTEM_ENTITY_CODE 1 2 

                                                

     Filter Predicates 

                                                   

     CPLX_755.SYSTEM_ENTITY_CODE(+) IS NOT NULL 

                                          

     TABLE ACCESS 

     POLICY_REGISTER 3 4 

                                             

     INDEX 

     POLICY_REGISTER_PTI2 3 2 

                                                

     Access Predicates 

                                                   

     CPLX_742.ACCOUNT_SYSTEM_CODE=CPLX_743.SYSTEM_ENTITY_CODE 

                                                

     Filter Predicates 

                                                   

     CPLX_742.ACCOUNT_SYSTEM_CODE IS NOT NULL 

                                       

     INDEX 

     POLICY_ENTITY_REGISTER_PK 1 2 

                                          

     Access Predicates 

                                             

     AND 

                                                

     CPLX_742.POLICY_TERM_ID=CPLX_748.POLICY_TERM_ID 

                                                

     CPLX_748.ENTITY_TYPE=U'ACCOUNT' 

                                          

     Filter Predicates 

                                             

     CPLX_748.ENTITY_TYPE=U'ACCOUNT' 

                                 

     INDEX 

     IDX_FLAGS_REGISTER_FK1 1 2 

                                    

     Access Predicates 

                                       

     CPLX_742.POLICY_TERM_ID=CPLX_747.POLICY_TERM_ID(+) 

                                    

     Filter Predicates 

                                       

     CPLX_747.POLICY_TERM_ID(+) IS NOT NULL 

                              

     INDEX 

     IDX_FLAGS_REGISTER_FK1 1 2 

                                 

     Filter Predicates 

                                    

     CPLX_747.POLICY_TERM_ID(+) IS NOT NULL 

                           

     VIEW PUSHED PREDICATE 

       1 4 

                              

     NESTED LOOPS 

       2211951387 4 

                                 

     INDEX 

     SYSTEM_ENTITY_CODE_PK 1 2 

                                    

     Access Predicates 

                                       

     CPLX_746.SYSTEM_ENTITY_CODE=CPLX_742.CUSTOMER_SYSTEM_CODE 

                                 

     INDEX 

     ENTITY_ADDRESS_INFO_PTI2 2207695152 2 

                                    

     Access Predicates 

                                       

     AND 

                                          

     CPLX_758.SYSTEM_ENTITY_CODE(+)=CPLX_742.CUSTOMER_SYSTEM_CODE 

                                          

     CPLX_758.PRIMARY_ADDRESS_YN(+)=U'Y' 

                                    

     Filter Predicates 

                                       

     CPLX_746.SYSTEM_ENTITY_CODE=CPLX_758.SYSTEM_ENTITY_CODE(+) 

                        

     VIEW PUSHED PREDICATE 

       1 5 

                           

     NESTED LOOPS 

       1115 5 

                              

     INDEX 

     IDX_ER_ENTTYPE_SEC 1 3 

                                 

     Access Predicates 

                                    

     AND 

                                       

     CPLX_744.ENTITY_TYPE=U'INSURED' 

                                       

     CPLX_744.SYSTEM_ENTITY_CODE=CPLX_742.INSURED_SYSTEM_CODE 

                              

     INDEX 

     IDX_EAI_SEC_PRIMADD_FULLNAME 1485 2 

                                 

     Access Predicates 

                                    

     AND 

                                       

     CPLX_756.SYSTEM_ENTITY_CODE(+)=CPLX_742.INSURED_SYSTEM_CODE 

                                       

     CPLX_756.PRIMARY_ADDRESS_YN(+)=U'Y' 

                                 

     Filter Predicates 

                                    

     CPLX_744.SYSTEM_ENTITY_CODE=CPLX_756.SYSTEM_ENTITY_CODE(+) 

                     

     VIEW PUSHED PREDICATE 

       1 4 

                        

     NESTED LOOPS 

       1 4 

                           

     INDEX 

     POLICY_REGISTER_PK 1 2 

                              

     Access Predicates 

                                 

     CPLX_753.POLICY_TERM_ID=CPLX_743.GOVERNING_POLICY_TERM_ID 

                           

     INDEX 

     IDX_FLAGS_REGISTER_FK1 1 2 

                              

     Access Predicates 

                                 

     CPLX_754.POLICY_TERM_ID(+)=CPLX_743.GOVERNING_POLICY_TERM_ID 

                              

     Filter Predicates 

                                 

     CPLX_753.POLICY_TERM_ID=CPLX_754.POLICY_TERM_ID(+) 

                  

     VIEW PUSHED PREDICATE 

       132 4 

                     

     NESTED LOOPS 

       1540398345 4 

                        

     INDEX 

     SYSTEM_ENTITY_CODE_PK 1 2 

                           

     Access Predicates 

                              

     CPLX_749.SYSTEM_ENTITY_CODE=CPLX_748.SYSTEM_ENTITY_CODE 

                        

     INDEX 

     ENTITY_ADDRESS_INFO_PTI2 1537434312 2 

                           

     Access Predicates 

                              

     AND 

                                 

     CPLX_750.SYSTEM_ENTITY_CODE(+)=CPLX_748.SYSTEM_ENTITY_CODE 

                                 

     CPLX_750.PRIMARY_ADDRESS_YN(+)=U'Y' 

                           

     Filter Predicates 

                              

     CPLX_749.SYSTEM_ENTITY_CODE=CPLX_750.SYSTEM_ENTITY_CODE(+) 

               

     VIEW PUSHED PREDICATE 

       892 5 

                  

     NESTED LOOPS 

       1045094350 5 

                     

     INDEX 

     IDX_ER_ENTTYPE_SEC 1 3 

                        

     Access Predicates 

                           

     AND 

                              

     CPLX_745.ENTITY_TYPE=U'BROKER' 

                              

     CPLX_745.SYSTEM_ENTITY_CODE=CPLX_742.BROKER_SYSTEM_CODE 

                     

     INDEX 

     ENTITY_ADDRESS_INFO_PTI2 10397748153 2 

                        

     Access Predicates 

                           

     AND 

                              

     CPLX_757.SYSTEM_ENTITY_CODE(+)=CPLX_742.BROKER_SYSTEM_CODE 

                              

     CPLX_757.PRIMARY_ADDRESS_YN(+)=U'Y' 

                        

     Filter Predicates 

                           

     CPLX_745.SYSTEM_ENTITY_CODE=CPLX_757.SYSTEM_ENTITY_CODE(+) 

            

     INLIST ITERATOR 

            

               

     TABLE ACCESS 

     SEGMENTATION_MASTER 2 81 

                  

     Filter Predicates 

                     

     POLIC751.DATA_SEGMENT=:B1 

                  

     INDEX 

     IDX_SEGMENTATION_MASTER_COMB 11 1 

                     

     Access Predicates 

                        

     AND 

                           

     OR 

                              

     POLIC751.STATE=:B1 

                              

     POLIC751.STATE=U'ALL' 

                           

     OR 

                              

     POLIC751.PRODUCT_CODE=:B2 

                              

     POLIC751.PRODUCT_CODE=U'ALL' 

                           

     OR 

                              

     POLIC751.OPERATING_COMPANY=:B3 

                              

     POLIC751.OPERATING_COMPANY=U'ALL' 

                           

     OR 

                              

     POLIC751.BILL_TYPE=:B4 

                              

     POLIC751.BILL_TYPE=U'ALL' 

                     

     Filter Predicates 

                        

     OR 

                           

     POLIC751.AGENT_NO=U'ALL' 

                           

     POLIC751.BROKER_SYSTEM_CODE=:B1