Just a note that the findings below, only detail my particular case, and may not reflect your environment, always contact your support and provide them the correct files.
In my case SQL with high version count was causing contention with a lot of SQL on my database version 18.104.22.168. This was due to Adaptive Plans features seen with MMON_SLAVE which also uses the Adaptive Plans features. I reviewed Version Counts and the “SQL ordered by Sharable Memory”.
select * from table(version_rpt(‘SQL_ID’));
The version_rpt can be found:
|High SQL Version Counts – Script to determine reason(s) (Doc ID 438755.1)|
select sql_id,version_COUNT,SHARABLE_MEM/1024/1024,PERSISTENT_MEM/1024/1024 from gv$sqlarea where version_COUNT>20 or sql_id=’SQL_ID’ order by sql_id,inst_id;
From the version_rpt some statements were having more version count due to bind value’s selectivity mis-match and due to lack of proper statistics over the tables involved
The bind value’s selectivity does not match that used to optimize the existing child cursor. When adaptive cursor sharing is used and the cursor is bind aware, then if the selectivity is outside of the current ranges and a new plan is desirable then a new child is raised with this as the reason code for non-sharing of the previous plan.
#### due to having skewed data in your base tables, optimizer creating multiple child’s, instead of using existing child cursor.
Cardinality feedback is being used and therefore a new plan could be formed for the current execution, I suggested that maybe cardinality feedback was introducing the instability and disabling it might be a reasonable thing to try.
Work arounds and/or temporary solutions:
one can use a hint (or) maintain proper statistics over the base tables involved in the SQl query.
set on the query:
/*+ opt_param(‘_optimizer_use_feedback’ ‘false’) */
Or set at system level:
alter system set “_optimizer_use_feedback”=0 scope=spfile sid=’*’;
reducing shareable memory:
Retrieve current default parameters:
Select x.ksppinm name, y.ksppstvl value
from x$ksppi x, x$ksppcv y
where x.ksppinm in (‘optimizer_adaptive_features’,’_optimizer_dsdir_usage_control’,’_report_capture_cycle_time’,’_optimizer_use_feedback’)
and y.indx = x.indx
and x.inst_id = USERENV(‘Instance’)
and y.inst_id = USERENV(‘Instance’) ;
alter system set optimizer_adaptive_features=false scope=spfile sid=’*’;
alter system set “_optimizer_dsdir_usage_control”=0 scope=spfile sid=’*’;
alter system set “_report_capture_cycle_time”=0 scope=spfile sid=’*’;