AnandS24 Member since 2017 3 posts
Virgin Media Communications Networks Ltd
Posted: 11 months ago
Last activity: 11 months 3 weeks ago

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 Enterprise12.

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***

Pega Marketing System Administration Data Integration
Share this page LinkedIn