23ai, new parameters DB_FLASHBACK_LOG_DEST_SIZE and DB_FLASHBACK_LOG_DEST

2 Replies

Oracle database has the Oracle Flashback Technology that allows you to view old images of your data without the need to restore your database. You can use restore points, restore tables, and rows, and do a lot of things. To use it (in a simple way), you need to enable the archivelog and flashback mode for your database and Oracle will create additional logs while you change the data.

Unfortunately, it is exactly these logs that create some issues. Jonathan Lewis already described this issue, and in resume, while changing the data you need to write more because you will use UNDO + Flashback logs. In essence, you write more every time. 

Until Oracle 23ai, it was not possible to change the place where you write these logs, (more or less) it will always be where you write your archivelogs (when using the fast recovery area). So, archivelogs and flashback logs are tight where they reside. Luckily this changed, and the new features of 23ai explain:

The idea is to put the flashback logs in a dedicated (and fast) disk to reduce the impact of writing them.

How it works?

Now with 23ai, we have two new parameters that can be used:

  • DB_FLASHBACK_LOG_DEST: This is the path where you want to write the flashback logs.
  • DB_FLASHBACK_LOG_DEST_SIZE: This is the size (like the DB_RECOVERY_FILE_DEST_SIZE) that can be used for the flashback log. If you reach the limit, the oldest one will be rewritten.

How to use it?

First I need to describe my test environment, I have the patch below where I can simulate writing in different disk scenarios:

  • /opt/oracle/oradata/FREE/recovery/:  Where it is the fast recovery area. It points to a disk at NAS (7.200 rpm disk, and 1Gbps network).
  • /opt/oracle/oradata/REMOTESSD/: Where the tablespace is. It points to an NVME disk running in my NAS (1Gbps network).
  • /opt/oracle/oradata/LOCALSSD/: It points to a local NVME disk in my machine.

And for the test, I will use the same example provided by Jonathan Lewis. I will create one tablespace (tbsload), and one table with the active index:

[oracle@o23fdc11 ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Mon Jun 17 22:30:31 2024
Version 23.4.0.24.05

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO
         4 PDBDC1                         MOUNTED
SQL> alter pluggable database PDBDC1 open;

Pluggable database altered.

SQL> alter session set container = PDBDC1;

Session altered.

SQL> create bigfile tablespace  tbsload datafile '/opt/oracle/oradata/REMOTESSD/tbsload.dbf' size 5G autoextend on next 1G maxsize unlimited;

Tablespace created.

SQL> set timing on
SQL> create table t1 segment creation immediate tablespace tbsload
  2  as
  3  select * from all_objects
  4  where   rownum < 1
  5  ;

Table created.

SQL> create index t1_i1 on t1(object_name, object_id) tablespace tbsload;

Index created.

SQL>

With that, I can insert several times to check the impact of the flashback logs. My focus here is just to check the impact of having the flashbacks and using the new parameters. I will do some tests using: just archivelogs, the traditional case with flashback logs in the same place as archivelogs, and the last one using the new parameters.  I will collect the inserts several times to check the time (with simple set timing on) and share the results.

Just archivelogs

Below you can see that I just used the arhivelogs. The database is not in running with flashback enabled:

SQL> select LOG_MODE, FLASHBACK_ON from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   NO

SQL> alter session set container = PDBDC1;

Session altered.

SQL> set timing on
SQL> select count(*) from t1;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.00
SQL>
SQL> insert /*+ append */ into t1
  2  with object_data as (
  3          select --+ materialize
  4                  *
  5          from
  6                  all_objects
  7          where
  8                  rownum <= 50000
  9  ),
 10  counter as (
 11          select  --+ materialize
 12                  rownum id
 13          from dual
 14          connect by
 15                  level <= 32
 16  )
 17  select
 18         /*+ leading (ctr obj) use_nl(obj) */
 19          obj.*
 20  from
 21          counter         ctr,
 22          object_data     obj
 23  ;

1600000 rows created.

Elapsed: 00:00:10.19
SQL> rollback;

Rollback complete.

Elapsed: 00:00:01.00
SQL> 
...
...

1600000 rows created.

Elapsed: 00:00:10.34
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> 
...
...

1600000 rows created.

Elapsed: 00:00:10.23
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.22
SQL> 
...
...

1600000 rows created.

Elapsed: 00:00:10.29
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.21
SQL> 
...
...

1600000 rows created.

Elapsed: 00:00:10.33
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL>

So, for these 5 executions, we got an average time of 10.276 seconds (10.19, 10.34, 10.23, 10.29, 10.33). You see above that between every execution I called the rollback. If you are interested, if I commit (between every execution), the average time was 14.558 seconds (10.29, 13.97, 16.06, 18.22, 14.25).

Archivelogs and Flashback logs at Fast Recovery Area

The next step was simple, I enabled the flashback mode and tried again. Just to remember, in this case, both the archivelogs and flashback logs are in the Fast Recovery Area and it reside on my NAS drive (path /opt/oracle/oradata/FREE/recovery/):

SQL> alter session set container = cdb$root;

Session altered.

Elapsed: 00:00:00.00
SQL> alter database flashback on;

Database altered.

Elapsed: 00:00:02.17
SQL> alter session set container = PDBDC1;

Session altered.

Elapsed: 00:00:00.02
SQL>

SQL> select count(*) from t1;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.00
SQL>
...
...

1600000 rows created.

Elapsed: 00:00:14.47
SQL> rollback;

Elapsed: 00:00:00.00
SQL> 
...
...
1600000 rows created.

Elapsed: 00:00:14.77
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.70
SQL> 
...
...

1600000 rows created.

Elapsed: 00:00:15.94
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.01
SQL>
...
...
1600000 rows created.

Elapsed: 00:00:15.20
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> 
...
...

1600000 rows created.

Elapsed: 00:00:13.87
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.01
SQL>

So, doing the rollback between every execution the average time was 14,85 seconds (14.47, 14.77, 15.94, 15.2, 13.87). So, the execution was 44,51% slower compared with using only archivelogs.

For the commit, the average was 18.162 seconds (13.89, 18.44, 19.57, 19.06, 19.85). Representing one execution 24.75% slower.

Setting the DB_FLASHBACK_LOG_DEST_SIZE and DB_FLASHBACK_LOG_DEST

As discussed above (and in the docs) the idea to set these two parameters is to reduce the impact of enabling the flashback logs. So, in this example, I set the flashback logs to my local SSD:

SQL> alter session set container = cdb$root;

Session altered.

Elapsed: 00:00:00.08
SQL>
SQL> alter database flashback off;

Database altered.

Elapsed: 00:00:00.21
SQL>
SQL> show parameter flashback;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_log_dest                string
db_flashback_log_dest_size           big integer 0
db_flashback_retention_target        integer     1440
SQL> alter system set db_flashback_log_dest_size = 10G scope = both;

System altered.

Elapsed: 00:00:00.03
SQL> alter system set db_flashback_log_dest='/opt/oracle/oradata/LOCALSSD' scope = both;

System altered.

Elapsed: 00:00:00.03
SQL>

As you see I disabled the flashback log before setting the new parameters. I recommend doing this (if you can) to have a clean start. You can see that after enabling the flashback logs they are in the desired place:

SQL> set linesize 255
SQL> col NAMEFL format a70
SQL> alter database flashback on;

Database altered.

Elapsed: 00:00:00.43
SQL> SELECT sequence# as seq, name as namefl, bytes/1024/1024 as size_mb, to_char(first_time, 'DD/MM/RRRR HH24:MI') as firsttime  from v$flashback_database_logfile order by first_time;

       SEQ NAMEFL                                                                    SIZE_MB FIRSTTIME
---------- ---------------------------------------------------------------------- ---------- ----------------
         1 /opt/oracle/oradata/LOCALSSD/FREE/flashback/o1_mf_m719cdk2_.flb               200 17/06/2024 23:06
         1 /opt/oracle/oradata/LOCALSSD/FREE/flashback/o1_mf_m719ch5x_.flb               200

Elapsed: 00:00:00.00
SQL>

And doing the same example I got these results:

SQL> alter session set container = PDBDC1;

Session altered.

Elapsed: 00:00:00.01
SQL> select count(*) from t1;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.00
SQL>
...
...

1600000 rows created.

Elapsed: 00:00:11.63
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> 
...
...
1600000 rows created.

Elapsed: 00:00:11.57
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.01
SQL> 
...
...
1600000 rows created.

Elapsed: 00:00:11.63
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> 
...
...
1600000 rows created.

Elapsed: 00:00:11.69
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.01
SQL> 
...
...
1600000 rows created.

Elapsed: 00:00:11.78
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.01
SQL>

So, the numbers don’t lie, and setting just the flashback logs to the fast location (remember I am still running in archivelog mode and have them at the fast recovery area) the average was 11.66 seconds (11.63, 11.57, 11.63, 11.69, 11.78). This is only 13.46% slower compared with using just archivelogs. And it was 21.48% faster than running with archivelogs and flashback logs writing at the same slow path.

Interestingly, the numbers don’t lie, and doing the commit in the middle of every execution I got the average of 13.626 seconds (13.77, 15.41, 13.7, 12.96, 12.29). And surprisingly this was 6.4% faster than our baseline (using just archivelog). And it was 24% faster than running with archivelogs and flashback logs writing at the the fast recovery area.

Summary

The resume was clear, running the database in archivelog mode with flashback log on as faster disks, is a clear advantage. These new parameters for 23ai help when you can put your flashback logs in other (fast) locations. 

In one additional post, I cover special cases when setting these parameters. Some interesting details.

 

Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies, or opinions. The information here was edited to be useful for general purposes, specific data and identifications were removed to allow reach the generic audience and to be useful for the community. Post protected by copyright” 

2 thoughts on “23ai, new parameters DB_FLASHBACK_LOG_DEST_SIZE and DB_FLASHBACK_LOG_DEST”

  1. Pingback: 23ai, additional details for DB_FLASHBACK_LOG_DEST_SIZE and DB_FLASHBACK_LOG_DEST - Fernando Simon

  2. Pingback: Some Summer Reading – Oracle Analytics by Adrian Ward

Leave a Reply

Your email address will not be published. Required fields are marked *