一次有意思的錯選執行計劃問題定位(涉及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，發現優化器選擇的就是索引范圍掃描，其成本值最低，而全表掃描的成本值如下：

無論如何，是不會選擇全表掃描的啊？<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD48cD7G5Mq1v6rKvLXEyrG68rKiw7vT0LXa0rvKsbzkt7TTprn9wLSjrNK7zPVTUUzT777ko6xPcmFjbGW8xsvjtcSzybG+1tDL99L9t7bOp8mow+jX7tPFo6y1q9Om08OzzNDy1MvQ0Mqxw7vT0NPDo6y2+MrH08O1xMirse3JqMPoo6zFxbP9yc/D5ry41taywrLiuvOjrMqjz8K1xL/JxNyyu7bgwcuho8bkyrXOyszi0tG+rcv10KG1vc6qyrLDtNOm08OzzNDy1rTQ0LXEU1FMy/fS/cqn0KfBy6O/PC9wPjxwPr6tuf29+NK7sr3FxbLpo6y3os/W06bTw7PM0PLW0LbUsunRr8z1vP6x5MG/yrnTw8HLT3JhY2xlRGJUeXBlLk5WYXJjaGFyMrXEtqjS5aOstavKtbzK19a2zsDg0M3Kx1ZBUkNIQVIyo6y8tHdoZXJlIFZBUkNIQVIyID0gTlZBUkNIQVIyo6zEx8O0yse38dLyzqrV4tbWwODQzbK7xqXF5KOs1Oyzydf2wcvS/sq916q7u6OstbzWwsv30v3Kp9Cno7+/ycTc08O5/U5WQVJDSEFSMrXExfPT0b7N0tG+rbeiz9bOysziwcujrNPJ09rS1Mewu/mxvsO708O5/dXi1tbA4NDNo6zL+dLU1rvKx7uz0smjrNDo0qrR6dakoaM8L3A+PHA+PGJyIC8mZ3Q7PC9wPjxwPsq10emjujwvcD48cD4xLiC2qNLlwcvSu9XFsuLK1LHto6y2qNLlTlZBUkNIQVIywODQzbXEseTBv6OsxKPE4tOm08OzzNDyoaM8YnIgLyZndDs8L3A+PHA+PHByZSBjbGFzcz0="brush:sql;">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(*)
----------
	 1

SQL> 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 = :x
630ztwp0w2b6f		  1

SQL> select * from table(dbms_xplan.display_cursor('630ztwp0w2b6f',0,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	630ztwp0w2b6f, child number 0
-------------------------------------
select count(*) from t_n where object_name = :x

Plan 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$1

Outline 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'));

CAUSE

The bind variable "string" is using a different datatype to the column that is being queried.

SOLUTION

1. Create a function based index on the column.

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

OR

說明的很是詳細了，如果應用程序(例如.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. 自己需要提高的地方還很多，寬度深度都如此，加油。