SYS_OP_C2C隐式类型转换导致的性能问题

  昨日，一哥们在微信上问我一条SQL优化问题，说的比较急，他百思不得其解，明明单独测试的时候能够走索引，为什么表一关联，打死都不走索引，就算加了HINTS也不走索引，让我帮忙看看。下面就构造一个类似的SQL，如下所示：

select  *

  from t1

  left join t2

    on t1.name = t2.name

 where t1.name = '09DZ8H3XG8ORAH0HUZQI';

  在这里我要说一下，他原来发的是用PL/SQL Developer发的执行计划，如下所示：

 

  这里的t1,t2表的name都有索引，而且有很好的选择性，那按理说，根据条件：

on t1.name = t2.name

 where t1.name = '09DZ8H3XG8ORAH0HUZQI';

t1.name=’09DZ8H3XG8ORAH0HUZQI’的条件应该应该谓词传递给t2.name,转为t2.name=’ 09DZ8H3XG8ORAH0HUZQI’,那么t2表应该要走索引，然而却没有走索引，通过上面的执行计划可以看出一个问题：

全表扫描那行的cardinality=31010,然而真实的结果是：

select count(*) from t2 where t2.name='09DZ8H3XG8ORAH0HUZQI';

 

  COUNT(*)

----------

         1

 

1 row selected.

  第一眼想到的是，这T2表统计信息不对啊，然而T2表的统计信息是刚收集过的：

select num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name='T2';

 

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED

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

   3101013     3101013 2020-07-12 23:32:27

而且是100%收集啊，所以统计信息没有问题。
而且单独测试的时候正常走索引：

回头一想，有个大问题，啥大问题呢？

像我平时做SQL Tuning,我很少用PL/SQL Developer啊，这东西显示的执行计划，一般看的不是很直观，还有很多信息需要你自己添加，上面的执行计划就漏掉了最重要的谓词信息。我们做SQL Tuning最好使用SQL*PLUS，文本格式，便于分析，我又让他把SET AUTOTRACE TRACEONLY 的执行计划弄出来给我看看，如下：

Execution Plan

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

Plan hash value: 2757452810

 

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

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

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

|   0 | SELECT STATEMENT                     |        | 31010 |  2180K|  4757   (3)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER                  |        | 31010 |  2180K|  4757   (3)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     1 |    46 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN                  | IDX_T1 |     1 |       |     3   (0)| 00:00:01 |

|*  4 |   TABLE ACCESS FULL                  | T2     | 31010 |   787K|  4753   (3)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   3 - access("T1"."NAME"=U'09DZ8H3XG8ORAH0HUZQI')

   4 - filter("T1"."NAME"=SYS_OP_C2C("T2"."NAME"(+)) AND

              SYS_OP_C2C("T2"."NAME"(+))=U'09DZ8H3XG8ORAH0HUZQI')

 

Statistics

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

          0  recursive calls

          0  db block gets

      16368  consistent gets

          0  physical reads

          0  redo size

        831  bytes sent via SQL*Net to client

        487  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

  靠，瞬间发现了问题根源，还是SQL*PLUS亲切啊，明显通过谓词部分发现了一个陌生的函数：SYS_OP_C2C，这TMD明显是ORACLE内部隐式类型转换的函数嘛，然后对于cardinality的计算使用函数计算，所以不是准确的，后面的字符串加了个U’,这明显是NVARCHAR2啊，立马让他DESC T1和T2表：

desc t1

 Name                                                  Null?    Type

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

 ID                                                             NUMBER

 NAME                                                           NVARCHAR2(100)

 

desc t2

 Name                                                  Null?    Type

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

 ID                                                             NUMBER

 NAME                                                           VARCHAR2(100)

  很明显这是类型不一致，当t1.name=t2.name,因为NVARCHAR2的优先级高于VARCHAR2，所以把T2.name做了隐式类型转换，所以嘛，有索引也用不上啦。以前的NVARCHAR2前面叫N’,现在改成U’,其实是一样的东西，通过dump可以看出：

select dump('09DZ8H3XG8ORAH0HUZQI') a,

dump(n'09DZ8H3XG8ORAH0HUZQI') b,

dump(u'09DZ8H3XG8ORAH0HUZQI') c,

dump(SYS_OP_C2C('09DZ8H3XG8ORAH0HUZQI')) d

from dual;
A

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

B

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

C

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

D

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

Typ=96 Len=20: 48,57,68,90,56,72,51,88,71,56,79,82,65,72,48,72,85,90,81,73

Typ=96 Len=40: 0,48,0,57,0,68,0,90,0,56,0,72,0,51,0,88,0,71,0,56,0,79,0,82,0,65,0,72,0,48,0,72,0,85,

0,90,0,81,0,73

Typ=96 Len=40: 0,48,0,57,0,68,0,90,0,56,0,72,0,51,0,88,0,71,0,56,0,79,0,82,0,65,0,72,0,48,0,72,0,85,

0,90,0,81,0,73

Typ=96 Len=40: 0,48,0,57,0,68,0,90,0,56,0,72,0,51,0,88,0,71,0,56,0,79,0,82,0,65,0,72,0,48,0,72,0,85,

0,90,0,81,0,73

 

  通过SYS_OP_C2C将VARCHAR2转成了NVARCHAR2，SO，解决这个问题：

1.  改语句，将t1.name加上to_char,这样可以避免t2.name的类型转换，因为t1.name已经在where里有条件，这样也不影响t1.name走索引

select *

  from t1

  left join t2

    on to_char(t1.name) = t2.name

 where t1.name = '09DZ8H3XG8ORAH0HUZQI';

执行计划如下：

Execution Plan

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

Plan hash value: 4205057668

 

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

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

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

|   0 | SELECT STATEMENT                     |        |     1 |    72 |     8   (0)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER                  |        |     1 |    72 |     8   (0)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     1 |    46 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN                  | IDX_T1 |     1 |       |     3   (0)| 00:00:01 |

|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2     |     1 |    26 |     4   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN                  | IDX_T2 |     1 |       |     2   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   3 - access("T1"."NAME"=U'09DZ8H3XG8ORAH0HUZQI')

   5 - access("T2"."NAME"(+)=SYS_OP_C2C("T1"."NAME"))

 

2.  如果改不了语句，那么就对t2.name建立函数索引，如下所示走了函数索引：

create index idx1_t2 on t2(SYS_OP_C2C(NAME));

Execution Plan

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

Plan hash value: 4208491579

 

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

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

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

|   0 | SELECT STATEMENT                      |         | 31010 |  2180K|  4645   (1)| 00:00:01 |

|   1 |  MERGE JOIN OUTER                     |         | 31010 |  2180K|  4645   (1)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | T1      |     1 |    46 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN                   | IDX_T1  |     1 |       |     3   (0)| 00:00:01 |

|   4 |   BUFFER SORT                         |         | 31010 |   787K|  4641   (1)| 00:00:01 |

|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2      | 31010 |   787K|  4641   (1)| 00:00:01 |

|*  6 |     INDEX RANGE SCAN                  | IDX1_T2 | 12404 |       |     2   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

   3 - access("T1"."NAME"=U'09DZ8H3XG8ORAH0HUZQI')

   6 - access("T2"."SYS_NC00003$"(+)=U'09DZ8H3XG8ORAH0HUZQI')

3.  改表设计，保持t1.name和t2.name的一致性，这个应该在做设计的时候就考虑，这里不做阐述。

 

至此，问题解决。

  题外话，我们经常会遇到类型转换问题导致索引失效，要么是写SQL时候自己加了TO_CHAR,TO_DATE,TO_NUMBER，要么因为类型不一致，ORACLE做了隐式类型转换导致索引失效。要避免这种问题，还是要在表设计的时候，使用常用类型，避免使用一些不常用的比如NVARCHAR2,TIMESTAMP等，写SQL时候遇到类型不一致的，要先测试好，避免上线后出现问题。

附测试语句：

drop table t1;

drop table t2;

create table t1(id number,name nvarchar2(100));

create table t2(id number,name varchar2(100));

create index idx_t1 on t1(name);

create index idx_t2 on t2(name);

begin

  dbms_stats.gather_table_stats(ownname => user,tabname => 't1',no_invalidate => false);

  dbms_stats.gather_table_stats(ownname => user,tabname => 't2',no_invalidate => false);

end;

/

insert into t1

select level,dbms_random.string(opt => 'x',len => 20)

from dual

connect by level<100000;

insert into t2

select level,dbms_random.string(opt => 'x',len => 20)

from dual

connect by level<1000000;

insert into t2

select * from t1

where rownum<1000;

commit;

--问题语句

select *

  from t1

  left join t2

    on t1.name = t2.name

 where t1.name = '09DZ8H3XG8ORAH0HUZQI';