浅谈Oracle绑定变量-白红宇

绑定变量在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

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

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博客，原文链接http://blog.51cto.com/ylw6006/899353如需转载请自行联系原作者

ylw6006