浅谈Oracle绑定变量
浅谈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
- 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.
|
评论暂时关闭