试验绑定变量带来的软硬解析情况

转自：hlu

TEST: 本次实验的环境是10.101.50.35开启两个session，一个SID为138，一个为135

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as bsderp


SQL>
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered

SQL> SET ERVEROUTPUT ON;
Cannot SET ERVEROUTPUT

SQL> SET TIMING ON;
SQL> DECLARE
  2     TYPE rc IS REF CURSOR;
  3     l_rc rc;
  4     l_dummy all_objects.object_name%TYPE;
  5     l_start NUMBER DEFAULT dbms_utility.get_time;
  6  BEGIN
  7     FOR i IN 1 .. 1000 LOOP
  8     OPEN l_rc FOR 'select object_name from all_objects where object_id = '||i; 通常写法
  9     FETCH l_rc INTO l_dummy;
 10     CLOSE l_rc;
 11     END LOOP;
 12     dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds ...');
 13  END;
 14  /

PL/SQL procedure successfully completed

Executed in 24.547 seconds 执行时间

SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
  2     TYPE rc IS REF CURSOR;
  3     l_rc rc;
  4     l_dummy all_objects.object_name%TYPE;
  5     l_start NUMBER DEFAULT dbms_utility.get_time;
  6  BEGIN
  7     FOR i IN 1 .. 1000 LOOP
  8     OPEN l_rc FOR 'select object_name from all_objects where object_id = :x' USING i; 绑定变量
  9     FETCH l_rc INTO l_dummy;
 10     CLOSE l_rc;
 11     END LOOP;
 12     dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds ...');
 13  END;
 14  /

PL/SQL procedure successfully completed

Executed in 0.25 seconds 执行时间 可以和普通的写法比较下，看看差别是多少倍

参考：http://topic.csdn.net/u/20100914/11/b5ae6bd8-c146-40d9-9e69-cc1458d11f7f.html


上面同样目的的两条SQL执行后对于软硬解析的影响：
提示：SID为135的是使用了绑定变量；SID为138是没有使用绑定变量的情况！


/**
解释一下v$sesstat中相关取值的重要概念
1 > opened cursors current (当前单个session打开的cursors数量)
2 > parse count (hard) (当前session硬解析次数)
3 > parse count (total) (当前session解析总次数)
4 > session cursor cache count (设置session_cached_cursors参数后,"相同"sql被解析3次后被cache在session cursor cache中的数量
5 > session cursor cache hits (软软解析的次数 a "softer" soft parse )
6 > 手工计算一下软解析次数parse count (total) - session cursor cache hits - parse count (hard) = parse count(soft)