Oracle — sqlpatch of fixed execution plan

Time:2021-7-20

Today, let’s do a test using sqlpatch fixed execution plan

1. Use table for test

create table tab2(c1 number, c2 number, c3 varchar2(10));
declare
  a number;
begin
  a := 1;
  for i in 1 .. 50 loop
    for j in 1 .. 100 loop
      insert into tab2 values(a,j,'a');
      commit;
      a := a+1;
    end loop;
  end loop;
end;
/
create index ind2_2 on tab2(c2);

2. Observe sqlplan

SQL> conn test/test
SQL> explain plan for select * from tab2 where c2=1;

The analysis of the meaning of the word されました.

SQL> select * from table (dbms_xplan.display(format=>'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2156729920

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    50 |   450 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB2 |    50 |   450 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - SEL$1 / [email protected]$1

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

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "TAB2"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.4')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C2"=1)

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

   1 - "TAB2"."C1"[NUMBER,22], "C2"[NUMBER,22], "TAB2"."C3"[VARCHAR2,10]

In line 37, you can choose the right one.

3. Create sqlplan using index scan through hint

SQL> explain plan for select /*+ index(tab2 ind2_2) */ * from tab2 where c2=1;

The analysis of the meaning of the word されました.

SQL> select * from table (dbms_xplan.display(format=>'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3201770281

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    50 |   450 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB2   |    50 |   450 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND2_2 |    50 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - SEL$1 / [email protected]$1
   2 - SEL$1 / [email protected]$1

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

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$1" "TAB2"@"SEL$1" ("TAB2"."C2")) 
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.4')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"=1)

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

   1 - "TAB2"."C1"[NUMBER,22], "C2"[NUMBER,22], "TAB2"."C3"[VARCHAR2,10]
   2 - "TAB2".ROWID[ROWID,10], "C2"[NUMBER,22]

In 40 rows, choose the "right" and "right".

4. Put hint “index” into_ RS_ ASC (@ “sel $1” “TAB2” @ “sel $1” (“TAB2.” C2 “)) is used as sqlpatch.

SQL> connect / as sysdba
SQL> begin
 dbms_sqldiag_internal.i_create_patch (
  sql_text => 'select * from tab2 where c2=1',
  hint_text => 'INDEX_RS_ASC(@"SEL$1" "TAB2"@"SEL$1" ("TAB2"."C2"))',
  name => 'test patch'
 );
end;
/  2    3    4    5    6    7    8

PL / SQL is in normal state.

5. Look at the results.

SQL> conn test/test
SQL> set lin 120 pages 999
SQL> set autot on explain
SQL> select * from tab2 where c2=1;

        C1         C2 C3
---------- ---------- ------------------------------
       601          1 a
      ... ...
      
      4101          1 a

50 lines, select the first line.

Planning for travel
----------------------------------------------------------
Plan hash value: 3201770281

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    50 |   450 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB2   |    50 |   450 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND2_2 |    50 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"=1)

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