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

Leave a Reply

Your email address will not be published. Required fields are marked *