Oracle SQL 性能调优：使用SqlPatch固定执行计划

【免责声明】本公众号文章仅代表个人观点，与任何公司无关。

今天我们来做一个使用SqlPatch固定执行计划的TEST.

1.做TEST用TABLE.

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.观察SQLPLAN

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

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  TAB2@SEL$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]

37行

复制

3.通过Hint做成使用Index Scan的SQLPLAN

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

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  TAB2@SEL$1
   2 - SEL$1  TAB2@SEL$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]

40行

复制

4.把Hint“INDEX_RS_ASC(@"SEL1" "TAB2"@"SEL1""TAB2"@"SEL1" ("TAB2"."C2"))”作成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


复制

5.看看结果。

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行。

実行計画
----------------------------------------------------------
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
复制

后续文章更加精彩，欢迎关注本公众号或访问【阅读原文】。

手把手系列（帮助个人技术成长）：

SQL调优和诊断从哪入手？

获取SQL执行计划最基础的方法是啥？

一学就会的获取SQL执行计划和性能统计信息的方法

在线Oracle SQL学习环境--Live SQL

公众号2020年总结