There is a question on OTN Forums about excessive flashback log generates with select statement . Basically OP says there are flashback logs generated when they do select only.
there are different answers in the thread which can be true or false, but when I first read the question I immediately think if delayed block cleanout have the similar effect on flashback log generation as well, so I tested.
I initially thought v$sesstats counters might reveal something but no luck and then I checked v$flashback_database_stat for the generated flashback_data_size and I think I hit the jackpot.
HR@ORACOS> select * from v$flashback_database_stat; BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE ----------------- ----------------- -------------- ---------- ---------- ------------------------ 20100527 15:32:53 20100527 15:50:16 875266048 1207132160 2038729728 0 20100527 14:32:50 20100527 15:32:53 248160256 127295488 450139648 1.3215E+10 20100527 13:32:48 20100527 14:32:50 10452992 15646720 4400640 1.5549E+10 20100527 12:32:43 20100527 13:32:48 745693184 948461568 1311620608 2.2789E+10 20100527 11:25:56 20100527 12:32:43 1262026752 1984741376 2358546432 2.7212E+10 HR@ORACOS> set autotrace traceonly statistics HR@ORACOS> update base_table_np set y='INVALID'; commit; 4021808 rows updated. Statistics ---------------------------------------------------------- 2512 recursive calls 8341430 db block gets 4069140 consistent gets 120569 physical reads 1908471980 redo size 848 bytes sent via SQL*Net to client 793 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 4021808 rows processed HR@ORACOS> set autotrace off; HR@ORACOS> select * from v$flashback_database_stat; HR@ORACOS> BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE ----------------- ----------------- -------------- ---------- ---------- ------------------------ 20100527 15:32:53 20100527 16:00:36 1236664320 2021974016 4019910656 0 20100527 14:32:50 20100527 15:32:53 248160256 127295488 450139648 1.3215E+10 20100527 13:32:48 20100527 14:32:50 10452992 15646720 4400640 1.5549E+10 20100527 12:32:43 20100527 13:32:48 745693184 948461568 1311620608 2.2789E+10 20100527 11:25:56 20100527 12:32:43 1262026752 1984741376 2358546432 2.7212E+10 HR@ORACOS> set autotrace traceonly statistics HR@ORACOS> select * from base_table_np; 4021808 rows selected. Statistics ---------------------------------------------------------- 139 recursive calls 0 db block gets 53908 consistent gets 4404 physical reads 1652384 redo size ------->DELAYED BLOCKS CLEANOUTS 175008833 bytes sent via SQL*Net to client 88996 bytes received via SQL*Net from client 8045 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 4021808 rows processed HR@ORACOS> set autotrace off HR@ORACOS> select * from v$flashback_database_stat; ------>CHECK THE increase in FLASHBACK_DATA HR@ORACOS> BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE ----------------- ----------------- -------------- ---------- ---------- ------------------------ 20100527 15:32:53 20100527 16:01:11 1305264128 2054594560 4021728256 0 20100527 14:32:50 20100527 15:32:53 248160256 127295488 450139648 1.3215E+10 20100527 13:32:48 20100527 14:32:50 10452992 15646720 4400640 1.5549E+10 20100527 12:32:43 20100527 13:32:48 745693184 948461568 1311620608 2.2789E+10 20100527 11:25:56 20100527 12:32:43 1262026752 1984741376 2358546432 2.7212E+10
I am still not %100 convinced because I have no idea what is going on behind the scenes and I don’t have Julian Dyke (ppt file ) wisdom/time to go more deep into the issue and I stopped here.
If you have something to say about the issue comments are welcomed.
Tested on 11.2.0.1 with single active instance
Coskan,
That strikes me as a very good hypothesis; and the volume the OP is seeing could appear to be unreasonably large because a single block cleanout usually generates a small amount of redo but requires the whole block to go into the flashback log. (Is this another reason for avoiding 16KB and 32KB blocks ? 😉 )
Comment by Jonathan Lewis — May 29, 2010 @ 10:27 am