Suboptimal Execution Plan with Join Predicate Push-Down Transformation

Nenad Noveljic Leave a Comment

The optimizer is generating an expensive plan with the join predicate push-down (JPPD) transformation on a 12.2 database – the estimated cost is around 3,376,000. In contrast, I’m getting a much cheaper plan – with a cost of around 84,000 – after disabling the tranformation by setting “_push_join_predicate” to false.

So, surprisingly, the optimizer somehow misses a better plan when doing the JPPD transformation.

It’s a complex query, which I couldn’t model so far. Therefore, I’m going to describe the problem by using the information from the CBO (10053) trace.

First, the optimizer is calculating the cost of pushing join predicates from the query block with the annotation #4 to three different query blocks – annotated as: #10, #11 and #12 :

JPPD: Checking validity of push-down in query block SET$BADDE9FB_1 (#4)
JPPD:   Checking validity of push-down from query block SET$BADDE9FB_1 (#4) to query block SEL$2DE89145 (#10)
Check Basic Validity for Non-Union View for query block SEL$2DE89145 (#10)
JPPD:     Passed validity checks
JPPD:   Checking validity of push-down from query block SET$BADDE9FB_1 (#4) to query block SEL$41289C4A (#11)
Check Basic Validity for Non-Union View for query block SEL$41289C4A (#11)
JPPD:     Passed validity checks
JPPD:   Checking validity of push-down from query block SET$BADDE9FB_1 (#4) to query block SEL$A0D20652 (#12)
Check Basic Validity for Non-Union View for query block SEL$A0D20652 (#12)
JPPD:     Passed validity checks
JPPD: JPPD:   Pushdown from query block SET$BADDE9FB_1 (#4) passed validity checks. 
Join-Predicate push-down on query block SET$BADDE9FB_1 (#4)
JPPD: Using search type: linear
JPPD: Considering join predicate push-down
JPPD: Starting iteration 1, state space = (10,11,12) : (0,0,0)
JPPD: Updated best state, Cost = 41463.286373
JPPD: Starting iteration 2, state space = (10,11,12) : (1,0,0)
JPPD: Not update best state, Cost = 41463.286373
JPPD: Starting iteration 3, state space = (10,11,12) : (0,1,0)
JPPD: Not update best state, Cost = 41463.286373
JPPD: Starting iteration 4, state space = (10,11,12) : (0,0,1)
JPPD: Not update best state, Cost = 41463.286373

As you can see, the state space (10,11,12) : (0,0,0) is the one with the lowest cost. In other words, it would be most efficient not to do JPPD at all.

Also the subsequent log entry is indicating that JPPD shouldn’t be done:

JPPD: Will not use JPPD from query block SET$BADDE9FB_1 (#4)

But later on, strangely, the optimizer ignores that and, in the end, applies the JPPD on all three query blocks:

JPPD: Checking validity of push-down in query block SET$BADDE9FB_1 (#4)
JPPD: JPPD:   Pushdown from query block SET$BADDE9FB_1 (#4) passed validity checks. 
JPPD: Performing join predicate push-down (final phase) from query block SET$BADDE9FB_1 (#4) to query block SEL$A0D20652 (#12)
JPPD:   Pushing predicate "BRIEFANREDE"."VIN01ID"(+)="VIN01"."ID"
from query block SET$BADDE9FB_1 (#4) to query block SEL$A0D20652 (#12)
JPPD: Push dest of pred 0xa8637960 is qb 0xbac44950:query block SEL$A0D20652 (#12)

JPPD: Performing join predicate push-down (final phase) from query block SET$BADDE9FB_1 (#4) to query block SEL$41289C4A (#11)
JPPD:   Pushing predicate "ORG12_RM_GESCHAEFTSFELD"."GES01_ID"(+)="GES01"."ID"
from query block SET$BADDE9FB_1 (#4) to query block SEL$41289C4A (#11)
JPPD: Push dest of pred 0xa8633870 is qb 0xcbbc3de8:query block SEL$41289C4A (#11)

JPPD: Performing join predicate push-down (final phase) from query block SET$BADDE9FB_1 (#4) to query block SEL$2DE89145 (#10)
JPPD:   Pushing predicate "EAM"."GES01_GESCHAEFTSBEZIEHUNG_GBZ"(+)="VIN01"."GES01_GBZ_GBZ"
from query block SET$BADDE9FB_1 (#4) to query block SEL$2DE89145 (#10)
JPPD: Push dest of pred 0x8fa13010 is qb 0x858777a8:query block SEL$2DE89145 (#10)

In the trace I haven’t found any clue about what leads the optimizer to suddenly start doing JPPD.

Also, I posted this problem on Oracle-L, but haven’t got any answers so far.

In the meantime, you can use the Python script jppd_parser.py to parse the CBO trace and check if your execution plan is affected by this problem.

You can see the sample output is bellow:

JPPD: Will not use JPPD from query block SEL$2DE89145 (#6)
JPPD: Will not use JPPD from query block SET$12FE3EEB_2 (#5)
JPPD: Will not use JPPD from query block SEL$2DE89145 (#10)
JPPD: Will not use JPPD from query block SET$12FE3EEB_1 (#4)
JPPD: Performing join predicate push-down (final phase) from query block SEL$2DE89145 (#6) to query block SEL$EBDEBDD3 (#9)
JPPD: Performing join predicate push-down (final phase) from query block SET$12FE3EEB_2 (#5) to query block SEL$A0D20652 (#8)
JPPD: Performing join predicate push-down (final phase) from query block SET$12FE3EEB_2 (#5) to query block SEL$41289C4A (#7)
JPPD: Performing join predicate push-down (final phase) from query block SET$12FE3EEB_2 (#5) to query block SEL$2DE89145 (#6)
JPPD: Performing join predicate push-down (final phase) from query block SEL$2DE89145 (#10) to query block SEL$EBDEBDD3 (#13)
JPPD: Performing join predicate push-down (final phase) from query block SET$12FE3EEB_1 (#4) to query block SEL$A0D20652 (#12)
JPPD: Performing join predicate push-down (final phase) from query block SET$12FE3EEB_1 (#4) to query block SEL$41289C4A (#11)
JPPD: Performing join predicate push-down (final phase) from query block SET$12FE3EEB_1 (#4) to query block SEL$2DE89145 (#10)
JPPD: Performing join predicate push-down (final phase) from query block SEL$2DE89145 (#6) to query block SEL$EBDEBDD3 (#9)
JPPD: Performing join predicate push-down (final phase) from query block SET$12FE3EEB_2 (#5) to query block SEL$A0D20652 (#8)
JPPD: Performing join predicate push-down (final phase) from query block SET$12FE3EEB_2 (#5) to query block SEL$41289C4A (#7)
JPPD: Performing join predicate push-down (final phase) from query block SET$12FE3EEB_2 (#5) to query block SEL$2DE89145 (#6)
JPPD: Performing join predicate push-down (final phase) from query block SEL$2DE89145 (#10) to query block SEL$EBDEBDD3 (#13)
JPPD: Performing join predicate push-down (final phase) from query block SET$12FE3EEB_1 (#4) to query block SEL$A0D20652 (#12)
JPPD: Performing join predicate push-down (final phase) from query block SET$12FE3EEB_1 (#4) to query block SEL$41289C4A (#11)
JPPD: Performing join predicate push-down (final phase) from query block SET$12FE3EEB_1 (#4) to query block SEL$2DE89145 (#10)

A possible problem - JPPD was done from the following QBs:
SET$12FE3EEB_1 (#4)
SET$12FE3EEB_2 (#5)
SEL$2DE89145 (#6)
SEL$2DE89145 (#10)

In particular, the script lists the query blocks for which the optimizer first had decided not to do the JPPD, but reverted this decision afterwards.

As I already mentioned, you can try disabling the transformation (_push_join_predicate=false) as a workaround and see if you’re getting a better execution plan.

Update on February 6, 2019:
The Python script jppd_parser.py transfered to Github.

References:

Pagano, Mauro. (2017, January 30). Chasing the optimizer

Thanks for sharing

Nenad Noveljic

More Posts Twitter LinkedIn

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.