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

最新推荐文章于 2022-03-14 17:42:01 发布

weixin_33894640

最新推荐文章于 2022-03-14 17:42:01 发布

阅读量126

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/19880614/1228168

版权

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

oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html

转载于:https://blog.51cto.com/19880614/1228168

weixin_33894640

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle RAC环境单独节点插入数据也会导致全局等待（一）

在RAC环境中，登陆到一个实例，在处理的数据完全与另外实例内存中数据无关的情况下，也会导致gc全局等待产生。这一篇描述现象。环境如下：SQL> conn yangtk/yangtk 已连接。SQL> select * from v$version;BANNER------------------------------------------------...
复制链接

扫一扫