[20150513]函数索引与CURSOR_SHARING=FORCE

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

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

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

--如果不修改代码,一个最简单的方式就是修改参数CURSOR_SHARING = FORCE(补充一点:我个人不建议修改SIMILAR,实际上11G这个参数还
可以设置,但是无效的.),但是在这种情况下如果存在函数索引,可能导致这个参数修改可能带了另外的问题,就是索引无效,选择全表扫描.

--我这里举一个例子,说明另外的情况,参考链接如下,我仅仅重复测试:
http://oracle-randolf.blogspot.com/2015/04/function-based-indexes-and.html

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
as
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
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   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
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   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
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   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
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   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
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   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)

上一篇:android源码编译完成之后


下一篇:使用ecs服务器如何构建mysql数据的高可用高性能