RAC的cache fusion對資料塊訪問效率的影響

說明:測試RAC的cache fusion對資料塊訪問效率的影響

第一步，建立測試表

BYS@ bysrac1>create table test9 as select * from dba_objects;
Table created.
################################

第二步：在節點1進行更新不提交併查詢

BYS@ bysrac1>conn / as sysdba
Connected.
SYS@ bysrac1>select instance_name from v$instance;
INSTANCE_NAME
----------------
bysrac1
SYS@ bysrac1>select distinct sid from v$mystat;
       SID
----------
        53
col spid for a10
SYS@ bysrac1>col machine for a15
SYS@ bysrac1>select b.spid,a.sid,a.serial#,a.machine from v$session a,v$process b where a.paddr =b.addr and a.sid='53';
SPID              SID    SERIAL# MACHINE
---------- ---------- ---------- ---------------

15241              53        104 bysrac1.bys.com

SYS@ bysrac1>alter session set tracefile_identifier=test9_1;     給TRACE語句加個標識test9_1
Session altered.

SYS@ bysrac1>exec sys.dbms_system.set_ev('53','104',10046,12,'');    開啟TRACE語句
PL/SQL procedure successfully completed.
SYS@ bysrac1>update bys.test9 set subobject_name = 'test' where object_id<20000;
16914 rows updated.
SYS@ bysrac1>set autotrace traceonly stat     讓只輸出統計資訊，不輸入資料。
SYS@ bysrac1>select * from bys.test9 where object_id <20000;
16914 rows selected.
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1342  consistent gets
          0  physical reads
          0  redo size
     855042  bytes sent via SQL*Net to client
      12816  bytes received via SQL*Net from client
       1129  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      16914  rows processed
SYS@ bysrac1>exec sys.dbms_system.set_ev('53','104',10046,0,'');
PL/SQL procedure successfully completed.
SYS@ bysrac1>exit
############################################################

第三步：在節點2對同一表進行查詢

SQL> conn / as sysdba
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
bysrac2
SQL> select distinct sid from v$mystat;
       SID
----------
        37
col spid for a10
SQL> col machine for a15
SQL> select b.spid,a.sid,a.serial#,a.machine from v$session a,v$process b where a.paddr =b.addr and a.sid='37';
SPID              SID    SERIAL# MACHINE
---------- ---------- ---------- ---------------
17047              37        447 bysrac2.bys.com
SQL> alter session set tracefile_identifier=test9_2;     給TRACE語句加個標識test9_2
Session altered.
SQL> exec sys.dbms_system.set_ev('37','447',10046,12,'');  開啟TRACE語句
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly stat
SQL> select * from bys.test9 where object_id <20000;
16914 rows selected.
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1342  consistent gets
          0  physical reads
          0  redo size
     855042  bytes sent via SQL*Net to client
      12816  bytes received via SQL*Net from client
       1129  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      16914  rows processed
SQL> exec sys.dbms_system.set_ev('37','447',10046,0,'');
PL/SQL procedure successfully completed.
SQL> exit
####################################

第四步：分別在兩個節點上使用tkprof將TRACE檔案生成方便閱讀的格式

節點1：
[oracle@bysrac1 trace]$ ls
alert_bysrac1.log
bysrac1_ora_15241_TEST9_1.trc
bysrac1_ora_15241_TEST9_1.trm
[oracle@bysrac1 trace]$ cd $ORACLE_HOME/bin
[oracle@bysrac1 bin]$ tkprof  /u01/diag/rdbms/bysrac/bysrac1/trace/bysrac1_ora_15241_TEST9_1.trc  /home/oracle/test9_1.trc
TKPROF: Release 11.2.0.1.0 - Development on Thu Oct 24 12:48:22 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
[oracle@bysrac1 bin]$ cd
[oracle@bysrac1 ~]$ ls
alert_bysrac1.log  Desktop  fullback.sh  oradiag_oracle  racfull_5_1  racfull_6_1 test9_1.trc
#####################
節點2：
[oracle@bysrac2 ~]$ cd -
/u01/diag/rdbms/bysrac/bysrac2/trace
[oracle@bysrac2 trace]$ ls
alert_bysrac2.log      bysrac2_ora_17047_TEST9_2.trc
bysrac2_arc2_5210.trc  bysrac2_ora_17047_TEST9_2.trm
bysrac2_arc2_5210.trm
[oracle@bysrac2 trace]$ cd $ORACLE_HOME/bin
[oracle@bysrac2 bin]$ tkprof /u01/diag/rdbms/bysrac/bysrac2/trace/bysrac2_ora_17047_TEST9_2.trc /home/oracle/test9_2.trc
TKPROF: Release 11.2.0.1.0 - Development on Thu Oct 24 12:49:01 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
[oracle@bysrac2 bin]$ cd
[oracle@bysrac2 ~]$ ls
alert_bysrac2.log  inita.ora  oradiag_oracle  test9_2.trc
######################################

第五步：檢視重新格式化的TRACE檔案轉儲中資訊如下

節點1：---內容較多，僅擷取了查詢語句部分
********************************************************************************
SQL ID: 6ns41jwh1fz2u
Plan Hash: 1544281142
select *
from
 bys.test9 where object_id <20000
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1129      0.06       0.15          0       1342          0       16914
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1131      0.06       0.15          0       1342          0       16914
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows     Row Source Operation
-------  ---------------------------------------------------
  16914  TABLE ACCESS FULL TEST9 (cr=1342 pr=0 pw=0 time=184505 us cost=67 size=3484845 card=16835)
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    1129        0.00          0.01
  SQL*Net message from client                  1129        0.00          0.49
********************************************************************************
節點2：---內容較多，僅擷取了查詢語句部分。--可以看到有gc cr multi block request等待事件,同時可以對比出節點2執行此查詢所需CPU及查詢總時間較多。
gc cr multi block request實際就是global cache cr multi block request，10G以後global cache被簡稱為gc,在RAC應用系統裡面，這是一個常見的等待事件。
工作原理：
當程式請求資料庫塊時，首先會在本地的CACHE裡面檢視是否存在，這種檢視是根據DBA (Data Block Address) 轉化為cache buffers chains,然後再從hash bucket確認是否存在。
如果在本地沒發現有塊的CACHE，程式就會請求resource master授予共享訪問給資料塊，然後再去獲取資料塊的CACHE。
如果請求的BLOCK CACHE在遠端的節點，resource master就會使用內部通訊把遠端的CACHE傳輸到本地。當請求的CACHE BUFFER是共享模式的，遠端節點就會克隆一個然後傳輸到本地。非則，就建立PI映像，然後傳輸到本地。
********************************************************************************
SQL ID: 6ns41jwh1fz2u
Plan Hash: 1544281142
select *
from
 bys.test9 where object_id <20000
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1129      0.09       0.65          0       1342          0       16914
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1131      0.09       0.65          0       1342          0       16914
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows     Row Source Operation
-------  ---------------------------------------------------
  16914  TABLE ACCESS FULL TEST9 (cr=1342 pr=0 pw=0 time=92380 us cost=67 size=3484845 card=16835)
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    1129        0.00          0.01
  Disk file operations I/O                        1        0.00          0.00
  ges message buffer allocation                  21        0.00          0.00
  gc cr multi block request                      25        0.04          0.44
  SQL*Net message from client                  1129        0.00          0.35
********************************************************************************