为了模拟真实环境,考虑在并发情况下,对比使用绑定变量和不使用绑定变量情况的多个性能指标。
测试使用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)的等待次数。