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