|
雖然INDEX(索引)在SQL Tuning有著明顯的效果，但不是每個有執行問題的SQL都可由建立索引得到效能提升(執行時間縮短)。可能原因Optimizer(最佳化程式產生器)認為使用索引的成本超過使用Full Table Scan(全表格掃瞄)來得大，也可能是索引因為某種原因無法被使用。
下面所介紹的問題為某金融業原本在使用Function Based Index時，可以提升整體的執行效率，但有一天該SQL的執行時間突然變長，經檢視Execution Plan(執行計劃)後，發現該執行計劃並沒有使用Function Based Index，反而使用全表格掃瞄，導致該SQL的執行效率變差。
建立測試環境
SQL> create table t1 as select * from all_objects;
SQL> create index t1_fidx on t1(substr(object_name,3,6);
SQL> execute
dbms_stats.gather_table_stats('HR','T1',cascade=>true);
開始測試(Function Based Index有被使用)
SQL> select substr(object_name,3,6)
2 from t1
3 where object_id=10000;
SUBSTR(OBJEC
------------
S_YOID
SQL> select object_id,object_name
2 from t1
3 where substr(object_name,3,6)='S_YOID';
部分結果省略
OBJECT_ID OBJECT_NAME
---------- -------------------
76776 SYS_YOID0000076775$
76778 SYS_YOID0000076777$
76780 SYS_YOID0000076779$
76782 SYS_YOID0000076781$
323 rows selected.
顯示執行計劃
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------
SQL_ID 7ryzyq3x7vyzh, child number 0
----------------------------------------------------
select object_id,object_name from hr.t1 where
substr(object_name,3,6)='S_YOID'
Plan hash value: 546753835
------------------------------------------------------------------------
|Id|Operation | Name |Rows|Bytes|Cost (%CPU)| Time |
------------------------------------------------------------------------
|0 |SELECT STATEMENT | | | | 3 (100) | |
|1 |TABLE ACCESS BY INDEX ROWID| T1 | 3 | 111 | 3 (0) | 00:00:01|
|*2|INDEX RANGE SCAN |T1_IDX| 3 | | 1 (0) | 00:00:01|
------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------------------------------------
2 - access("T1"."SYS_NC00016$"='S_YOID')
由執行計劃來看，發現T1_IDX的確有被使用。
問題模擬
SQL> select object_id,object_name
2 from t1
3 where substr(object_name,3,6)='S_YOID';
部分結果省略
OBJECT_ID OBJECT_NAME
---------- ---------------------------------------------------
76776 SYS_YOID0000076775$
76778 SYS_YOID0000076777$
76780 SYS_YOID0000076779$
76782 SYS_YOID0000076781$
323 rows selected.
顯示執行計劃
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID 6tbu3a7rs94aq, child number 0
--------------------------------------------------------------------
select object_id,object_name from hr.t1 where
substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
Plan hash value: 3617692013
--------------------------------------------------------------------
| Id | Operation |Name| Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 302 (100) | |
|* 1 | TABLE ACCESS FULL| T1 | 753 | 22590 | 302 (1) | 00:00:04 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------------------------
1 - filter(SUBSTR("OBJECT_NAME",:SYS_B_0,:SYS_B_1)=:SYS_B_2)
依照上面所顯示的執行計劃，發現T1_IDX並未被使用，而是使用全表格掃瞄。
但是為何會發生這種變化?
請注意執行計劃所顯示的SQL內容，原本使用者所輸入的where substr(object_name,3,6)=’S_YOID’被最佳化程式產生器變換為where substr(object_name,:SYS_B_0,:SYS_B_1)=:SYS_B_2。這個轉換造成T1_IDX無法被使用。但為何會發生此種轉換呢?
:SYS_B_0,:SYS_B_1,:SYS_B_2這些變數是由最佳化程式產生器自動產生用來取代Literal(字面)，用以減少Hard Parse(硬解析)次數，以提升Shared Pool的空間使用效率與減少硬解析所造成CPU負擔。而這個自動轉換由cursor_sharing這個參數所控制。當cursor_sharing=force時，最佳化程式產生器將自動將SQL的字面變換為變數，而這些變數名稱為:SYS_B_n。
檢查cursor_sharing參數
SQL> show parameter cursor_sharing
NAME TYPE VALUE
---------------------- ------------------ --------------
cursor_sharing string FORCE
發現目前的cursor_sharing參數值的確為force。
問題解決方法
將cursor_sharing參數值恢復為exact
SQL> alter system set cursor_sharing=exact;
|
