Oracle: A possible method to address a slow query if there is time pressure

Sometimes there is no time for a long analysis of a slow query and a fix (workaround) has to be provided asap. In such cases it often helps to check if a query did run faster with a previous OPTIMIZER_FEATURES_ENABLE-setting. If that is the case, then you also want to find out which optimizer bug fix caused a suboptimal plan to be generated. The following blog shows a way to find a quick workaround for a slowly performing query. However, please consider this to be a workaround and that you still should do an analysis to fix the “real” root cause.

So, let’s consider that you have a performance issue with a query and you want to test if an optimizer bug fix caused a suboptimal plan. How can you find the optimizer bug fix which caused that plan asap?

REMARK: I do assume that you have a script /tmp/sql.sql with the sql-query to reproduce the slowly running SQL.

Let’s get started to find the optimizer bug-fix:

Problem: Query is running slow in 19c. The query has been provided in the script /tmp/sql.sql

Currently we are on 19.26. and do have OPTIMIZER_FEATURES_ENABLE set to the default, which is 19.1.0 in 19c:

SQL> show spparameter optimizer_features_enable

SID	 NAME			       TYPE	   VALUE
-------- ----------------------------- ----------- ----------------------------
*	 optimizer_features_enable     string

–> not explicitly set in the spfile, so it’s set to default:

SQL> show parameter optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      19.1.0

SQL> show parameter fix_control
SQL> 

–> no _fix_control-parameter set.

According Active Session History (ASH) the query did run for days in the past (!!!):

SQL>  select sql_exec_start, sql_plan_hash_value, (count(*)*10)/3600 active_hours, max(sample_time)-min(sample_time) duration
  2  from dba_hist_active_sess_history
  3  where sql_id='8cdydzbsh10dd'
  4  group by sql_exec_start, sql_plan_hash_value
  5  order by 1;
 
SQL_EXEC_START       SQL_PLAN_HASH_VALUE ACTIVE_HOURS DURATION
-------------------- ------------------- ------------ ----------------------------
05-APR-2025 07:36:03           126619261        80.12 +000000003 08:14:27.685
07-APR-2025 07:36:07           126619261        32.33 +000000001 08:22:43.551
08-APR-2025 16:06:00           126619261        47.68 +000000001 23:45:26.074
10-APR-2025 15:57:22           126619261        17.76 +000000000 17:47:30.125
19-APR-2025 05:32:25           126619261       178.23 +000000007 10:31:45.271
21-APR-2025 05:32:46           126619261       134.60 +000000005 14:48:49.602
23-APR-2025 05:33:08           126619261        86.01 +000000003 14:09:08.540
 
7 rows selected.

REMARK: Please consider that using ASH requires the diagnostics pack to be licensed

1. Check if there is an Optimizer-Version-Setting, where the issue did not happen

We can test with different OPTIMIZER_FEATURES_ENABLE (OFE) settings, on what release this query may initially became slow with. But what different OPTIMIZER_FEATURES_ENABLE settings do we have? There’s a simple method to find that out. You just provide a non-existing OFE and the error tells you what OFE-settings are available:

SQL> alter session set optimizer_features_enable=blabla;
ERROR:
ORA-00096: invalid value BLABLA for parameter optimizer_features_enable, must
be from among 19.1.0.1, 19.1.0, 18.1.0, 12.2.0.1, 12.1.0.2, 12.1.0.1, 11.2.0.4,
11.2.0.3, 11.2.0.2, 11.2.0.1, 11.1.0.7, 11.1.0.6, 10.2.0.5, 10.2.0.4, 10.2.0.3,
10.2.0.2, 10.2.0.1, 10.1.0.5, 10.1.0.4, 10.1.0.3, 10.1.0, 9.2.0.8, 9.2.0,
9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4, 8.1.3, 8.1.0, 8.0.7, 8.0.6, 8.0.5,
8.0.4, 8.0.3, 8.0.0

So now we can go backwards with OFE-settings to find a version where the query may have run fast:

alter session set OPTIMIZER_FEATURES_ENABLE='18.1.0';
@/tmp/sql.sql

--> Ctrl-C after some time.

alter session set OPTIMIZER_FEATURES_ENABLE='12.2.0.1';
@/tmp/sql.sql

--> Ctrl-C after some time.

alter session set OPTIMIZER_FEATURES_ENABLE='12.1.0.2';
@/tmp/sql.sql

--> Ctrl-C after some time.

alter session set OPTIMIZER_FEATURES_ENABLE='12.1.0.1';
@/tmp/sql.sql

--> Ctrl-C after some time.

alter session set OPTIMIZER_FEATURES_ENABLE='11.2.0.4';
@/tmp/sql.sql

--> Ctrl-C after some time.

alter session set OPTIMIZER_FEATURES_ENABLE='11.2.0.3';
@/tmp/sql.sql

--> Ctrl-C after some time.

alter session set OPTIMIZER_FEATURES_ENABLE='11.2.0.2';
@/tmp/sql.sql

--> Ctrl-C after some time.

alter session set OPTIMIZER_FEATURES_ENABLE='11.2.0.1';
@/tmp/sql.sql

Elapsed: 00:00:00.36

--> With 11.2.0.1 the query finished in 0.36 secs.

I.e. a change between 11.2.0.1 and 11.2.0.2 caused the optimizer to produce a suboptimal plan.

2. Test which bug fix caused the suboptimal plan

We can generate a script, which tests all _fix_control-settings which were introduced with OFE=11.2.0.2 to see what fix caused the query to run slow:

set lines 200 pages 999 trimspool on
spool /tmp/sql_fc.sql

select 'alter session set "_fix_control"='''||to_char(bugno)||':'||to_char(value)||''';'||chr(10)||
       'PROMPT '||to_char(bugno)||chr(10)||
       '@/tmp/sql.sql' 
from v$system_fix_control 
where OPTIMIZER_FEATURE_ENABLE='11.2.0.2';

...

spool off

REMARK: The CHR(10) inserts the necessary linefeeds in the script.

SQL> !vi /tmp/sql_fc.sql
--> remove all lines, which are not necessary
SQL> !cat /tmp/sql_fc.sql
alter session set "_fix_control"='6913094:1';
PROMPT 6913094
@/tmp/sql.sql

alter session set "_fix_control"='6670551:1';
PROMPT 6670551
@/tmp/sql.sql

...

alter session set "_fix_control"='9407929:1';
PROMPT 9407929
@/tmp/sql.sql

alter session set "_fix_control"='10359631:1';
PROMPT 10359631
@/tmp/sql.sql

SQL> 

First I do set OFE to 11.2.0.1 and then run my generated script to enable the fixes one after the other until it becomes slow:

SQL> alter session set optimizer_features_enable='11.2.0.1';
SQL> @?/tmp/sql_fc.sql

Session altered.

8602840

1 row selected.


Session altered.

8725296

1 row selected.

...

Session altered.

9443476

1 row selected.


Session altered.

9195582

--> after enabling fix for bugno 9195582 there is no output generated anymore, i.e. the query becomes slow.

So, we could identify the fix for bugno 9195582, which causes the optimizer to produce a suboptimal plan.

3. Details about bug 9195582 and the implementation of a workaround

The description field of v$system_fix_control provides more details about the bug-fix:

SQL> select description from v$system_fix_control where bugno=9195582;

DESCRIPTION
----------------------------------------------------------------
leaf blocks as upper limit for skip scan blocks
SQL> 

Searching in My Oracle SUpport for the bug resulted in MOS Note

Bug 9195582 – Skip Scan overcosted when an index column has high NDV (Doc ID 9195582.8)

In there we have the following description:

Symptoms:

Performance Of Query/ies Affected 

Description

The estimate for skip scan blocks will now be no more than leaf blocks.
Index skip scans will be used more often than previously.

REDISCOVERY INFORMATION:
If an index skip scan is not being selected for a query due to having a
high cost and one of the skip scan index columns has NDV which is higher
than the number of leaf blocks of the index then you may be facing this
bug.

Workaround
Force the skip scan using a hint.

Interestingly the faster plan did not have a skip scan in it, but a change in the estimation of blocks for skip scans, may of course also lead to other plans being skipped or considered.

At this point I did a relogin to the DB and ran my query with the appropriate fix turned off to verify that the query runs fast:

SQL> select value from v$session_fix_control where bugno=9195582;

     VALUE
----------
         1

SQL> alter session set "_fix_control"='9195582:OFF';

Session altered.

SQL> select value from v$session_fix_control where bugno=9195582;

     VALUE
----------
         0

SQL> @/tmp/sql.sql

Elapsed: 00:00:00.36

--> OK, it works around the issue.

For that workaround to become active, we can implement a SQL patch for the query with the issue. As I did not have the query anymore in the shared pool, I had to take the query text from Automatic Workload Repository (AWR) to create the SQL Patch:

REMARK: Again, please consider that AWR requires to diagnostics pack to be licensed.

set serveroutput on
declare
        v1      varchar2(128);
        v_sql   clob;
begin
        select sql_text into v_sql from dba_hist_sqltext where sql_id='8cdydzbsh10dd';
        v1 :=   dbms_sqldiag.create_sql_patch(
                        sql_text  => v_sql,
                        hint_text => q'{opt_param('_fix_control' '9195582:OFF')}',
                        name    => 'switch_off_fix_9195582'
                );
        dbms_output.put_line(v1);
end;
/
switch_off_fix_9195582

PL/SQL procedure successfully completed.

Later on you may check if the query runs fast by checking the data in the shared pool or in the AWR history:

select executions, (elapsed_time/executions)/1000000 avg_elapsed_secs, sql_patch from v$sql where sql_id='8cdydzbsh10dd';

Summary:

Sometimes you have to be fast to implement a workaround for a slowly running query. One option is to check if a query did run faster with older OPTIMIZER_FEATURES_ENABLE-settings and, if that is the case, identify the bugno which caused a suboptimal plan to be produced. Always implement only the smallest change possible (just disable a bug fix instead of going back to a previous OFE-setting) and change as local to the problem as possible (i.e. add a hint or a SQL Patch to a query and, if possible, do not change a parameter on session or even system level). And finally, document your change and try to get rid of that workaround as soon as you have time to do a deeper analysis.

The Oracle Support tool SQLTXPLAIN (see MOS Note “All About the SQLT Diagnostic Tool (Doc ID 215187.1)”) contains the XPLORE utility, which goes much deeper for a single SQL-statement and checks the plan change caused by all “_fix_control”-settings and optimizer-changes (underscore parameters) between releases and produces html-output.