As you should already know, extended support for Oracle Database 11.2.0.4 on-premises ended on December 31, 2020.

If you are planning to upgrade your 11g and other databases, unless you have specific requirements, the recommended release is obviously Oracle Database 19c. It is the Long Term Support Release of the 12.2 release branch.

Of course, the best way to mitigate the risk of performance issue due to the release upgrade is to TEST your application thoroughly. However, TESTING is expensive and time-consuming and very often, we need a strategy to ensure the stability of our application’s performance post upgrade.

Based on your goal, you can choose several strategy: AWR Snapshot comparison, SQL Performance Analyzer (SPA), SQL Plan Management (SPM), Real Application Testing (RAT).

This writing is to highlight a performance issue for those of you who choose to use SPM when upgrading an 11g database to 19c version.

After upgrading a database of our important customer from 11.2.0.4 to 19.8 on Solaris SPARC (64-bit), we have observed that some queries with SQL plan baseline enabled and called within a stored procedure started to have poor performance. This degradation was caused by a high number of child cursors. For some of them, the version_count has grown to around 8k.

At the end of our analysis, we have found that the new hidden parameter _optimizer_batch_table_access_by_rowid introduced starting from 12c causes this performance issue.

On 20 Sept 2020, we opened an Oracle Service Request providing a test case that reproduces the issue but, at the time of this writing, it is still under Development Working and therefore, there is no fix to solve permanently this problem.

How can I check if my cursor is affected by this performance issue?

1. a SQL_PLAN_BASELINE or a SQL_PROFILE is active on your sql_id;

select sql_id, sql_plan_baseline, sql_profile from v$sqlarea where sql_id ='2926rywsgjp8m';

SQL_ID        SQL_PLAN_BASELINE              SQL_PROFILE
------------- ------------------------------ ------------------------------
2926rywsgjp8m SQL_PLAN_8duuvuntm6gcf3bd84649

2. The sql_id has high version_count;

select sql_id, version_count, plan_hash_value, sql_plan_baseline, sql_profile from v$sqlarea where sql_id ='2926rywsgjp8m';

SQL_ID        VERSION_COUNT PLAN_HASH_VALUE SQL_PLAN_BASELINE              SQL_PROFILE
------------- ------------- --------------- ------------------------------ -----------
2926rywsgjp8m          4700       957934184 SQL_PLAN_8duuvuntm6gcf3bd84649

select sql_id, child_number, plan_hash_value from v$sql where sql_id='2926rywsgjp8m';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
2926rywsgjp8m            0       957934184
2926rywsgjp8m            2       957934184
2926rywsgjp8m            3       957934184
2926rywsgjp8m            4       957934184
2926rywsgjp8m            5       957934184
2926rywsgjp8m            6       957934184
2926rywsgjp8m            7       957934184
2926rywsgjp8m            8       957934184
2926rywsgjp8m            9       957934184
2926rywsgjp8m           10       957934184
...
2926rywsgjp8m         4700       957934184

3. Baseline execution plan performs “TABLE ACCESS BY INDEX ROWID” operation;

Plan hash value: 957934184

-------------------------------------
| Id  | Operation                   |
-------------------------------------
|   0 | SELECT STATEMENT            |
|*  1 |  TABLE ACCESS BY INDEX ROWID|
|*  2 |   INDEX RANGE SCAN          |
-------------------------------------

4. Looking for why a particular child cursor is not shared with existing child cursors, USER_BIND_PEEK_MISMATCH is set to YES.

select SQL_ID,CHILD_NUMBER,USER_BIND_PEEK_MISMATCH from v$sql_shared_cursor where sql_id='2926rywsgjp8m';

SQL_ID        CHILD_NUMBER U
------------- ------------ -
2926rywsgjp8m            0 N
2926rywsgjp8m            2 N
2926rywsgjp8m            3 Y
2926rywsgjp8m            4 Y
2926rywsgjp8m            5 Y
2926rywsgjp8m            6 Y
2926rywsgjp8m            7 Y
2926rywsgjp8m            8 Y
2926rywsgjp8m            9 Y
2926rywsgjp8m           10 Y
...
2926rywsgjp8m         4700 Y

5. 10053 trace file shows the following:

...
SPM: finding a match for the generated plan, planId = 2796312904
SPM: planId's of plan baseline are: 1004029513
SPM: using qksan to reproduce, cost and select accepted plan, sig = 9721965191854505358 cntRepro = 0
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 1004029513
...
SPM: planId in plan baseline = 1004029513, planId of reproduced plan = 1004029513
SPM: best cost so far = 6.003076, current accepted plan cost = 6.003076
...

What do we recommend you?

Set the parameter _cursor_obsolete_threshold to a lower value than the new default, which is 8192. This parameter value is the maximum limit for obsoleting the parent cursors.

High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance (Doc ID 2431353.1)

For 12.2 and higher non-CDB environments, set the parameter “_cursor_obsolete_threshold” back to the old default.

alter system set "_cursor_obsolete_threshold"=1024 COMMENT='MOS Note: 2431353.1’ scope=spfile;
-- restart database

Based on your needs, apply one of the following workarounds:

At system or session level, you can set the parameter optimizer_features_enable to 11.2.0.4 or lower value

-- session level
alter session set optimizer_features_enable='11.2.0.4';

-- system level
alter system set optimizer_features_enable='11.2.0.4' sid='*' scope=both;

or the hidden parameter _optimizer_batch_table_access_by_rowid to false

-- session level
alter session set "_optimizer_batch_table_access_by_rowid"=false;
 
-- system level
alter system set "_optimizer_batch_table_access_by_rowid"=false sid='*' scope=both;

At statement level, you can disable the sql_plan_baseline on your sql_id as follows

-- Example
DECLARE
  l_plans INTEGER;
BEGIN
    l_plans := DBMS_SPM.alter_sql_plan_baseline(sql_handle => <SQL_HANDLE_NAME>, plan_name => 'SQL_PLAN_8duuvuntm6gcf3bd84649', attribute_name => 'ENABLED', attribute_value => 'NO');
END;
/

If you need to ensure performance stability after disabling baselines, the recommendation is to apply a sql_patch instead of a sql_plan_baseline or a sql_profile

-- Example
DECLARE
  l_sqlpatch VARCHAR2(32000);
BEGIN
    l_sqlpatch := DBMS_SQLDIAG.CREATE_SQL_PATCH(sql_id => '2926rywsgjp8m', hint_text => q'[OPT_PARAM('optimizer_features_enable' '11.2.0.4')]', name => 'spch_2926rywsgjp8m');
END;
/

Alessandro

(Banner Image by Irvin John Mabli from Pixabay)