0 积分	0 好友	5 主题

发消息

[Exadata硬件] 大量"gc buffer busy"长时间等待同一个数据块，请教！

1^#

发表于 2015-1-6 20:53:19 | 查看: 4473| 回复: 4

本帖最后由 data_data 于 2015-1-15 15:16 编辑

描述：rac 2节点，发现节点1出现大量"gc buffer busy"，均为insert 语句，另一节点没有异常等待事件。
基本情况：
--所有sql一共集中等待3个数据块
PRODUCTION:sys@uwnms21 SQL>select p1,p2,count(*) from v$session where event='gc buffer busy'  group by p1,p2
  2  ;

      P1       P2 COUNT(*)
---------- ---------- ----------
   173    174521       8
   134    36996       13
   228 2424305       4


--这些sql都是insert语句，且长时间等待同一个块。
PRODUCTION:sys@uwnms21 SQL>select sid,SERIAL#,sql_id,seconds_in_wait,p1,p1text,p2,p2text,p3,p3text from v$session where event='gc buffer busy' ;

   SID SERIAL# SQL_ID       SECONDS_IN_WAIT       P1 P1TEXT          P2 P2TEXT          P3 P3TEXT
---------- ---------- ------------- --------------- ---------- ---------- ---------- ---------- ---------- ----------
   102    60749 ftx1b8upcpw05          3600       134 file#          36996 block#       65537 id#
   125    17613 dyz7wgzwuymsd          241       173 file#       174521 block#       65537 id#
   130    49629 8yancth5ujnhz          43896       228 file#       2424305 block#       65537 id#
   159    8525 dyz7wgzwuymsd          4695       134 file#          36996 block#       65537 id#
   172    51171 awnh92gxhb8cg          2702       134 file#          36996 block#       65537 id#
   200    23593 2abnyt51tkkfq          6571       134 file#          36996 block#       65537 id#
   219    19001 cnrdxxu2gumjg          10703       134 file#          36996 block#       65537 id#
   237    39017 ap4f5hvrp5p2s          1003       134 file#          36996 block#       65537 id#
   284    42716 3wfp1vbg58m5k          714       134 file#          36996 block#       65537 id#
   311    54300 8yancth5ujnhz          25037       228 file#       2424305 block#       65537 id#
   353    24023 5g0yg9zg66yqa          3449       134 file#          36996 block#       65537 id#
   366    47084 8yancth5ujnhz          48155       228 file#       2424305 block#       65537 id#
   370    9253 5g0yg9zg66yqa          4778       134 file#          36996 block#       65537 id#
   416    24072 5g0yg9zg66yqa          34687       173 file#       174521 block#       65537 id#
   493    42096 5g0yg9zg66yqa          15940       173 file#       174521 block#       65537 id#
   715    64450 f33wqpy746r1u          41231       173 file#       174521 block#       65537 id#
   800    43739 47hnpg4602smh          3400       134 file#          36996 block#       65537 id#
   863    32604 gqudmnaqf4k5k          10980       173 file#       174521 block#       65537 id#
   902    38768 2dh2w0vtwncgj             0       322 file#       2760828 block#       65537 id#
   906    24278 f33wqpy746r1u          4611       134 file#          36996 block#       65537 id#
   925    11294 8yancth5ujnhz          2800       228 file#       2424305 block#       65537 id#
   1084    38126 f33wqpy746r1u          14097       173 file#       174521 block#       65537 id#
   1114    43939 228ma2kkb1f98          37218       134 file#          36996 block#       65537 id#
   1115    29400 f9t7xuxd0ytt9          6493       173 file#       174521 block#       65537 id#
   1161    3957 ap4f5hvrp5p2s          6137       173 file#       174521 block#       65537 id#
   1271    54450 f33wqpy746r1u          8201       134 file#          36996 block#       65537 id#
--所有块的状态都是being read from disk,且通过查询，这些块都是段头块。
SQL>select objd,ts# ,status from v$bh where FILE#=134 and block#=36996;

   OBJD       TS# STATUS
---------- ---------- -------
9982200       49 read

SQL>select objd,ts# ,status from v$bh where FILE#=173 and block#=174521;
   OBJD       TS# STATUS
---------- ---------- -------
7228064       43 read
7228064       43 read

SQL>select objd,ts# ,status from v$bh where FILE#=228 and block#=2424305;

   OBJD       TS# STATUS
---------- ---------- -------
7252591       48 read

#########################search object############################
--以下是根据p1,p2查询到的对象。
select owner,object_type ,object_name from dba_objects where data_object_id=9982200 or object_id=9982200;

OWNER                         OBJECT_TYPE       OBJECT_NAME
------------------------------ ------------------- ------------------------------
PRDB                         INDEX PARTITION    IDX1_TPD_RADIO_BTS_GD

select owner,object_type ,object_name from dba_objects where data_object_id=7228064 or object_id=7228064;

no rows selected

select owner,object_type ,object_name from dba_objects where data_object_id=7252591 or object_id=7252591;

OWNER                         OBJECT_TYPE       OBJECT_NAME
------------------------------ ------------------- ------------------------------
PRDB                         TABLE PARTITION    TPD_RADIO_BTS_GD

插入的表为临时表，temp_source，tmp_tpc_radio_db，tmp_radio2bsc_2，与等待的块对象并无直接关系。
而且从另一节点 v$bh视图中并找到所等待的块。
查询表TPD_RADIO_BTS_GD也没有被锁定。

第一次遇到这种情况，请教！

分享0