绑定变量是Oracle解决硬解析的首要利器，能解决OLTP系统中librarycache的过度耗用以提高性能。然刀子磨的太快，使起来锋利，却容易折断。凡事皆有利弊二性，因地制宜，因时制宜，全在如何权衡而已。本文讲述了绑 定变量的使用方法，以及绑定变量的优缺点、使用场合。一、绑定变量提到绑定变量，就不得不了解硬解析与软解析。硬解析简言之即一条SQL语句没有被运行过，处于首次运行， 则需要对其进行语法分析，语义识别，跟据统计信息生成最佳的执行计划，然后对其执行。而软解析呢，则是由 于在librarycache已经存在与该SQL语句一致的SQL语句文本、运行环境，即有相同的父游标与子游标，采用拿来主义，直接执行即可。软解析同样经历语法分析，语义识别，且生成hashvalue，接下来在librarycache搜索相同的hashvalue，如存在在实施软解析。C:Usersmxq>sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on 星期六 5月 30 20:16:40 2015 Copyright （c） 1982, 2011, Oracle. All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options清空以前共享池数据 SQL> alter system flush shared_pool;
System altered SQL> select cust_id from T_SMSGATEWAY_MT where cust_id=467 and dest_addr=1817156 1555; CUST_ID ---------- 467 467 467 SQL> select cust_id from T_SMSGATEWAY_MT where cust_id=467 and dest_addr=1810839 9505;未选定行SQL> select cust_id from T_SMSGATEWAY_MT where cust_id=467 and dest_addr=1897116 7925;未选定行在下面可以看到oracle把没条执行都重新硬解析一遍生成三个值，这样效率不高 SQL> select sql_text,hash_value from v$sql where sql_text like "%dest_addr=18%"; SQL_TEXT -------------------------------------------------------------------------------- HASH_VALUE ---------- select sql_text from v$sql where sql_text like "%dest_addr=18%" 261357771 select cust_id from T_SMSGATEWAY_MT where cust_id=467 and dest_addr=18108399505 2971670234 select * from T_SMSGATEWAY_MT where cust_id=467 and dest_addr=18171561555 4160363108 SQL> select sql_text,hash_value from v$sql where sql_text like "%dest_addr=18%";
SQL_TEXT HASH_VALUE -------------------------------------------------------------------------------- ---------- select cust_id from T_SMSGATEWAY_MT where cust_id=467 and dest_addr=18971167925 3796536237 select * from T_SMSGATEWAY_MT where cust_id=467 and dest_addr=18108399505 2768207417 select * from T_SMSGATEWAY_MT where cust_id=467 and dest_addr=18171561555 2177737916 在这里开始使用绑定变量 SQL> var dest number; SQL> exec :dest:=15392107000;
PL/SQL procedure successfully completed dest --------- 15392107000
SQL> select cust_id from T_SMSGATEWAY_MT where cust_id=467 and dest_addr=:dest;
CUST_ID --------- dest --------- 15392107000
SQL> exec :dest:=15310098199;
PL/SQL procedure successfully completed dest --------- 15310098199
SQL> select cust_id from T_SMSGATEWAY_MT where cust_id=467 and dest_addr=:dest;
CUST_ID --------- dest --------- 15310098199
生成一个值说明oracle只硬解析一遍 SQL> select sql_text,hash_value from v$sql where sql_text like "%dest_addr=:dest%";
SQL_TEXT HASH_VALUE -------------------------------------------------------------------------------- ---------- select sql_text,hash_value from v$sql where sql_text like "%dest_addr=:dest%" 627606763 select cust_id from T_SMSGATEWAY_MT where cust_id=467 and dest_addr=:dest 1140441667
易网时代-编程资源站