綁定變量在OLTP環境下，被廣泛的使用；這源于OLTP的特點和sql語句的執行過程，OLTP典型的事務短，類似的sql語句執行率高，并發大；oracle在執行sql語句前會對sql語句進行hash運算，将得到的hash值和share pool中的library cache中對比，如果未命中，則這條sql語句需要執行硬解析，如果命中，則隻需要進行軟解析；硬解析的執行過程是先進行語義，文法分析，然後生成執行計劃，最後執行sql語句，在OLTP系統中使用綁定變量可以很好的解決這個問題！
一：oltp環境下，使用綁定變量和不使用綁定變量對比
1：建立測試資料
[oracle@dg53 ~]$ sqlplus hr/hr
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 14 16:54:46 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table t1 as select object_id,object_name from dba_objects;
Table created.
SQL> create index i_t1 on t1(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
2：不使用綁定變量情況下，進行sql trace分析，執行1萬次，需要硬解析10003次，其中包含遞歸解析，解析時間為19.37s,cpu消耗為17.62
SQL> alter session set tracefile_identifier='HR01';
Session altered.
SQL> alter session set sql_trace=TRUE;
SQL> begin
2 for i in 1..10000
3 loop
4 execute immediate 'select * from t1 where object_id='||i;
5 end loop;
6* end;
PL/SQL procedure successfully completed.
SQL> alter session set sql_trace=FALSE;
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10003 17.62 19.37 0 0 0 0
Execute 10003 0.48 0.54 0 0 0 0
Fetch 7 0.00 0.01 1 13 0 4
total 20013 18.10 19.92 1 13 0 4
Misses in library cache during parse: 10000
10003 user SQL statements in session.
3 internal SQL statements in session.
10006 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: dg53_ora_24818_HR01.trc
Trace file compatibility: 10.01.00
Sort options: default
0 session in tracefile.
10003 user SQL statements in trace file.
3 internal SQL statements in trace file.
10006 SQL statements in trace file.
10006 unique SQL statements in trace file.
80071 lines in trace file.
78 elapsed seconds in trace file.
3：使用綁定變量情況下，進行sql trace分析，執行1萬次，隻需要硬解析5次，其中包含遞歸解析，解析時間和cpu時間基本忽略不計
SQL> alter session set tracefile_identifier='HR02';
4 execute immediate 'select * from t1 where object_id=:i' using i;
6 end;
Parse 5 0.00 0.00 0 0 0 0
Execute 10004 0.10 0.09 0 0 0 0
Fetch 10 0.00 0.01 0 29 0 7
total 10019 0.10 0.10 0 29 0 7
Misses in library cache during parse: 2
Misses in library cache during execute: 1
4 user SQL statements in session.
4 internal SQL statements in session.
8 SQL statements in session.
Trace file: dg53_ora_24818_HR02.trc
4 user SQL statements in trace file.
4 internal SQL statements in trace file.
8 SQL statements in trace file.
8 unique SQL statements in trace file.
10078 lines in trace file.
91 elapsed seconds in trace file.
二：使用綁定變量有如此好的效果，那麼這是不是百利無一害的技術手段呢？下面在OLAP環境下測試
1：建立測試資料，olap環境下分區的技術非常普遍，且資料量非常大
[root@dg53 ~]# su - oracle
[oracle@dg53 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 15 09:05:35 2012
SQL> conn /as sysdba
Connected.
SQL> create tablespace data01;
Tablespace created.
SQL> create tablespace data02;
SQL> create tablespace data03;
SQL> create tablespace data04;
SQL> conn hr/hr
SQL> create table t2 (object_id number,object_name varchar2(200))
2 partition by range(object_id)
3 (partition p1 values less than(5000) tablespace data01,
4 partition p2 values less than(10000) tablespace data02,
5 partition p3 values less than(15000) tablespace data03,
6* partition pm values less than(maxvalue) tablespace data04)
2 for i in 1..300
4 insert into t2 select object_id,object_name from dba_objects;
SQL> commit;
Commit complete.
SQL> create index i_t_id on t2(object_id) local
2 (partition p1 tablespace data01,
3 partition p2 tablespace data02,
4 partition p3 tablespace data03,
5 partition pm tablespace data04);
SQL> exec dbms_stats.gather_table_stats('HR','T2',CASCADE=>TRUE);
SQL> select count(*) from t2 partition(p1);
COUNT(*)
----------
1474800
SQL> select count(*) from t2 partition(p2);
1398900
SQL> select count(*) from t2 partition(p3);
1491900
SQL> select count(*) from t2 partition(pm);
10752600
2：查詢object_id落在1-5999之間的資料，檢視執行計劃,這裡選擇了全表掃描為最優的執行計劃
SQL> set autot traceonly
SQL> select object_id,count(*) from t2 where object_id between 1 and 5999 group by object_id;
5807 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1765100474
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time| Pstart| Pstop |
| 0 | SELECT STATEMENT | | 5484 | 27420 | 2650 (12)| 00:00:32| | |
| 1 | PARTITION RANGE ITERATOR| | 5484 | 27420 | 2650 (12)| 00:00:32| 1 | 2 |
| 2 | HASH GROUP BY | | 5484 | 27420 | 2650 (12)| 00:00:32| | |
|* 3 | TABLE ACCESS FULL | T2 | 1639K| 8005K| 2432 (4)| 00:00:30| 1 | 2 |
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OBJECT_ID"<=5999 AND "OBJECT_ID">=1)
Statistics
1 recursive calls
0 db block gets
10772 consistent gets
10643 physical reads
0 redo size
101752 bytes sent via SQL*Net to client
4642 bytes received via SQL*Net from client
389 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5807 rows processed
3：查詢object_id落在1000-15000之間的資料，檢視執行計劃,這裡選擇了索引通路掃描為最優的執行計劃
SQL> select object_id,count(*) from t2 where object_id between 1000 and 15000 group by object_id;
13600 rows selected.
Plan hash value: 3236792548
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |Pstart| Pstop |
| 0 | SELECT STATEMENT | | 12869 | 64345 | 8731 (2)| 00:01:45 || |
| 1 | PARTITION RANGE ALL | | 12869 | 64345 | 8731 (2)| 00:01:45 |1 | 4 |
| 2 | SORT GROUP BY NOSORT| | 12869 | 64345 | 8731 (2)| 00:01:45 || |
|* 3 | INDEX RANGE SCAN | I_T_ID | 3847K| 18M| 8731 (2)| 00:01:45 |1 | 4 |
3 - access("OBJECT_ID">=1000 AND "OBJECT_ID"<=15000)
filter("OBJECT_ID"<=15000 AND "OBJECT_ID">=1000)
9655 consistent gets
8115 physical reads
242794 bytes sent via SQL*Net to client
10351 bytes received via SQL*Net from client
908 SQL*Net roundtrips to/from client
13600 rows processed
結論：由此可見，使用綁定變量應該盡量保證使用綁定變量的sql語句執行計劃應當相同，否則将造成問題，因而綁定變量不适用于OLAP環境中！
三：在前面的測試中，1-5999之間的查詢，為什麼不選擇分區範圍掃描？1000-5000之間的查詢，為什麼不選擇全表掃描，使用索引，不會産生無謂的2次I/O嗎？要了解這些，就要開啟資料庫的10053時間，分析cbo如何選擇執行計劃？
1：分析1-5999之間查詢的10053事件
SQL> alter session set tracefile_identifier='HR03';
SQL> alter session set events '10053 trace name context forever,level 1';
SQL> alter session set events '10053 trace name context off';
Session altered.
trace檔案關鍵内容：
***************************************
Column Usage Monitoring is ON: tracking level = 1
****************
QUERY BLOCK TEXT
select object_id,count(*) from t2 where object_id between 1 and 5999 group by object_id
*********************
QUERY BLOCK SIGNATURE
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=54910 hint_alias="T2"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
Using NOWORKLOAD Stats
CPUSPEED: 587 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T2 Alias: T2 (Using composite stats)
(making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 15078669 #Blks: 71051 AvgRowLen: 28.00
PARTITIONS::
PRUNED: 2
ANALYZED: 2 UNANALYZED: 0
#Rows: 15078669 #Blks: 10756 AvgRowLen: 28.00
Index Stats::
Index: I_T_ID Col#: 1
USING COMPOSITE STATS
LVLS: 2 #LB: 33742 #DK: 50440 LB/K: 1.00 DB/K: 303.00 CLUF: 15299802.00
Column (#1): OBJECT_ID(NUMBER)
AvgLen: 5.00 NDV: 50440 Nulls: 0 Density: 1.9826e-05 Min: 33 Max: 54914
SINGLE TABLE ACCESS PATH
Table: T2 Alias: T2
Card: Original: 15078669 Rounded: 1639470 Computed: 1639469.86 Non Adjusted: 1639469.86
Access Path: TableScan
Cost: 2432.43 Resp: 2432.43 Degree: 0
Cost_io: 2355.00 Cost_cpu: 545542277
Resp_io: 2355.00 Resp_cpu: 545542277
Access Path: index (index (FFS))
Index: I_T_ID
resc_io: 7383.00 resc_cpu: 2924443977
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 7798.09 Resp: 7798.09 Degree: 1
Cost_io: 7383.00 Cost_cpu: 2924443977
Resp_io: 7383.00 Resp_cpu: 2924443977
Access Path: index (IndexOnly)
resc_io: 3671.00 resc_cpu: 358846806
ix_sel: 0.10873 ix_sel_with_filters: 0.10873
Cost: 3721.93 Resp: 3721.93 Degree: 1
Best:: AccessPath: TableScan
Cost: 2432.43 Degree: 1 Resp: 2432.43 Card: 1639469.86 Bytes: 0
Grouping column cardinality [ OBJECT_ID] 5484
2：分析1000-5000之間查詢的10053事件
SQL> alter session set tracefile_identifier='HR04';
***************************************
select object_id,count(*) from t2 where object_id between 1000 and 15000 group by object_id
#Rows: 15078669 #Blks: 71051 AvgRowLen: 28.00
Card: Original: 15078669 Rounded: 3847127 Computed: 3847127.03 Non Adjusted: 3847127.03
Cost: 16073.05 Resp: 16073.05 Degree: 0
Cost_io: 15544.00 Cost_cpu: 3727344901
Resp_io: 15544.00 Resp_cpu: 3727344901
resc_io: 7383.00 resc_cpu: 3049910030
Cost: 7815.89 Resp: 7815.89 Degree: 1
Cost_io: 7383.00 Cost_cpu: 3049910030
Resp_io: 7383.00 Resp_cpu: 3049910030
resc_io: 8611.00 resc_cpu: 842035120
ix_sel: 0.25514 ix_sel_with_filters: 0.25514
Cost: 8730.52 Resp: 8730.52 Degree: 1
Best:: AccessPath: IndexFFS Index: I_T_ID
Cost: 7815.89 Degree: 1 Resp: 7815.89 Card: 3847127.03 Bytes: 0
Grouping column cardinality [ OBJECT_ID] 12869
本文以《讓oracle跑的更快》為指導，如有雷同，不勝榮幸！
本文轉自斬月部落格51CTO部落格，原文連結http://blog.51cto.com/ylw6006/899353如需轉載請自行聯系原作者
ylw6006
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)