RAC環境單獨節點插入資料也會導致全域性等待（一）

在RAC環境中，登陸到一個例項，在處理的資料完全與另外例項記憶體中資料無關的情況下，也會導致gc全域性等待產生。

這一篇描述現象。

 

 

環境如下：

SQL> conn yangtk/yangtk       
已連線。
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
testrac1

SQL> select instance_name from gv$instance;

INSTANCE_NAME
----------------
testrac1
testrac2

在伺服器上本機登陸到節點1，為了確保節點2中不會有任何資料快取，所有物件都完全在節點1上建立：

SQL> create table t                 
  2  as select *          
  3  from dba_objects
  4  where 1 = 0;

表已建立。

SQL> create table t_big
  2  as select a.*
  3  from dba_objects a, dba_sequences b;

表已建立。

監測剛才的會話的等待事件：

SQL> conn / as sysdba
已連線。
SQL> select sid from v$session where username = 'YANGTK';

       SID
----------
       143

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SQL*Net message from client    driver id  1650815232 #bytes              1                     0

利用生成的t_big表向t表中插入大量的資料：

SQL> insert into t
  2  select *
  3  from t_big;

已建立6430135行。

在插入的同時，檢查這個會話的等待事件：

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
gc current multi block request file#               7 block#          44968 id#          33554433

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
enq: FB - contention           name|mode  1178730502 tablespace          7 dba          29405707
                                                      #

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           2282 blocks             16

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           3178 blocks             16

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           3962 blocks             15

SQL> select event, p1text, p1, p2text, p2, p3text, p3
from v$session_wait
  2    3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           4730 blocks             15

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           5563 blocks             16

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
gc current multi block request file#               7 block#          50920 id#          33554433

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           7691 blocks             16

.
.
.

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
gc cr multi block request      file#               7 block#          42643 class#              1

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#          43179 blocks             16

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#          44283 blocks             16

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
gc current multi block request file#               7 block#          88552 id#          33554433

可以看到，除了正常的產生大量的db file scattered read之外，插入還產生了gc current multi block request以及gc cr multi block request等待事件。

而如果關閉例項2：

bash-2.03$ srvctl stop inst -d testrac -i testrac2

下面清除t表：

SQL> truncate table t;

表被截斷。

再次測試插入：

SQL> insert into t
  2  select * 
  3  from t_big;

已建立6430135行。

檢查等待事件：

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
row cache lock                 cache id            5 mode                0 request             5

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           2698 blocks             16

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           3360 blocks             16

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
row cache lock                 cache id            5 mode                0 request             5

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           4650 blocks             16

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           5019 blocks             16

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           5387 blocks             16

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           5813 blocks             16

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           6283 blocks             16

SQL> select event, p1text, p1, p2text, p2, p3text, p3
  2  from v$session_wait
  3  where sid = 143;

EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read         file#               7 block#           6827 blocks             16

則Oracle不會導致任何的gc型別的等待。