SQL Patch another example

In a post I wrote last year about SQL Patch, I only showed how to use SQL Patch to influence the execution plan of a query with hint injection which disables the existing hardcoded hint. Here I am going to use another example to show how to add a new hint.

With Oracle 19c, create_sql_patch from the package dbms_sqldiag can be used. For the following query, an index full scan is used.


15:06:30 SQL> select count(*) from emp t;

  COUNT(*)
----------
    851010

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 3505053556

--------------------------------------------------------------------------------------
| Id  | Operation        | Name                      | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                           |     1 |   135   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |                           |     1 |            |          |
|   2 |   INDEX FULL SCAN| IX_HOR_SCHED_CREATED_TIME |   880K|   135   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


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

Now I am giving it a full table scan hint. First find the query SQL ID.

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 in 
(select sql_id from v$sql where sql_fulltext like 'select count(*) from emp%')  
and executions>0 order by LAST_ACTIVE_TIME;

Then check its execution plan:

14:30:27 SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('gh6dnmvduw84m', 0, FORMAT=>'OUTLINE BASIC NOTE'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(*) from emp t

Plan hash value: 3505053556

------------------------------------------------------
| Id  | Operation        | Name                      |
------------------------------------------------------
|   0 | SELECT STATEMENT |                           |
|   1 |  SORT AGGREGATE  |                           |
|   2 |   INDEX FULL SCAN| IX_HOR_SCHED_CREATED_TIME |
------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('optimizer_index_cost_adj' 1)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T"@"SEL$1" "IX_HOR_SCHED_CREATED_TIME")
      END_OUTLINE_DATA
  */


29 rows selected.

Now, create a SQL Patch with the full table scan hint. The trick here is the hint must include query block names such as @”SEL$1″. For example the output above has

INDEX(@"SEL$1" "T"@"SEL$1" "IX_HOR_SCHED_CREATED_TIME")

Without it, the hint won’t be resolvable by the optimizer. And you will see the SQL Patch is used when checking the executuion plan, but there will be a Hint report section which shows somethig like below.

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (N - Unresolved (1))
---------------------------------------------------------------------------
   1 -  SEL$E41D89D5
         N -  USE_HASH(tab1 tab2)

Use the PL/SQL block below to create the SQL Patch for “select /*+ FULL(t) */ count(*) from emp t;” As you can see, the hint text is ‘FULL(@”SEL$1″ “T”@”SEL$1”)‘ with @”SEL$1″ attached.

DECLARE
  l_patch_name  VARCHAR2(32767);
BEGIN

  -- SQL ID
  l_patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
    sql_id    => 'gh6dnmvduw84m',
    hint_text => 'FULL(@"SEL$1" "T"@"SEL$1")',
    name      => 'gh6dnmvduw84m');
END;
/

14:31:32 SQL> select name, CATEGORY, SIGNATURE, CREATED, status , SQL_TEXT from dba_sql_patches where name='gh6dnmvduw84m';

NAME            CATEGORY                     SIGNATURE CREATED                            STATUS   SQL_TEXT
--------------- --------------- ---------------------- ---------------------------------- -------- ----------------------------------------
gh6dnmvduw84m   DEFAULT            3913986059900980868 12-FEB-21 02.31.31.854625 PM       ENABLED  select count(*) from emp t

Elapsed: 00:00:00.01

Now let’s run the query again, index full scan is gone and a full table scan is used with indication “SQL patch “gh6dnmvduw84m” used for this statement” and there is no Hint report section because it succeeds. If for whatever reason there is something wrong with the hint, you will see the Hint report section.

14:31:57 SQL> set autotrace on
14:32:01 SQL> select count(*) from emp t;

  COUNT(*)
----------
    871796

Elapsed: 00:00:26.79

Execution Plan
----------------------------------------------------------
Plan hash value: 2634367177

--------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     1 |   364K  (1)| 00:00:15 |
|   1 |  SORT AGGREGATE    |                   |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP |   860K|   364K  (1)| 00:00:15 |
--------------------------------------------------------------------------------

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


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

Checking outline:

14:33:32 SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('gh6dnmvduw84m', 0, FORMAT=>'OUTLINE BASIC NOTE'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(*) from emp t

Plan hash value: 2634367177

------------------------------------------------
| Id  | Operation          | Name              |
------------------------------------------------
|   0 | SELECT STATEMENT   |                   |
|   1 |  SORT AGGREGATE    |                   |
|   2 |   TABLE ACCESS FULL| EMP |
------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('optimizer_index_cost_adj' 1)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

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

Checking with ADVANCED option:

14:34:16 SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('gh6dnmvduw84m', 0, 'ADVANCED'));

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

Plan hash value: 2634367177

--------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |       |   364K(100)|          |
|   1 |  SORT AGGREGATE    |                   |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP |   860K|   364K  (1)| 00:00:15 |
--------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('optimizer_index_cost_adj' 1)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (rowset=1019)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   2 -  SEL$1 / T@SEL$1
           -  FULL(@"SEL$1" "T"@"SEL$1")

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

Query Block Registry:
---------------------

  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T]]></t><s><![C
        DATA[SEL$1]]></s></h></f></q>



59 rows selected.

Elapsed: 00:00:00.08

Also we can get the hint text from sqlobj$data

14:54:27 SQL> select EXACT_MATCHING_SIGNATURE from v$sql where sql_id = 'gh6dnmvduw84m';

EXACT_MATCHING_SIGNATURE
------------------------
     3913986059900980868

select cast(extractvalue(value(x), '/hint') as varchar2(60)) as outline_hints
from xmltable('/outline_data/hint'
	passing (select xmltype(comp_data) xml
				from sys.sqlobj$data
				where signature = 3913986059900980868)) x;

OUTLINE_HINTS
------------------------------------------------------------
FULL(@"SEL$1" "T"@"SEL$1")

select comp_data from sqlobj$data where signature = 3913986059900980868;

Drop the SQL Patch:

14:57:14 SQL> exec sys.DBMS_SQLDIAG.DROP_SQL_PATCH(name => 'gh6dnmvduw84m');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

Reference:

Oracle

JoeLi's TechLife

SQL Patch another example

One thought on “SQL Patch another example”

Leave a Reply Cancel reply

Share this:

Like this:

Related

One thought on “SQL Patch another example”

Leave a Reply Cancel reply