Skip to Main Content
  • Questions

  • Flashback Archive Internal History table is not compressed -- Kindly suggest

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Srikanth.

Asked: November 30, 2016 - 4:57 pm UTC

Last updated: December 09, 2016 - 4:03 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,

Once flashback archive is enabled for a table, a corresponding history table will be created by oracle internally. It is automatically partitioned and compressed as well. But when I have enabled FBA for a table, the history table is partitioned but not compressed. Can you please help me.

Hi Connor.. Currrently I didin't have the previous environment. So I am replicating the same in another environment. Please find the below example :

create table test444(id number);
create table succeeded.

alter table testuser.test444 flashback archive TESTFDA1;
alter table testuser.test444 succeeded.

insert into test444 values(1);
1 rows inserted
commit;
commited

select * from user_flashback_archive_tables where table_name = 'TEST444';
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
TEST444 TESTUSER TESTFDA1 SYS_FBA_HIST_170975 ENABLED

SELECT TABLE_NAME,PARTITION_NAME,COMPRESSION from USER_TAB_PARTITIONS;

TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
SYS_FBA_HIST_170975 HIGH_PART DISABLED


select dbms_metadata.get_ddl('TABLE','SYS_FBA_HIST_170975') from dual;

"
CREATE TABLE "TESTUSER"."SYS_FBA_HIST_170975"
( "RID" VARCHAR2(4000),
"STARTSCN" NUMBER,
"ENDSCN" NUMBER,
"XID" RAW(8),
"OPERATION" VARCHAR2(1),
"ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "FLASHTEST_TS"
PARTITION BY RANGE ("ENDSCN")
(PARTITION "HIGH_PART" VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS 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 "FLASHTEST_TS" ) "

So here history table is not compressed. Will this create any problem? Or did I miss anything? Also as this history table is created by oracle itself, we cannot perform any operations on the same. Will this impact the performance? Kindly provide your suggestion.

and Connor said...

Because we now support both compressed AND uncompressed archive tables (because the latter is free, and the former requires an advanced compression license), you nominate this at creation time on the flashback archive

eg

CREATE FLASHBACK ARCHIVE my_fda TABLESPACE my_ts RETENTION 1 YEAR OPTIMIZE DATA;


Syntax guide is here

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5010.htm#SQLRF20008

Hope this helps.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Flashback Archive Compression

Srikanth Bitra, December 06, 2016 - 11:57 am UTC

Thanks a lot Connor,

I have gone through the link and found below :
Note:
The [NO] OPTIMIZE DATA clause is available starting with Oracle Database 11g Release 2 (11.2.0.4).

So is this history tables compression related to version? Because from about note, the OPTIMIZE DATA clause is available from 11.2.0.4

I have tried the same in 11.2.0.3.0 and the history table got compressed automatically. Please find the below example.

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.3.0

SQL> create table flash(id number);

Table created.

SQL> alter table trims.flash flashback archive fdatrims;

Table altered.

SQL> insert into flash values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> update flash set id = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dba_flashback_archive_tables where table_name = 'FLASH';

TABLE_NAME OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- -------------
FLASH TRIMS
FDATRIMS
SYS_FBA_HIST_75588 ENABLED

SQL> select TABLE_NAME, PARTITION_NAME, COMPRESSION from user_tab_partitions where table_name = 'SYS_FBA_HIST_75588';

TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
SYS_FBA_HIST_75588 HIGH_PART ENABLED

SQL> set long 50000
SQL> select dbms_metadata.get_ddl('TABLE','SYS_FBA_HIST_75588') from dual;

DBMS_METADATA.GET_DDL('TABLE','SYS_FBA_HIST_75588')
--------------------------------------------------------------------------------

CREATE TABLE "TRIMS"."SYS_FBA_HIST_75588"
( "RID" VARCHAR2(4000),
"STARTSCN" NUMBER,
"ENDSCN" NUMBER,
"XID" RAW(8),
"OPERATION" VARCHAR2(1),
"ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
COMPRESS FOR OLTP
STORAGE(

DBMS_METADATA.GET_DDL('TABLE','SYS_FBA_HIST_75588')
--------------------------------------------------------------------------------
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "BIGDATACDC"
PARTITION BY RANGE ("ENDSCN")
(PARTITION "HIGH_PART" VALUES LESS THAN (MAXVALUE) 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 "BIGDATACDC" )

DBMS_METADATA.GET_DDL('TABLE','SYS_FBA_HIST_75588')
--------------------------------------------------------------------------------

So here history table has been compressed automatically. Where as it was not done in 11.2.0.4.
My questions are :
Is this history tables compression related to versions?
If the history tables are not compressed, will it effect overall performance? Please suggest.

Flashback Archive Compression

Srikanth Bitra, December 06, 2016 - 12:04 pm UTC

Thanks a lot Connor,

I have gone through the link and found below :
Note:
The [NO] OPTIMIZE DATA clause is available starting with Oracle Database 11g Release 2 (11.2.0.4).

So is this history tables compression related to version? Because from about note, the OPTIMIZE DATA clause is available from 11.2.0.4

I have tried the same in 11.2.0.3.0 and the history table got compressed automatically. Please find the below example.

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.3.0

SQL> create table flash(id number);

Table created.

SQL> alter table trims.flash flashback archive fdatrims;

Table altered.

SQL> insert into flash values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> update flash set id = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dba_flashback_archive_tables where table_name = 'FLASH';

TABLE_NAME OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- -------------
FLASH TRIMS
FDATRIMS
SYS_FBA_HIST_75588 ENABLED

SQL> select TABLE_NAME, PARTITION_NAME, COMPRESSION from user_tab_partitions where table_name = 'SYS_FBA_HIST_75588';

TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
SYS_FBA_HIST_75588 HIGH_PART ENABLED

SQL> set long 50000
SQL> select dbms_metadata.get_ddl('TABLE','SYS_FBA_HIST_75588') from dual;

DBMS_METADATA.GET_DDL('TABLE','SYS_FBA_HIST_75588')
--------------------------------------------------------------------------------

CREATE TABLE "TRIMS"."SYS_FBA_HIST_75588"
( "RID" VARCHAR2(4000),
"STARTSCN" NUMBER,
"ENDSCN" NUMBER,
"XID" RAW(8),
"OPERATION" VARCHAR2(1),
"ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
COMPRESS FOR OLTP
STORAGE(

DBMS_METADATA.GET_DDL('TABLE','SYS_FBA_HIST_75588')
--------------------------------------------------------------------------------
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "BIGDATACDC"
PARTITION BY RANGE ("ENDSCN")
(PARTITION "HIGH_PART" VALUES LESS THAN (MAXVALUE) 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 "BIGDATACDC" )

DBMS_METADATA.GET_DDL('TABLE','SYS_FBA_HIST_75588')
--------------------------------------------------------------------------------

So here history table has been compressed automatically. Where as it was not done in 11.2.0.4.
My questions are :
Is this history tables compression related to versions?
If the history tables are not compressed, will it effect overall performance? Please suggest.
Connor McDonald
December 07, 2016 - 2:25 pm UTC

In 11.2.0.3 and below, FDA is a separate licensed option, and *always* uses compression.

In 11.2.0.4 and above, FDA is free if you do *not* compress, and is a separate license if you want it compressed

Flashback Archive Compression

Srikanth, December 08, 2016 - 7:03 am UTC

Thanks Connor,

So you mean to say that if we need the Flashback Archive internal History tables to be compressed, a licenced version of Oracle is required. Please correct me if my understanding is wrong.
Chris Saxon
December 08, 2016 - 5:04 pm UTC

To use the optimize data clause you need an Advanced Compression license.

Advanced Compression License

Srikanth Bitra, December 09, 2016 - 6:12 am UTC

Thanks Chris,

Can you please guide how to find whether my database is having advanced compression license or not.

If License is not present, how can I raise request for Advance Compression License? Please suggest.
Chris Saxon
December 09, 2016 - 4:03 pm UTC

You need to speak with your Oracle sales rep to confirm that.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.