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

來源:互聯網
上載者:User

一次有意思的錯選執行計畫問題定位(涉及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. 自己需要提高的地方還很多,寬度深度都如此,加油。

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.