如何定位产生redo量大的表

1. 概述

短信告警又来通知说某套系统的归档目录使用过半,经过简单查看NBU的备份日志,发现在10:50,NBU备份归档成功,而当前时间是11:26左右。
难道在短短半个多小时的归间就生成了200多G归档?经过到归档目录中进行查看,确实如此,平均1分钟生成好几个归档日志

# df -h
Filesystem                  Size  Used Avail Use% Mounted on
/dev/mapper/vg00-lv_root    9.9G  2.3G  7.1G  25% /
tmpfs                       126G     0  126G   0% /dev/shm
/dev/sda1                   517M   62M  429M  13% /boot
/dev/mapper/vg00-lv_oracle   40G   25G   13G  66% /oracle
/dev/mapper/vg00-lv_usr      16G  8.8G  6.2G  59% /usr
/dev/mapper/vg00-lv_var      16G  2.5G   13G  17% /var
/dev/mapper/vg00-lv_tmp     7.9G  147M  7.4G   2% /tmp
/dev/mapper/vg00-lv_core    148G  188M  140G   1% /core
tmpfs                       4.0K     0  4.0K   0% /dev/vx
/dev/vx/dsk/ssddg/archlog   300G  202G   98G  68% /archlog
/dev/vx/dsk/sasdg/ocrvote   4.0G  107M  3.9G   3% /ocrvote
/dev/vx/dsk/sasdg/sysdata   400G  315G   86G  79% /sysdata
/dev/vx/dsk/sasdg/sasdata   6.0T  5.8T  275G  96% /sasdata
/dev/vx/dsk/bakdg/bakvol    2.0T  1.1T  926G  54% /home/veritas_backup

# ls -lrt
total 197203040
-r--r----- 1 oracle oinstall      62464 Dec 26 10:51 1_28830_885644533.dbf
-r--r----- 1 oracle oinstall 1808728576 Dec 26 10:51 2_31776_885644533.dbf
-rw-r----- 1 oracle oinstall   30162944 Dec 26 10:51 snapcf_WLWJFZW.f
-r--r----- 1 oracle oinstall 2077529600 Dec 26 10:56 2_31777_885644533.dbf
-r--r----- 1 oracle oinstall      32256 Dec 26 10:56 1_28831_885644533.dbf
-r--r----- 1 oracle oinstall 2130918912 Dec 26 10:56 2_31778_885644533.dbf
-r--r----- 1 oracle oinstall 1969920512 Dec 26 10:56 2_31779_885644533.dbf
-r--r----- 1 oracle oinstall 1914870272 Dec 26 10:56 2_31780_885644533.dbf
-r--r----- 1 oracle oinstall      39424 Dec 26 10:57 1_28832_885644533.dbf
-r--r----- 1 oracle oinstall 1892815872 Dec 26 10:57 2_31781_885644533.dbf
-r--r----- 1 oracle oinstall 2137124864 Dec 26 10:57 2_31782_885644533.dbf
-r--r----- 1 oracle oinstall 2124384768 Dec 26 10:58 2_31783_885644533.dbf
-r--r----- 1 oracle oinstall       2560 Dec 26 10:58 1_28833_885644533.dbf
-r--r----- 1 oracle oinstall 2124813312 Dec 26 10:58 2_31784_885644533.dbf
-r--r----- 1 oracle oinstall 1941977600 Dec 26 10:58 2_31785_885644533.dbf
-r--r----- 1 oracle oinstall 2112339456 Dec 26 10:58 2_31786_885644533.dbf
......
-r--r----- 1 oracle oinstall 2063001088 Dec 26 11:22 2_31861_885644533.dbf
-r--r----- 1 oracle oinstall       8704 Dec 26 11:23 1_28859_885644533.dbf
-r--r----- 1 oracle oinstall 2118465536 Dec 26 11:23 2_31862_885644533.dbf
-r--r----- 1 oracle oinstall 2125807104 Dec 26 11:23 2_31863_885644533.dbf
-r--r----- 1 oracle oinstall 2098237440 Dec 26 11:23 2_31864_885644533.dbf
-r--r----- 1 oracle oinstall       2560 Dec 26 11:24 1_28860_885644533.dbf
-r--r----- 1 oracle oinstall 2114093056 Dec 26 11:24 2_31865_885644533.dbf
-r--r----- 1 oracle oinstall 2116428288 Dec 26 11:24 2_31866_885644533.dbf
-r--r----- 1 oracle oinstall 2076578816 Dec 26 11:24 2_31867_885644533.dbf
-r--r----- 1 oracle oinstall       6144 Dec 26 11:24 1_28861_885644533.dbf
-r--r----- 1 oracle oinstall 2117682688 Dec 26 11:24 2_31868_885644533.dbf
-r--r----- 1 oracle oinstall 2077947904 Dec 26 11:25 2_31869_885644533.dbf
-r--r----- 1 oracle oinstall 2121817600 Dec 26 11:25 2_31870_885644533.dbf
-r--r----- 1 oracle oinstall      12800 Dec 26 11:25 1_28862_885644533.dbf
-r--r----- 1 oracle oinstall 2128550400 Dec 26 11:25 2_31871_885644533.dbf
-r--r----- 1 oracle oinstall 2108316672 Dec 26 11:26 2_31872_885644533.dbf
-r--r----- 1 oracle oinstall 2129376768 Dec 26 11:26 2_31873_885644533.dbf

那问题就来了,为什么会生成这么多归档日志,具体是哪张表生成的

2. 如何快速定位归档日志是由谁产生的

对于已经产生的归档日志想要分析具体哪张表生成的日志比较多,那只能使用LogMiner工具来挖,然后再进行统计分析。
对于归档日志还在拼命生成的情况下,可以使用如下简便方法:

2.1 生成dba_tab_modifications的快照1

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2016-12-26 11:32:11

SQL> create table tmp1_dtm as select * from dba_tab_modifications;
Table created.

等待1分钟左右……

2.2 生成dba_tab_modifications的快照2

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2016-12-26 11:33:25

SQL> create table tmp2_dtm as select * from dba_tab_modifications;
Table created.

2.3 统计哪张表的DML量大

一般DML量大的表生成的日志就比较多

set linesize 200

with a as (SELECT table_owner,
                  table_name,
                  SUM(inserts) AS i,
                  SUM(updates) AS u,
                  SUM(deletes) AS d
             FROM tmp1_dtm
            GROUP BY table_owner, table_name),
     b as (SELECT table_owner,
                  table_name,
                  SUM(inserts) AS i,
                  SUM(updates) AS u,
                  SUM(deletes) AS d
             FROM tmp2_dtm
            GROUP BY table_owner, table_name)
select * from (
  select /*+no_merge(a) no_merge(b)*/
         (b.i - a.i) as iCnt,
         (b.u - a.u) as uCnt,
         (b.d - a.d) as dCnt,
         a.table_owner, a.table_name
    from a, b
   where a.table_owner = b.table_owner
     and a.table_name = b.table_name
   order by 3 desc                          -- 按delete排序
) where rownum < 20;

如果需要按insert或update排序,修改order by即可

      ICNT       UCNT       DCNT TABLE_OWNER                    TABLE_NAME
---------- ---------- ---------- ------------------------------ ------------------------------
   1009076          0          0 WLWXXXX                        B_FLOW_FMT_201612
    504538          0          0 WLWXXXX                        B_FLOW_FMT_SEND
       178        375          0 WLWXX                          T_PM_NOTIFY
       143          0          0 SYS                            AUD$
        84          0          0 WLWXX                          T_CARD_ACTIVATE
        75          0          0 WLWXXXX                        B_VOICE_FMT_SEND
        75          0          0 WLWXXXX                        IOT_IOT_CDR_201612
        75          0          0 WLWXXXX                        B_VOICE_FMT_201612
        21         34          0 WLWXX                          B_SMS_SEND
        14         19        189 SYS                            MON_MODS$
        12        201          0 SYS                            MON_MODS_ALL$
        11          0          0 WLWXX                          T_PM_NOTIFY_LOG
        10         10          0 WLWXXXX                        RECV_FILE_INFO
        10          0          0 SYS                            COL$
        10          0          0 WLWXXXX                        EXCH_FILE_RECORD
         8          0          0 WLWXXXX                        B_SMS_FORMAT_201612
         8          0          0 WLWXXXX                        IOT_IOT_MDR_201612
         8          0          0 WLWXX                          T_PAY_RECORDS
         7          0          0 WLWXX                          T_ACCESS_LOG
 19 rows selected

这里就定位到B_FLOW_FMT_201612,在1分钟里有100W的数据行插入,后面就是通知业务确认是否正常。

关于紫砂壶

感悟技术人生
此条目发表在Oracle故障诊断分类目录,贴了标签。将固定链接加入收藏夹。