通過綁定變數最佳化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 ProductionWith 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/zlmConnected.
--建立測試表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_traceSQL> 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_traceSQL> 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           0Execute  10000      0.23       0.26          0          0          0           0Fetch        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.trcTrace file compatibility: 11.1.0.7Sort 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_traceSQL> 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_traceSQL> 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           0Execute  10015      0.13       0.11          0          0          0           0Fetch       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.trcTrace file compatibility: 11.1.0.7Sort 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系統。




在Oracle的OLTP系統中為何需要使用綁定變數

提高libary cache的效能，避免硬解析
 
ORACLE綁定變數窺視有什作用？

在OLTP系統中綁定變數效能的提高很明顯，這個大家都清楚，但是綁定變數有時也會產生一些不好的執行計畫，特別是需要長條圖的列。
從ORACLE9i開始提供了綁定變數窺視的功能，就是在ORACLE第一次解析SQL時會將變數的真實值代入產生執行計畫，以後對所有的同樣的綁定變數SQL都採用這個執行計畫了。
如果第一次的真實值恰好是比較特殊的值，那這將嚴重影響產生的執行計畫和以後的執行效率.
對於oracle11g提供新的特性自適應遊標共用(Adaptive Cursor Sharing),對於一個同樣綁定變數的SQL可以有多個執行計畫，從而達到動態最佳化執行計畫的作用，這個還沒有真正試過。
不過根據描述感覺oracle11g要實現這個效果對每個SQL的解析及記憶體都增加了，另外以後對於同樣的SQL要增加長條圖的判斷，執行計畫CUBE的判斷，如果結果不一樣那就最佳化了,但如果判斷的結果一樣，那等於重複做了這些工作，感覺實際應用中99.9%的結果是一樣的，所以11g這樣做增加記憶體增加每個SQL的判斷時間值不值，特別是對於高並發的業務系統，大部份的SQL的執行時間都很小。
對上都只是我的假想，不過感覺綁定變數窺視的作用的真的不明顯，在OLTP系統中對於特殊值還是不用綁定變數更好，在OLAP中如果特殊值很多建議不要用綁定變數更好。11g的改進不太清楚效果，不過感覺成本比較高。