Question & Answer
Question
Cursor Sharing in Sterling Application explained within context of Oracle 11g
Answer
Oracle has deprecated the cursor_sharing=similar option database version 11g and intends to remove it in the version 12 of the database.
Prior to Oracle 11g:
Setting cursor_sharing=force can sometimes lead to less than optimum execution plans, with use of bind variables against columns containing skewed data. This is because, the optimizer peaks at the bind variable value during the hard parse of the statement, with a potential effect on every execution of the statement regardless of the value of the bind variable.
Sterling recommends setting cursor_sharing=similar as a middle ground between no literal replacement (which can cause many cursors to be generated) and forced literal replacement (which causes a potentially sub-optimal plan to be shared for all literals).
In Oracle 11g:
Oracle 11g uses Adaptive Cursor Sharing (ACS) to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement.
By setting cursor_sharing to force and letting adaptive cursor sharing kick in, the optimizer can choose optimal plans for different values, if the same plan is appropriate for several values, they will share a single child cursor.
Sterling recommends ACS along with cursor_sharing=force. Histograms on skewed indexed columns are required for ACS to take effect.
Sample ADC SQL:
select * from v$sql_cs_selectivity;
select * from v$sql_cs_histogram;
select * from v$sql;
select * from v$sql_cs_statistics;
select * from v$sql_cs_statistics;
[{"Product":{"code":"SS6PEW","label":"IBM Sterling Order Management"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":"Not Applicable","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}},{"Product":{"code":"SS6PEW","label":"IBM Sterling Order Management"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":"Not Applicable","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}},{"Product":{"code":"SS6PEW","label":"IBM Sterling Order Management"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":"Not Applicable","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]
Historical Number
FAQ4513
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21517868