SQL隐式转换带来的性能问题

碰到一SQL异常慢，从执行计划来看，走了正常的索引，经过进一步分析：
原SQL语句:

SELECT ROWNUM AS ROWNUMBER__,T.*
  FROM (Select T1.ID          AS "InvMainID",
               T2.CONSIGNCODE AS "ConsignCode",               
               T2.ORDERCODE  AS "OrderCode",
               T3.CREATETIME AS "CreateTime",
               T2.CUSCODE    AS "CusCode",
               T2.BILLDATE   AS "OutTime",
               T4.ISCANCEL   AS "IsCancel",
               T1.ISPRINT    AS "IsPrint",
               T3.TITLE      AS "Title",
               T3.ACCEPTER   AS "Accepter",
               T3.POSTCODE   AS "PostCode",
               T2.INVAMT     AS  "InvAmt",
               T3.INVCONTENT AS "InvContent",
               T1.PRINTOR    AS "HandleMan",
               T4.ISRED      AS "IsRed",
               T4.INVNO      AS "InvNo",
               T4.INVCODE    AS "InvCode",
               T1.PRINTTIME  AS "PrintTime",
               T2.INVAMT     AS "ConsignAmt",
               T3.ADDRESS    AS "Address",
               T2.ORGCODE    AS "OrgCode"        
          From mbs7_inv.INV_CONSIGN T2        
          Join mbs7_inv.INV_INVORDER T3
            on T2.ORDERCODE = T3.ORDERCODE        
          Join mbs7_inv.WL_PSINFO T5
            on T2.CONSIGNCODE = T5.CONSIGNCODE        
          left Join mbs7_inv.Inv_InvMain T1
            on T2.CONSIGNCODE = T1.CONSIGNCODE        
          left Join mbs7_inv.Inv_Record T4
            on T1.ID = T4.InvMainID        
         Where ((((T2.INVAMT >= 1) And (T2.ISCANCEL = 0)) And
               (T3.STATUS = 0)) And (T5.PSRESULT = 1))
         ORDER BY T4.INVNO ASC, T1.ISPRINT DESC) T 
 WHERE T4."InvCode" = '144011320565'
   AND T4."InvNo" >= '00011109'
   AND T4."InvNo" <= '00011163'
   AND ROWNUM   <= 100

执行计划如下：

这里主要是红色部分实现了隐式转换连接，并且导致执行计划出现异常，COST递增很大，现象：
第一次执行很慢，1-2分钟才出来结果，应用出现超时,而第二次以后执行较快，5秒内可出来。

分析：
尝试通过创建索引，接受sql_profile的方式优化，但效果不明显，第一次执行照样很慢；
后来发现该语句的执行计划 cost消耗较大的部分存在SYS_OP_C2C这种隐式转换比较，于是
发现表T1和T2的连接字段分别为varchar2和nvarchar2，这种情况下由于循环通过隐式转换，
最终的COST较大，而第二次执行，由于转换后的值已存在SGA里，所以直接比较即可，速率
较快，为了印证这点，后来把SGA刷新后，问题立刻重现。

解决方案：
创建索引,并且把T1表的连接字段改成varchar2，执行计划中隐式转换消失了，COST大幅度下降，第一次
执行时间3秒内，第二次以后执行在2秒内,cost从2万多下降到100多.

实施操作:

SQL> CREATE INDEX mbs7_inv.ix_code_PSRESULT on mbs7_inv.wl_psinfo(CONSIGNCODE,PSRESULT) tablespace inv_index;
SQL> drop index mbs7_inv.IX_WL_PSINFO_PSRESULT;
SQL> alter table mbs7_inv.INV_INVMAIN modify CONSIGNCODE VARCHAR2(20);
 
Table altered

优化后的执行计划：

这里消除了隐式转换后，计划使用了正常的索引连接，COST大幅度下降！

---

更多DBA案例更新，请关注我们CSDN博客!
https://topdbs.blog.csdn.net