OraFAQ Forum: Server Administration » Should table compression decrease redo?

Home » RDBMS Server » Server Administration » Should table compression decrease redo? (11g et al, any platform)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Should table compression decrease redo? [message #610393]

Wed, 19 March 2014 13:34

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Hi,

Just thinking out loud, shouldn't the same algorithm when writing to data blocks for compressed tables apply to when writing to redo logs?

I performed a quick test and couldn't find any difference in redo size.

Although I am inclined to think that redo logs will contain the DML statments, can somebody confirm my understanding? And forgive me for my naiveness in advance.

Thanks!

SQL> 
SQL> DROP TABLE ALLONOC;

Table dropped.

SQL> 
SQL> DROP TABLE ALLOC;

Table dropped.

SQL> 
SQL> CREATE TABLE ALLONOC AS SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM ALL_OBJECTS WHERE 1=0;

Table created.

SQL> 
SQL> CREATE TABLE ALLOC COMPRESS AS SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM ALL_OBJECTS WHERE 1=0;

Table created.

SQL> 
SQL> SET AUTOTRACE ON STATISTICS
SQL> 
SQL> INSERT /*+ APPEND */ INTO ALLONOC SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM ALL_OBJECTS ORDER BY 1, 2, 3;

77373 rows created.


Statistics
----------------------------------------------------------                      
       1705  recursive calls                                                    
       1220  db block gets                                                      
     100634  consistent gets                                                    
          2  physical reads                                                     
      72392  redo size                                                          
        827  bytes sent via SQL*Net to client                                   
        866  bytes received via SQL*Net from client                             
          3  SQL*Net roundtrips to/from client                                  
       3471  sorts (memory)                                                     
          0  sorts (disk)                                                       
      77373  rows processed                                                     

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> INSERT /*+ APPEND */ INTO ALLOC SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM ALL_OBJECTS ORDER BY 1, 2, 3;

77373 rows created.


Statistics
----------------------------------------------------------                      
       1685  recursive calls                                                    
       1018  db block gets                                                      
     100624  consistent gets                                                    
          2  physical reads                                                     
      68952  redo size                                                          
        828  bytes sent via SQL*Net to client                                   
        864  bytes received via SQL*Net from client                             
          3  SQL*Net roundtrips to/from client                                  
       3471  sorts (memory)                                                     
          0  sorts (disk)                                                       
      77373  rows processed                                                     

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> SET AUTOTRACE OFF
SQL> 
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(USER,'ALLONOC')

PL/SQL procedure successfully completed.

SQL> 
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(USER,'ALLOC')

PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT TABLE_NAME, BLOCKS FROM USER_TABLES WHERE TABLE_NAME IN ('ALLONOC','ALLOC');

TABLE_NAME                         BLOCKS                                       
------------------------------ ----------                                       
ALLOC                                 316                                       
ALLONOC                               481                                       

SQL> 
SQL> SPOOL OFF

Report message to a moderator

Re: Should table compression decrease redo? [message #610394 is a reply to message #610393]

Wed, 19 March 2014 13:44

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

I think you have the expected result.
When using compression, rows are inserted as normal until the block is filled to percent free, at which point the compression (which is actually de-duplication) is triggered: it is asynchronous with regard to the insert. The redo, generated during the insert, would therefore be the same.
I am of course open to correction on this. I hadn't thought of it before, does it sound a reasonable explanation?

Report message to a moderator

Re: Should table compression decrease redo? [message #610789 is a reply to message #610394]

Mon, 24 March 2014 05:58

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

One thing, basic compressed tables have a default PCTFREE 0, where oracle thinks that the table will be mostly read-only:

SQL> create table testcomp compress as select object_type from user_objects where 1=0;

Table created.

SQL> create table testnocomp as select object_type from user_objects where 1=0;

Table created.

SQL> select table_name, compression, pct_free from user_tables where table_name IN ('TESTCOMP', 'TESTNOCOMP');

TABLE_NAME                     COMPRESS   PCT_FREE
------------------------------ -------- ----------
TESTCOMP                       ENABLED           0
TESTNOCOMP                     DISABLED         10

In addition to what you mentioned about the asynchronous activity, I'm also inclined to think that the redo logs contain equivalents of DML statements in proprietary format (so that they can be replayed on the same database and others, e.g. using Goldengate) and therefore possibly cannot be compressed, unlike the physically formatted blocks in the data file and the buffer cache.

Report message to a moderator

Re: Should table compression decrease redo? [message #610800 is a reply to message #610789]

Mon, 24 March 2014 07:54

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Quote:

m also inclined to think that the redo logs contain equivalents of DML statements in proprietary format (so that they can be replayed on the same database and others, e.g. using Goldengate)

No. Look up redo generation in the docs.

Report message to a moderator

Previous Topic:	Confusion about nologging
Next Topic:	adding a column to a policy in VPD

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 05:22:50 CST 2024