Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Help with performance tuning of query with multiple left joins

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;
Answers
-
Post plan in thread, most volunteers won't open a *.zip file from a random source.
-
Large Query Plan in attached text file.
-
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