SQL Patches – what hints do they exactly use?

There are a couple of blog posts out there that describe how to inject hints into given SQL statements without modifying anything inside the code. One of these blog posts was written by the Optimizer Team. There is quite a lot of information of how to create SQL Patches and how to check the execution plans for the usage of these SQL Patches, but I wasn’t really able to find out where the details are stored inside the database.

Let’s walk through it. First things first, the base query:

SQL> conn scott/tiger
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> select * from table(dbms_xplan.display_cursor('g59vz2u4cu404', 0, 'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------SQL_ID  g59vz2u4cu404, child number 0
-------------------------------------
select count(*) from emp

Plan hash value: 2937609675

--------------------------------------------
| Id  | Operation        | Name   | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT |        |        |
|   1 |  SORT AGGREGATE  |        |      1 |
|   2 |   INDEX FULL SCAN| PK_EMP |     14 |
--------------------------------------------

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

You see, I executed a query and wanted to see the exact execution statistics. Since they are not gathered, the DBMS_XPLAN output notifies me with the information how to get these information.
Assuming that we are not able to change the code to introduce the hint, I create a SQL Patch.

SQL> conn / as sysdba
Connected.
SQL> declare
  1    txt varchar2(2000);
  2  begin
  3    select sql_fulltext into txt
  4    from v$sql where sql_id = 'g59vz2u4cu404';
  5    dbms_sqldiag_internal.i_create_patch(
  6      sql_text => txt,
  7      hint_text => 'gather_plan_statistics',
  8      name => 'my_patch'
  9    );
 10  end;
 11  /

PL/SQL procedure successfully completed.

I used V$SQL to get the exact SQL text for creating the SQL patch.

Now the query is using my newly created SQL patch:

SQL> conn scott/tiger
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> select * from table(dbms_xplan.display_cursor('g59vz2u4cu404', 0, 'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------SQL_ID  g59vz2u4cu404, child number 0
-------------------------------------
select count(*) from emp

Plan hash value: 2937609675

-------------------------------------------------------------------------------------
| Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |      1 |        |      1 |00:00:00.01 |       1 |
|   1 |  SORT AGGREGATE  |        |      1 |      1 |      1 |00:00:00.01 |       1 |
|   2 |   INDEX FULL SCAN| PK_EMP |      1 |     14 |     14 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------

Note
-----
   - SQL patch "my_patch" used for this statement

But this does not show what the patch exactly does. There is a view DBA_SQL_PATCHES that has some information, but nothing about the hints in these patches.

SQL> desc dba_sql_patches
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                      NOT NULL VARCHAR2(128)
 CATEGORY                                  NOT NULL VARCHAR2(128)
 SIGNATURE                                 NOT NULL NUMBER
 SQL_TEXT                                  NOT NULL CLOB
 CREATED                                   NOT NULL TIMESTAMP(6)
 LAST_MODIFIED                                      TIMESTAMP(6)
 DESCRIPTION                                        VARCHAR2(500)
 STATUS                                             VARCHAR2(8)
 FORCE_MATCHING                                     VARCHAR2(3)
 TASK_ID                                            NUMBER
 TASK_EXEC_NAME                                     VARCHAR2(128)
 TASK_OBJ_ID                                        NUMBER
 TASK_FND_ID                                        NUMBER
 TASK_REC_ID                                        NUMBER

When looking at the definition of that view, we see a join between “sqlobj$” and “sqlobj$auxdata”. And the latter one has a column “COMP_DATA” which contains everything I wanted to know in XML format. To extract this information I use queries like this. First identify the SQL signature and then use this signature to get the SQL patch information.

SQL> conn / as sysdba
Connected.
SQL> set numformat 99999999999999999999999999
SQL> select EXACT_MATCHING_SIGNATURE from v$sql where sql_id = 'g59vz2u4cu404';

   EXACT_MATCHING_SIGNATURE
---------------------------
        2640606212120450132

SQL> select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
  1  from   xmltable('/outline_data/hint'
  2         passing (select xmltype(comp_data) xml
  3                  from   sys.sqlobj$data
  4                  where  signature = 2640606212120450132)) x;

OUTLINE_HINTS
--------------------------------------------------------------------------------
gather_plan_statistics

That’s it.

Finally, I get rid of the SQL patch by dropping it.

SQL> exec dbms_sqldiag.drop_sql_patch(name => 'my_patch');

PL/SQL procedure successfully completed.

Nice feature that we got, but remember that the use of DBMS_SQLDIAG package may require additional licensing. How that helps anyway.