[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)