通过绑定变量优化OLTP系统性能，变量oltp系统性能

通过绑定变量优化OLTP系统性能，变量oltp系统性能

之前给南京某客户优化一套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 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse    10000      6.26       6.53          0          0          0           0 Execute  10000      0.23       0.26          0          0          0           0 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total    20000      6.50       6.79          0          0          0           0
Misses in library cache during parse: 10000
10003  user  SQL statements in session.     0  internal SQL statements in session. 10003  SQL statements in session. ******************************************************************************** Trace file: zlm11g_ora_14341_ZLM01.trc Trace file compatibility: 11.1.0.7 Sort options: default
       1  session in tracefile.    10003  user  SQL statements in trace file.        0  internal SQL statements in trace file.    10003  SQL statements in trace file.    10003  unique SQL statements in trace file.    90068  lines in trace file.      138  elapsed seconds in trace file.
分析：刚才的那段PL/SQL的语句被硬解析了10000次，并且执行了10000次，CPU总共消耗了6.26+0.23=6.50，花费时间6.53+0.26=6.79，可以看到，在trace文件中共有90068行，由于同样结构的SQL语句，未使用绑定变量，使Oracle认为每个语句都不同，因此产生了非常多的SQL语句，zlm01.log日志文件大小约为12M。

2.使用绑定变量的情况 --清空shared_pool
SQL> alter system flush shared_pool;

System altered.
--设置tracle文件标识符 SQL> alter session set tracefile_identifier='ZLM02';
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' using s;     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 zlm11g_ora_14546_ZLM02.trc [oracle@zlm trace]$ tkprof zlm11g_ora_14546_ZLM02.trc /home/oracle/zlm02.log

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 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute  10015      0.13       0.11          0          0          0           0 Fetch       19      0.00       0.00          0         47          0          12 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total    10035      0.13       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: zlm11g_ora_14546_ZLM02.trc Trace file compatibility: 11.1.0.7 Sort options: default
       1  session in tracefile.        4  user  SQL statements in trace file.       12  internal SQL statements in trace file.       16  SQL statements in trace file.       16  unique SQL statements in trace file.    20156  lines in trace file.      118  elapsed seconds in trace file.
分析：使用绑定变量以后，前后对比一下，资源消耗降低了非常多。运行了10000次的SQL语句，只解析了1次，执行次数虽然多了15次，但CPU时间为0.13，消耗时间为0.11，基本可以忽略不计，trace文件中只有20156行，内容非常较之前要低了非常多，zlm02.log文件仅19k大小。
SQL> select sql_id,sql_text,executions from v$sqlarea where sql_text like '%select * from t1 where object_id=%';
SQL_ID        SQL_TEXT                                           EXECUTIONS ------------- -------------------------------------------------- ---------- 28gj7tsy13xq8 select * from t1 where object_id=:i                     10000 --采用绑定变量的select语句也被执行了10000次

总结：在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系统。

提高libary cache的性能，避免硬解析
 

在OLTP系统中绑定变量性能的提高很明显，这个大家都清楚，但是绑定变量有时也会产生一些不好的执行计划，特别是需要直方图的列。
从ORACLE9i开始提供了绑定变量窥视的功能，就是在ORACLE第一次解析SQL时会将变量的真实值代入产生执行计划，以后对所有的同样的绑定变量SQL都采用这个执行计划了。
如果第一次的真实值恰好是比较特殊的值，那这将严重影响产生的执行计划和以后的执行效率.
对于oracle11g提供新的特性自适应游标共享(Adaptive Cursor Sharing),对于一个同样绑定变量的SQL可以有多个执行计划，从而达到动态优化执行计划的作用，这个还没有真正试过。
不过根据描述感觉oracle11g要实现这个效果对每个SQL的解析及内存都增加了，另外以后对于同样的SQL要增加直方图的判断，执行计划CUBE的判断，如果结果不一样那就优化了,但如果判断的结果一样，那等于重复做了这些工作，感觉实际应用中99.9%的结果是一样的，所以11g这样做增加内存增加每个SQL的判断时间值不值，特别是对于高并发的业务系统，大部份的SQL的执行时间都很小。
对上都只是我的假想，不过感觉绑定变量窥视的作用的真的不明显，在OLTP系统中对于特殊值还是不用绑定变量更好，在OLAP中如果特殊值很多建议不要用绑定变量更好。11g的改进不太清楚效果，不过感觉成本比较高。