一个隐式转换引起的性能故障问题的解决过程

今天开发人员过来说一个每天执行上千次的procedure速度比较慢，希望能调整一下但不知如何下手。于是过去瞅了一眼，本来以为事情会比较简单。 因为思路很清晰嘛。执行步骤如下。

1，  在plsql developer中新开一个sql窗口；

2，  设置10046跟踪事件

3，  Tkprof解析跟踪结果

4，  定位到故障sql后根据需要实施优化。

但没想到事情还是起了点周折，前面几步非常顺利，但定位到故障之后的解决过程还是花了我一定的时间，最后一个偶然的思路终于将隐藏较深的变量隐式转换的一个问题给揪出来了，特此记录一下。

测试环境创建

create table jax_t20100113(
fid varchar2(10),
fname varchar2(400)
);

insert into jax_t20100113
select lpad(rownum,10,'0'), lpad(rownum,200,'0') from dual
connect by rownum < 100000
;

commit;

create index ind_jax_t20100113 on jax_t20100113(fid);

create or replace procedure jax_p20100113(
a_id in out nvarchar2
)
as
begin
  select fid into a_id from jax_t20100113 where fid = a_id;
end;

第一步，设置10046跟踪事件，确认执行计划

 

SQL> alter session set tracefile_identifier = 'jax';

 

Session altered

 

SQL> alter session set events '10046 trace name context forever,level 8';

 

Session altered

 

SQL>

SQL> declare

  2     a _id nvarchar2(10);

  3  begin

  4     a _id := '0000009625';

  5    select fid into a_id from jax_t20100113 where fid = a_id;

  6  end;

  7  /

 

PL/SQL procedure successfully completed

 

SQL> alter session set events '10046 trace name context off';

 

Session altered

 

第二步，tkprof解析跟踪结果

 

SQL> show parameter user_dump_dest

 

NAME                                 TYPE        VALUE

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

user_dump_dest                       string      /oracle/admin/crmtest/udump

 

使用oracel用户登录到数据库服务器所在的操作系统中

切换到user_dump_dest目录

[oracle@localhost ~]$ cd /oracle/admin/crmtest/udump

[oracle@localhost udump]$ ls -lt |head -1

total 4264

[oracle@localhost udump]$ ls -lt |head -3

total 4264

-rw-r----- 1 oracle dba    6769 Jan 13 23:21 crmtest_ora_5409_jax.trc

-rw-r----- 1 oracle dba    5301 Jan 13 23:20 crmtest_ora_5399_jax.trc

[oracle@localhost udump]$ tkprof crmtest_ora_5409_jax.trc crmtest_ora_5409_jax.trc1

 

TKPROF: Release 9.2.0 .4.0 - Production on Wed Jan 13 23:24:45 2010

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

 

[oracle@localhost udump]$

使用more操作系统命令，或者使用ftp将跟踪结果下载到本地后可以从里边解析出造成性能故障的语句。

SELECT fid

from

 jax_t20100113 where fid = :b1

 

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      0.36       0.36          0       3039          0           1

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

total        3      0.36       0.36          0       3039          0           1

 

Misses in library cache during parse: 0

Optimizer goal: CHOOSE

Parsing user id: 69     (recursive depth: 1)

 

Rows     Row Source Operation

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

      1  TABLE ACCESS FULL JAX_T20100113 (cr=3039 r=0 w=0 time=365746 us)

 

 

至此，应该说性能问题解决了一大半了。

查看jax_t20100113用户表的定义可知，fid为varchar2类型，而procedure中传入的参数a_id为nvarchar2类型。

我们知道，oracle在两个数据进行对比的时候如果类型不同， 会将范围较小者先转换为范围较大者，然后再执行对比， 这里就会将varchar2先转换为nvarchar2类型，也就相当于在字段fid上加了一个隐式函数。于是上述的语句就转换为了

Select f_id from jax_t20100113 where to_nvarchar(f_id) = a_id。

 

修改存储过程定义之后问题解决。