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