隱式型別轉換（SYS_OP_C2C）-記一次SQL調優

對客戶資料庫生成的AWR報告中發現的一個簡單的SQL引起了我的注意：
SELECT* FROM t_ol_srcorder_m WHERE olorderno = :olorderno1 AND ROWNUM <= 1

Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Id	SQL Module	SQL Text
374.76	96	3.90	7.79	25.30	75.26	7ja8tx2udc4vb	Erp2Ec.exe	SELECT * FROM t_ol_srcorder_m ...

一個小時的AWR報告中，這個SQL執行了96次，每次耗時接近4秒。於是拿來這個SQL文字，先explain plan

使用explain plan可以看到SQL走了索引。不過這個是預估的，應該是不準確的。

查詢v$sql_plan

從v$sql_plan中可以發現，這個SQL實際的執行計劃採用的是table access full，也就是全表掃描。

對此，不得其解。因為我並沒有做10053事件窺探實際的繫結變數。

 select * from v$sql_plan p where p.SQL_ID  = '7ja8tx2udc4vb'

ADDRESS     HASH_VALUE  SQL_ID      PLAN_HASH_VALUE   CHILD_ADDRESS     CHILD_NUMBER      TIMESTAMP   OPERATION   OPTIONS

00000005B29B6878  3033928555  7ja8tx2udc4vb     2791902860  00000005B102E190  2     2016/9/20 14:28:53    SELECT STATEMENT 

00000005B29B6878  3033928555  7ja8tx2udc4vb     2791902860  00000005B102E190  2     2016/9/20 14:28:53    COUNT STOPKEY

00000005B29B6878  3033928555  7ja8tx2udc4vb     2791902860  00000005B102E190  2     2016/9/20 14:28:53    TABLE ACCESS      FULL

嘗試使用dbms_sqltune

DECLARE

   l_sql_id      v$session.prev_sql_id%TYPE;

   l_tuning_task VARCHAR2(30);

 BEGIN

   l_sql_id      := '7ja8tx2udc4vb';

   l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);

   --:tuning_task  := l_tuning_task;

   dbms_output.put_line(l_tuning_task);

   dbms_sqltune.execute_tuning_task(l_tuning_task);

   dbms_output.put_line(l_tuning_task);

 END;

SELECT dbms_sqltune.report_tuning_task('dbms_output輸出的taskid') FROM dual; 

1- Restructure SQL finding (see plan 1 in explain plans section)

----------------------------------------------------------------

  謂詞 SYS_OP_C2C("T_OL_SRCORDER_M"."OLORDERNO")=:B1 (在執行計劃的行 ID 2 處使用) 包含索引列

  "OLORDERNO" 的隱式資料型別轉換。此隱式資料型別轉換使優化程式無法選擇表 "H2"."T_OL_SRCORDER_M" 的索引。

 

  Recommendation

  --------------

 

竟然存在一個隱式的型別轉換。這樣的話這麼長的執行時間也就可以理解了。

 

這樣的話就好理解多了。

 

查了一下sys_op_c2c, 應當是應用層把引數的型別轉換了。等價於對引數使用了to_nchar函式進行了轉換。

 

自己做了一下實驗，果然對變數進行to_nchar操作會導致隱式型別轉換

SQL> var objname varchar2(20)

SQL> begin :objname := 'T1'; end;

  2  /

 

PL/SQL 過程已成功完成。

 

SQL> select * from t1 where object_name = to_nchar(:objname);

 

 

執行計劃

----------------------------------------------------------

Plan hash value: 3617692013

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   944 |   190K|   339   (1)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| T1   |   944 |   190K|   339   (1)| 00:00:05 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter(SYS_OP_C2C("OBJECT_NAME")=SYS_OP_C2C(:OBJNAME))

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

統計資訊

----------------------------------------------------------

          7  recursive calls

          0  db block gets

       1318  consistent gets

          0  physical reads

          0  redo size

       1607  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed


對於這個SQL，應用不能調整的話，只能建立函式索引
create index idx_t_ol_srcorder_no on t_ol_srcorder_m(SYS_OP_C2C(olorderno))

但是應用能調整這個問題是最好的。

參考：http://joze-senegacnik.blogspot.com/2009/12/what-is-purpose-of-sysopc2c-internal.html
        http://blog.csdn.net/msdnchina/article/details/37876187

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-2125198/，如需轉載，請註明出處，否則將追究法律責任。