[20190219]那个更快(11g).txt
–//前几天测试11g query result cache rc latches时,链接http://blog.itpub.net/267265/viewspace-2632907/
–//有网友指出测试有问题,建立索引唯一,并不会导致select count(*) from t,选择索引执行.实际上执行计划还是全表扫描.
–//也就有了如下测试,不过结果有点让我吃惊,设置not null反而更慢.通过测试说明:
–//另外我也做了10g下的测试,链接如下:http://blog.itpub.net/267265/viewspace-2636321/ => [20190215]那个更快(10g).txt
1.环境:
scott@book> @ ver1
port_string version banner
—————————— ————– ——————————————————————————–
x86_64/linux 2.4.xx 11.2.0.4.0 oracle database 11g enterprise edition release 11.2.0.4.0 – 64bit production
create table t as select rownum id from dual ;
–//分析表略.
–//另外说明一下,先建立表主要避免编译过程时报错.
2.建立测试环境:
create table job_times (sid number, time_ela number,method varchar2(20));
create or replace procedure do_work
(
p_iterations in number
,p_method in varchar2
)
is
l_rowid rowid;
v_t number;
begin
insert into job_times values ( sys_context (‘userenv’, ‘sid’) ,dbms_utility.get_time ,p_method) returning rowid into l_rowid;
for i in 1 .. p_iterations
loop
select count (*) into v_t from t;
end loop;
update job_times set time_ela = dbms_utility.get_time – time_ela where rowid = l_rowid;
commit;
end;
/
create or replace procedure do_work1
(
p_iterations in number
,p_method in varchar2
)
is
l_rowid rowid;
v_t number;
begin
insert into job_times values ( sys_context (‘userenv’, ‘sid’) ,dbms_utility.get_time ,p_method) returning rowid into l_rowid;
for i in 1 .. p_iterations
loop
select count (*) into v_t from t where id=1;
end loop;
update job_times set time_ela = dbms_utility.get_time – time_ela where rowid = l_rowid;
commit;
end;
/
3.测试:
–//执行脚本如下:注一定要等n个会话执行完成在回车,进行下一项测试.
–//可以打开另外的会话执行select method,count(*),avg(time_ela),sum(time_ela) from job_times group by method order by 3 ;
–//确定测试是否完成.
$ cat bb.txt
delete from job_times;
commit ;
drop table t purge;
create table t as select rownum id from dual ;
execute sys.dbms_stats.gather_table_stats ( ownname => user,tabname => ‘t’,estimate_percent => null,method_opt => ‘for all columns size 1 ‘,cascade => true ,no_invalidate => false);
alter procedure do_work compile ;
alter procedure do_work1 compile ;
host sleep 5
host seq &&1 | xargs -i{} echo “sqlplus -s -l scott/&&2 <<< \”execute do_work(&&3,’null’)\” & ” | bash > /dev/null
host read -p ‘wait finish…’
create unique index pk_t on t(id);
alter table t modify (id not null);
host seq &&1 | xargs -i{} echo “sqlplus -s -l scott/&&2 <<< \”execute do_work(&&3,’notnull’)\” & ” | bash > /dev/null
host read -p ‘wait finish…’
host seq &&1 | xargs -i{} echo “sqlplus -s -l scott/&&2 <<< \”execute do_work1(&&3,’id=1_unique_index’)\” & ” | bash > /dev/null
host read -p ‘wait finish…’
drop index pk_t ;
create index pk_t on t(id);
host seq &&1 | xargs -i{} echo “sqlplus -s -l scott/&&2 <<< \”execute do_work1(&&3,’id=1_index’)\” & ” | bash > /dev/null
host read -p ‘wait finish…’
alter table t result_cache (mode force);
host seq &&1| xargs -i{} echo “sqlplus -s -l scott/&&2 <<< \”execute do_work(&&3,’result_cache’)\” & ” | bash > /dev/null
host read -p ‘wait finish…’
–//简单说明:执行需要3个参数,参数1:启动连接数,参数2:scott口令,参数3,循环次数.
–//执行如下: @ bb.txt 50 book 1e6
–//第1种方式:执行计划是全表扫描,逻辑读2(10g下这里是3),看到的等待事件是cursor: pin s.很奇怪11g下看不到latch: cache buffers chains相关等待事件.
–//第2种方式:建立唯一索引,加入约束id not null,这样执行计划index full scan,逻辑读1.看到的等待事件是latch: cache buffers chains,偶尔能看到cursor: pin s.
–//第3种方式:执行语句加入谓词id=1,这样执行计划index unique scan,逻辑读1.看到的等待事件是cursor: pin s,在11g下latch: cache buffers chains看不到.
注:在这种情况cbc latch减少一半比其它方式.
–//第4种方式:索引修改非唯一,执行语句加入谓词id=1,这样执行计划是index range scan,逻辑读1.看到的等待事件是latch: cache buffers chains.偶尔能看到cursor: pin s.
–//第5种方式:设置result_cache=force;逻辑读0,看到的等待事件是cursor: pin s.
–//测试结果如下:
scott@book> select method,count(*),round(avg(time_ela),0),sum(time_ela) from job_times group by method order by 3 ;
method count(*) round(avg(time_ela),0) sum(time_ela)
——————– ———- ———————- ————-
result_cache 50 8611 430536
id=1_unique_index 50 9494 474714
null 50 10664 533197
id=1_index 50 28160 1407987
notnull 50 29279 1463928
–//你可以发现结果按照快慢排序 result_cache => id=1_unique_index => null => id=1_index,notnull,实际上最后2个结果很接近.
–//使用result_cache 最快很好理解,为什么设置列null比not null快许多呢?
–//而且设置字段id null是全表扫描,至少2个逻辑读(对于ctas建立的表),而设置字段id not null 走的是快速全索引扫描(1个逻辑读).
–//看测试结果 not null的情况下几乎慢了3倍.
–//实际上字段设置 not null更慢.因为这时出现cursor: pin s 外,还出现外还大量出现 latch: cache buffers chains,而全表扫描
–//反而不出现latch: cache buffers chains等待事件.这样设置not null反而更慢.
–//11g在处理latch: cache buffers chains上做了一些优化,读读情况下有时候看不到cbc latch.
–//不过id=1_unique_index这样的情况下反而比10g执行要慢.
–//在10g下测试如下:
scott@test> select method,count(*),round(avg(time_ela),0),sum(time_ela) from job_times group by method order by 3 ;
method count(*) round(avg(time_ela),0) sum(time_ela)
——————– ———- ———————- ————-
id=1_unique_index 50 4864 243192
notnull 50 34134 1706713
id=1_index 50 34703 1735173
null 50 37234 1861717
–//实际上10g,11g是硬件配置一样,os安装也一样.
–//附上监测wait脚本:
$ cat wait.sql
select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,status,state,wait_time_micro,seconds_in_wait,wait_class
from v$session where ( wait_class<>’idle’ or (status=’active’ and state=’waited known time’))
and sid not in (select sid from v$mystat where rownum=1)
order by event ;