前一段时间巡检发现一套数据库归档量近几日发生了爆增，虽然最终定位并非数据库自身原因，这里也总结下分析步骤和思路。
1、确认日志切换频率，明显30号和1号日志切换比之前增长一倍。
select to_char(first_time,'yyyy-mon-dd') "date", to_char(first_time,'dy') day, to_char(sum(decode(to_char(first_time,'hh24'),'00',1,0)),'999') "00", to_char(sum(decode(to_char(first_time,'hh24'),'01',1,0)),'999') "01", to_char(sum(decode(to_char(first_time,'hh24'),'02',1,0)),'999') "02", to_char(sum(decode(to_char(first_time,'hh24'),'03',1,0)),'999') "03", to_char(sum(decode(to_char(first_time,'hh24'),'04',1,0)),'999') "04", to_char(sum(decode(to_char(first_time,'hh24'),'05',1,0)),'999') "05", to_char(sum(decode(to_char(first_time,'hh24'),'06',1,0)),'999') "06", to_char(sum(decode(to_char(first_time,'hh24'),'07',1,0)),'999') "07", to_char(sum(decode(to_char(first_time,'hh24'),'08',1,0)),'999') "08", to_char(sum(decode(to_char(first_time,'hh24'),'09',1,0)),'999') "09", to_char(sum(decode(to_char(first_time,'hh24'),'10',1,0)),'999') "10", to_char(sum(decode(to_char(first_time,'hh24'),'11',1,0)),'999') "11", to_char(sum(decode(to_char(first_time,'hh24'),'12',1,0)),'999') "12", to_char(sum(decode(to_char(first_time,'hh24'),'13',1,0)),'999') "13", to_char(sum(decode(to_char(first_time,'hh24'),'14',1,0)),'999') "14", to_char(sum(decode(to_char(first_time,'hh24'),'15',1,0)),'999') "15", to_char(sum(decode(to_char(first_time,'hh24'),'16',1,0)),'999') "16", to_char(sum(decode(to_char(first_time,'hh24'),'17',1,0)),'999') "17", to_char(sum(decode(to_char(first_time,'hh24'),'18',1,0)),'999') "18", to_char(sum(decode(to_char(first_time,'hh24'),'19',1,0)),'999') "19", to_char(sum(decode(to_char(first_time,'hh24'),'20',1,0)),'999') "20", to_char(sum(decode(to_char(first_time,'hh24'),'21',1,0)),'999') "21", to_char(sum(decode(to_char(first_time,'hh24'),'22',1,0)),'999') "22", to_char(sum(decode(to_char(first_time,'hh24'),'23',1,0)),'999') "23" , count(*) total from v$log_history group by to_char(first_time,'yyyy-mon-dd'), to_char(first_time,'dy') order by to_date(to_char(first_time,'yyyy-mon-dd'),'yyyy-mon-dd');
date day 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 total
-------------- ------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------
2022-nov-27 sun 71 33 52 36 53 51 68 45 56 58 51 56 74 56 49 63 51 50 78 54 54 47 33 59 1298
2022-nov-28 mon 59 32 54 44 52 39 65 113 117 96 65 148 110 79 62 67 97 56 84 47 55 58 76 56 1731
2022-nov-29 tue 60 32 54 36 60 42 70 47 60 59 44 56 63 51 49 51 48 51 83 112 122 138 136 164 1688
2022-nov-30 wed 124 83 97 102 121 116 142 84 119 126 100 110 142 87 116 124 97 137 165 102 117 126 68 137 2742
2022-dec-01 thu 133 82 90 106 114 124 150 123 95 131 102 114 130 104 118 181 69 0 0 0 0 0 0 0 1966
2、查看每日的日志量，日志切换频繁不见得日志量就大，还是要确认下日志量的情况。从查询结果看，最近两天单日归档量快到4t了。日常也就2t左右。
select dest_id,to_char(first_time,'yyyy-mm-dd') time,count(*),sum(blocks*block_size)/1024/1024/1024 gb
from v$archived_log =================》即使是rac也无需使用gv$视图。
where dest_id=1
and
first_time>to_date('20221126 00:00','yyyymmdd hh24:mi')
and
first_time dest_id time count(*) gb
---------- ---------- ---------- ----------
1 2022-11-26 1308 1949.53655
1 2022-11-27 1298 1890.3195
1 2022-11-28 1731 2526.82131
1 2022-11-29 1688 2589.85447
1 2022-11-30 2742 3995.80157
1 2022-12-01 2405 3509.1859 3、确认下活动会话是否有增长，如果活动会话有增长的话，可能是由于交易量上升导致的归档量变大。根据查询结果，除去两个异常时段有会话数激增，其他时段并没有发现活跃会话有明显的增长。
11-29号：
select to_char(sample_time,'yyyy/mm/dd hh24'),count(*) from dba_hist_active_sess_history where
sample_time>to_date('20221129 06:00','yyyymmdd hh24:mi')
and sample_time 12:00','yyyymmdd hh24:mi')
group by to_char(sample_time,'yyyy/mm/dd hh24') order by 1;
to_char(sampl count(*)
------------- ----------
2022/11/29 06 4869
2022/11/29 07 4518
2022/11/29 08 8142
2022/11/29 09 108017 ============》当天出现数据库异常，导致会话堵塞。
2022/11/29 10 85392
2022/11/29 11 3078
11-30号：
select to_char(sample_time,'yyyy/mm/dd hh24'),count(*) from dba_hist_active_sess_history where
sample_time>to_date('20221130 06:00','yyyymmdd hh24:mi')
and sample_time 12:00','yyyymmdd hh24:mi')
group by to_char(sample_time,'yyyy/mm/dd hh24') order by 1;
to_char(sampl count(*)
------------- ----------
2022/11/30 06 4392
2022/11/30 07 10890 ==================》当天由于归档目录满了，导致会话堵塞。
2022/11/30 08 4963
2022/11/30 09 4722
2022/11/30 10 3549
2022/11/30 11 4070
12-1号
select to_char(sample_time,'yyyy/mm/dd hh24'),count(*) from dba_hist_active_sess_history where
sample_time>to_date('20221201 06:00','yyyymmdd hh24:mi')
and sample_time 12:00','yyyymmdd hh24:mi')
group by to_char(sample_time,'yyyy/mm/dd hh24') order by 1;
to_char(sampl count(*)
------------- ----------
2022/12/01 06 4481
2022/12/01 07 4146
2022/12/01 08 5200
2022/12/01 09 4671
2022/12/01 10 4283
2022/12/01 11 3394 4、参考mos文档how to identify the causes of high redo generation (doc id 2265722.1)，选取日志生成量比较大的06-07点，查看block changed的对象情况。
对比了三天的block changed，发现12月1号和11月30号的top4都是一样的，而在11月29号中并没有这几个对象，这几个对象从名字看应该是一个表和三个索引。12月1号早晨6点-7点：select to_char(begin_interval_time,'yy-mm-dd hh24') snap_time, dhso.object_name, sum(db_block_changes_delta) block_changed from dba_hist_seg_stat dhss, dba_hist_seg_stat_obj dhso, dba_hist_snapshot dhs where dhs.snap_id = dhss.snap_id and dhs.instance_number = dhss.instance_number and dhss.obj# = dhso.obj# and dhss.dataobj# = dhso.dataobj# and begin_interval_time between to_date('22-12-01 06:00','yy-mm-dd hh24:mi')
and to_date('22-12-01 07:00','yy-mm-dd hh24:mi') group by to_char(begin_interval_time,'yy-mm-dd hh24'), dhso.object_name having sum(db_block_changes_delta) > 0 order by sum(db_block_changes_delta) desc ;
snap_time object_name block_changed
----------- ------------------------------ -------------
22-12-01 06 ix_gtmp_wind_record_log 106597968
22-12-01 06 ix2_gtmp_wind_record_log 68668736
22-12-01 06 ix1_gtmp_wind_record_log 48441232
22-12-01 06 gtmp_wind_record_log 46247936
22-12-01 06 gz45_vn_bm_bal 21052960
22-12-01 06 ** transient: 23179648 18150128
22-12-01 06 pk_o32_ttradestock 16278256
22-12-01 06 pk_gz45_vn_dayf_confirm 7566992
11月30号早晨6点到7点：select to_char(begin_interval_time,'yy-mm-dd hh24') snap_time, dhso.object_name, sum(db_block_changes_delta) block_changed from dba_hist_seg_stat dhss, dba_hist_seg_stat_obj dhso, dba_hist_snapshot dhs where dhs.snap_id = dhss.snap_id and dhs.instance_number = dhss.instance_number and dhss.obj# = dhso.obj# and dhss.dataobj# = dhso.dataobj# and begin_interval_time between to_date('22-11-30 06:00','yy-mm-dd hh24:mi')
and to_date('22-11-30 07:00','yy-mm-dd hh24:mi') group by to_char(begin_interval_time,'yy-mm-dd hh24'), dhso.object_name having sum(db_block_changes_delta) > 0 order by sum(db_block_changes_delta) desc ;
snap_time object_name block_changed
----------- ------------------------------ -------------
22-11-30 06 ix_gtmp_wind_record_log 59098880
22-11-30 06 ix2_gtmp_wind_record_log 44366832
22-11-30 06 ix1_gtmp_wind_record_log 39393808
22-11-30 06 gtmp_wind_record_log 24130704
22-11-30 06 gz45_vn_bm_bal 21051680
22-11-30 06 pk_o32_ttradestock 16285792
22-11-30 06 gz45_t_r_fr_aststat 6459584
22-11-30 06 ix_gz45_vn_bm_bal 5837776
11月29号早晨6点-7点：select to_char(begin_interval_time,'yy-mm-dd hh24') snap_time, dhso.object_name, sum(db_block_changes_delta) block_changed from dba_hist_seg_stat dhss, dba_hist_seg_stat_obj dhso, dba_hist_snapshot dhs where dhs.snap_id = dhss.snap_id and dhs.instance_number = dhss.instance_number and dhss.obj# = dhso.obj# and dhss.dataobj# = dhso.dataobj# and begin_interval_time between to_date('22-11-29 06:00','yy-mm-dd hh24:mi')
and to_date('22-11-29 07:00','yy-mm-dd hh24:mi') group by to_char(begin_interval_time,'yy-mm-dd hh24'), dhso.object_name having sum(db_block_changes_delta) > 0 order by sum(db_block_changes_delta) desc ;
snap_time object_name block_changed
----------- ------------------------------ -------------
22-11-29 06 gz45_vn_bm_bal 21660112
22-11-29 06 pk_o32_ttradestock 16292176
22-11-29 06 idx_ods_wind_cbondagency 11994048
22-11-29 06 pk_ods_wind_cbondagency 9990224
22-11-29 06 o32_ttradestock 9007728
22-11-29 06 ods_wind_cbondagency 7276320
22-11-29 06 gz45_t_r_fr_aststat 6671232
22-11-29 06 ix_gz45_vn_bm_bal 5930496
5、根据以上对象可以找到执行的相应的sql语句。
select to_char(begin_interval_time,'yyyy_mm_dd hh24') when, dbms_lob.substr(sql_text,4000,1) sql, dhss.instance_number inst_id, dhss.sql_id, executions_delta exec_delta, rows_processed_delta rows_proc_delta
from dba_hist_sqlstat dhss, dba_hist_snapshot dhs, dba_hist_sqltext dhst
where upper(dhst.sql_text) like '%gtmp_wind_record_log%'
and ltrim(upper(dhst.sql_text)) not like 'select%'
and dhss.snap_id=dhs.snap_id
and dhss.instance_number=dhs.instance_number
and dhss.sql_id=dhst.sql_id
and begin_interval_time
between to_date('22-12-01 06:00','yy-mm-dd hh24:mi')
and to_date('22-12-01 07:00','yy-mm-dd hh24:mi')；
inst_id sql_id exec_delta rows_proc_delta
---------- ------------- ---------- ---------------
2022_12_01 06
insert into etl.gtmp_wind_record_log (id, rec_id, opmode, opdate, table_name) se
lect id, rec_id, opmode, opdate, tablename from winddf.record_log@windnewdb a wh
ere a.tablename=:b1
2 61z1sgnsccx6m 184 96347952
inst_id sql_id exec_delta rows_proc_delta
---------- ------------- ---------- ---------------
delete from etl.gtmp_wind_record_log a where a.id>:b2 or a.id<:b1
2 d7k797d3827cg 221 96610616 6、其实上面的结论从awr报告中也能的出来，但是awr报告只能显示top5，没办法看到后面的。当我想从awr报告里证明上述结论的时候，我发现这个处于top的对象竟然是temp表。按我以前的理解，对临时表的操作是不会产生redo的。从度娘上查了下，临时表本身的操作虽然不会产生redo，但是对临时表操作需要产生undo，产生undo自然会产生redo，只是比普通表产生的redo要少很多。
总结
日常碰到的日志量突增大部分都是交易量上涨、业务逻辑调整等导致的，但是如果你直接去问开发人员，他可能会说啥也没变，这个时候你就会挺懵逼（这是本人亲身经历哈）。如果你拿着你查到的东西去找他，他可能就不会这么硬气了。所以啊，虽然可能不是数据库的问题，但作为万能的dba，你也的会查啊。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」 关注作者 【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。 评论