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

SQLplusDB

发布于 2022-08-19 20:16:28

46000

代码可运行

文章被收录于专栏：Oracle数据库技术Oracle数据库技术

运行总次数：0

代码可运行

编者按：

本文作者系Scott（中文名陈晓辉），现任大连华信资深分析师，ORACLE数据库专家，曾就职于甲骨文中国。个人主页：segmentfault.com/u/db_perf ，经其本人授权发布。

今天我们来做一个使用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

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-02-19，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自 SQL和数据库技术微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

登录后参与评论

0 条评论

热度

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

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐