代码改变世界

ORACLE中内部函数SYS_OP_C2C和隐式类型转换

2018-08-17 09:40  潇湘隐者  阅读(3900)  评论(0编辑  收藏  举报

什么是SYS_OP_C2C呢?官方的介绍如下:

 

SYS_OP_C2C is an internal function which does an implicit conversion of varchar2 to national character set using TO_NCHAR function. Thus, the filter completely changes as compared to the filter using normal comparison.

 

 

简单的来说,SYS_OP_C2C 是一个内部函数,功能是将VARCHAR2的数据类型转换成国家字符集的NVARCHAR2类型,内部通过TO_NCHAR函数实现。

 

 

 

其实为什么要介绍一下这个内部函数,是因为最近一个同事在优化一个SQL语句时,发现即使创建了相关字段的索引,但是SQL语句的执行计划仍然不走索引,而走全表扫描,个人在分析了后,发现即使这个索引涉及的三个字段的选择率不高,但是也不是不走索引的原因,而是因为隐式转换问题(全局临时表的跟物理表关联的字段数据类型不一致),如果Automatic SQL Tuning - SQL Profiles Tuning Advisor建议创建基于SYS_OP_C2C的函数索引,或者执行计划中有类似 filter(SYS_OP_C2CCOLUMN).....这样的信息,那么你应该检查是否出现了隐式类型转换(implicit type conversion

 

 

什么是隐式类型转换(implicit type conversion)?

 

 

如果进行比较的两个值的数据类型不同,则 ORACLE 必须将其中一个值进行类型转换使其能够比较。这就是所谓的隐式类型转换。通常当开发人员将数字存储在字符列时会导致这种问题的产生。ORACLE 在运行时会强制转化其中一个值,(由于固定的规则)在索引字符列使用 to_number。由于添加函数到索引列所以导致索引不被使用。实际上,ORACLE 也只能这么做,类型转换是一个应用程序设计因素。由于转换是在每行都进行的,这会导致性能问题。详见:

Document 232243.1 ORA-01722 ORA-01847 ORA-01839 or ORA-01858 From Queries with Dependent Predicates

 

 

官方文档SYS_OP_C2C Causing Full Table/Index Scans (文档 ID 732666.1)中有介绍:

 

APPLIES TO:

 

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]

Information in this document applies to any platform.

This problem can occur on any platform.

 

SYMPTOMS

 

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>

 

 

  

关于SYS_OP_C2C内部函数出现的可能原因,概况起来就是

 

1)正在执行一个带有绑定变量的查询

 

    关于这个,以前也曾经遇到过这样的案例,参考这篇博文ORACLE绑定变量隐式转换导致性能问题

 

2)绑定变量经由application.NET, J2EE等)使用 "string" 类型的绑定变量来绑定。

 

3)该查询错误的执行了全表扫描/索引扫描,而没有使用索引唯一扫描或者索引范围扫描

 

4)使用advanced 选项查看explain plan, sqltxlain or 10053 trace,你会注意到在"Predicate Information"部分  会显示一个 "filter(SYS_OP_C2C)".

 

 

解决方案:

 

1. 建立一个基于函数的索引。

 

e.g create index <index_name> on <table_name> (SYS_OP_C2C(<column>));

 

2. 让绑定变量定义的数据类型与该列的数据类型一致。

 

 

 

 

参考资料:

 

https://hoopercharles.wordpress.com/2015/02/08/to-n-or-not-to-n-is-sys_op_c2c-the-question-oracle-nvarchar-slow/

SYS_OP_C2C Causing Full Table/Index Scans (文档 ID 732666.1)