12.1 and 12.2
Have a large complicated query with ANSI joins with group bys.
Included in the query are views are with further large complicated ANSI join queries and unions.
Query takes 29-30 seconds where 27-28 seconds is parse elapsed. Verified in a 10046 trace.
Query itself takes <1 second without the parse.
Its well known nested views built with ANSI joins can cause parsing issues from 12 onwards, lots of tickets on support. Through trial and error I identified the culprit as one of the inline views in the select.
I tested a materialized view of the problematic view, use that in my query instead and that resolves, parse goes. MVIEW refreshes in < 1 second. query runs in < 1 second as expected, data good
Populate a temp table from the view, use that, same thing, parse goes. data good.
So I try a select * from my parse_problem_view in a CTE with a materialize hint,
with my_view as (select /*+ materialize */* from my_parse_problem_view)
select * from my_view, vw2, t1, t2, t3
where.....(lots of other ANSI joins)
it doesnt work. I can see from the plan that the materialize worked, and I still get my high parse but why? Isnt oracle supposed to materialize that data and present it to the query in a temp table when you materialize it? I dont want it to go optimizing its select against the other query blocks (which I guess is where the parse is spending its time) but maybe it has to do that anyway even with materialize.
Any other suggestions? There are many notes on support around parsing and oracle 12, Ive tried several underscore parameters and testing turning off all the adaptive settings, none work.
I dont need my main query tuned here, I have a workaround with a temp table or mview generation and have other options around rewriting the nested views not to use ANSI, more for my own curiosity on how to get rid of parsing, is there a quick fix I could try.