之前给南京某客户优化一套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 reuse autoextend on next 10m maxsize 1G extent management local segment space management auto;
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> exec dbms_stats.gather_table_stats('ZLM','T1',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
2.不使用绑定变量的情况
--设置tracle文件标识符
SQL> alter session set tracefile_identifier='ZLM01';
Session altered.
--开启sql_trace
SQL> alter session set sql_trace=true;
Session altered.
--执行PL/SQL程序段
SQL> begin
2 for s in 1..10000
3 loop
4 execute immediate 'select * from t1 where object_id='||s;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
--关闭sql_trace
SQL> alter session set sql_trace=false;
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 zlm11g_ora_14341_ZLM01.trc
[oracle@zlm trace]$ tkprof zlm11g_ora_14341_ZLM01.trc /home/oracle/zlm01.log
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
------- ------ -------- ---------- ---------- ---------- ---------- ----------