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

[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...'