隱式型別轉換(SYS_OP_C2C)-記一次SQL調優

abstractcyj發表於2016-09-20

對客戶資料庫生成的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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章