More version count of child cursor causing performance issue in Oracle
Many child cursors may be generated for a query with bind variables.
1. Check the V$SQL view is_shareable column value is ‘Y’.
select sql_id,IS_SHAREABLE from v$sql where sql_id = 'adss234fda';
2. If it is shareable is ‘Y’ then you have to disable the following parameter.
Alter system set "_optimizer_extended_cursor_sharing_rel" = none;
Adaptive cursor sharing generates lots of child cursors causing problem. If upper parameter not help then you can try this one.
1.If more child cursors may be produced for a query with bind variables and not fixed by setting upper parameter.
First check the caused is done from adaptive cursor sharing by executing the query. Its increased as bind variable used.
Select * from v$sql_cs_selectivity
2. Stop the Adaptive cursor sharing to overcome from this performance problem.
If its not fixed by first parameter then you should stop following parameter also.
Alter system set "_optimizer_extended_cursor_sharing"=NONE;
If both are not working then you stop adaptive parameter:
alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both;