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