[20190221]sql patch 问题.txt

[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等等。