18234 2735046
--//这样测试1就快于测试2. --//改成执行select sysdate into v_d from dual ;代码看看.
$ seq 150 | xargs -I %# -P 150 bash -c "sqlplus -s -l scott/book @m1.txt 1e6 B1_150 %# >/dev/null" $ seq 150 | xargs -I %# -P 150 bash -c "sqlplus -s -l scott/book @m2.txt 1e6 B2_150 %# >/dev/null"
SCOTT@book> select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by method; METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA) -------------------- ---------- ---------------------- ------------- A1_150 150 16718 2507696 A2_150 150 18234 2735046 B1_150 150 17613 2641914 B2_150 150 21185 3177713
--//你可以可以看出sql语句分散后大概提高10%上下. --//另外我的测试还加入了sleep, $ seq 150 | xargs | tr ' ' + | bc -l 11325 --//11325/50 = 226.5秒,排除这个因素.平均每个扣除226.5/150 = 1.51秒.哎!!脚本写的有问题,考虑欠缺,应该sleep在前面,再次重复测试: --//从这次测试也看出,自己的测试设计不严谨,没有考虑一些细节问题,从另外一个方面也可以看出不使用绑定变量对数据库的危害,特别是oltp系统. --//补充: $ cat m1.txt set verify off host sleep $(echo &&3/50 | bc -l ) insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,'&&2') ; commit ; declare v_id number; v_d date; begin for i in 1 .. &&1 loop --select /*+ &&3 */ 1 into v_id from dual ; select /*+ &&3 */ sysdate into v_d from dual ; end loop; end ; / update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method='&&2'; commit; quit
$ cat m2.txt set verify off host sleep $(echo &&3/50| bc -l ) insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,'&&2') ; commit ; declare v_id number; v_d date; begin for i in 1 .. &&1 loop --select 1 into v_id from dual ; select sysdate into v_d from dual ; &n |