Oracle Flashback Overhead

I was looking into an issue recently with 2 enviroments same copy of database, same spfile file settings, but
an INSERT was taking twice as long.

AWR showed top wait event behind CPU as ‘db file sequential read’

On closer inspection of db’s it turned out one had Flashback database enabled the other didn’t and the wait
was due to that when we done testing on the same env with flashback disabled.

It turns out the sequential reads are reads the database must do of the UNDO TABLESPACE to form the flashback
logs for an INSERT row, that is the overhead.

Lets prove this below as well.

  1. Record current values for ‘db file sequential read’ wait

SQL> col event for a30
SQL> select event, TOTAL_WAITS, TIME_WAITED from v$system_event where event=’db file sequential read’;

EVENT TOTAL_WAITS TIME_WAITED


db file sequential read 100689 11131

  1. Test A, with flashback off:

SQL> alter database flashback off;

Database altered.

SQL> select name, flashback_on from v$database;

NAME FLASHBACK_ON


DB12102A NO

SQL> create table test_a as select * from FIVE_MILLION_ROWS where 1=2;

Table created.

SQL> insert into test_a select * from FIVE_MILLION_ROWS;

5000000 rows created.

Elapsed: 00:00:09.56

SQL> select event, TOTAL_WAITS, TIME_WAITED from v$system_event where event=’db file sequential read’;

EVENT TOTAL_WAITS TIME_WAITED


db file sequential read 100729 11132

so just under 10 seconds and waits on ‘db file sequential read’ have not gone up much.

  1. Test B with flashback on:

SQL> alter database flashback on;

Database altered.

SQL> select name, flashback_on from v$database;

NAME FLASHBACK_ON


DB12102A YES

SQL> select event, TOTAL_WAITS, TIME_WAITED from v$system_event where event=’db file sequential read’;

EVENT TOTAL_WAITS TIME_WAITED


db file sequential read 100745 11133

SQL> create table test_b as select * from FIVE_MILLION_ROWS where 1=2;

SQL> insert into test_b select * from FIVE_MILLION_ROWS;

5000000 rows created.

Elapsed: 00:00:13.81

nearly 14 seconds for same insert with flashback on

SQL> select event, TOTAL_WAITS, TIME_WAITED from v$system_event where event=’db file sequential read’;

EVENT TOTAL_WAITS TIME_WAITED


db file sequential read 102539 11308

extra waits have gone up on db file sequential read, time_waied in centiseconds , so just under 2 seconds extra
for sequential reads.

obviously this is a basic example and time will magnify up depending on data and amount of change.

Not sure why Oracle does not let us backup flashback logs, imagine we had a standby database , we just set flashback on that and backup the logs, so If we could run a primary without flashback overhead, but use logs if needed from standby to rewind it.