[20150513]函数索引与CURSOR_SHARING=FORCE-白红宇

[20150513]函数索引与CURSOR_SHARING=FORCE.txt

--经常awr报表,大量听到的建议是你们的应用没有使用绑定变量.国内的许多项目这个问题更加严重,我敢打赌国内80%甚至更高的比例在

--应用中没有绑定变量(OLTP系统).

--如果一个新项目我只要看一下程序使用绑定变量的情况,就知道这个项目是垃圾还是豆腐渣工程.到目前为止我接触的项目仅仅有1个做

--的稍微好一点.

--如果不修改代码,一个最简单的方式就是修改参数CURSOR_SHARING = FORCE(补充一点:我个人不建议修改SIMILAR,实际上11G这个参数还

可以设置,但是无效的.),但是在这种情况下如果存在函数索引,可能导致这个参数修改可能带了另外的问题,就是索引无效,选择全表扫描.

--我这里举一个例子,说明另外的情况,参考链接如下,我仅仅重复测试:

SCOTT@test> @ver1

PORT_STRING VERSION BANNER

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

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

create table t

select * from all_objects;

create index t_idx on t (owner || ' ' || object_name);

exec dbms_stats.gather_table_stats(null, 't')

set echo on linesize 200 pagesize 0

alter session set cursor_sharing = force;

select /*+ full(t) */ * from t where owner || ' ' || object_name = 'BLA';

select * from table(dbms_xplan.display_cursor);

SCOTT@test> @dpc '' ''

PLAN_TABLE_OUTPUT

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

SQL_ID ar3tw7r1rvawk, child number 0

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

select /*+ full(t) */ * from t where owner || :"SYS_B_0" || object_name

= :"SYS_B_1"

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 193 (100)| |

|* 1 | TABLE ACCESS FULL| T | 1 | 118 | 193 (2)| 00:00:01 |

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

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

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

1 - SEL$1 / T@SEL$1

Predicate Information (identified by operation id):

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

1 - filter("OWNER"||' '||"OBJECT_NAME"=:SYS_B_1)

--注意看语句已经转换为select /*+ full(t) */ * from t where owner || :"SYS_B_0" || object_name = :"SYS_B_1".

--但是下面的filter条件filter("OWNER"||' '||"OBJECT_NAME"=:SYS_B_1),没有转换.

select /*+ index(t) */ * from t where owner || ' ' || object_name = 'BLA';

select * from table(dbms_xplan.display_cursor);

SCOTT@test> @dpc '' ''

PLAN_TABLE_OUTPUT

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

SQL_ID 6kzz3vw5x8x3b, child number 0

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

select /*+ index(t) */ * from t where owner || :"SYS_B_0" ||

object_name = :"SYS_B_1"

Plan hash value: 470836197

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

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

| 0 | SELECT STATEMENT | | | | 4 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 118 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 3 (0)| 00:00:01 |

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

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

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

1 - SEL$1 / T@SEL$1

2 - SEL$1 / T@SEL$1

Predicate Information (identified by operation id):

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

2 - access("T"."SYS_NC00016$"=:SYS_B_1)

--可以发现可以使用索引.奇怪的是语句已经转化为

--select /*+ index(t) */ * from t where owner || :"SYS_B_0" ||object_name = :"SYS_B_1".

select /*+ index(t) */ * from t where owner || 'A' || object_name = 'BLA';

select * from table(dbms_xplan.display_cursor);

SCOTT@test> @dpc '' ''

PLAN_TABLE_OUTPUT

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

SQL_ID 6kzz3vw5x8x3b, child number 1

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

select /*+ index(t) */ * from t where owner || :"SYS_B_0" ||

object_name = :"SYS_B_1"

Plan hash value: 3778778741

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

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

| 0 | SELECT STATEMENT | | | | 53682 (100)| |

|* 1 | TABLE ACCESS BY INDEX ROWID| T | 752 | 88736 | 53682 (1)| 00:00:02 |

| 2 | INDEX FULL SCAN | T_IDX | 75193 | | 432 (1)| 00:00:01 |

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

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

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

1 - SEL$1 / T@SEL$1

2 - SEL$1 / T@SEL$1

Predicate Information (identified by operation id):

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

1 - filter("OWNER"||:SYS_B_0||"OBJECT_NAME"=:SYS_B_1)

--依旧可以使用索引,也许是这个条件特殊.

--10g下我也测试看看:

SCOTT@test> @ &r/ver1

PORT_STRING VERSION BANNER

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

x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

--建表过程略.

select /*+ full(t) */ * from t where owner || ' ' || object_name = 'BLA';

SCOTT@test> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID ar3tw7r1rvawk, child number 0

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

select /*+ full(t) */ * from t where owner || :"SYS_B_0" || object_name

= :"SYS_B_1"

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 159 (100)| |

|* 1 | TABLE ACCESS FULL| T | 1 | 98 | 159 (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

1 - filter("OWNER"||' '||"OBJECT_NAME"=:SYS_B_1)

select /*+ index(t) */ * from t where owner || ' ' || object_name = 'BLA';

SCOTT@test> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 6kzz3vw5x8x3b, child number 0

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

select /*+ index(t) */ * from t where owner || :"SYS_B_0" || object_name =

:"SYS_B_1"

Plan hash value: 470836197

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 98 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("T"."SYS_NC00014$"=:SYS_B_1)

select /*+ index(t) */ * from t where owner || 'A' || object_name = 'BLA';

select * from table(dbms_xplan.display_cursor);

SCOTT@test> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 6kzz3vw5x8x3b, child number 1

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

select /*+ index(t) */ * from t where owner || :"SYS_B_0" || object_name =

:"SYS_B_1"

Plan hash value: 3778778741

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

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

| 0 | SELECT STATEMENT | | | | 35550 (100)| |

|* 1 | TABLE ACCESS BY INDEX ROWID| T | 502 | 49196 | 35550 (1)| 00:07:07 |

| 2 | INDEX FULL SCAN | T_IDX | 50234 | | 288 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OWNER"||:SYS_B_0||"OBJECT_NAME"=:SYS_B_1)