好久不见，绑定变量发生隐式类型转换性能优化案例分享

        欢迎关注“数据库运维之道”公众号，一起学习数据库技术!

        本期将为大家分享“SQL语句绑定变量发生隐式类型转换”的性能优化案例。

        关键词：direct path read、SYS_OP_C2C、隐式类型转换

        客户反馈业务系统卡住了，DBA检查数据库发现某条高并发的SQL语句出现隐式类型转换，导致执行计划无法走索引定位数据。

SELECT 字段1，字段2，字段3 
 FROM "YWZD"."TEST01" "T"
 WHERE ("T"."infoId" = :EntityKeyValue1)
Bind Variables :
 1 -  (NVARCHAR2(32)):bvh576sp8w

        首先，DBA接到用户报障后，第一反应就是查询数据库等待事件，包括当前活动会话event统计信息、近1小时哪条语句执行频繁最高。

set linesize 150
set pagesize 500
col event for a40
col event_info for a100
select inst_id,event,count(*) cnt
from gv$session
where wait_class <> 'Idle'
group by inst_id,event
order by count(*) desc;

    INST_ID EVENT                       CNT
---------- ---------------------------------------- ----------
     1 direct path read                   195
     2 direct path read                   124
   
select 'last 1 hour event: '||event "event_info", sql_id, count(*)
  from v$active_session_history
 where sample_time > sysdate - 1 / 24
   and session_type = 'FOREGROUND'
   and wait_class <> 'Idle'
 group by event, sql_id
 having count(*)>100
 order by 3 desc;

               event_info                      SQL_ID         COUNT(*)
-------------------------------------------- ------------- ----------
last 1 hour event: direct path read          71cva5kkk3wd8     164480
last 1 hour event: gc buffer busy release    1azfwx81dmy4r     6606

        通过上述的两条排查语句，可以快速定位到SQL_ID为71cva5kkk3wd8的SQL语句产生大量的direct path read事件。同时，将SQL语句反馈给开发单位。

        接着，检查SQL语句对应的执行计划，可以看到执行计划中出现“久违”的关键字“SYS_OP_C2C”，这个明显跟隐式类型转换有关系。SYS_OP_C2C 是一个内部函数，功能是将VARCHAR2的数据类型转换成国家字符集的NVARCHAR2类型，通过内部TO_NCHAR函数实现。

alter session set cursor_sharing=force;
set linesize 1000
set pagesize 999
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(to_char('&sql_id'),NULL));

set linesize 1000
col value_string for a30
col name for a30
select SQL_ID,NAME,POSITION,DATATYPE,DATATYPE_STRING,VALUE_STRING 
from V$SQL_BIND_CAPTURE WHERE SQL_ID = '&sql_id';

        最后，检查表结构对应的字段类型设计，结果看到字段类型为CHAR(10)。很明显字段类型与参数值类型不匹配，数据库优化器自动进行内部类型转换，进而导致全表扫描。

        （1）通过上述分析，我们建议开发单位修改字段的类型或者创建索引。开发单位为了尽快恢复业务，选择创建索引。

create index YWZD.IDX_infoId on "YWZD"."TEST01"(SYS_OP_C2C("infoId"));

        （2）创建函数索引后，查询效率得到明显改善与提升。重新验证执行计划，看到COST代价值10484下降至27。

        （3）如何找出其他存在隐式转换的SQL？可以对生产环境的SQL进行全面审查，杜绝大多数存在隐式类型转换的SQL。通过以下两条语句找出数据库中存在隐式转换的SQL。

SELECT
     SQL_ID,
     PLAN_HASH_VALUE
 FROM
     V$SQL_PLAN X
 WHERE
     X.FILTER_PREDICATES LIKE '%INTERNAL_FUNCTION%'
 GROUP BY
     SQL_ID,
     PLAN_HASH_VALUE;


SELECT
     SQL_ID,
     PLAN_HASH_VALUE
 FROM
     V$SQL_PLAN X
 WHERE
     X.FILTER_PREDICATES LIKE '%SYS_OP_C2C%'
 GROUP BY
     SQL_ID,
     PLAN_HASH_VALUE;

        通常ORACLE数据库存在显式类型转换（Explicit Datatype Conversion）和隐式类型转换（Implicit Datatype Conversion）两种类型转换方式。如果进行比较或运算的两个值的数据类型不同时（源数据的类型与目标数据的类型），而且此时又没有转换函数时，那么ORACLE必须将其中一个值进行类型转换，使其能够运算。这就是所谓的隐式类型转换。其中隐式类型转换是自动进行的，当然，只有在这种转换是有意义的时候，才会自动进行。

create table TEST01 as select * from dba_objects;
create index ix_object_name on TEST01(object_name);
variable v_object_name nvarchar2(32);
exec :v_object_name :='OBJ$';
select count(*) from TEST01 where object_name=:v_object_name;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

• SQL Statements Performed Across Database Links run Slowly. Explain Plan Shows Function SYS_OP_C2C has been Applied to Predicates, and Query uses a Full Table Scan. (Doc ID 2010872.1)
• SYS_OP_C2C Causing Full Table/Index Scans (Doc ID 732666.1)
• On Implicit Conversions and More
• Basic Elements of Oracle CQL
• Data Type Comparison Rules

        以上就是本期关于“SQL语句绑定变量发生隐式类型转换”的性能优化案例分享。希望能给大家带来帮助。

        欢迎关注“数据库运维之道”公众号，一起学习数据库技术!