[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};
}