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