Month: December 2018

Using SQL Patch To Inject Hints

In my previous post (Execution Plans, Hints and Outlines in Oracle 18c) we saw how to use a SQL Hint to modify the order in which the CBO joins the tables. Today I want to show you a cool feature to inject this change without re-writing your SQL statement.
Let’s use the same SQL statement:
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’));
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                    |                  |    80 |  1920 |     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        |    81 |   567 |     3   (0)| 00:00:01 |
—————————————————————————————————
Query Block Name / Object Alias (identified by operation id):
————————————————————-
   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
————-
  /*+
      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
  */
The idea is to use the Hint /*+ LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”) */ without the need to actually write it into the SQL text.

In order to do this, Oracle provides a procedure called DBMS_SQLDIAG.CREATE_SQL_PATCH. 
We can invoke it using the SQL text or the SQL_ID of our statement. 

In my example, I’m going to use the SQL_ID 8b2gpq57hxdws.

Here will be the syntax to create the SQL patch:

DECLARE
  patch_name  VARCHAR2(32767);
BEGIN
  patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
    sql_id    => ‘8b2gpq57hxdws’,
    hint_text => ‘LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”)’,
    name      => ‘TEST_PATCH’);
END;
/
PL/SQL procedure successfully completed.
Now, let’s get the execution plan again:

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’));
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
      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
  */
Pretty cool, right?

As always, this is not the ultimate solution but it may buys us time to have the vendor or developers fix the code (hahaha, is not a joke!!!).

Anyway, another tool to keep in our radar after migrations, upgrades and major patches.

Thanks,
Alfredo

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

Exdata’s Cell Server Crashing

“RS-7445 [Serv CELLSRV hang detected] [It will be restarted] [] [] [] [] [] [] [] [] [] []”
“A kernel crash has caused the system to reboot.”
This ended up to be related to bug 25374245.
The workaround is to monitor the cell offload server resident memory and manually restart it before it gets above 40 GBs.
You can do this in rolling mode using below commands:
cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
cellcli -e alter cell restart services cellsrv;
More information can be found in below MOS note:
Exadata Cell Node Crashed with memory starvation (Doc ID 2421920.1)
Thanks,
Alfredo