RAC环境单独节点插入数据也会导致全局等待（一）

在RAC环境中，登陆到一个实例，在处理的数据完全与另外实例内存中数据无关的情况下，也会导致gc全局等待产生。

这一篇描述现象。

 

 

环境如下：

SQL> conn yangtk/yangtk      

已连接。

SQL> select * from v$version;

BANNER

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

Oracle Database10gEnterpriseEdition Release10.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类型的等待。

 

 

（以上内容摘于网络，如有侵权，请告之，将第一时间删除）