This content has been marked as final. Show 5 replies
check the action item in ADDM report.
Investigate application logic for possible use of bind variables instead
Alternatively, you may set the parameter "cursor_sharing" to "force".
If i do set cursor_sharing=force
what will be effect on database performance.
please tell me any effect database performance after applying cursor_sharing=force
Here are some examples and differences in implementing cursor sharing =EXACT,SIMILAR and FORCE.
Hope this link helps you understand in detail.
it'll cause every sql parsed to be rebuilded internally with binds. i.e. select * from user_tables where table_name='XY'; will be parsed as where table_name=:b1.
It can have huge impact regarding the CBO parser usage of statistics. Once you parse your first sql with your first 'bind' value, execution plan will be generated, and the same plan will be used for all future values, which can lead to 'wrong' and long execution times. Also your histogram statistics on database tables becomes more or less inrelevant at this point. After you bounce your instance or flush shared_pool, the execution plan will be lost, until the next sql call generate new plan using some new bind value.