--软解析:  SQL语句完全匹配

--下面两个语句,需要硬解析(因为没有完全匹配)

select count(*) from t1 where id=5;

select count(*) from t1 where id=6;

--上面语句写成绑定变量形式就可以软解析

execute immediate 'select count(*) from t1 where id=:i' using x;

1: 软解析测试
SQL> SELECT NAME,VALUE
  2  FROM V$MYSTAT A,
  3       V$STATNAME B
  4  WHERE A.STATISTIC#=B.STATISTIC#
  5    AND NAME LIKE 'parse count%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                      12
parse count (hard)                                                        0
parse count (failures)                                                    0

--使用绑定变量(软解析)

SQL> BEGIN
  2    for i in 1..10000 LOOP
  3           execute immediate 'insert into t1 values(:v1)' using i;
  4    end loop;
  5    commit;
  6  end;
  7  /

PL/SQL 过程已成功完成。

SQL> SELECT NAME,VALUE
  2  FROM V$MYSTAT A,
  3       V$STATNAME B
  4  WHERE A.STATISTIC#=B.STATISTIC#
  5    AND NAME LIKE 'parse count%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                   10031
parse count (hard)                                                        1          #上面的操作没有产生什硬解析
parse count (failures)                                                    0

SQL> SET linesize 1000
SQL> col SQL_TEXT format a80
SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS
  2  from v$sqlarea
  3  where upper(sql_text) like '%T1%'
  4    AND ROWNUM <10;

SQL_TEXT                                                                         VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
insert into t1 values(:v1)                                                                   1       10000      10000

2: 硬解析测试

SQL> SELECT NAME,VALUE
  2  FROM V$MYSTAT A,
  3       V$STATNAME B
  4  WHERE A.STATISTIC#=B.STATISTIC#
  5    AND NAME LIKE 'parse count%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                     194
parse count (hard)                                                       24
parse count (failures)                                                    0

--不使用绑定变量

SQL> BEGIN
  2    for i in 1..10000 LOOP
  3           execute immediate 'insert into t1 values('|| i|| ')';
  4    end loop;
  5    commit;
  6  end;
  7  /

PL/SQL 过程已成功完成。

SQL> SELECT NAME,VALUE
  2  FROM V$MYSTAT A,
  3       V$STATNAME B
  4  WHERE A.STATISTIC#=B.STATISTIC#
  5    AND NAME LIKE 'parse count%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                   10287
parse count (hard)                                                    10050    #上面操作产生很多硬解析
parse count (failures)                                                    0

select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS
from v$sqlarea
where upper(sql_text) like '%T1%';

SQL_TEXT                                                                         VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
insert into t1 values(3818)                                                                  1        0     0
insert into t1 values(3819)                                                                  1        1     1
insert into t1 values(3820)                                                                  1        1     1
insert into t1 values(3821)                                                                  1        1     1
insert into t1 values(3822)                                                                  1        1     1
insert into t1 values(3823)                                                                  1        1     1
insert into t1 values(3824)                                                                  1        1     1
insert into t1 values(3825)                                                                  1        1     1
insert into t1 values(3826)                                                                  1        1     1