GC Buffer Busy Waits处理

http://www.dbaleading.com/Article.asp?id=40

http://www.ardentperf.com/2007/09/12/gc-buffer-busy-waits-in-rac-finding-hot-blocks/

使用ash

1、从dba_hist_snapshot中查得相关时间窗口

select min(begin_interval_time) min, max(end_interval_time) max 
from dba_hist_snapshot
where snap_id between 12831 and 12838;

MIN                            MAX
------------------------------ ------------------------------
12-SEP-07 09.00.17.451 AM      12-SEP-07 05.00.03.683 PM

这是我们要使用的时间窗口：今天 9am to 5pm .

2、查看相关的最多的等待类

select wait_class_id, wait_class, count(*) cnt 
from dba_hist_active_sess_history
where snap_id between 12831 and 12838
group by wait_class_id, wait_class
order by 3;

WAIT_CLASS_ID WAIT_CLASS                            CNT
------------- ------------------------------ ----------
   3290255840 Configuration                         169
   2000153315 Network                               934
   4108307767 System I/O                           7199
   3386400367 Commit                               7809
   4217450380 Application                         12248
   3875070507 Concurrency                         14754
   1893977003 Other                               35499
                                                  97762
   3871361733 Cluster                            104810
   1740759767 User I/O                           121999

我们看到在 ASH中有相当多的Cluster等待事件。

3、下一步，查看相关的具体等待事件

select event_id, event, count(*) cnt from dba_hist_active_sess_history
where snap_id between 12831 and 12838 and wait_class_id=3871361733
group by event_id, event
order by 3;

  EVENT_ID EVENT                                           CNT
---------- ---------------------------------------- ----------
3905407295 gc current request                                4
3785617759 gc current block congested                       10
2705335821 gc cr block congested                            15
 512320954 gc cr request                                    16
3794703642 gc cr grant congested                            17
3897775868 gc current multi block request                   17
1742950045 gc current retry                                 18
1445598276 gc cr disk read                                 148
1457266432 gc current split                                229
2685450749 gc current grant 2-way                          290
 957917679 gc current block lost                           579
 737661873 gc cr block 2-way                               699
2277737081 gc current grant busy                           991
3570184881 gc current block 3-way                         1190
3151901526 gc cr block lost                               1951
 111015833 gc current block 2-way                         2078
3046984244 gc cr block 3-way                              2107
 661121159 gc cr multi block request                      4092
3201690383 gc cr grant 2-way                              4129
1520064534 gc cr block busy                               4576
2701629120 gc current block busy                         14379
1478861578 gc buffer busy                                67275

我们看到gc buffer busy是最多的.. 我们看到从今天早9到下午5点，在ash采样期间共有67,275个sessions在等待此事件。让我们看下，在被采样的时候，这些session 在执行什么sql.

4、我们看下这些sql的sql_id

select sql_id, count(*) cnt from dba_hist_active_sess_history
where snap_id between 12831 and 12838 
and event_id in (2701629120, 1478861578)
group by sql_id
having count(*)>1000
order by 2;

SQL_ID               CNT
------------- ----------
6kk6ydpp3u8xw       1011
2hvs3mpab5j0w       1022
292jxfuggtsqh       1168
3mcxaqffnzgfw       1226
a36pf34c87x7s       1328
4vs8wgvpfm87w       1390
22ggtj4z9ak3a       1574
gsqhbt5a6d4uv       1744
cyt90uk11a22c       2240
39dtqqpr7ygcw       4251
8v3b2m405atgy      42292

 

5、查看相关sql的内容，确定是哪些表引起的。

select sql_text from dba_hist_sqltext where sql_id='8v3b2m405atgy';

SQL_TEXT
---------------------------------------------------------------------------
insert into bigtable(id, version, client, cl_business_id, cl_order_id, desc

select count(distinct(current_obj#)) from dba_hist_active_sess_history
where snap_id between 12831 and 12838 
and event_id=1478861578 and sql_id='8v3b2m405atgy';

COUNT(DISTINCT(CURRENT_OBJ#))
-----------------------------
                           14

select current_obj#, count(*) cnt from dba_hist_active_sess_history
where snap_id between 12831 and 12838 
and event_id=1478861578 and sql_id='8v3b2m405atgy'
group by current_obj#
order by 2;

CURRENT_OBJ#        CNT
------------ ----------
     3122841          1
     3122868          3
     3173166          4
     3324924          5
     3325122          8
     3064307          8
          -1         10
     3064369        331
           0        511
     3122795        617
     3064433        880
     3208619       3913
     3208620       5411
     3208618      22215


select object_id, owner, object_name, subobject_name, object_type from dba_objects
where object_id in (3208618, 3208619, 3208620);

 OBJECT_ID OWNER      OBJECT_NAME                    SUBOBJECT_NAME                 OBJECT_TYPE
---------- ---------- ------------------------------ ------------------------------ -------------------
   3208618 JSCHDER    BIGTABLE_LOG                   P_2007_09                      TABLE PARTITION
   3208619 JSCHDER    BIGTABL_LG_X_ID                P_2007_09                      INDEX PARTITION
   3208620 JSCHDER    BIGTABL_LG_X_CHANGE_DATE       P_2007_09                      INDEX PARTITION

6、进一步，我们确认是哪些块引起的，并确定是不是表头块。

select current_file#, current_block#, count(*) cnt 
from dba_hist_active_sess_history
where snap_id between 12831 and 12838 
and event_id=1478861578 and sql_id='8v3b2m405atgy' 
and current_obj# in (3208618, 3208619, 3208620)
group by current_file#, current_block#
having count(*)>50
order by 3;

CURRENT_FILE# CURRENT_BLOCK#        CNT
------------- -------------- ----------
         1330         238073         51
         1542          22645         55
         1487         237914         56
         1330         238724         61
         1330         244129         76
         1487         233206        120


select segment_name, header_file, header_block
from dba_segments where owner='JHEIDER' and partition_name='P_2007_09'
and segment_name in ('PLACEMENTS_LOG','PLCMNTS_LG_X_ID',
  'PLCMNTS_LG_X_CHANGE_DATE');

SEGMENT_NAME                   HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
BIGTABL_LG_X_CHANGE_DATE              1207       204809
BIGTABL_LG_X_ID                       1207       196617
BIGTABLE_LOG                          1209        16393

7、同时，也可用如下的脚本来测试：

col object format a60
col i format 99
select * from (
select o.owner||'.'||o.object_name||decode(o.subobject_name,NULL,'','.')||
  o.subobject_name||' ['||o.object_type||']' object,
  instance_number i, stat
from (
  select obj#||'.'||dataobj# obj#, instance_number, sum(
GC_BUFFER_BUSY_DELTA
) stat
  from dba_hist_seg_stat
  where (snap_id between 12831 and 12838)
  and (instance_number between 1 and 6)
  group by rollup(obj#||'.'||dataobj#, instance_number)
  having obj#||'.'||dataobj# is not null
) s, dba_hist_seg_stat_obj o
where o.dataobj#||'.'||o.obj#=s.obj#
order by max(stat) over (partition by s.obj#) desc,
  o.owner||o.object_name||o.subobject_name, nvl(instance_number,0)
) where rownum<=40;

具体的相关解释，，这里不做具体，自己figure out.

转载于:https://www.cnblogs.com/taowang2016/archive/2013/04/22/3035452.html