Oracle 变量绑定与变量窥视合集系列三

三 用示例演示一次分析，多次执行的示例，并对给出演示结果

我们分别演示硬解析 软解析 执行不同组合

实验

没有绑定变量

LEO1@LEO1> drop table leo3 purge;        清理环境

Table dropped.

LEO1@LEO1> drop table leo4 purge;

Table dropped.

LEO1@LEO1> drop table leo5 purge;

Table dropped.

LEO1@LEO1> create table leo3 as select * from dba_objects;         创建三张表

Table created.

LEO1@LEO1> create table leo4 as select * from dba_objects;

Table created.

LEO1@LEO1> create table leo5 as select * from dba_objects;

Table created.

LEO1@LEO1> select object_type from leo3 where object_id=100;      执行4次

OBJECT_TYPE

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

EDITION

LEO1@LEO1> select object_type from leo3 where object_id=200;

OBJECT_TYPE

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

INDEX

LEO1@LEO1> select object_type from leo3 where object_id=300;

OBJECT_TYPE

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

TABLE

LEO1@LEO1> select object_type from leo3 where object_id=400;

OBJECT_TYPE

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

TABLE

LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select object_type from leo3 where %' order by 1;

SQL_TEXT                                     PARSE_CALLS   LOADS   EXECUTIONS

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

select object_type from leo3 where object_id=100     1            1        1

select object_type from leo3 where object_id=200     1            1        1

select object_type from leo3 where object_id=300     1            1        1

select object_type from leo3 where object_id=400     1            1        1

从视图上可以看出oracle认为这4条sql语句是完全不一样的，每执行一遍，都需要做一次硬解析。

绑定变量1

LEO1@LEO1> variable leo number;                            定义变量

LEO1@LEO1> execute :leo:=100;                              变量赋值 leo=100

PL/SQL procedure successfully completed.

LEO1@LEO1> select object_type from leo4 where object_id=:leo;   引用这个变量

OBJECT_TYPE

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

EDITION

LEO1@LEO1> execute :leo:=200;                              变量赋值 leo=200

PL/SQL procedure successfully completed.

LEO1@LEO1> select object_type from leo4 where object_id=:leo;   引用这个变量

OBJECT_TYPE

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

INDEX

LEO1@LEO1> execute :leo:=300;                              变量赋值 leo=300

PL/SQL procedure successfully completed.

LEO1@LEO1> select object_type from leo4 where object_id=:leo;   引用这个变量

OBJECT_TYPE

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

TABLE

LEO1@LEO1> execute :leo:=400;                              变量赋值 leo=400

PL/SQL procedure successfully completed.

LEO1@LEO1> select object_type from leo4 where object_id=:leo;   引用这个变量

OBJECT_TYPE

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

TABLE

LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select object_type from leo4 where %' order by 1;

SQL_TEXT                                     PARSE_CALLS   LOADS   EXECUTIONS

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

select object_type from leo4 where object_id=:leo    4             1        4

SQL_TEXT：我们跟踪的sql语句

PARSE_CALLS：硬解析+软解析次数       其中硬解析1次+3次软解析

LOADS：硬解析次数                    1次

EXECUTIONS：执行次数                 4次

绑定变量：

Oracle认为这4条SQL是完全一样的（除了谓词部分）所以第一次执行的时候做一次硬解析后续3条SQL只做软解析，比上一个少了三次硬解析，性能提高

绑定变量2

LEO1@LEO1> begin

for leo in 1..4 loop

execute immediate 'select object_type from leo5 where object_id=:leo' using leo;

end loop;

end;

/

PL/SQL procedure successfully completed.

LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select object_type from leo5 where %' order by 1;

SQL_TEXT                                     PARSE_CALLS   LOADS   EXECUTIONS

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

select object_type from leo5 where object_id=:leo    1             1        4

绑定变量：

Oracle认为这4条SQL是完全一样的（除了谓词部分），和上面不同的是只做了1次硬解析没有软解析，反复执行了4次。我们做了一个循环，用leo变量代替谓词常量，每次都用相同的执行计划（执行计划不需要重新生成），只是改变一下常量值而已。

 

Leonarding
2013.2.3
天津&winter
分享技术~成就梦想
Blog：www.leonarding.com