1. Performance Tuning Guide
  2. Recommending SQL Plan Management (SPM)

Recommending SQL Plan Management (SPM)

SPM provides the ability to build baselines that are known to be efficient and that are composed of existing plans or plans imported either from other instances or previous versions. SQL plan baselines are used to maintain performance of the matching SQL statements.

Siebel has been exhaustively tested on 19c with above recommendations and deliver extreme performance, however, data shape, volume and complex SQL statements due to customization in customer Siebel deployments might behave differently. Hence, it is strongly recommended to use SPM to harness the SQL statements and blocking any SQL plan changes by the Optimizer unless they perform better.

Further, the customer has the option of creating baselines in earlier Oracle releases once the Siebel has been upgraded to the targeted release for deployment and carry them over as part of Oracle upgrade. This will ensure plan stability during the initial rollout while ensuring that plan evolution takes care of any statements that may experience regressions.

This topic contains the following sections:

Recommended Approach for Creating SQL Baseline

The recommended approach to create SQL Baseline is to generate it in your production or in performance testing environment in 11g or 12c once the Siebel has been upgraded to the targeted release for going live and fine-tune the SQL statement.

Once the SQL Baseline is created, you can Export/Import the Baseline into the performance testing environment in Oracle 19c Database and future releases to further test and tune the SQL statements and then move it to Oracle 19c Database and future releases production upgrade. Or, in case of in-place upgrade to 19c Database and future releases , if you are already satisfied with the performance, you can create the SQL Baseline, upgrade your Oracle 11g or 12c production database and make sure that the SQL Baseline is set to be used. The SPM Baseline is preserved and honored after the upgrade has completed.

The main behind reason this recommendation is that Oracle 19c is certified with Siebel 18.6 onward. If your Siebel application is being upgraded from pre-IP2017 like Siebel IP2016 or earlier versions to post Siebel 18.6, there will be limited additional SQL statements post Siebel CRM Innovation Pack 2017 due to use of Workspaces, and Repository tables. The performance testing on Siebel 21.4 does not show that these queries have performance impact.

Note: There have been number of Workspace queries that were not using bind variables. They have been mostly addressed in Siebel 20.x and last ones in Siebel 21.4.
  • You will see queries on S_RR_* runtime tables whenever repository objects are accessed by the application only for the first time. Thereafter the content is cached.
  • You will see workspace queries on S_LST_OF_VAL table since this is the only seed table that is workspace enabled.
  • The SQLs remain the same as they were in IP15/IP16 for BCs that are NOT workspace enabled unless they involve querying List of Values (S_LST_OF_VAL table) or new customizations are done for Siebel upgrade that requires Oracle 19c Database and future releases upgrades.
    • all BCs used or customized by customers are not workspace enabled
    • In future, there will be more workspace-enabled BCs, hence the SQL statements involving these entities will change and potentially will require performance tuning

The above approach might require creating a SQL Baseline that contains all the SQL statements which could produce a huge Baseline size. The other approach is upgrading to Oracle Database and future releases in performance test environment, simulate the production workload, and tune the poor performing SQL statements. Once you are satisfied with the performance, create the SQL Baseline only for the SQL statements that are tuned. It can be carried to production through SPM Export/Import utility (details are provided in later sections of this document).

The creation of SPM SQL baseline can be globally divided into two categories:

Automatic Plan Capture

Automatic plan capture is enabled by setting the init.ora parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE (default FALSE). When enabled, a SQL plan baseline will be automatically created for any repeatable SQL statement provided it doesn’t already have one.

Note: Automatic plan capture is not enabled by default as it would result in a SQL plan baseline being created for every repeatable SQL statement executed on the system, including all monitoring and recursive SQL statements. On an extremely busy system this could potentially flood the SYSAUX tablespace with unnecessarily large SQL plan baselines. However, this is the most straightforward and comprehensive approach to implement SPM SQL Baselines.

Manual Plan Capture

Manually loading plans into SPM is the most common method to populate SQL plan baselines and is especially useful when a database is being upgraded from a previous version. Execution plans that are manually loaded and automatically accepted to create new SQL plan baselines.

Plans can be manually loaded from different sources, using either the functions in the DBMS_SPM package or through Oracle Enterprise Manager (EM):

  • From a SQL Tuning Set
  • From the cursor cache
  • From the AWR repository (from Oracle Database 12c Release 2)
  • Unpacked from a staging table
  • From existing stored outlines

There are three following options to create the SPM baseline using DBMS_SPM apart from optimizer_capture_sql_plan_baselines:

  • LOAD_PLANS_FROM_CURSOR_CACHE: This function loads plans in the shared SQL area (also called the cursor cache) into SQL plan baselines.
  • LOAD_PLANS_FROM_SQLSET: This function loads plans in an STS into SQL plan baselines.
  • LOAD_PLANS_FROM_AWR: This function loads plans from AWR into SQL plan baselines.

It is recommended to load the SQL Plan Baseline from the SQL Tuning Set as there are number of filters available to select the SQL statements.

Note: To reduce the number of SQL statements in the SQL Baseline, it's recommended that you use filters. SQL Profiles, Stored Outlines, and SQL patches for optimized SQL statements can be added to the SQL Baseline manually in case they're missed due to the use of filters (see the corresponding sections).

Managing and Monitoring SQL Plan Baselines

All aspects of managing and monitoring SQL plan baselines can be done through Oracle Enterprise Manager or the PL/SQL packages DBMS_SPM, DBMS_XPLAN and the DBA view DBA_SQL_PLAN_BASELINES.

For more information about SQL Plan Management please refer to Oracle 19c SQL Tuning Guide, Chapter 28: “Managing SQL Plan Baselines”, or Database Performance Tuning Guide in Oracle Database reference documentation.