绑定变量在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;
Session altered.
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;
Session altered.
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';
Session altered.
SQL> alter session set sql_trace=TRUE;
Session altered.
SQL> begin
2 for i in 1..10000
3 loop
4 execute immediate 'select * from t1 where object_id=:i' using i;
5 end loop;
6 end;
PL/SQL procedure successfully completed.
SQL> alter session set sql_trace=FALSE;
Session altered.
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
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.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: dg53_ora_24818_HR02.trc
Trace file compatibility: 10.01.00
Sort options: default
0 session in tracefile.
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> create tablespace data01;
Tablespace created.
SQL> create tablespace data02;
Tablespace created.
SQL> create tablespace data03;
Tablespace created.
SQL> create tablespace data04;
Tablespace created.
SQL> conn hr/hr
Connected.
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)
Table created.
SQL> begin
2 for i in 1..300
3 loop
4 insert into t2 select object_id,object_name from dba_objects;
5 end loop;
6 end;
PL/SQL procedure successfully completed.
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);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','T2',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select count(*) from t2 partition(p1);
COUNT(*)
----------
1474800
SQL> select count(*) from t2 partition(p2);
COUNT(*)
----------
1398900
SQL> select count(*) from t2 partition(p3);
COUNT(*)
----------
1491900
SQL> select count(*) from t2 partition(pm);
COUNT(*)
----------
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.
Execution Plan
----------------------------------------------------------
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 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">=1000 AND "OBJECT_ID"<=15000)
filter("OBJECT_ID"<=15000 AND "OBJECT_ID">=1000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9655 consistent gets
8115 physical reads
0 redo size
242794 bytes sent via SQL*Net to client
10351 bytes received via SQL*Net from client
908 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
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';
Session altered.
SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.
SQL> select object_id,count(*) from t2 where object_id between 1 and 5999 group by object_id;
5807 rows selected.
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)
Index: I_T_ID
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';
Session altered.
SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.
SQL> select object_id,count(*) from t2 where object_id between 1000 and 15000 group by object_id;
13600 rows selected.
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 1000 and 15000 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)
#Rows: 15078669 #Blks: 71051 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: 3847127 Computed: 3847127.03 Non Adjusted: 3847127.03
Access Path: TableScan
Cost: 16073.05 Resp: 16073.05 Degree: 0
Cost_io: 15544.00 Cost_cpu: 3727344901
Resp_io: 15544.00 Resp_cpu: 3727344901
Access Path: index (index (FFS))
Index: I_T_ID
resc_io: 7383.00 resc_cpu: 3049910030
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 7815.89 Resp: 7815.89 Degree: 1
Cost_io: 7383.00 Cost_cpu: 3049910030
Resp_io: 7383.00 Resp_cpu: 3049910030
Access Path: index (IndexOnly)
Index: I_T_ID
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博客作者ylw6006的原创作品，谢绝转载，否则将追究法律责任
bind variables10053SQL/PER Tuning