I'm using Oracle 18c but optimizer_features_enable is set to 12.1.0.1. I have a WITH clause defined with multiple FUNCTION and there is only SELECT statements in the WITH clause. I ran the WITH clause in Oracle SQL Developer and I killed it after 12 hours.
Solution #1 failed:
I ran DBMS_REDEFINITION.REDEF_TABLE for every table used in the WITH clause. It did not help. My understanding that DBMS_REDEFINITION.REDEF_TABLE will get rid of fragmentation and gather statistics for a table and associated indexes.
Solution #2 worked:
I ran dbms_stats.gather_schema_stats for the schema which contains all the tables used in the WITH clause and other tables not used in the WITH clause. This helps and the WITH clause will finish in less than 1 second. The PLAN_HASH_VALUE in sys.daba_hist_sqlstat is different in this run from previous runs.
What is the difference between Solution #1 and Solution #2 that makes Solution #2 changed the PLAN_HASH_VALUE? There is no SQL Profile and no SQL Plan BaseLine. I notice that the IS_RESOLVED_ADAPTIVE_PLAN has Y in V$SQLAREA for the WITH clause.
Thanks for any help.