[20190219]那个更快(11g).txt

[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 ;