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

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

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

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';

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

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;

实验模拟脚本
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);