7 积分	1 好友	10 主题

发消息

[SQL调优] 进程hang在了gc cr request等待事件上。

1^#

发表于 2013-8-30 00:30:33 | 查看: 4944| 回复: 4

   大家好：
            环境是10.2.0.5.12 aix 6.1

         目前存在的问题是，awr功能hang住。其实简单一下就是，下面这条SQL
         select * from WRH$_SQL_PLAN where rowid=DBMS_ROWID.ROWID_CREATE(1,8989,3,162822,0);
在1节点上能正常访问，而在2节点上面切出现了gc cr request等待。
下面是1节点
SQL> select * from WRH$_SQL_PLAN where rowid=DBMS_ROWID.ROWID_CREATE(1,8989,3,162822,0);

SNAP_ID    DBID SQL_ID          PLAN_HASH_VALUE       ID OPERATION                   OPTIONS
---------- ---------- ------------------ --------------- ---------- ------------------------------ ------------------------------
OBJECT_NODE                                                                                                                      OBJECT# OBJECT_OWNER
-------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------
OBJECT_NAME                   OBJECT_ALIAS                                                    OBJECT_TYPE       OPTIMIZER          PARENT_ID    DEPTH POSITION SEARCH_COLUMNS    COST
------------------------------- ----------------------------------------------------------------- -------------------- -------------------- ---------- ---------- ---------- -------------- ----------
CARDINALITY    BYTES OTHER_TAG
可以正常访问。

下面是2节点
select * from WRH$_SQL_PLAN where rowid=DBMS_ROWID.ROWID_CREATE(1,8989,3,162822,0);
一直不还回，通过event看到gc cr request等待，通过10046看到的是
WAIT #6: nam='gc cr block lost' ela= 844446 p1=3 p2=162822 p3=1 obj#=8989 tim=6010923969199
WAIT #6: nam='cr request retry' ela= 4 file#=3 block#=162822 p3=0 obj#=8989 tim=6010923969336
WAIT #6: nam='gc cr block lost' ela= 388785 p1=3 p2=162822 p3=1 obj#=8989 tim=6010924358255
WAIT #6: nam='cr request retry' ela= 3 file#=3 block#=162822 p3=0 obj#=8989 tim=6010924358385
WAIT #6: nam='gc cr block lost' ela= 825706 p1=3 p2=162822 p3=1 obj#=8989 tim=6010925184236
WAIT #6: nam='cr request retry' ela= 4 file#=3 block#=162822 p3=0 obj#=8989 tim=6010925184359
WAIT #6: nam='gc cr block lost' ela= 829845 p1=3 p2=162822 p3=1 obj#=8989 tim=6010926014338
WAIT #6: nam='cr request retry' ela= 4 file#=3 block#=162822 p3=0 obj#=8989 tim=6010926014467
WAIT #6: nam='gc cr block lost' ela= 846922 p1=3 p2=162822 p3=1 obj#=8989 tim=6010926861518
WAIT #6: nam='cr request retry' ela= 3 file#=3 block#=162822 p3=0 obj#=8989 tim=6010926861640
WAIT #6: nam='gc cr block lost' ela= 831026 p1=3 p2=162822 p3=1 obj#=8989 tim=6010927692791
WAIT #6: nam='cr request retry' ela= 7 file#=3 block#=162822 p3=0 obj#=8989 tim=6010927692917
WAIT #6: nam='gc cr block lost' ela= 441770 p1=3 p2=162822 p3=1 obj#=8989 tim=6010928134789
WAIT #6: nam='cr request retry' ela= 3 file#=3 block#=162822 p3=0 obj#=8989 tim=6010928134921
WAIT #6: nam='gc cr block lost' ela= 922933 p1=3 p2=162822 p3=1 obj#=8989 tim=6010929057977
WAIT #6: nam='cr request retry' ela= 4 file#=3 block#=162822 p3=0 obj#=8989 tim=6010929058108

不知道是什么原因导致的。还请大家帮忙分析一下。

ml也可以见我开的SR：
your Service Request Number 3-7723737691

分享0

收藏0 回复只看该作者道具举报

harryzhang

2^#

发表于 2013-8-30 10:14:37

网络是否有丢包？

netstat -in

回复只看该作者道具举报

qq7343696

3^#

发表于 2013-8-30 10:59:30

Name  Mtu Network    Address          Ipkts Ierrs Opkts Oerrs  Coll
en0 1500  link#2    6c.ae.8b.5.1e.a0       0    0    15    0    0
en0 1500  192.168.128 192.168.128.2          0    0    15    0    0
en16  1500  link#3    0.21.5e.8b.47.bc  2255953    0  2180744    3    0
en16  1500  1.1.1    1.1.1.1          2255953    0  2180744    3    0
en18  1500  link#4    0.21.5e.8b.53.d4  2214056    0  2118294    3    0
en18  1500  10.10.10 10.10.10.1       2214056    0  2118294    3    0
en20  1500  link#5    34.40.b5.a8.ca.38 1361622491    0 3957098164    2    0
en20  1500  130.85.50 130.85.50.20    1361622491    0 3957098164    2    0
en20  1500  130.85.50 130.85.50.22    1361622491    0 3957098164    2    0
en21  1500  link#6    5c.f3.fc.ed.45.4e 3993155647    0 3405758624 155666    0
en21  1500  192.168.1 192.168.1.2    3993155647    0 3405758624 155666    0
lo0 16896 link#1                      18925117    0 18925063    0    0
lo0 16896 127       127.0.0.1       18925117    0 18925063    0    0
lo0 16896 ::1%1                      18925117    0 18925063    0    0

回复只看该作者道具举报

qq7343696

4^#

发表于 2013-8-30 10:59:46

harryzhang 发表于 2013-8-30 10:14
网络是否有丢包？

netstat -in

Name  Mtu Network    Address          Ipkts Ierrs Opkts Oerrs  Coll
en0 1500  link#2    6c.ae.8b.5.1e.a0       0    0    15    0    0
en0 1500  192.168.128 192.168.128.2          0    0    15    0    0
en16  1500  link#3    0.21.5e.8b.47.bc  2255953    0  2180744    3    0
en16  1500  1.1.1    1.1.1.1          2255953    0  2180744    3    0
en18  1500  link#4    0.21.5e.8b.53.d4  2214056    0  2118294    3    0
en18  1500  10.10.10 10.10.10.1       2214056    0  2118294    3    0
en20  1500  link#5    34.40.b5.a8.ca.38 1361622491    0 3957098164    2    0
en20  1500  130.85.50 130.85.50.20    1361622491    0 3957098164    2    0
en20  1500  130.85.50 130.85.50.22    1361622491    0 3957098164    2    0
en21  1500  link#6    5c.f3.fc.ed.45.4e 3993155647    0 3405758624 155666    0
en21  1500  192.168.1 192.168.1.2    3993155647    0 3405758624 155666    0
lo0 16896 link#1                      18925117    0 18925063    0    0
lo0 16896 127       127.0.0.1       18925117    0 18925063    0    0
lo0 16896 ::1%1                      18925117    0 18925063    0    0

回复只看该作者道具举报

Maclean Liu(刘相兵

5^#

发表于 2013-8-30 11:01:45

晕，至少给个aWR吧？别提三无问题啊！！！

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册