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

最新推荐文章于 2024-09-30 09:09:52 发布

ckawt40802

最新推荐文章于 2024-09-30 09:09:52 发布

阅读量121

点赞数

文章标签：数据库

在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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-681565/