本帖最后由 leonarding 于 2013-2-5 08:57 编辑
【绑定变量系列一】用示例说明绑定变量的应用领域是OLTP而不是OLAP http://www.itpub.net/thread-1762519-1-1.html【绑定变量系列二】用示例演示一次hard parse |soft parse |softer soft parse http://www.itpub.net/thread-1762520-1-1.html【绑定变量系列三】用示例演示一次分析，多次执行的示例，并对给出演示结果 http://www.itpub.net/thread-1762521-1-1.html【绑定变量系列四】演示一个父游标产生3个子游标的示例并分别说明每个子游标产生原因 http://www.itpub.net/thread-1762522-1-1.html【绑定变量系列五】演示ACS（adaptiver cursor sharing)的效果 http://www.itpub.net/thread-1762523-1-1.html
我们分别演示硬解析 软解析 执行不同组合实验没有绑定变量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-------------------EDITIONLEO1@LEO1> select object_type from leo3 where object_id=200;OBJECT_TYPE-------------------INDEXLEO1@LEO1> select object_type from leo3 where object_id=300;OBJECT_TYPE-------------------TABLELEO1@LEO1> select object_type from leo3 where object_id=400;OBJECT_TYPE-------------------TABLELEO1@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
11select object_type from leo3 where object_id=200 1
11select object_type from leo3 where object_id=300 1
11select object_type from leo3 where object_id=400 1
11从视图上可以看出oracle认为这4条sql语句是完全不一样的，每执行一遍，都需要做一次硬解析。绑定变量1LEO1@LEO1> variable leo number;
定义变量LEO1@LEO1> execute :leo:=100;
变量赋值 leo=100PL/SQL procedure successfully completed.LEO1@LEO1> select object_type from leo4 where object_id=:leo; 引用这个变量OBJECT_TYPE-------------------EDITIONLEO1@LEO1> execute :leo:=200;
变量赋值 leo=200PL/SQL procedure successfully completed.LEO1@LEO1> select object_type from leo4 where object_id=:leo; 引用这个变量OBJECT_TYPE-------------------INDEXLEO1@LEO1> execute :leo:=300;
变量赋值 leo=300PL/SQL procedure successfully completed.LEO1@LEO1> select object_type from leo4 where object_id=:leo; 引用这个变量OBJECT_TYPE-------------------TABLELEO1@LEO1> execute :leo:=400;
变量赋值 leo=400PL/SQL procedure successfully completed.LEO1@LEO1> select object_type from leo4 where object_id=:leo; 引用这个变量OBJECT_TYPE-------------------TABLELEO1@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=:leo4
14SQL_TEXT：我们跟踪的sql语句PARSE_CALLS：硬解析+软解析次数 其中硬解析1次+3次软解析LOADS：硬解析次数
1次EXECUTIONS：执行次数
4次绑定变量：Oracle认为这4条SQL是完全一样的（除了谓词部分）所以第一次执行的时候做一次硬解析后续3条SQL只做软解析，比上一个少了三次硬解析，性能提高绑定变量2LEO1@LEO1> beginfor leo in 1..4 loopexecute 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=:leo1
14绑定变量：Oracle认为这4条SQL是完全一样的（除了谓词部分），和上面不同的是只做了1次硬解析没有软解析，反复执行了4次。我们做了一个循环，用leo变量代替谓词常量，每次都用相同的执行计划（执行计划不需要重新生成），只是改变一下常量值而已。
Leonarding
2013.2.3
天津&winter
分享技术~成就梦想
Blog：www.leonarding.com
|
