Coskan’s Approach to Oracle

May 27, 2010

Flashback log generation with select statements

Filed under: Basics, Tips — coskan @ 3:50 pm

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

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers