Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Hendrik.

Asked: February 17, 2022 - 8:59 am UTC

Last updated: May 13, 2022 - 8:45 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hello Tom,

After Migration to Oracle 19c from 11.2.0.4, we found at least one query which completely changes the explain plan and is very slow.
Details and Pre-Requisite:

All DB Stats are built.
All Indexes are up to date.
Open a new DB session.
optimizer_mode is all_rows based on default v$parameter

The following actions are done always in the same session :

Execute the query with optimizer_features_enable = '19.1.0' based on v$parameter
The query makes several nested loops on the top,
based on a merge join cartesian with 2 tables a 30.000 records which are hash joint right outer
The query runs more than 30 minutes

ALTER SESSION SET optimizer_features_enable = '11.2.0.4'
The query uses only Hash join, no nested loops
The query is back in Milliseconds

Additionally, I found the following work around by using optimizer_features_enable = '19.1.0' which helps to solve the problem.
But I don’t like them at all.

Using and ordered Hint
The explain plan switches back to use only hash joins
The query is back within milliseconds
But at the end of the explain plan I got
ERROR: an uncaught error in function display has happened; please contact Oracle support
Please provide also a DMP file of the used plan table TOAD_PLAN_TABLE
ORA-00904: : invalid identifier

Setting Hidden Parameter : ALTER SESSION SET "_optimizer_cartesian_enabled" = FALSE;
Explain Plan uses hash join and nested loop but no Merge Join Cartesian
The query is back in milliseconds

Any ideas?
Thanks Tom and Kind Regards
Hendrik

and Chris said...

Looks like you've hit a costing difference between 11g and 19c.

To help us investigate, please get the plans for both versions like this:

alter session set statistics_level = all;
set serveroutput off

<your query>

select * 
from   dbms_xplan.display_cursor( format => 'ALLSTATS LAST ALL +NOTE +ADAPTIVE +OUTLINE' );


and share the results with us.

In the meantime to lock the query into using the faster plan look into SQL Plan Management:

https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-19c-5324207.pdf

By creating a baseline for the query, you restrict the optimizer to using the "good" plan.

Rating

  (6 ratings)

Comments

plan 19c

Hendrik, February 18, 2022 - 3:26 pm UTC

Hi Chris,
Thanks a lot for your mail.
Unfortunatly .. the chat size here is to small to send a plan and for sure not 2.
Attachment seems to be also not possible.
Any ideas ?
per mail ?

SR created

Hendrik, February 21, 2022 - 9:42 am UTC

Hi Chris,
we have now created a Service Request for this issue.
Kind Regards
Hendrik

Chris Saxon
February 21, 2022 - 12:18 pm UTC

OK, thanks for letting us know

Same issue for me

Denis JEANNERET, May 05, 2022 - 3:08 pm UTC

Hi,

Then finally, how the issue was fixed ?

Kind regards,
DJE
Chris Saxon
May 09, 2022 - 2:30 pm UTC

We'll have to see if the OP (Hendrik) comes back with an update.

Oracle CBO at 19c

Hendrik, May 11, 2022 - 4:38 am UTC

Hi Chris,
well ...at the end we created a SQL*Profile for the Query which fixes our problem. I tried the same query several times with an ordered Hint which then was also found by the SQL Tuning Advisor.

I really had the hope, with Oracle 19c, I can get rid of baselines and SQL*Profiles to fix CBO problems.
But yes … let’s wait for the next Oracle Release :-)
Have a great day.
Hendrik

Connor McDonald
May 11, 2022 - 5:42 am UTC

Baselines are here to stay, because there can never be such a thing as a "perfect optimizer" because that would require an infinite amount of statistics in order to know in advance the best way to run the SQL.

As an aside, I prefer the LEADING hint to ORDERED because you have no constraints over how you write your query

CBO Issue

Hendrik, May 12, 2022 - 11:25 am UTC

just as a summarize of your answer :

Leading Hint did not work.
Had to use ordered which was also recommended by the SQL Tuning Advisor.
SQL Tuning Advisor recommended to use SQL Profiles and not Baseline.
The same query runs properly without hint in 11g. Using Hint means one step back in 19c.

Thanks

Chris Saxon
May 12, 2022 - 1:29 pm UTC

Thanks for sharing. I don't think the SQL Tuning Advisor recommends baselines - profiles are a way you can get a query to use the "right" plan, then add a baseline to it to lock it in.

Other alternatives...

Rajeshwaran Jeyabal, May 13, 2022 - 4:03 am UTC

Another alternative would be to set OFE at session level to 11.2.0.4 run the queries get the plan and lock them using SPM and post that SPM will evolve over the period of time with the best plans for execution.
Chris Saxon
May 13, 2022 - 8:45 am UTC

Yes, that could help

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.