Incorrect Explain Plan (12c and adaptative plans)

[mamv]

The new explain plans after upgrading to 12c were driving me crazy, but after some reading and debugging I found out that PL/SQL Developer is showing inactive steps from the adaptative plan.

Example:

The Hash join doesn't make any sense.

Using the dbms_xplan.display_cursor(format => 'allstats last adaptive') I get:

Where we can see that the Hash Join is inactive, so PL/SQL Developer is incorrectly showing the inactive steps.

Hope you fix this in the next release.

Regards,
Manuel

 

[Marco Kalter]

Thanks for reporting this. We'll investigate.

 

[T-Gergely]

Can this be fixed in a PSD 12 Beta?

 

[Marco Kalter]

Yes, it's on the list.

 

[T-Gergely]

It's still the same in PSD12B5

 

[T-Gergely]

It's still a problem in PSD12B6.

 

[T-Gergely]

It's still a problem in PSD12B7.

 

[T-Gergely]

Marco?

 

[Gustavo]

I work a lot with performance tuning. We're not upgrading to 12c until October, but if this issue remains until then, I'll have to look for another tool for the first time in 21 years.

 

[Marco Kalter]

We have not yet been able to find a method to determine from the plan_table column contents that a step is inactive.

 

[T-Gergely]

We have not yet been able to find a method to determine from the plan_table column contents that a step is inactive.

Check the Analyze R12c CBO features from OTHER_XML of V$SQL_PLAN section. You can use the skp attribute from the other_xml column.

And please do ask for help for the other problems, too if you're interested in fixing them. It's not a shame.