之前给南京某客户优化一套OLTP数据库，其数据库中在某个时间段，会执行大量结构非常相似的查询语句，造成shared_pool被大量占用，导致数据库性能下降。碰到这种情况，其实最佳优化方案，就是让应用厂商修改相应代码，通过增加绑定变量，来有效减少相似SQL语句执行时的硬解析数，降低对shared_pool的消耗。下面来做一个关于绑定变量的测试：
1.创建测试用户并赋予权限
[Oracle@zlm ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 14 14:47:32 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
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> create user zlm identified by zlm;
User created.
--赋权限
SQL> grant dba to zlm;
Grant succeeded.
--创建表空间
SQL> create tablespace zlm datafile '/u01/app/oracle/oradata/zlm11g/zlm01.dbf' size 100m ;
Tablespace created.
--设置缺省表空间
SQL> alter user zlm default tablespace zlm;
User altered.
--连接用户
SQL> conn zlm/zlm
Connected.
--创建测试表
SQL> create table t1 as select object_id,object_name from dba_objects;
Table created.
--创建索引
SQL> create index inx_t1_id on t1(object_id);
Index created.
--收集表的统计信息
SQL>
PL/SQL procedure successfully completed.
2.不使用绑定变量的情况
--设置tracle文件标识符
SQL> alter session set ;
Session altered.
--开启sql_trace
SQL> alter session set ;
Session altered.
--执行PL/SQL程序段
SQL> begin
2 for s in 1..10000
3 loop
4 execute immediate ;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
--关闭sql_trace
SQL> alter session set ;
Session altered.
SQL> !
[oracle@zlm ~]$ cd /u01/app/oracle/diag/rdbms/zlm11g/zlm11g/trace/
[oracle@zlm trace]$ ll -lrth | grep ZLM01.trc
-rw-r----- 1 oracle oinstall 7.3M Sep 14 15:00
[oracle@zlm trace]$
TKPROF: Release 11.2.0.3.0 - Development on Sun Sep 14 15:05:46 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@zlm trace]$
--查看用tkprof格式化后的日志zlm01.log最后一段
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
0 0 0 0
0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
0 0 0 0
Misses in library cache during parse: 10000
0 internal SQL statements in session.
********************************************************************************
Trace file:
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
0 internal SQL statements in trace file.
138 elapsed seconds in trace file.
分析：刚才的那段PL/SQL的语句被，CPU总共消耗了6.26+0.23=，花费时间6.53+0.26=，可以看到，在trace文件中共有90068行，由于同样结构的SQL语句，未使用绑定变量，使Oracle认为每个语句都不同，因此产生了非常多的SQL语句，zlm01.log日志文件大小约为。
2.使用绑定变量的情况
--清空shared_pool
SQL>
System altered.
--设置tracle文件标识符
SQL> alter session set ;
Session altered.
--开启sql_trace
SQL> alter session set ;
Session altered.
--运行PL/SQL程序段
SQL> begin
2 for s in 1..10000
3 loop
4 execute immediate ;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
--关闭sql_trace
SQL> alter session set sql_trace=false;
Session altered.
SQL> !
--再次查看用tkprof格式化以后的内容
[oracle@zlm trace]$ ll -lrth | grep ZLM02.trc
-rw-r----- 1 oracle oinstall 18K Sep 14 15:16
[oracle@zlm trace]$
TKPROF: Release 11.2.0.3.0 - Development on Sun Sep 14 15:17:09 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@zlm trace]$
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
0 0 0 0
0 0 0 0
Fetch 19 0.00 0.00 0 47 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
0 47 0 12
Misses in library cache during parse: 1
4 user SQL statements in session.
12 internal SQL statements in session.
16 SQL statements in session.
********************************************************************************
Trace file:
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
12 internal SQL statements in trace file.
118 elapsed seconds in trace file.
分析：使用绑定变量以后，前后对比一下，资源消耗降低了非常多。，执行次数虽然多了15次，但CPU时间为，消耗时间为，基本可以忽略不计，trace文件中只有20156行，内容非常较之前要低了非常多，zlm02.log文件仅大小。
SQL> select from where sql_text like '%select * from t1 where object_id=%';
SQL_ID SQL_TEXT EXECUTIONS
------------- -------------------------------------------------- ----------
总结：在OLTP等报表系统中，当我们的应用中如果执行结构非常类似的语句：如，select * from t1 where object_id='10',select * from t1 where object_id='100',……如果不加绑定变量，会大大增加硬解析的次数，10000次执行，就有10000次硬解析(第一次执行时)，如果再次执行，可能会因为在shared_pool缓存中已经存在，会有一部分软解析，而使硬解析数减少，而一旦使用了绑定变量，就算把shared_pool清空掉，也只需很少的几次硬解析，就可以执行10000次查询语句，大大减少了SGA中对shared_pool的占用，提高查询性能。如果在OLAP中，使用绑定变量需要谨慎，未必一定会提高性能，具体情况还需具体分析，这种情况仅仅适合OLTP系统。
：