Fixing Poorly Performing sql after a Database Upgrade with sql Plan Baselines

On each new version of the database, Oracle introduces new features and bug fixes to make the core engine of the optimizer smarter and more efficient than it was in the previous version.

In the latest 12c version of the database there is an array of these new features available, all with the goal of making sure that for any SQL statement the optimizer chooses the fastest execution plan it can.  It does while making sure that it does not take too long in working out what that plan should be in the first place. Adaptive Execution Plans, Dynamic Plans, Automatic Re-optimization and Statistics Feedback are a few of the 12c features in this area.

Having performed more database upgrades in my 16 year Oracle career than I care to mention, I can say from experience that no matter how intelligent the optimizer gets, there will still be occasions when it just gets it plan wrong!

One common feature of the half dozen or so 12c database upgrades that I have performed for various customers over the last few months is that most pieces of SQL after the upgrade will run in the same time as before, however there are those few that run a little faster or a little slower, and the handful that run significantly faster or significantly slower.

It is the “significantly slower” ones that are important to identify and fix as part of the development and testing process of the upgrade. By “significantly slower” I mean a runtime pre-upgrade of a few seconds or minutes which after the upgrade has degraded to a few hours (or days!).

There has always been at least one such case in each of the 12c upgrades I have performed so far, but never more than five in total for any one system, so this is the ballpark figure you can expect.

Identifying regressed SQL

Oracle provide some feature-rich and intuitive tools that can help you to identify any SQL that regresses in performance following an upgrade, such as Database Replay and Real Application User Testing.

These are unfortunately often extra cost options that require additional Oracle licences (if your system is on premise) or at least the ‘High Performance’ offering for Oracle Database Cloud customers.

If your business already holds the necessary licenses then certainly use these tools to get your value for money, but if can be a very expensive option to purchase these packs from Oracle for an upgrade project when there might only be one piece of SQL that is a problem post-upgrade that needs fixing.

In the absence of these tools often the only way to find these problem SQL’s is to test it manually – either with the use of third party performance testing and benchmarking tools, or users just following a test plan in the application.

What is important at this stage is to make sure that when you are testing and finding something running much slower after the upgrade that other possible contributing factors are ruled out.

Do this by performing an initial baseline performance test of the non-production environment before it is upgraded and then performing exactly the same test again afterwards in the same conditions.

For example: don’t clone the environment between the tests; don’t change the memory configuration of the database or server; don’t try to compare the performance of a non-prod environment post-upgrade with your production environment pre-upgrade, since chances are you are not comparing like-for-like.

In my case I used Flashback Database and Guaranteed Restore points to make the dataset identical between each test run:

1. Enable flashback database
2. Take a guaranteed restore point
3. Carry out initial baseline performance testing
4. Restore database to restore point
5. Upgrade database
6. Carry out post-upgrade performance testing

It is clearly impossible to test every single piece of SQL that the database may have to service in these tests; instead target the testing on business-critical functions.  Examples’s include, how long Payroll takes to run in an HCM system, or Create Accounting in a Financials system; overnight batch jobs which must complete by a certain time; any programs which are already known to be long-running; or the response time of a query on a website or a Form.

Fixing regressed SQL

Oracle again provide an array of tools that can be used to force a piece of SQL to use a particular execution plan – some are extra cost options, some are included in Enterprise Edition licences.

For the rest of this article I will be concentrating on SQL Plan Baselines which is one of the features that does not require additional licenses!

Generate SQL Plan Baselines to fix poor SQL performance

SQL Plan Management has been around since version 11g of the database. It is a feature that enables the system to automatically control SQL plan evolution by maintaining baselines.

STEP 1: Verify the culprit!

You have found a piece of SQL that runs much slower after the upgrade than it did before.

You think that the database upgrade has caused this, but you want to prove that.

This can be done easily by toggling the database initialisation parameter OPTIMIZER_FEATURES_ENABLE back to the previous database version.

This is one of those nice parameters that can be changed dynamically without a database bounce and can be done at system or session level.

• Run the program/query/SQL in the upgraded environment with trace enabled. If running from SQL*Plus this can be done at session level; if running from within an application like eBusiness Suite this can be done from within the front-end (enable trace on concurrent program definition, or turn on trace in OA Framework), or even do this at database level.
• Let the program/query/SQL run and then tkprof the trace file.
• Check the tkprof trace file and what the most expensive SQL is – note the SQL_ID, elapsed time and execution plan.
• Now set the optimizer features back to the previous database version (11.2.0.4 in this example).

sqlplus / as sysdb
a
alter system set optimizer_features_enable='11.2.0.4';
 

• Run the program/query/SQL again, with trace enabled.
• If the process now finishes in acceptable runtimes again, this proves that the problem is the new version of the database optimizer choosing a non-performant plan for this SQL.
• Compare the new trace file with the first trace file – you should see a different execution plan with a radically improved elapsed time.

STEP 2: Generate a SQL Plan Baseline

• We now want to force the database to treat this particular SQL as it did before the upgrade by using the plan it generated in the previous version.
• The first step is to configure the database to capture SQL Plan Baselines – by default this is turned off.

sqlplus / as sysdba

alter system set OPTIMIZER_FEATURES_ENABLE='11.2.0.4';

alter database set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

• Only repeatable statements are captured as SQL Plan Baselines, so now run the problem SQLtwice with this configration.
• When complete, put the initialisation parameters back to their previous values.

sqlplus / as sysdbaalter system set

OPTIMIZER_FEATURES_ENABLE='12.1.0.2';alter database set

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

STEP 3: Test it is fixed

• There should now be a SQL Plan baseline created for the problem SQL using the ‘good’ version of the execution plan.
• You check this by querying the data dictionary view DBA_SQL_PLAN_BASELINES. Your particular SQL you can find either by the SQL_ID noted from the trace files or just by the SQL_TEXT.
• If you run the SQL again now with the 12c version of the optimiser it will use the execution plan stored in the SQL Plan Baseline – the ‘good’ plan generated under the previous version – and runtime will return to pre-upgrade levels. All other SQL will continue to use the new plans generated by the 12c optimiser.
• NOTE: this only works if initialisation parameter OPTIMIZER_USE_SQL_PLAN_BASELINES is set to TRUE (which is the default).

STEP 4: Migrate fix to other environments

• Great, you have now fixed the problem SQL in your dev environment! But how do you propagate this fix to other instances, including production? One way would be to repeat the above steps on all databases as they are upgraded, but this is a laborious process and also runs the risk in Prod that SQL statements that you do not want to generate Plan Baselines with the previous version of the optimiser will be created when you have set OPTIMIZER_FEATURES_ENABLE and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES at system level.
• Instead, you can pack up the SQL Plan Baseline(s) created in your dev environment, export them out, import them to your next database and then unpack them. The import/unpack process can then be performed on subsequent iterations of the upgrade, including go-live as a post-upgrade step before the application is restarted – that way, the problem SQL will have the ‘good’ plan available to it the first time it is run!

1) In the fixed dev environment, pack up the SQL Plan Baseline:

sqlplus system

exec DBMS_SPM.CREATE_STGTAB_BASELINE(‘STGTAB’, user);

var x number;

begin

2) DataPump export the new stage table from the dev environment:

sqlplus / as sysdba

SQL> create or replace directory DATA_PUMP_DIR as ‘<PATH>’;

Directory created.

expdp system/<psswd> tables=STGTAB directory=DATA_PUMP_DIR dumpfile=<NAME>.dmp
 
3) When the next database is upgraded, copy the export file to a place where it can get it and import it:

sqlplus / as sysdba

SQL> create or replace DATA_PUMP_DIR as '<PATH>';impdp system/<psswd> tables=STGTAB
directory=DATA_PUMP_DIR dumpfile=<NAME>.dmp
 
4) Then unpack the SQL Plan Baseline:

sqlplus system

var x number;


Concluding Thoughts

This is just one method amongst many available in your toolset to fix poorly performing SQL after a database upgrade.

It is particularly suited to instances where there is a small number of SQL to deal with, there are no licenses held for some of the more advanced Oracle options, and there is a robust test plan in place that is able to identify any critical problem SQL as early as possible in the upgrade project.

I myself have made successful use of this method on a number of occasions in the ‘real world’ and can attest to its effectiveness!