More version count of child cursor causing performance issue

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;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.