The last version of Oracle in which CHOOSE was officially supported as an OPTIMIZER_MODE parameter value was 9.2.
This is what the documentation of Oracle 9.2 says about it:
choose
The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available.
If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains only some statistics, then the cost-based approach is used, and the optimizer must guess the statistics for the subjects without any statistics. This can result in sub-optimal execution plans. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.
In subsequent versions CHOOSE does not appear in the documentation as a valid value, and the “What’s New in Oracle Performance?” chapter in the documentation of Oracle 10.1 includes this sentence:
Some consequences of the desupport of RBO are:
• CHOOSE and RULE are no longer supported as OPTIMIZER_MODE initialization parameter values …
But the Oracle database keeps using CHOOSE, and therefore keeps potentially using the RBO, internally, even in version 12c. And last week I hit a performance issue that was caused because of this fact and some poor circumstances.
Here is a simplified example, running on Oracle 12.1.0.2.
We have two tables – PARENTS and CHILDREN – with an “on delete cascade” foreign key constraint.
> create table parents (
id number not null primary key
);
Table created.
> insert into parents select rownum from dual connect by level<=10000;
10000 rows created.
> create table children (
id number not null primary key,
parent_id number not null references parents (id) on delete cascade,
filler varchar2(4000)
);
Table created.
> insert into children
select rownum,mod(rownum,10000)+1,lpad('x',4000,'x')
from dual
connect by level<=100000;
100000 rows created.
> commit;
Commit complete.
There is an index that supports the foreign key, and it is a function-based index. Note that the leading column of the index is simply the referencing column.
> create index children_idx on children (parent_id, substr(filler,1,10)); Index created.
Now let’s delete a record from the PARENTS table.
> alter system flush buffer_cache;
System altered.
> set timing on
> delete parents where id=1;
1 row deleted.
Elapsed: 00:00:27.80
> set timing off
> roll
Rollback complete.
Why did it take so much time?
When we delete a record from PARENTS, Oracle implicitly deletes the child records of this parent (because of the “on delete cascade” foreign key). This is the recursive SQL statement:
delete from "DEMO"."CHILDREN" where "PARENT_ID" = :1
Oracle uses the CHOOSE optimizer mode for the implicit deletion from CHILDREN.
> select value,isdefault from v$sql_optimizer_env where sql_id='f7j1aq9z8k6r1' and child_number=0 and name='optimizer_mode'; VALUE ISDEFAULT -------------------- --------- choose NO 1 row selected.
Unfortunately I forgot to gather statistics on the CHILDREN table. The lack of statistics means that Oracle chose to use the rule-based optimizer for the implicit DELETE statement.
And this means that it cannot use the CHILDREN_IDX index, because it is a function-based index.
> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('f7j1aq9z8k6r1',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f7j1aq9z8k6r1, child number 0
-------------------------------------
delete from "DEMO"."CHILDREN" where "PARENT_ID" = :1
Plan hash value: 3379301338
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | DELETE STATEMENT | |
| 1 | DELETE | CHILDREN |
|* 2 | TABLE ACCESS FULL| CHILDREN |
---------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PARENT_ID"=:1)
Note
-----
- rule based optimizer used (consider using cbo)
23 rows selected.
If I execute the DELETE FROM CHILDREN statement explicitly, the CBO chooses the good execution plan that uses the index, because the statistics on the index have been automatically gathered during the index creation. It’s the missing statistics on the table that caused the implicit (recursive) DELETE statement to use the RBO.
> alter system flush buffer_cache; System altered. > set timing on > delete from "DEMO"."CHILDREN" where "PARENT_ID" = 1; 10 rows deleted. Elapsed: 00:00:00.21 > set timing off > select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 0cm664fx8b944, child number 0 ------------------------------------- delete from "DEMO"."CHILDREN" where "PARENT_ID" = 1 Plan hash value: 1030488021 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | | | 2 (100)| | | 1 | DELETE | CHILDREN | | | | | |* 2 | INDEX RANGE SCAN| CHILDREN_IDX | 10 | 40100 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("PARENT_ID"=1) 19 rows selected.
Even if I didn’t have statistics on the index, the CBO would still choose the index for performing the explicit DELETE, because it would use dynamic sampling:
> exec dbms_stats.delete_index_stats (user,'CHILDREN_IDX')
PL/SQL procedure successfully completed.
> delete from "DEMO"."CHILDREN" where "PARENT_ID" = 3;
10 rows deleted.
> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a8x8wszgx1g6r, child number 0
-------------------------------------
delete from "DEMO"."CHILDREN" where "PARENT_ID" = 3
Plan hash value: 1030488021
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 3 (100)| |
| 1 | DELETE | CHILDREN | | | | |
|* 2 | INDEX RANGE SCAN| CHILDREN_IDX | 1066 | 2111K| 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PARENT_ID"=3)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
23 rows selected.
Finally, if the index were a regular one rather than function-based, then a good plan would have been chosen for the recursive SQL, because the RBO prefers an index range scan over a full table scan:
> drop index children_idx;
Index dropped.
> create index children_idx on children (parent_id, id);
Index created.
> alter system flush buffer_cache;
System altered.
> set timing on
> delete parents where id=4;
1 row deleted.
Elapsed: 00:00:00.14
> set timing off
> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('f7j1aq9z8k6r1',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f7j1aq9z8k6r1, child number 0
-------------------------------------
delete from "DEMO"."CHILDREN" where "PARENT_ID" = :1
Plan hash value: 1030488021
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | DELETE STATEMENT | |
| 1 | DELETE | CHILDREN |
|* 2 | INDEX RANGE SCAN| CHILDREN_IDX |
------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PARENT_ID"=:1)
Note
-----
- rule based optimizer used (consider using cbo)
23 rows selected.