Question
Review changes to Oracle Cursor_Sharing from EXACT to FORCE
Our Pega Application used in Customer Care is built on : -
Pega 7.1.7
Pega Marketing 7.2
Pega NBAA 7.2
DATABASE: Oracle Database 12c Enterprise Proprietary information hidden.0
We are live for almost 3 years now, and evaluating few performance improvements that we could do. With this post, I would like to request Pega SME's and Community Members to share their best knowledge and experience for below consideration in our Application. There are numerous OOTB Pega queries(several hundreds), mostly from the Pega Marketing and NBAA framework, that are OOTB and not all of them use bind variables. The problem we have observed is with oracle not pinning or not using the best sql plan for same Pega OOTB queries(many such Pega OOTB queries dont use bind variables), thus Oracle may betreating these queries differently every time. And most of the times, due to Oracle picking random SQL plans, we see performance degradation associated with same. Thus, we are considering a change in one of Database parameters for cursor_sharing. Not sure whether this will help us significantly especially with the SQL Plan management issue with bind variables, given Pega would not be making changes to any OOTB queries logic and use of bind variables. This is currently making Oracle SQL Plan management difficult to achieve.
In an attempt to assess whether the following change could help us with performance improvement, we would like to set the following init.ora variable:
Our Pega Application used in Customer Care is built on : -
Pega 7.1.7
Pega Marketing 7.2
Pega NBAA 7.2
DATABASE: Oracle Database 12c Enterprise Proprietary information hidden.0
We are live for almost 3 years now, and evaluating few performance improvements that we could do. With this post, I would like to request Pega SME's and Community Members to share their best knowledge and experience for below consideration in our Application. There are numerous OOTB Pega queries(several hundreds), mostly from the Pega Marketing and NBAA framework, that are OOTB and not all of them use bind variables. The problem we have observed is with oracle not pinning or not using the best sql plan for same Pega OOTB queries(many such Pega OOTB queries dont use bind variables), thus Oracle may betreating these queries differently every time. And most of the times, due to Oracle picking random SQL plans, we see performance degradation associated with same. Thus, we are considering a change in one of Database parameters for cursor_sharing. Not sure whether this will help us significantly especially with the SQL Plan management issue with bind variables, given Pega would not be making changes to any OOTB queries logic and use of bind variables. This is currently making Oracle SQL Plan management difficult to achieve.
In an attempt to assess whether the following change could help us with performance improvement, we would like to set the following init.ora variable:
Present Configuration - cursor_sharing=EXACT
Desirable Configuration - cursor_sharing=FORCE
However, the above change could have impact to the Application, that we are not very sure about, and wanted to discuss and get best opinion. Does Pega allow this setting as per changes mentioned above?
If yes, are their any known disadvantages or any other impact we should be considering/evaluating in addition?
Would there be any KB/KX article already published by Pega on similar topic that we can refer?
***Moderator Edit-Vidyaranjan: Updated Platform Capability***
PEG
GB
I don't see how the setting could impact the functionality.
But I have see other system when this is set to FORCE (this was evident from the SQL).
In terms of overheads; I think you may want to search for the term: _optim_peek_user_binds
There are a few articles regarding this setting and how you may need to set this to false in conjunction with the cursor_sharing option.
But there is no hard/fast rule for this as far as I know.
Virgin Media Communications Networks Ltd
GB
Thanks for your inputs, we shall look at articles related to "_optim_peek_user_binds" and consider using in conjunction with cursor_sharing as FORCED. But, can I additionally request your opinion about, whether Pega as a product supports this config? Actually there is not a particular Pega OOTB query that we are dealing with in current context, there are host of such OOTB queries, so our query is in general to know, whether Pega supports or "does_not_support" for cursor_sharing as FORCED. Our purpose is not to gain performance, but to enable us to use sql plan management optimally, because there are numerous Pega OOTB SQL queries which do not use bind variables. These OOTB SQL queries that don't use bind variables, is something we cant fix by ourselves, and also something which Pega won’t fix either(by fix we mean to say - use bind variables). Thus, in our current discussion context, we are dealing with lack of sql plan management due to numerous Pega OOTB queries that don't use bind variables. So our query is in general to know, whether Pega supports or "does_not_support" for cursor_sharing as FORCED.
Thanks for your inputs, we shall look at articles related to "_optim_peek_user_binds" and consider using in conjunction with cursor_sharing as FORCED. But, can I additionally request your opinion about, whether Pega as a product supports this config? Actually there is not a particular Pega OOTB query that we are dealing with in current context, there are host of such OOTB queries, so our query is in general to know, whether Pega supports or "does_not_support" for cursor_sharing as FORCED. Our purpose is not to gain performance, but to enable us to use sql plan management optimally, because there are numerous Pega OOTB SQL queries which do not use bind variables. These OOTB SQL queries that don't use bind variables, is something we cant fix by ourselves, and also something which Pega won’t fix either(by fix we mean to say - use bind variables). Thus, in our current discussion context, we are dealing with lack of sql plan management due to numerous Pega OOTB queries that don't use bind variables. So our query is in general to know, whether Pega supports or "does_not_support" for cursor_sharing as FORCED. If we could get some opinion from you, other Pega SME in this community forum, that Pega product supports cursor_sharing as FORCED, then we can give a try by changing this setting and observe the outcome for use of SQL PLAN Management with OOTB SQL queries which don't use bind variables. If there is no functional impact in Pega product side, if we use this setting, we would like to take that assurance from here and proceed with our test with the changed setting. Would there be any articles or Pega documents guiding to try this out?
Regards
Anand
Updated: 24 Oct 2019 16:28 EDT
Pegasystems Inc.
US
If the execution plan variation is literal variables, using force will be helpful. Though Oracle 12c has dynamic adaptive features, which sometimes may cause sql execution plans variations even the same bind variable is involved.
Virgin Media Communications Networks Ltd
GB
Thank you for your response. Actually there is not a particular Pega OOTB query, and our purpose is not to gain performance, but to enable us to use sql plan management optimally, because there are numerous Pega OOTB SQL queries which do not use bind variables. These OOTB SQL queries that don't use bind variables, is something we cant fix by ourselves, and also something which Pega won’t fix either(by fix we mean to say - use bind variables). Thus, in our current discussion context, we are dealing with lack of sql plan management due to numerous Pega OOTB queries that don't use bind variables. So our query is in general to know, whether Pega supports or "does_not_support" for cursor_sharing as FORCED. If we could get some opinion from you, other Pega SME in this community forum, that Pega product supports cursor_sharing as FORCED, then we can give a try by changing this setting and observe the outcome for use of SQL PLAN Management with OOTB SQL queries which don't use bind variables. If there is no functional impact in Pega product side, if we use this setting, we would like to take that assurance from here and proceed with our test with the changed setting. Would there be any articles or Pega documents guiding to try this out?
Regards
Anand
Thank you for your response. Actually there is not a particular Pega OOTB query, and our purpose is not to gain performance, but to enable us to use sql plan management optimally, because there are numerous Pega OOTB SQL queries which do not use bind variables. These OOTB SQL queries that don't use bind variables, is something we cant fix by ourselves, and also something which Pega won’t fix either(by fix we mean to say - use bind variables). Thus, in our current discussion context, we are dealing with lack of sql plan management due to numerous Pega OOTB queries that don't use bind variables. So our query is in general to know, whether Pega supports or "does_not_support" for cursor_sharing as FORCED. If we could get some opinion from you, other Pega SME in this community forum, that Pega product supports cursor_sharing as FORCED, then we can give a try by changing this setting and observe the outcome for use of SQL PLAN Management with OOTB SQL queries which don't use bind variables. If there is no functional impact in Pega product side, if we use this setting, we would like to take that assurance from here and proceed with our test with the changed setting. Would there be any articles or Pega documents guiding to try this out?
Regards
Anand