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

alter session set statistics_level = all;
set serveroutput off

<your query>

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