商云方的技术博客 (沪ICP备14046774号-1)

说明：本实验主要验证SQL中使用文字标量导致硬解析（hard parse) 和使用绑定变量导致软解析（soft parse);

实验数据库：EBS 12.1.3 Demo数据库

数据库参数 cursor_sharing 默认等于 EXACT；

以下操作在PLSQL DEveloper SQL Window中操作

————————————————————————————————————————

–模拟EBS登录
begin
   fnd_global.APPS_INITIALIZE(user_id =>1318,resp_id =>21623 ,resp_appl_id => 660 )  ;
   mo_global.init(p_appl_short_name => ‘ONT’);
end ;

–打开Session级的TRace
alter session set sql_trace=true;
–设置TRace文件名的前缀，便于查找
alter session set tracefile_identifier=’SYF20130630′;

–执行一段匿名块程序
declare
  v_order_number        varchar2(100);
  v_order_number_result varchar2(100);
begin

  select ORDER_NUMBER    into v_order_number_result
    from oe_order_headers_v   where ORDER_NUMBER = ‘777849’;
  
  select ORDER_NUMBER    into v_order_number_result
    from oe_order_headers_v   where ORDER_NUMBER = ‘777848’;
  
  v_order_number := 777847;
  select ORDER_NUMBER    into v_order_number_result
    from oe_order_headers_v   where ORDER_NUMBER = v_order_number;
  
  v_order_number := 777846;
  select ORDER_NUMBER    into v_order_number_result
    from oe_order_headers_v   where ORDER_NUMBER = v_order_number;

end;
–结束Session级的TRace，生成Trace文件
alter session set sql_trace=false;

–Trace File 在$user_dump_dest路径下，这个路径的具体值可以在commond window下执行如下语句获得
show param user_dump_dest;

———————————————————————————————————–

获得trc文件后进行tkprof

然后查看tkprof后的文件可以看到如下效果：

********************************************************************************

SQL ID: 6hf9x8a7sxzua
Plan Hash: 796084466
SELECT ORDER_NUMBER
FROM
 OE_ORDER_HEADERS_V WHERE ORDER_NUMBER = ‘777849’
call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.25       0.26          0          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.05          2         27          0           1
——- ——  ——– ———- ———- ———- ———-  ———-
total        3      0.26       0.31          2         30          0           1

********************************************************************************

SQL ID: 6z7x7xu7u8yk7
Plan Hash: 796084466
SELECT ORDER_NUMBER
FROM
 OE_ORDER_HEADERS_V WHERE ORDER_NUMBER = ‘777848’
call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.22       0.23          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         27          0           1
——- ——  ——– ———- ———- ———- ———-  ———-
total        3      0.22       0.23          0         27          0           1

很显然，上述两句SQL是类似的，但由于SQL语句中使用的是文字标量，且数据库参数cursor_sharing=EXACT，导致了硬解析。

********************************************************************************

SQL ID: 9zt28w8ru22ny
Plan Hash: 796084466
SELECT ORDER_NUMBER
FROM
 OE_ORDER_HEADERS_V WHERE ORDER_NUMBER = :B1
call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.24       0.24          0          0          0           0
Fetch        2      0.00       0.00          0         54          0           2
——- ——  ——– ———- ———- ———- ———-  ———-
total        6      0.24       0.24          0         54          0           2

很显然 在解析这两句话的时候，即是使用绑定变量的方式，其Parse 时间为0，说明只是进行了软解析。

v_order_number := 777847;
  select ORDER_NUMBER    into v_order_number_result
    from oe_order_headers_v   where ORDER_NUMBER = v_order_number;
  
  v_order_number := 777846;
  select ORDER_NUMBER    into v_order_number_result
    from oe_order_headers_v   where ORDER_NUMBER = v_order_number;