Execution Plans, Hints and Outlines in Oracle 18c

Today's post is about execution plans. This quick example using the HR sample schema, highlights the importance of using SQL Hints to influence the execution path.

Let me use below SQL as an example:

SELECT

    COUNT(*)

FROM

    hr.employees a,

    hr.departments b,

    hr.locations c

WHERE

    a.department_id = b.department_id

    AND b.location_id = c.location_id

    AND a.salary > 3000

    AND c.postal_code IS NOT NULL;

  COUNT(*)

----------

        79

Let me also produce the explain plan for this SQL using the 'ADVANCED' format:

explain plan SET statement_id = 'ex_plan' for SELECT

    COUNT(*)

FROM

    hr.employees a,

    hr.departments b,

    hr.locations c

WHERE

    a.department_id = b.department_id

    AND b.location_id = c.location_id

    AND a.salary > 3000

    AND c.postal_code IS NOT NULL;

SELECT PLAN_TABLE_OUTPUT

  FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan','ADVANCED'));

Explained.

PLAN_TABLE_OUTPUT

                                                                                                                                                                  -------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3098668569

---------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                  |     1 |    24 |     8  (13)| 00:00:01 |

|   1 |  SORT AGGREGATE                |                  |     1 |    24 |            |          |

|*  2 |   HASH JOIN                    |                  |   102 |  2448 |     8  (13)| 00:00:01 |

|   3 |    MERGE JOIN                  |                  |    27 |   459 |     5  (20)| 00:00:01 |

|*  4 |     TABLE ACCESS BY INDEX ROWID| LOCATIONS        |    22 |   220 |     2   (0)| 00:00:01 |

|   5 |      INDEX FULL SCAN           | LOC_ID_PK        |    23 |       |     1   (0)| 00:00:01 |

|*  6 |     SORT JOIN                  |                  |    27 |   189 |     3  (34)| 00:00:01 |

|   7 |      VIEW                      | index$_join$_002 |    27 |   189 |     2   (0)| 00:00:01 |

|*  8 |       HASH JOIN                |                  |       |       |            |          |

|   9 |        INDEX FAST FULL SCAN    | DEPT_ID_PK       |    27 |   189 |     1   (0)| 00:00:01 |

|  10 |        INDEX FAST FULL SCAN    | DEPT_LOCATION_IX |    27 |   189 |     1   (0)| 00:00:01 |

|* 11 |    TABLE ACCESS FULL           | EMPLOYEES        |   103 |   721 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

PLAN_TABLE_OUTPUT                                                                  

-------------------------------------------------------------------------------------------------------------------------------

   1 - SEL$1       

   4 - SEL$1        / C@SEL$1

   5 - SEL$1        / C@SEL$1

   7 - SEL$FD64DD72 / B@SEL$1

   8 - SEL$FD64DD72

   9 - SEL$FD64DD72 / indexjoin$_alias$_001@SEL$FD64DD72

  10 - SEL$FD64DD72 / indexjoin$_alias$_002@SEL$FD64DD72

  11 - SEL$1        / A@SEL$1

Outline Data

-------------

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------------------------

  /*+

      BEGIN_OUTLINE_DATA

      USE_HASH(@"SEL$1" "A"@"SEL$1")

      USE_MERGE(@"SEL$1" "B"@"SEL$1")

      LEADING(@"SEL$1" "C"@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")

      FULL(@"SEL$1" "A"@"SEL$1")

      INDEX_JOIN(@"SEL$1" "B"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID")

              ("DEPARTMENTS"."LOCATION_ID"))

      INDEX(@"SEL$1" "C"@"SEL$1" ("LOCATIONS"."LOCATION_ID"))

      OUTLINE_LEAF(@"SEL$1")

      OUTLINE_LEAF(@"SEL$FD64DD72")

      ALL_ROWS

      DB_VERSION('18.1.0')

      OPTIMIZER_FEATURES_ENABLE('18.1.0')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT                                                                                                                                                                                             

-------------------------------------------------------------------------------------------------------------------------------

   2 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")

   4 - filter("C"."POSTAL_CODE" IS NOT NULL)

   6 - access("B"."LOCATION_ID"="C"."LOCATION_ID")

       filter("B"."LOCATION_ID"="C"."LOCATION_ID")

   8 - access(ROWID=ROWID)

  11 - filter("A"."SALARY">3000)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

PLAN_TABLE_OUTPUT  

-------------------------------------------------------------------------------------------------------------------------------

   2 - (#keys=1)

   3 - (#keys=0) "B"."DEPARTMENT_ID"[NUMBER,22]

   4 - "C"."LOCATION_ID"[NUMBER,22]

   5 - "C".ROWID[ROWID,10], "C"."LOCATION_ID"[NUMBER,22]

   6 - (#keys=1) "B"."LOCATION_ID"[NUMBER,22], "B"."DEPARTMENT_ID"[NUMBER,22]

   7 - "B"."LOCATION_ID"[NUMBER,22], "B"."DEPARTMENT_ID"[NUMBER,22]

   8 - (#keys=1) "B"."DEPARTMENT_ID"[NUMBER,22], "B"."LOCATION_ID"[NUMBER,22]

   9 - ROWID[ROWID,10], "B"."DEPARTMENT_ID"[NUMBER,22]

  10 - ROWID[ROWID,10], "B"."LOCATION_ID"[NUMBER,22]

  11 - "A"."DEPARTMENT_ID"[NUMBER,22]

PLAN_TABLE_OUTPUT                                                                                     

-------------------------------------------------------------------------------------------------------------------------------

Note

-----

   - this is an adaptive plan

80 rows selected.

Ok, let's try to digest this humongous output.

The very first part of the report is the Plan Hash Value. This is a unique identifier and will be very useful to differentiate this execution plan from others that the CBO can produce.

Plan hash value: 3098668569

The second part is the graphical representation of the execution plan itself. Here we can see each operation and its actual cost, rows, bytes and time.

---------------------------------------------------------------------------------------------------

| Id  | Operation                                                | Name              | Rows  | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                                 |                                  |     1 |  24 |8 (13)| 00:00:01 |

|   1 |  SORT AGGREGATE                                  |                                  |     1 |  24 |            |          |

|*  2 |   HASH JOIN                                            |                                  |  102 |2448|8 (13)| 00:00:01 |

|   3 |    MERGE JOIN                                        |                                  |    27 | 459|5 (20)| 00:00:01 |

|*  4 |     TABLE ACCESS BY INDEX ROWID            | LOCATIONS                 |    22 | 220|2  (0)| 00:00:01 |

|   5 |      INDEX FULL SCAN                                | LOC_ID_PK                 |    23 |      |1   (0)| 00:00:01 |

|*  6 |     SORT JOIN                                          |                                  |    27 |189 |3  (34)|00:00:01 |

|   7 |      VIEW                                                 | index$_join$_002        |  27 |  189 |2 (0)| 00:00:01 |

|*  8 |       HASH JOIN                                        |                                  |      |                 |          |

|   9 |        INDEX FAST FULL SCAN                     | DEPT_ID_PK                |  27 |   189 |1(0)| 00:00:01 |

|  10 |        INDEX FAST FULL SCAN                     | DEPT_LOCATION_IX    |  27 | 189 |1 (0)| 00:00:01 |

|* 11 |    TABLE ACCESS FULL                             | EMPLOYEES                 |103 | 721 |3 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

  

This graphical representation is very useful and most of the times it can quickly help us drill down any potential problems in the path. Still from here is kind of difficult to identify what is the order the CBO is joining these 3 tables (well, is not actually difficult but imagine if you are joining 50 of them).

The next section is the text representation of the execution plan.  

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------------------------

   1 - SEL$1       

   4 - SEL$1        / C@SEL$1

   5 - SEL$1        / C@SEL$1

   7 - SEL$FD64DD72 / B@SEL$1

   8 - SEL$FD64DD72

   9 - SEL$FD64DD72 / indexjoin$_alias$_001@SEL$FD64DD72

  10 - SEL$FD64DD72 / indexjoin$_alias$_002@SEL$FD64DD72

  11 - SEL$1        / A@SEL$1

From this is clear that is following C, B and then A.

Now comes the Outline Data. Outlines are just a collection of hints pertaining to each SQL statement. The CBO uses this collection to maintain the same execution plan for each has plan value.

Outline Data

-------------

PLAN_TABLE_OUTPUT 

-------------------------------------------------------------------------------------------------------------------------------

  /*+

      BEGIN_OUTLINE_DATA

      USE_HASH(@"SEL$1" "A"@"SEL$1")

      USE_MERGE(@"SEL$1" "B"@"SEL$1")

      LEADING(@"SEL$1" "C"@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")

      FULL(@"SEL$1" "A"@"SEL$1")

      INDEX_JOIN(@"SEL$1" "B"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID")

              ("DEPARTMENTS"."LOCATION_ID"))

      INDEX(@"SEL$1" "C"@"SEL$1" ("LOCATIONS"."LOCATION_ID"))

      OUTLINE_LEAF(@"SEL$1")

      OUTLINE_LEAF(@"SEL$FD64DD72")

      ALL_ROWS

      DB_VERSION('18.1.0')

      OPTIMIZER_FEATURES_ENABLE('18.1.0')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Look how the LEADING hint is also showing the path (order) of the join, which is C, B and A.

LEADING(@"SEL$1" "C"@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")

Does this mean that I can alter the execution plan by adding a hint and forcing the order to be A, B and C as original intended?

Well, let's try it.

I'm going to explain below SQL:

explain plan SET statement_id = 'ex_plan1' for SELECT /*+ LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1" "C"@"SEL$1") */

    COUNT(*)

FROM

    hr.employees a,

    hr.departments b,

    hr.locations c

WHERE

    a.department_id = b.department_id

    AND b.location_id = c.location_id

    AND a.salary > 3000

    AND c.postal_code IS NOT NULL;

And here are some pieces of the output:

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2122977163

----------------------------------------------------------------------------------------------

| Id  | Operation                 | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |                  |     1 |    24 |     8   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE           |                  |     1 |    24 |            |          |

|*  2 |   HASH JOIN               |                  |    80 |  1920 |     8   (0)| 00:00:01 |

|*  3 |    HASH JOIN              |                  |    80 |  1120 |     5   (0)| 00:00:01 |

|*  4 |     TABLE ACCESS FULL     | EMPLOYEES        |    81 |   567 |     3   (0)| 00:00:01 |

|   5 |     VIEW                  | index$_join$_002 |    27 |   189 |     2   (0)| 00:00:01 |

|*  6 |      HASH JOIN            |                  |       |       |            |          |

|   7 |       INDEX FAST FULL SCAN| DEPT_ID_PK       |    27 |   189 |     1   (0)| 00:00:01 |

|   8 |       INDEX FAST FULL SCAN| DEPT_LOCATION_IX |    27 |   189 |     1   (0)| 00:00:01 |

|*  9 |    TABLE ACCESS FULL      | LOCATIONS        |    22 |   220 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1      

   4 - SEL$1        / A@SEL$1

   5 - SEL$FD64DD72 / B@SEL$1

   6 - SEL$FD64DD72

   7 - SEL$FD64DD72 / indexjoin$_alias$_001@SEL$FD64DD72

   8 - SEL$FD64DD72 / indexjoin$_alias$_002@SEL$FD64DD72

   9 - SEL$1        / C@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

PLAN_TABLE_OUTPUT 

-------------------------------------------------------------------------------------------------------------------------------

      USE_HASH(@"SEL$1" "C"@"SEL$1")

      USE_HASH(@"SEL$1" "B"@"SEL$1")

      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1" "C"@"SEL$1")

      FULL(@"SEL$1" "C"@"SEL$1")

      INDEX_JOIN(@"SEL$1" "B"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID")

              ("DEPARTMENTS"."LOCATION_ID"))

      FULL(@"SEL$1" "A"@"SEL$1")

      OUTLINE_LEAF(@"SEL$1")

      OUTLINE_LEAF(@"SEL$FD64DD72")

      ALL_ROWS

      DB_VERSION('18.1.0')

      OPTIMIZER_FEATURES_ENABLE('18.1.0')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

See now how the Hash Plan Value changed?

The execution plan and outline information also show that the order on how the CBO joined the tables changed from C, B and A to A, B and C.

Knowing how to read execution plans and the understanding of paths and outlines is really useful, especially in emergency situations where a sub-optimal plan is affecting production.

Thanks,

Alfredo