Estimated execution plan alternatives

Question

As far as I understand, given a query, Oracle tries different approaches based on some heuristics, where I guess the most driving factor is minimizing the number of rows to work with, and then chooses the best one based on that and other factors. When you write EXPLAIN PLAN, it gives you the finally chosen plan.

Is it possible to see the other alternatives that Oracle has tried while choosing the estimated execution plan? To research things like: if I have a "theory" about this way being more efficient and I want to know why Oracle didn't try it that way, I could "explore" the other alternatives, look for the one that matches my expectations, and then compare their estimated costs and cardinalities to understand why Oracle thought the other plan was worst.

Is it possible to get more info about the rationale behind the chosen estimated plan and/or compare the different plan alternatives to understand its final decision?

Answer 1

You can do a 10053 trace.

alter session set events '10053 trace name context forever';

Then run the SQL.

The trace file generated will provide details info on why the optimizer made certain decisions. There are several blogs and docs out there that describe how to read and interpret it.

However, in most cases it isn't necessary to 10053 trace a parse. Most of Oracle's decision making is based on expected rows from each step in the plan, which drives decisions about the next step in the plan. By examining the anticipated # of rows reported in an execution plan you can usually understand why the next step is what it is, if you take into account what the options are given the structure available and the SQL.

Answer 2

View and modify outline data to generate alternative execution plans. The outline data is a set of hints that enforce specific execution plans. The package DBMX_XPLAN can generate outline hints, and those hints can be modified to disable a key part of an execution plan. By disabling a key part of an execution plan, which is often as simple as adding the prefix NO_ to the hint, Oracle will be forced to generate the next best execution plan.

Sample schema and query

This demonstration uses two simple tables with 1 million rows each. The values in the tables are simply the numbers 1 to 1000000. The first column is indexed, and statistics are gathered on both tables. The sample query is simply an equijoin between the two tables.

--drop table test1 purge;
--drop table test2 purge;

create table test1(a number not null, b number) nologging;
insert /*+ append */ into test1 select level, level from dual connect by level <= 1000000;
create index test1_idx on test1(a);

create table test2(a number not null, b number) nologging;
insert /*+ append */ into test2 select level, level from dual connect by level <= 1000000;
create index test2_idx on test2(a);

begin
    dbms_stats.gather_table_stats(user, 'TEST1');
    dbms_stats.gather_table_stats(user, 'TEST2');
end;
/

select *
from test1
join test2
    on test1.a = test2.a;

Best plan: hash joins

Since the join between these two tables returns a large percentage of rows, in this case 100%, a hash join and a full table scan is the best option. Notice how the execution plan is generated using the +outline option, which generates the Outline Data section. There are a lot of details in those undocumented hints and it's not always easy to understand all of them.

explain plan for
select *
from test1
join test2
    on test1.a = test2.a;

select * from table(dbms_xplan.display(format => '+outline'));

Plan hash value: 497311279
 
------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |  1000K|    19M|       |  3284   (2)| 00:00:01 |
|*  1 |  HASH JOIN         |       |  1000K|    19M|    20M|  3284   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST1 |  1000K|  9765K|       |   588   (3)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST2 |  1000K|  9765K|       |   588   (3)| 00:00:01 |
------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$58A6D7F6" "TEST2"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "TEST1"@"SEL$1" "TEST2"@"SEL$1")
      FULL(@"SEL$58A6D7F6" "TEST2"@"SEL$1")
      FULL(@"SEL$58A6D7F6" "TEST1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$1" >"SEL$2")
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("TEST1"."A"="TEST2"."A")
 
Note
-----
   - this is an adaptive plan

Second-best plan: hash join with new join order

Using the outline hints, the best plan can be disabled by changing USE_HASH into NO_USE_HASH. The new execution plan below is almost identical, except it switches the join order - TEST2 now comes before TEST1. The join order can often make a big difference, but it doesn't matter here because the two tables are identical.

explain plan for
select
  /*+
      BEGIN_OUTLINE_DATA
      NO_USE_HASH(@"SEL$58A6D7F6" "TEST2"@"SEL$1")
      END_OUTLINE_DATA
  */
   *
from test1
join test2
    on test1.a = test2.a;

select * from table(dbms_xplan.display(format => '+outline'));


Plan hash value: 3916441650
 
------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |  1000K|    19M|       |  3284   (2)| 00:00:01 |
|*  1 |  HASH JOIN         |       |  1000K|    19M|    20M|  3284   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST2 |  1000K|  9765K|       |   588   (3)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST1 |  1000K|  9765K|       |   588   (3)| 00:00:01 |
------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$58A6D7F6" "TEST1"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "TEST2"@"SEL$1" "TEST1"@"SEL$1")
      FULL(@"SEL$58A6D7F6" "TEST1"@"SEL$1")
      FULL(@"SEL$58A6D7F6" "TEST2"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$1" >"SEL$2")
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("TEST1"."A"="TEST2"."A")
 
Note
-----
   - this is an adaptive plan

Third-best plan: merge join

Using the same technique, an extra NO_USE_HASH hint excludes the second-best plan. Oracle is forced to try again and this time generates a plan with a merge join. Notice how the cost has increased from 3284 to 8957.

explain plan for
select
  /*+
      BEGIN_OUTLINE_DATA
      NO_USE_HASH(@"SEL$58A6D7F6" "TEST2"@"SEL$1")
      NO_USE_HASH(@"SEL$58A6D7F6" "TEST1"@"SEL$1")
      END_OUTLINE_DATA
  */
   *
from test1
join test2
    on test1.a = test2.a;

select * from table(dbms_xplan.display(format => '+outline'));

Plan hash value: 3601910386
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |  1000K|    19M|       |  8957   (2)| 00:00:01 |
|   1 |  MERGE JOIN                  |           |  1000K|    19M|       |  8957   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1     |  1000K|  9765K|       |  4330   (1)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | TEST1_IDX |  1000K|       |       |  2244   (1)| 00:00:01 |
|*  4 |   SORT JOIN                  |           |  1000K|  9765K|    38M|  4627   (2)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | TEST2     |  1000K|  9765K|       |   588   (3)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      USE_MERGE(@"SEL$58A6D7F6" "TEST2"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "TEST1"@"SEL$1" "TEST2"@"SEL$1")
      FULL(@"SEL$58A6D7F6" "TEST2"@"SEL$1")
      INDEX(@"SEL$58A6D7F6" "TEST1"@"SEL$1" ("TEST1"."A"))
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$1" >"SEL$2")
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("TEST1"."A"="TEST2"."A")
       filter("TEST1"."A"="TEST2"."A")

The more common use for outline hints is to take an execution plan from a good plan on a good system, and force that plan into a system that isn't working well. This approach is usually a bad idea, because it's best to work with the optimizer instead of forcing all of its decisions. But, in practice, there's not always time to find the root cause of optimizer problems, so copying and or modifying the outline hints can be a useful shortcut to fix a problem quickly.