使用绑定变量和不使用绑定变量的效率对比测试——《Oracle高效设计》学习笔记

为了模拟真实环境，考虑在并发情况下，对比使用绑定变量和不使用绑定变量情况的多个性能指标。

测试使用statspack，如果没有安装此工具，可以参考《Oracle9i Database Performance Tuning Guide and Reference Release 2》

 

create table job_parameters
( jobid number primary key,
  iterations number,
  table_idx number );

 

不使用绑定变量的存储过程如下：

create or replace procedure dont_bind( p_job in number )
as
    l_rec job_parameters%rowtype;
begin
    select * into l_rec from job_parameters where jobid = p_job;
    for i in 1 .. l_rec.iterations
    loop
        execute immediate
        'insert into t' || l_rec.table_idx || '
         values ( ' ||  i || ' )';
        commit;
    end loop;
    delete from job_parameters where jobid = p_job;
    commit;
end;
/

 

使用绑定变量的存储过程如下：
create or replace procedure bind( p_job in number )
as
    l_rec job_parameters%rowtype;
begin
    select * into l_rec from job_parameters where jobid = p_job;
    for i in 1 .. l_rec.iterations
    loop
        execute immediate
        'insert into t' || l_rec.table_idx || ' values ( :x )' using i;
        commit;
    end loop;
    delete from job_parameters where jobid = p_job;
    commit;
end;
/

 

授予用户对dbms_lock的执行权限，用户名和密码根据实际情况需要修改

conn sys/oracle as sysdba;

grant execute on dbms_lock to system;

 

授予用户对statspack包的执行权限，用户名和密码根据实际情况需要修改

conn perfstat/perfstat;

grant execute on statspack to system;

 

 

模拟真实环境的存储过程：

create or replace procedure simulation
( p_procedure in varchar2, p_jobs in number, p_iters in number )
authid current_user
as
    l_job number;
    l_cnt number;
begin
    for i in 1 .. p_jobs
    loop
        begin
            execute immediate 'drop table t' || i;
        exception
            when others then null;
        end;
        execute immediate 'create table t' || i || ' ( x int )';
    end loop;

    for i in 1 .. p_jobs
    loop
        dbms_job.submit( l_job, p_procedure || '(JOB);' );
        insert into job_parameters
        ( jobid, iterations, table_idx )
        values ( l_job, p_iters, i );
    end loop;
   
    statspack.snap;
    commit;
    loop
        dbms_lock.sleep(30);
        select count(*) into l_cnt from job_parameters;
        exit when (l_cnt = 0);
    end loop;
    statspack.snap;
end;
/

 

测试5个用户并发执行，插入1万行记录的情况：

execute simulation('dont_bind',5,10000);
execute simulation('bind',5,10000);

 

生成statspack报告

@?/rdbms/admin/spreport

 

测试结果摘录如下：

	未使用绑定变量	使用绑定变量
Soft Parse %:	1.46	93.69
% Non-Parse CPU	40.78	100.00
CPU Time(s)	26	8
Hard parses(Per Second)	1,618.06	0.42
Executes(Per Second)	1,687.77	1,619.35
Transactions(Per Second)	1,613.42	1,613.42
log file parallel write (waits)	8,291	4,881
latch free(waits)	1,425	3

 

测试结论：

使用绑定变量优势明显，可以极大减少硬解析的次数，减少串行设备闩（Latch）的等待次数。