default initial extents bloat flashback archives

Here is a quick post concerning storage space requirements when using the Total Recall database feature. We did not notice this when we first set things up and were quite surprised when we looked a little closer at the space usage patterns of our flashback archives after a few weeks.

The default size for the initial extents of partitions was changed in 11.2.0.2 from 64kB to 8MB (details), propably with the intent that any table worth partitioning would usually be big enough to call for large extents anyway. But when you are using flashback archives, a new partition will be generated for you daily, no matter if you actually change anything in the base table (and generate fba data) or not. Multiply this by the number of tables you have enabled for total recall and this may easily add up to a significant amount of data.

This really would not be a very big issue if there FBAs would make use of deferred segment creation. But even though this is enabled at the instance level, the archives are implicitly created with “SEGMENT CREATION IMMEDIATE”. This is a look at the DDL of one of the underlying fba_hist tables

 CREATE TABLE "GPM"."SYS_FBA_HIST_75880" 
   (	"RID" VARCHAR2(4000 BYTE), 
	"STARTSCN" NUMBER, 
	"ENDSCN" NUMBER, 
	"XID" RAW(8), 
	"OPERATION" VARCHAR2(1 BYTE), 
	"DTYPE" VARCHAR2(124 BYTE), 
	"ID" NUMBER(19,0), 
	"CODE" VARCHAR2(64 BYTE), 
	"NAME" VARCHAR2(512 BYTE), 
	[...]
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR OLTP 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FB_ARCHIVE" 
  PARTITION BY RANGE ("ENDSCN") 
 (PARTITION "PART_14188342"  VALUES LESS THAN (14188342) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR OLTP LOGGING 
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FB_ARCHIVE" , 
...

At this moment there is no way to change the partitioning parameters (to either make the initial extents smaller or use set segment creation to deferred) that are being used for flashback archives so the only chance to influence this is by altering the new (hidden) parameter file _partition_large_extents _before_ enabling total recall for a table.

4 thoughts on “default initial extents bloat flashback archives

  1. Did this go away by 11.2.0.4? I am yet to see the same behavior.

    create table SCOTT.SYS_FBA_HIST_1079756
    (
    rid VARCHAR2(4000),
    startscn NUMBER,
    endscn NUMBER,
    xid RAW(8),
    operation VARCHAR2(1),
    x NUMBER
    )
    partition by range (ENDSCN)
    (
    partition HIGH_PART values less than (MAXVALUE)
    tablespace FDA_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
    initial 8M
    next 1M
    minextents 1
    maxextents unlimited
    )
    );

  2. In Oracle 11.2.0.4, you can set the Initial size to 64K and force the FDA tables to be created immediately by performing certain DDL changes such as adding a simple constraint (1=1).

    create table foo_SmallExtents(x number);

    alter table foo_SmallExtents flashback ARCHIVE;

    ALTER SESSION SET “_partition_large_extents”=false;

    — Add a fake constraint to trigger the flashback table creation
    alter table foo_SmallExtents add constraint x check (1=1);
    alter table foo_SmallExtents drop constraint x;

    — DDL of the HIST table, note “initial 64k”
    create table SYS_FBA_HIST_1079822
    (
    rid VARCHAR2(4000),
    startscn NUMBER,
    endscn NUMBER,
    xid RAW(8),
    operation VARCHAR2(1),
    x NUMBER
    )
    partition by range (ENDSCN)
    (
    partition HIGH_PART values less than (MAXVALUE)
    tablespace FDA_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    )
    );

  3. cool, I did not know an alter session was enough, I always set this on the db level. The trick with the constraint is also neat, I usually just updated a single row but may be even more convenient.

  4. FYI: Tried to reproduce this, when enabling the constraint, session hung on wait event “fbar timer”.
    searching support shows this is a bug that was on 11.2.0.1 however Im on 11.2.0.4 so its obviously still there
    “Bug 9473303 DDL on a table with Flashback Data Archive enabled may hang”
    Had to kill the session and generate the history partitions with an insert and update instead. HTH

Leave a Reply

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