oracle绑定变量及举例.docx

1. 什么是绑定变量，及举例什么是绑定变量，问什么要进行绑定变量？Sql语句的执行要经过解析、执行、提取等几个阶段，其中解析最消耗资源，解析的过程中要进行语法、语义和权限的检查，如果这些检查都通过了，则进行执行，执行完成之后将sql语句的执行计划存储在共享池中 ，如果下一次有相同的sql语句要执行，则不需要解析，直接按照已经存在的执行计划进行执行，就可以节省资源当多个sql语句执行的时候大多数情况下是条件相同，只是条件里面的值不同。例如： A 用户：select * from t where ID=1B 用户：select * from t where ID=2绑定变量就是将条件谓词中不同的值保存在一个中间变量中，Oracle对用户每次发起的sql语句做hash运算时，都产生相同的hash 值，使用相同的执行计划，作为一个sql语句来执行。 Select * from t where ID=:X下面是绑定变量和非绑定变量的性能比较1) 绑定变量执行alter session set sql_trace=true;beginfor x in 1.10000 loopexecute immediate select * from t where object_name=:x using x;end loop;end;alter session set sql_trace=false;trace文件中的执行计划和统计信息SQL ID: gdp68zfsdqrbcPlan Hash: 1601196873select * from t where object_name=:xcall count cpu elapsed disk query current rows- - - - - - - -Parse 1 0.00 0.00 0 0 0 0Execute 10000 0.15 0.17 0 0 0 0Fetch 0 0.00 0.00 0 0 0 0- - - - - - - -total 10001 0.15 0.17 0 0 0 0Misses in library cache during parse: 0Optimizer mode: ALL_ROWSParsing user id: 5 (SYSTEM) (recursive depth: 1)Rows Row Source Operation- - 0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us cost=301 size=196 card=2)Rows Execution Plan- - 0 SELECT STATEMENT MODE: ALL_ROWS 0 TABLE ACCESS MODE: ANALYZED (FULL) OF T (TABLE)*OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows- - - - - - - -Parse 2 0.00 0.00 0 0 0 0Execute 3 1.28 1.25 0 0 0 1Fetch 0 0.00 0.00 0 0 0 0- - - - - - - -total 5 1.28 1.26 0 0 0 1Misses in library cache during parse: 2Misses in library cache during execute: 1OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows- - - - - - - -Parse 1 0.00 0.00 0 0 0 0Execute 10000 0.15 0.17 0 0 0 0Fetch 0 0.00 0.00 0 0 0 0- - - - - - - -total 10001 0.15 0.17 0 0 0 0Misses in library cache during parse: 010003 user SQL statements in session. 0 internal SQL statements in session.10003 SQL statements in session. 1 statement EXPLAINed in this session.*Trace file: D:appAdministratordiagrdbmsorclorcltraceorcl_ora_6912.trcTrace file compatibility: Sort 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. 4 unique SQL statements in trace file. 1 SQL statements EXPLAINed using schema: SYSTEM.prof$plan_table Default table was used. Table was created. Table was dropped. 60054 lines in trace file. 99 elapsed seconds in trace file.上面是在有绑定变量的情况下的信息统计l 执行时间=1.26+0.17l Cpu时间=1.28+0.15l 分析次数=3l 执行次数=100032) 没有绑定变量执行在另外一个会话中执行alter session set sql_trace=true;beginfor x in 1.10000 loopexecute immediate select * from t where object_id=|x;end loop;end;alter session set sql_trace=false;trace文件中的执行计划和统计信息,在不绑定变量的情况下，每执行一次sql都有统计信息和执行计划，所以下面的信息是trace文件中的汇总信息*OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows- - - - - - - -Parse 2 0.01 0.00 0 0 0 0Execute 3 1.87 2.04 0 0 0 1Fetch 0 0.00 0.00 0 0 0 0- - - - - - - -total 5 1.89 2.04 0 0 0 1Misses in library cache during parse: 2Misses in library cache during execute: 1OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows- - - - - - - -Parse 10000 6.09 6.01 0 0 0 0Execute 10000 0.28 0.19 0 0 0 0Fetch 0 0.00 0.00 0 0 0 0- - - - - - - -total 20000 6.37 6.21 0 0 0 0Misses in library cache during parse: 1000010003 user SQL statements in session. 0 internal SQL statements in session.10003 SQL statements in session.10000 statements EXPLAINed in this session.*Trace file: D:appAdministratordiagrdbmsorclorcltraceorcl_ora_6360.trcTrace file compatibility: Sort 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. 10000 SQL statements EXPLAINed using schema: SYSTEM.prof$plan_table Default table was used. Table was created. Table was dropped. 80063 lines in trace file. 96 elapsed seconds in trace file.上面是在有绑定变量的情况下的信息统计l 执行时间=2.04+6.21l Cpu时间=1.89+6.37l 分析次数=10002l 执行次数=10003综上：绑定变量的sql消耗的资源要远远少于不绑定变量的sql语句，如果未绑定变量的sql数量越多,所消耗的资源就越多。未绑定变量的sql所用的资源主要消耗在递归sql3) 测试在一个分区表中查询时使用相同的字段，但是谓词的数值不同，观察执行计划的差异首先创建一个分区表create table t1(object_id number,object_name varchar2(1000) partition by range(object_id)(partition p1 values less than(300000),partition p2 values less than(6000000),partition pm values less than(maxvalue) tablespace jlftst; 插入数据beginfor i in 1.8 loopinsert into t1 select object_id,object_name from dba_objects;commit;end loop;end; 创建索引，这是一个分区表的局部索引create index idx_t1 on t1(object_id) local;SQL select count(*) from t1; COUNT(*)- 7999992执行计划-Plan hash value: 2705263620-| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |-| 0 | SELECT STATEMENT | | 1 | 16891 (8)| 00:03:23 | | | 1 | SORT AGGREGATE | | 1 | | | | | 2 | PARTITION RANGE ALL| | 158M| 16891 (8)| 00:03:23 | 1 | 3 | 3 | TABLE ACCESS FULL | T1 | 158M| 16891 (8)| 00:03:23 | 1 | 3 |-Note- - dynamic sampling used for this statement (level=2)注意：这里的dynamic sampling，表明这个表没有分析对表进行分析exec dbms_stats.gather_table_stats(user,T1,cascade=true);cascade=true表示对索引一块儿进行分析SQL select count(*) from t1 partition(p1); COUNT(*)- 2399992SQL select count(*) from t1 partition(p2); COUNT(*)- 5600000SQL select count(*) from t1 partition(pm); COUNT(*)- 0SQL select object_id,count(*) from t1 where object_id1000000 and object_id select object_id,count(*) from t1 where object_id1 and object_id8000000 group by object_id;执行计划-Plan hash value: 3960330256-| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |-| 0 | SELECT STATEMENT | | 1001K| 4892K| | 15428 (4)| 00:03:06 | | | 1 | PARTITION RANGE ALL | | 1001K| 4892K| | 15428 (4)| 00:03:06 | 1 | 3 | 2 | HASH GROUP BY | | 1001K| 4892K| 91M| 15428 (4)| 00:03:06 | | |* 3 | INDEX FAST FULL SCAN| IDX_T1 | 7999K| 38M| | 4554 (2)| 00:00:55 | 1 | 3 |上面两个查询使用的查询条件相同，但是条件里面的值不同，从执行计划中可以看到索引的访问路径也不同，第1个语句是INDEX RANGE SCAN，第2个语句是INDEX FAST FULL SCAN，所以在OLAP系统中是不需要绑定变量的，因为即使相同的条件，执行计划也不 相同。4) 测试oracle存储过程是否使用了绑定变量代码入下：create or replace procedure PRC_test_bind as cursor c_RecordData is select ID,member_id from TBL_MEMBER_INFO ; r_RecordData c_RecordData%rowtype; v_count number; v_id number; begin v_count:=0; v_id:=0; open c_RecordData; loop fetch c_RecordData into r_RecordData; exit when c_RecordData%notfound; begin v_id:=r_RecordData.Id; select count(*) into v_count from tbl_flight_activity where id=v_id; exception when others then raise_application_error(-20102,出现错误!); end; end loop; close c_RecordData; null;end;通过10046 level4级别的跟踪发现使用的是绑定变量，trace文件片段如下：PARSING IN CURSOR #6 len=54 dep=1 uid=91 oct=3 lid=91 tim=19008521024 hv=1411062947 ad=2a0e0238 sqlid=448wsm1a1q753SELECT COUNT(*) FROM TBL_FLIGHT_ACTIVITY WHERE ID=:B1 END OF STMTBINDS #6: Bind#0 oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=0d576438 bln=22 avl=04 flg=05 value=137795EXEC #6:c=0,e=170,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1791493280,tim=19008521185FETCH #6:c=0,e=32,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=1,plh=179149328