ITPub博客

首页 > Linux操作系统 > Linux操作系统 > RAC环境单独节点插入数据也会导致全局等待(一)

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

原创 Linux操作系统 作者:yangtingkun 时间:2010-12-11 23:59:40 0 删除 编辑

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类型的等待。

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-681565/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10487708