ITPub博客

首页 > 数据库 > Oracle > [20190215]那个更快(10g).txt

[20190215]那个更快(10g).txt

原创 Oracle 作者:lfree 时间:2019-02-19 09:00:10 0 删除 编辑

[20190215]那个更快(10g).txt


--//昨天测试11g Query Result Cache RC Latches时,链接http://blog.itpub.net/267265/viewspace-2632907/

--//有网友指出测试有问题,建立索引唯一,并不会导致select count(*) from t,选择索引执行.实际上执行计划还是全表扫描.

--//不过我后面的测试还是让我有点吃惊....设置not null反而更慢..

--//在10g下重复测试看看.


1.环境:

SCOTT@test> @ &r/ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- ----------------------------------------------------------------

x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi


create table t as select rownum id from dual ;

create unique index pk_t on t(id);

--//分析表略.


--//多个会话同时并发执行如下语句.

--//方式一:注这个时候索引没用,因为id可以为null.

select count(*) from t;


--//方式二:

--//加入约束:alter table t modify (id  not null);也就是使用索引

alter table t modify (id  not null);

select count(*) from t;


--//方式三(索引唯一):

select count(*) from t where id=1;


--//方式四:(索引不唯一)

drop index pk_t;

create index pk_t on t(id);

select count(*) from t where id=1;



--//大家认为那个更快完成或者讲花的时间更少?通过测试说明问题.


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.测试:

--//测试脚本如下,10g不支持result cache.

--//执行脚本如下:注一定要等50个会话执行完成在回车,进行下一项测试.

--//可以打开另外的会话执行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);


hos seq &&1 | xargs -I{} echo "sqlplus -s -l  scott/&&2 <<< \"execute do_work(1e6,'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(1e6,'notnull')\" & "  | bash > /dev/null

host read -p 'wait finish...'


host seq &&1 | xargs -I{} echo "sqlplus -s -l  scott/&&2 <<< \"execute do_work1(1e6,'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(1e6,'id=1_index')\" & "  | bash > /dev/null

host read -p 'wait finish...'


--//注:参数1 启动连接数, 参数2 scott的密码.

--//说明:

--//第1次,id null,全表扫描T,逻辑读3个.主要等待事件是latch: cache buffers chains.

--//第2次:id not null,建立唯一索引.执行计划走INDEX FULL SCAN,逻辑读1.主要等待事件是latch: cache buffers chains.

--//第3次:查询增加where id=1,执行计划走 INDEX UNIQUE SCAN,逻辑读1,主要等待事件是cursor: pin S .

--//第4次:查询增加where id=1,由于索引不唯一,执行计划走INDEX RANGE SCAN,逻辑读1,主要等待事件是latch: cache buffers chains.


--//测试结果如下:

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                   4789        239432

notnull                      50                  34493       1724641

id=1_index                   50                  34568       1728409

null                         50                  38319       1915970


--//第2次测试结果:

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下not null 与id=1 索引非唯一的情况测试很接近,这个很好理解逻辑读都是1.主要等待事件都是latch: cache buffers chains.

--//而null的情况全表扫描的逻辑读是3,这样发生cbc latch的等待事件更加严重,这样比逻辑读1的情况下更加慢一些。

--//而id=1 并且使用唯一索引的情况下,oracle对这样情况进行特殊优化,仅仅有1次cbc latch拴锁。主要等待事件是cursor: pin S .

--//顺便贴上看等待事件的脚本(wait10g.sql).

select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,state,wait_time,seconds_in_wait from v$session where wait_class<>'Idle' 

and sid not in (select sid from v$mystat where rownum=1) 

order by event ;


--//而11g下呢? 贴出11.2.0.4下的测试结果如下:

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                   8805        440251

id=1_unique_index               50                   9505        475229

null                            50                  10841        542033

notnull                         50                  28310       1415522

id=1_index                      50                  29531       1476574


--//你可以发现与11g很大不同,10g下id 字段 null 与id 字段 not null差别不大.两者都后大量出现latch: cache buffers chains.

--//而id=1,如果看vage的书就明白,仅仅出现1次cbc latch.而且非常快.


--//实际上11g的环境与10g环境服务器配置基本一样.感觉11g在latch: cache buffers chains做了一些改进,全表扫描很奇怪看不到

--//cbc latch的等待事件.但是11g虽然减少cbc latch相关等待事件时间,但是通过索引唯一读取记录的需要时间是增加的(对比上面10g的

--//测试结果),其中一些细节我自己也讲不好,就不说明了.

--//另外11g下id not null,建立唯一索引.执行计划走INDEX FULL SCAN,逻辑读1.主要等待事件是latch: cache buffers chains.

--//这是11g下有如下两种情况能看到cbc latch的情况(notnull,id=1_index),尽管逻辑读为1,你可以发现是最慢的,而且时间比null

--//做全表扫描(逻辑读3)的情况下还慢1倍还多。


--//顺便贴上看等待事件的脚本(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 ;


--//补充11g的测试脚本如下,加入了将表设置为result_cahe=force的情况。


$ 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);


hos seq &&1 | xargs -I{} echo "sqlplus -s -l  scott/&&2 <<< \"execute do_work(1e6,'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(1e6,'notnull')\" & "  | bash > /dev/null

host read -p 'wait finish...'


host seq &&1 | xargs -I{} echo "sqlplus -s -l  scott/&&2 <<< \"execute do_work1(1e6,'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(1e6,'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(1e6,'result_cache')\" & "  | bash > /dev/null

host read -p 'wait finish...'


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2636321/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2858
  • 访问量
    6644846