一次有意思的錯選執行計畫問題定位(涉及SYS_OP_C2)，執行計畫sys_op_c2

一次有意思的錯選執行計畫問題定位(涉及SYS_OP_C2)，執行計畫sys_op_c2

這兩天和廣分的兄弟看了一個問題，比較有意思，過程也比較曲折。。。

問題現象：

1. 11g的庫，話說有一個應用程式新上線，應用中使用了綁定變數的方式執行一條簡單的SQL，例如select a from b where c = :x，c列是該表複合主鍵的前置列，表定義是varchar2類型，從spotlight監控看這條SQL的執行計畫是全表掃描，一次執行要1個小時，這張表是運行很久的引用分區表，資料量是億級，測試的時候正常，但很顯然測試的資料量可能和生產非常不一致，導致沒察覺。

2. 在sqlplus中手工執行SQL，變數使用字面值，例如select a from b where c = 'abc'，執行的非常快，查看執行計畫，是用的索引範圍掃描。

初步懷疑：

1. 對如此資料量的一張表，理應使用索引，但應用程式未使用索引，用的全表掃描，手工執行SQL時卻可以用到索引，那麼問題就在於為什麼對於應用程式，Oracle選擇了全表掃描的執行計畫，而不是索引？

開始時的幾種猜測：

1. 是否該表上線時有大量的資料變更，對執行計畫產生影響，且未到達夜維統計資訊收集的時間，造成因統計資訊不準導致錯誤執行計畫的可能？

> 經詢問，確認上線時未有大量資料的變更，且上線後手工收集過統計資訊。這種猜測不對。

2. 是否因為使用不同的查詢條件會有不同的執行計畫，由於綁定變數窺探的影響，導致採用了錯誤的執行計畫？進一步解釋猜測，例如第一次執行應用程式時，使用的條件值對應的執行計畫是全表掃描，由於使用綁定變數窺探的作用，又由於應用使用了綁定變數，接下來的每次執行都會採用全表掃描，除非shared_pool被清空或對該表有DDL操作，才會重新硬解析，有可能採用另外的執行計畫，這是綁定變數窺探的副作用。

> 因為他用的是引用分區，合格記錄在主表如果是儲存於多個分區中，是否Oracle認為全表掃描效率高，如果儲存於很少的分區，Oracle認為索引掃描效率高？

證明上述猜測的依據，就是無論哪種方式執行，應用程式或sqlplus，執行計畫都是全表掃描，但實際是sqlplus執行SQL時並沒有採用全表掃描，用的是索引範圍掃描。這種猜測不對。

3. 是不是索引設定為了invisible？

> invisible是11g的新特性，允許設定索引為invisible，效果是DML操作仍會維護索引，但最佳化器會忽略索引的存在，除非設定參數optimizer_use_invisible_indexes，否則即使使用該索引欄位，也不會使用索引。

但和問題2相同，使用sqlplus時會使用索引掃描，不可能應用程式執行時會臨時設定該參數，因為這參數是系統級的，不是session級的，需要重啟資料庫生效，這不是應用程式能做的，而且也沒有任何理由需要由應用程式來做這個操作。這種猜測不對。

究竟為什麼應用程式運行時，這個SQL使用了全表掃描，但sqlplus執行SQL時卻用的索引範圍掃描呢？

越來越邪乎了。。。

但哲學觀點證明，因果關聯，肯定有某種因素讓Oracle對不同情境使用了不同的執行計畫，就像@dbsnake所說的，90%的Oracle問題都是SQL的書寫不正確導致的，前兩天有幸聽了RWP中國巡講，Tom同樣提到了這點，不是Oracle做錯了，而是你可能給Oracle的某些錯誤資訊，讓其出現了這種錯誤。

如何進一步證明？

首先跑了一個10046，第一次反饋沒看出什麼問題。

接著跑一個sqlplus下執行SQL的10053，發現最佳化器選擇的就是索引範圍掃描，其成本值最低，而全表掃描的成本值如下：

無論如何，是不會選擇全表掃描的啊？

其實開始的時候並沒有第一時間反應過來，一條SQL語句，Oracle計算的成本中索引範圍掃描最優，但應用程式運行時沒有用，而是用的全表掃描，排除上面幾種猜測後，剩下的可能不多了。其實問題已經縮小到為什麼應用程式執行的SQL索引失效了？

經過進一步排查，發現應用程式中對查詢條件變數使用了OracleDbType.NVarchar2的定義，但實際欄位類型是VARCHAR2，即where VARCHAR2 = NVARCHAR2，那麼是否因為這種類型不符，造成做了隱式轉換，導致索引失效？可能用過NVARCHAR2的朋友就已經發現問題了，由於以前基本沒用過這種類型，所以只是懷疑，需要驗證。

實驗：

1. 定義了一張測試表，定義NVARCHAR2類型的變數，類比應用程式。

SQL> create table t_n as select * from dba_objects;Table created.SQL> create index idx_t_n on t_n (object_name);Index created.SQL> var x nvarchar2(128);SQL> exec :x := 'ABC';PL/SQL procedure successfully completed.

2. 首先使用explain plan for查看執行計畫

SQL> explain plan for select count(*) from t_n where object_name = :x;Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 3062759669-----------------------------------------------------------------------------| Id  | Operation  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------|   0 | SELECT STATEMENT  |    |  1 | 66 |  3   (0)| 00:00:01 ||   1 |  SORT AGGREGATE   |    |  1 | 66 | |    ||*  2 |   INDEX RANGE SCAN| IDX_T_N |722 | 47652 |  3   (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------   2 - access("OBJECT_NAME"=:X)Note-----   - dynamic sampling used for this statement (level=2)18 rows selected.

發現是用的索引範圍掃描啊，沒有任何問題？？？

3. explain plan for方式得到的執行計畫有可能是不準的，@dbsnake的書中有詳細介紹，那麼看看display_cursor方式得到的執行計畫有不同

SQL> select count(*) from t_n where object_name = :x;  COUNT(*)---------- 1SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select count(*) from t_n where object_name%';SQL_TEXT--------------------------------------------------------------------------------SQL_ID      VERSION_COUNT------------- -------------select count(*) from t_n where object_name = :x630ztwp0w2b6f  1SQL> select * from table(dbms_xplan.display_cursor('630ztwp0w2b6f',0,'advanced'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID630ztwp0w2b6f, child number 0-------------------------------------select count(*) from t_n where object_name = :xPlan hash value: 4075463224---------------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |  |  |   290 (100)|  ||   1 |  SORT AGGREGATE    |  |1 |    66 |       |  |PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------|*  2 |   TABLE ACCESS FULL| T_N  |    12 |   792 |   290   (1)| 00:00:04 |---------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1   2 - SEL$1 / T_N@SEL$1Outline Data-------------PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------  /*+      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')      DB_VERSION('11.2.0.1')      ALL_ROWS      OUTLINE_LEAF(@"SEL$1")      FULL(@"SEL$1" "T_N"@"SEL$1")      END_OUTLINE_DATA  */PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Peeked Binds (identified by position):--------------------------------------   1 - :X (NVARCHAR2(30), CSID=2000): 'ABC'Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(SYS_OP_C2C("OBJECT_NAME")=:X)PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - (#keys=0) COUNT(*)[22]Note-----   - dynamic sampling used for this statement (level=2)53 rows selected.

看到不同了。。。

注意filter中顯示SYS_OP_C2C("OBJECT_NAME")=:X，說明Oracle對左值使用了一個叫SYS_OP_C2C的函數，我們都知道這個常識，如果對索引欄位使用了函數，那麼是不會採用這個索引作為執行計畫的，肯定是全表掃描。

看來問題是找到關鍵了，但還沒完，這個SYS_OP_C2C是什麼，為什麼對VARCHAR2 = NVARCHAR2這種情況會調用這個函數？

首先搜到了MOS有篇文章SYS_OP_C2C Causing Full Table/Index Scans (文檔 ID 732666.1)，簡明扼要地說明了這個問題：

1) You are executing a query using bind variables.

2) The binding occurs via an application (eg. .NET, J2EE ) using a "string" variable to bind.

3) The query is incorrectly performing a full table/index scan instead of an unique/range index scan.  

4) When looking at advanced explain plan, sqltxplain or 10053 trace, you notice that the "Predicate Information" shows is doing a "filter(SYS_OP_C2C)".

e.g select * from table(dbms_xplan.display_cursor(&sql_id,null,'ADVANCED'));

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYS_OP_C2C("COL1")=:B1)            <=== filter operation occurring

CAUSE

The bind variable "string" is using a different datatype to the column that is being queried.  
This means that an implicit conversion of the data is required to execute the query.  SYS_OP_C2C is the implicit function which is used to convert the column between nchar and char.

SOLUTION

  1. Create a function based index on the column.

e.g create index <index_name> on <table_name> (SYS_OP_C2C(<column>));

OR
  2. Ensure that your bind "string" datatype and column datatype are the same.
      A java example where this can occurs is when defaultNChar=TRUE.  This will cause strings to bind as NVARCHAR2 causing the predicate that are subset datatypes to be converted to NVARCHAR2.
      e.g.    -Doracle.jdbc.defaultNChar=true
                <connection-property name="defaultNChar">true</connection-property>

說明的很是詳細了，如果應用程式(例如.NET，Java)中使用了“string”的綁定變數，查詢語句就會使用全表掃描/索引全掃描，不會使用到唯一索引掃描/索引範圍掃描。使用advanced選項的explain plan或10053等方式才能發現這個問題。

原因就是“string”的綁定變數是使用了另外一種和查詢列定義不同的資料類型。Oracle需要使用SYS_OP_C2C函數在NCHAR和CHAR類型之間做隱式轉換。

解決方案：

1. 建立函數索引。

2. 確保應用程式中使用的“string”類型和欄欄位類型一致。

另外，楊長老對這個問題也有過說明(http://blog.itpub.net/4227/viewspace-531728/)。

後話是，開發同事之前沒用過Oracle，寫程式時不知怎的就用了NVARCHAR2的定義，且測試和生產環境不一致，才導致了這個在上線後才出現的問題。

總結：

1. 一個很小的欄位定義，有可能造成意想不到的後果，說明瞭解Oracle一些基本原理的重要性，這裡不僅指提到的欄位類型。

2. 分析一個問題，要有正確的思路，要能抓到問題本質，像這個問題，就是不同方式執行同一條SQL，會有不同的執行計畫，那麼為什麼Oracle選擇了錯誤的執行計畫？是有原因的，不是Oracle自己選擇錯了，更多情況是我們給他的資訊錯了，影響了其選擇執行計畫的準確性。如果能排除一些參數影響，定位到什麼原因導致索引失效，進而查看欄欄位定義和應用程式中的欄位類型定義，這個問題就可能更快的找到原因。這點還有待加強。

3. 要有分析的方法，這裡10046、10053，包括display_cursor，都是可能找到問題的重要手段，一是要知道什麼情境下使用這些工具，二是知道如何使用這些工具，像explain plan for得到的執行計畫有可能是不準的，尤其在有綁定變數的情況，上述就論證了這點，關鍵看是否真正執行了這條SQL語句；還有就像使用display_cursor，前提是執行過這條SQL，並且執行計畫仍在緩衝中，通過v$sqlarea可以找到對應的SQLID，這都是基礎。

4. 要細心，對於上述問題，可能從10046中就可以看到謂詞條件帶有隱式轉換的線索，進而可以找到問題的真正原因，即使之前不知道SYS_OC_C2C，不知道NVARCHAR2和VARCHAR2之間的區別，也可以讓我們有正確的認識。

5. 要能類比出問題，像這裡使用var x nvarchar2的方式，就是類比了應用程式使用綁定變數的邏輯。

6. 自己需要提高的地方還很多，寬度深度都如此，加油。