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