Cost based optimizer is the most important piece of code in Oracle database as CBO is responsible for optimal execution of SQL queries.
CBO has been constantly developed, where each release introduces many new features/tricks and improvements.
As with any programming code, CBO is not immune on bugs in the code.
Good news is that Oracle has many features to fix issues with CBO.
One of those features is optimizer_features_enable parameter.
Hint from the title can be rarely seen in normal code, and belongs to hint category where the goal is to fix issues inside Cost Based Optimizer.
Most common case of optimizer_features_enable usage is to fix execution plan after database upgrade (which also means CBO upgrade) with main goal to revert execution plan as it was before the change.
This can also be accomplished by using underscore hints to disable some particular feature more precisely, but that can be seen in production rarely, only as a temporary solution till, for example scheduled downtime, when the patch can be applied, as CBO has been constantly developed with many new features introduced.
Recently developer has found a bug in CBO related to wrong results.
As can be seen from the code below, intention is to remove second part of the query while testing the first part (part above the minus), but the query returns no records at all which points to bug in CBO.
This is the problematic code:
SELECT SUPPLIER
from sups
where sup_name like 'XXXX%'
minus
SELECT SUPPLIER
FROM ORDHEAD
where order_no < 100000000
and 1=2;
Ver: 11.2.0.3.5
select * from table(dbms_xplan.display);
Plan hash value: 3244709878
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS ANTI | | 17 | 476 | 3848 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | SUPS | 1744 | 48832 | 207 (1)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| ORDHEAD | 5733K| 65M| 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | ORDHEAD_I2 | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
3 - filter("SUP_NAME" LIKE 'XXXX%')
4 - filter("ORDER_NO"<100000000)
5 - access("SUPPLIER"="SUPPLIER")
In this case, there is no even need to go deeper, as It is clear what is the problem with the execution plan.
In the line number 1 (FILTER) optimizer has applied the following short-cut statement:
1 – filter(NULL IS NOT NULL)
and this is the reason for getting the wrong results.
The same shortcut should be applied later, as can be seen from the execution plan with different versions of CBO.
In reality, the phase where CBO makes transformation and apply some new tricks to get the results faster are often the main cause of getting wrong results.
In such cases, I first check if the same query works properly with the newer and/or older version of database.
Ver: 11.2.0.4
Plan hash value: 1504942296
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 123 (3)| 00:00:02 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 2 | 48 | 122 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | SUPS | 2 | 48 | 121 (1)| 00:00:02 |
| 4 | SORT UNIQUE | | 1 | 12 | 1 (100)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| ORDHEAD | 1 | 12 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | PK_ORDHEAD | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("SUP_NAME" LIKE 'XXXX%')
5 - filter(null is not null)
7 - access("ORDER_NO"<100000000)
After the proper review, solution is simple:
1) Rewrite the query:
a)
SELECT SUPPLIER
from sups
where sup_name like 'XXXX%'
b)
SELECT /*+ optimizer_features_enable('11.2.0.3') */ SUPPLIER
from sups
where sup_name like 'XXXX%'
minus
SELECT /*+ optimizer_features_enable('11.2.0.3') */ SUPPLIER
FROM ORDHEAD
where order_no < 100000000
and 1=2;
Plan hash value: 3628054345
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 68 | 209 (2)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 2 | 56 | 208 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | SUPS | 2 | 56 | 207 (1)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 12 | 1 (100)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS FULL| ORDHEAD | 5733K| 65M| 47318 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("SUP_NAME" LIKE 'XXXX%')
5 - filter(NULL IS NOT NULL)
6 - filter("ORDER_NO"<100000000)
c)
SELECT SUPPLIER
from sups
where sup_name like 'XXXX%'
minus
SELECT SUPPLIER
FROM ORDHEAD
where order_no < 100000000
and rownum < 1;
2) Upgrade database by installing version of PSU or PatchSet where bug has been fixed (best solution if possible to schedule downtime).
3) Set the CBO version on:
a) SQL level (see 1a point): SELECT /*+ optimizer_features_enable(‘11.2.0.3’) */ SUPPLIER…
b) Session level: alter session set optimizer_features_enable = ‘11.2.0.3’;
c) System level (not recommended): alter system set optimizer_features_enable = ‘11.2.0.3’ scope = both;
Comments