Home » RDBMS Server » Server Administration » Should table compression decrease redo? (11g et al, any platform)
Should table compression decrease redo? [message #610393] Wed, 19 March 2014 13:34 Go to next message
d_seng
Messages: 53
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
Re: Should table compression decrease redo? [message #610394 is a reply to message #610393] Wed, 19 March 2014 13:44 Go to previous messageGo to next message
John Watson
Messages: 4489
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?
Re: Should table compression decrease redo? [message #610789 is a reply to message #610394] Mon, 24 March 2014 05:58 Go to previous messageGo to next message
d_seng
Messages: 53
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.
Re: Should table compression decrease redo? [message #610800 is a reply to message #610789] Mon, 24 March 2014 07:54 Go to previous message
John Watson
Messages: 4489
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.
Previous Topic: Confusion about nologging
Next Topic: adding a column to a policy in VPD
Goto Forum:
  


Current Time: Sat Aug 23 10:29:25 CDT 2014

Total time taken to generate the page: 0.31670 seconds