ITPub博客

首页 > 数据库 > Oracle > [20190221]sql patch 问题.txt

[20190221]sql patch 问题.txt

原创 Oracle 作者:lfree 时间:2019-02-21 09:18:40 0 删除 编辑

[20190221]sql patch 问题.txt


--//链接 http://www.itpub.net/thread-2108398-1-1.html ,遇到的问题,重复测试看看.


1.环境:

SCOTT@book> @ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


SCOTT@book> create table t01 as select * from dba_objects;

Table created.


SCOTT@book> create index ind_t01_object_id on t01(object_id);

Index created.

--//注:我的测试不能分析,分析执行计划会选择索引.


BEGIN

  SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch(

    sql_text  => 'select * from t01 where object_id between 10000 and 30000',

    hint_text => 'INDEX(T01,IND_T01_OBJECT_ID)',

    name      => 't01_sql_patch');

END;

/


select * from t01 where object_id between 10000 and 30000;


--//可以发现依旧选择全表扫描.


BEGIN

    DBMS_SQLDIAG.drop_sql_patch(name =>'t01_sql_patch');

END;

/

--//这样不行.


select /*+ index(t01) */ * from t01 where object_id between 10000 and 30000;


SCOTT@book> @ dpc '' outline

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  8x42dpxgg1fgp, child number 0

-------------------------------------

select /*+ index(t01) */ * from t01 where object_id between 10000 and

30000


Plan hash value: 1880243119


--------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name              | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                   |        |       |   463 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T01               |  26993 |  5456K|   463   (1)| 00:00:06 |

|*  2 |   INDEX RANGE SCAN          | IND_T01_OBJECT_ID |  26993 |       |    62   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------


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

-------------------------------------------------------------


   1 - SEL$1 / T01@SEL$1

   2 - SEL$1 / T01@SEL$1


Outline Data

-------------


  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

      DB_VERSION('11.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_RS_ASC(@"SEL$1" "T01"@"SEL$1" ("T01"."OBJECT_ID"))

      END_OUTLINE_DATA

  */


Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID">=10000 AND "OBJECT_ID"<=30000)


--//注意提示是INDEX_RS_ASC(@"SEL$1" "T01"@"SEL$1" ("T01"."OBJECT_ID")).

--//改写如下,以sys用户执行.

BEGIN

  SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch(

    sql_text  => 'select * from t01 where object_id between 10000 and 30000',

    hint_text => 'INDEX_RS_ASC(@"SEL$1" "T01"@"SEL$1" ("T01"."OBJECT_ID"))',

    name      => 't01_sql_patch');

END;

/


select * from t01 where object_id between 10000 and 30000;


SCOTT@book> @ dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  043grz1fjh9s7, child number 0

-------------------------------------

select * from t01 where object_id between 10000 and 30000

Plan hash value: 1880243119

--------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name              | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                   |        |       |   463 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T01               |  26993 |  5456K|   463   (1)| 00:00:06 |

|*  2 |   INDEX RANGE SCAN          | IND_T01_OBJECT_ID |  26993 |       |    62   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

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

-------------------------------------------------------------

   1 - SEL$1 / T01@SEL$1

   2 - SEL$1 / T01@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID">=10000 AND "OBJECT_ID"<=30000)


-----

   - dynamic sampling used for this statement (level=2)

   - SQL patch "t01_sql_patch" used for this statement

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

33 rows selected.


--//OK,现在可以使用索引了.也就是sql patch不能使用平时的提示.

--//我个人认为最好使用sql profile,或者sql planbase...sql patch一般使用比较特殊的提示比如bind_ware,result_cache等等。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2636531/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2852
  • 访问量
    6639092