Yesterday I was asked to look into an query performance issue with a legacy product which is still running on Oracle 11g (11.2.0.4). The query has a hard-coded hint which caused a full table scan, and in turn, that caused a lot of I/O because the table is quite large. Since this is a legacy application, there is no development or fix on it which means the sql text itself is untouchable.

Fortunately, Oracle 11g has introduced a feature SQL Patch which is a SQL managebility object generated by SQL Repair Advisor. It is managed through the PL/SQL package DBMS_SQLDIAG. After a SQL statement fails with a critical error, SQL Patch can circumvent the failure with a certain execution plan by directing the query optimizer to choose an alternate execution plan for future executions.

With 11g & 12.1, Oracle provides an undocumented package called DBMS_SQLDIAG_INTERNAL. It contains the function I_CREATE_PATCH that can add/disable hints using SQL Patch. From 12.2, a public function CREATE_SQL_PATCH is available in the package DBMS_SQLDIAG for the same purpose. It is just mapped from I_CREATE_PATCH with overloading to support SQL_ID as one of parameters.

Below I am using an example to show the usage of DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH in 11g. Because it doesn’t accept SQL_ID, we need a small PL/SQL block to find the SQL text from SQL_ID.

1. This TEST_TAB table has 13147263 entries. As you can see, when doing a count(*), it will use INDEX FAST FULL SCAN on the primary key.

09:06:32 SQL> set autotrace on
09:06:40 SQL> select count(*) from TEST_TAB;

  COUNT(*)
----------
  13147263

Elapsed: 00:00:00.78

Execution Plan
----------------------------------------------------------
Plan hash value: 1132661444

----------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |  8163   (1)| 00:01:38 |
|   1 |  SORT AGGREGATE       |            |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| TEST_TAB_PK |  9685K|  8163   (1)| 00:01:38 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      43104  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

2. Now I am going to force it using FULL table scan with a hint.

09:07:45 SQL> select /*+ FULL(TEST_TAB) */ count(*) from TEST_TAB;

  COUNT(*)
----------
  13147274

Elapsed: 00:00:02.53

Execution Plan
----------------------------------------------------------
Plan hash value: 267356494

----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |   155K  (1)| 00:31:07 |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_TAB |  9685K|   155K  (1)| 00:31:07 |
----------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     669242  consistent gets
       6117  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

3. Find the SQL ID gmqgavsrtjxwr from v$sql and note the plan hash value which matches the hash value above from 2.

select sql_id, sql_text from v$sql where sql_text like '%FULL%TEST_TAB';

09:24:37 SQL> select sql_id, hash_value, ELAPSED_TIME,child_number, EXECUTIONS, plan_hash_value, LAST_ACTIVE_TIME, ELAPSED_TIME/EXECUTIONS each from v$sql where sql_id='gmqgavsrtjxwr'


SQL_ID        HASH_VALUE ELAPSED_TIME CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE LAST_ACTIVE_TIME          EACH
------------- ---------- ------------ ------------ ---------- --------------- ------------------- ----------
gmqgavsrtjxwr  798554007      9507233            0          4       267356494 2020-03-05 09:24:02 2376808.25

4. Run the following PL/SQL blocks and provide the sql_id gmqgavsrtjxwr. The first one will get the exact sql text based on sql_id. The second one will create the SQL Patch with the name “gmqgavsrtjxwr” same as the sql_id. With Oracle 12.2, we can use sql_id directly in DBMS_SQLDIAG.CREATE_SQL_PATCH.

var sql_st clob
exec SELECT sql_fulltext INTO :sql_st FROM v$sql WHERE sql_id='&SQL_ID';


BEGIN
	sys.dbms_sqldiag_internal.i_create_patch (
              sql_text => :sql_st,
              hint_text => 'IGNORE_OPTIM_EMBEDDED_HINTS',
              name => '&SQL_ID');
END;
/

5. Once the SQL Patch is created, run the same query with FULL table scan hint again. You will see the plan has changed not to use FULL table scan, but back to INDEX FAST FULL SCAN on the primary key. Note in v$sql, the plan hash value matches.

09:29:30 SQL> select /*+ FULL(TEST_TAB) */ count(*) from TEST_TAB;

  COUNT(*)
----------
  13147392

Elapsed: 00:00:00.89

Execution Plan
----------------------------------------------------------
Plan hash value: 1132661444

----------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |  8163   (1)| 00:01:38 |
|   1 |  SORT AGGREGATE       |            |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| TEST_TAB_PK |  9685K|  8163   (1)| 00:01:38 |
----------------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      43104  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

09:29:02 SQL> select sql_id, hash_value, ELAPSED_TIME,child_number, EXECUTIONS, plan_hash_value, LAST_ACTIVE_TIME, ELAPSED_TIME/EXECUTIONS each from v$sql where sql_id='gmqgavsrtjxwr'

SQL_ID        HASH_VALUE ELAPSED_TIME CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE LAST_ACTIVE_TIME          EACH
------------- ---------- ------------ ------------ ---------- --------------- ------------------- ----------
gmqgavsrtjxwr  798554007       711911            0          1      1132661444 2020-03-05 09:29:16     711911

Elapsed: 00:00:00.00

If you need to see the information about the SQL Patch, you can query the view dba_sql_patches. And later if you don’t need it, you can drop it with DBMS_SQLDIAG.DROP_SQL_PATCH

exec sys.DBMS_SQLDIAG.DROP_SQL_PATCH(name => '&SQL_ID');

Quite handy way to overcome hard-coded hint, huh?

• Oracle