[20130520]统计那个对象生成的redo多.txt

http://timothyhopkins.net/2009/09/summarise-redo-by-object/

如果统计那个对象生成的redo多,一直是我比较头疼的问题.我以前遇到3次程序员编程错误,导致生成大量redo,要找到这些问题
可以通过awr,ash等报表,logminer来确定是那个对象执行问题,但是有时候定位问题并不是很好.

昨天找到以上链接,通过dump redo或者archivelog就可以知道那些对象生成的redo多.自己测试看看.

1.建立测试环境:

SQL> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from v$diag_info where name='Default Trace File';

   INST_ID NAME                 VALUE

---------- -------------------- --------------------------------------------------------------------------------------

         1 Default Trace File   /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_2878_local.trc

create table t1 as select * from dba_objects;

create table t2 as select * from t1;

create table t3 as select * from t1 where 1=0;

create index i_t1_object_id on t1(object_id);

alter system switch logfile ;

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log order by group#;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------

         1          1        424   52428800        512          1 YES INACTIVE            3229333374 2013-05-20 10:07:58   3229337621 2013-05-20 10:08:36

         2          1        425   52428800        512          1 YES INACTIVE            3229337621 2013-05-20 10:08:36   3229338070 2013-05-20 10:12:25

         3          1        426   52428800        512          1 NO  CURRENT             3229338070 2013-05-20 10:12:25   2.8147E+14

--确定跟踪文件以及当前使用那个redo sequence#=426.

2.建立测试:

delete from t1;

commit;

update t2 set OBJECT_NAME=lower(OBJECT_NAME);

commit ;

insert into t3  select * from t2;

commit ;

alter system switch logfile ;

SQL> select * from v$logfile order by group#;

    GROUP# STATUS  TYPE                                     MEMBER                                                       IS_

---------- ------- ---------------------------------------- ------------------------------------------------------------ ---

         1         ONLINE                                   /u01/app/oracle11g/oradata/test/redo01.log                   NO

         2         ONLINE                                   /u01/app/oracle11g/oradata/test/redo02.log                   NO

         3         ONLINE                                   /u01/app/oracle11g/oradata/test/redo03.log                   NO

SQL> select * from v$log order by group#;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------

         1          1        427   52428800        512          1 YES ACTIVE              3229342316 2013-05-20 10:14:06   3229345486 2013-05-20 10:14:09

         2          1        428   52428800        512          1 NO  CURRENT             3229345486 2013-05-20 10:14:09   2.8147E+14

         3          1        426   52428800        512          1 YES ACTIVE              3229338070 2013-05-20 10:12:25   3229342316 2013-05-20 10:14:06

--从结果可以看出使用redo sequence是426以及427,两个redo文件.(当前是428).

3.转储 redo文件:

sql> alter system dump logfile '/u01/app/oracle11g/oradata/test/redo03.log';

sql> alter system dump logfile '/u01/app/oracle11g/oradata/test/redo01.log';

$ perl redo_summary.pl /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_2878_local.trc

           OBJECT_ID                                REDO_SIZE

              274946                                 31144940

              274945                                 29023956

              274948                                 16169668

              274947                                  8666276

     NON-OBJECT REDO                                   650224

                   1                                    13820

                   0                                     5132

               68377                                     2332

                 289                                     2096

                  14                                     1388

                 383                                     1372

               67679                                     1352

                 287                                     1096

                   4                                      808

                 132                                      528

                 133                                      240

SQL> select object_name,object_id,data_object_id from dba_objects where wner=user and object_name like 'T_' or object_name ='I_T1_OBJECT_ID'order by 1;

OBJECT_NAME           OBJECT_ID DATA_OBJECT_ID

-------------------- ---------- --------------

I_T1_OBJECT_ID           274948         274948

T1                       274945         274945

T2                       274946         274946

T3                       274947         274947

--T1(object_id=274945)做的操作是delete操作,redo_size=29023956.
--T2(object_id=274946)做的操作是update操作,redo_size=31144940.
--T3(object_id=274947)做的操作是insert操作,redo_size= 8666276.
-- object_id=274948 是T1上的索引,可以发现产生的redo也不少.redo_size=16169668.

4.转储archivelog看看,应该结果也是一致的,另外产生的文件很大,我的测试
$ du -sm /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_2878_local.trc
415     /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_2878_local.trc
--415M.

--作者的链接使用管道文件,我也测试看看.这样不消耗磁盘空间.

SQL> select * from v$diag_info where name='Default Trace File';

   INST_ID NAME                 VALUE

---------- -------------------- --------------------------------------------------------------------------------------

         1 Default Trace File   /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_3733_local.trc

$ mkfifo /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_3733_local.trc

SQL> show parameter log_archive_dest_1

NAME                TYPE      VALUE

------------------- --------- ---------------------------------------

log_archive_dest_1  string    LOCATION=/u01/app/oracle11g/archivelog

$ ls -l /u01/app/oracle11g/archivelog/1_42[67]_798551880.dbf

-rw-r-----  1 oracle11g oinstall 46671360 2013-05-20 10:14:07 /u01/app/oracle11g/archivelog/1_426_798551880.dbf

-rw-r-----  1 oracle11g oinstall 41971712 2013-05-20 10:14:09 /u01/app/oracle11g/archivelog/1_427_798551880.dbf

$ perl redo_summary.pl /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_3733_local.trc

sql> alter system dump logfile '/u01/app/oracle11g/archivelog/1_426_798551880.dbf';

sql> alter system dump logfile '/u01/app/oracle11g/archivelog/1_426_798551880.dbf';

SQL> alter system dump logfile '/u01/app/oracle11g/archivelog/1_426_798551880.dbf';

alter system dump logfile '/u01/app/oracle11g/archivelog/1_426_798551880.dbf'

*

ERROR at line 1:

ORA-48186: OS check file exists system call failure

Additional information: 3

--11G不行.还是采用文件的方式.

$ perl redo_summary.pl /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_3733_local.trc

           OBJECT_ID                                REDO_SIZE

              274946                                 31144940

              274945                                 29023956

              274948                                 16169668

              274947                                  8666276

     NON-OBJECT REDO                                   650224

                   1                                    13820

                   0                                     5132

               68377                                     2332

                 289                                     2096

                  14                                     1388

                 383                                     1372

               67679                                     1352

                 287                                     1096

                   4                                      808

                 132                                      528

                 133                                      240

5.采用别的方式认证看看:
--删除重新建立看看.
SQL> set autotrace traceonly ;
SQL> delete from t1;
  45510392  redo size
-- 29023956 + 16169668=45193624 ,很接近.

SQL> update t2 set OBJECT_NAME=lower(OBJECT_NAME);
  27756536  redo size
-- 31144940 有一些差距,不知道为什么?

SQL> insert into t3  select * from t2;
    9011404  redo size
--  8666276 还是有一些差距.

6.附上redo_summary.pl的脚本:
--BTW,好像不复杂,但是我没看懂,不知道得出的结果是否正确.^_^.
http://timothyhopkins.net/wp-content/uploads/2009/09/redo_summary.zip

$ cat redo_summary.pl
# TDH 2008-09-26
# Analyses a formatted redo dump and
#       summarises redo bytes by object.
#

my $current_object;
my $current_bytes;
my %object_bytes = ();

sub DescendingNum {
   $object_bytes{$b} $object_bytes{$a};
}

while () {

        if (/LEN: 0x([a-f0-9A-F]+) VLD/) {

                if (defined($current_object)) {
                        $object_bytes{$current_object} += $current_bytes;
                }
                elsif (defined($current_bytes)) {
                        $object_bytes{"NON-OBJECT REDO"} += $current_bytes;
                }

                $current_bytes = hex($1);
                $current_object = undef;
        }
        elsif (/objn: (\d+) objd/) {
                $current_object = $1;
        }
}

printf "%20s %40s\n","OBJECT_ID","REDO_SIZE";

foreach my $key (sort DescendingNum(keys(%object_bytes))) {
                printf "%20s %40s\n",$key,$object_bytes{$key};
}