I have oracle 19.14 standard edition database. My optimizer_adaptive_reporting_only parameter is TRUE and optimizer_adaptive_plans is also TRUE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_plans boolean TRUE
optimizer_adaptive_reporting_only boolean TRUE
optimizer_adaptive_statistics boolean FALSE
As per the doc, with above setting optimizations run in reporting-only mode. The information required for an adaptive optimization is gathered, but no action is taken to change the plan.
I am not clear where I can see the report of which SQL would have benefit from adaptive plan.
Following SQL returned no rows.
select * from v$sql where is_resolved_adaptive_plan ='Y' or is_reoptimizable ='Y';
select * from v$sql_plan where lower(other_xml) like '%adaptive%';
Also , I tried test case from https://oracle-base.com/articles/12c/adaptive-plans-12cr1 but it is not giving me adaptive plan.
What is the process to know what sql will benefit when optimizer_adaptive_reporting_only = TRUE? if there is a sample sql , i could force adaptive plan , it would be great .