Oracle11gR2: Deferred Segment Creation and export behavior!

I guess by now most of us know what Oracle11gR2's feature - Deferred Segment Creation really means. This post is about the impact this feature going to have while exporting a schema or tables using the traditional export. By default, any new tables created would have SEGMENT CREATION DEFERRED option set (unless you are using CTAS). This default behavior could be turned off by setting DEFERRED_SEGMENT_CREATION to false. The impact with this is - if you happen to export any of the tables, which do not have any rows (meaning empty tables without segments) - they will NOT be exported. This is only true with the legacy/old export and not with the export pump utility(10g and above). Something we have to remember if we can't get rid of the old habit of using old export! Of course, export is deprecated.

Oracle11gR2 Table / Tablespace Compression

As far as I remember, oracle introduced the basic compression feature with Oracle9i, but what's new with 11g is the ability to compress data generated as a result of OLTP transactions (almost all DMLs) as well - previously it was only available for bulk operations. Another neat feature of 11g is the ability to evaluate whether not a table is a right candidate for compression , if yes what would be compression benefits in regards space utilization. This is done using DBMS_COMPRESSION (using Compression Advisor). I wanted to use this procedure to see how accurately it could give us the space savings and also see what would be impact on performance, in general.

I am using a 18million record table for this example.

1. Space usage for the uncompressed table:

SQL> select segment_name, bytes, a.blocks, compression, compress_for from
2 dba_segments a, dba_tables b
3 where a.segment_name = b.table_name and a.segment_name='T1';

SEGMENT_NAME BYTES BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
T1 1476395008 180224 DISABLED


2. Execute the DBMS_COMPRESSION procedure to get information on space savings:

(Note that this procedure example works only in 11gR2, the function names and the arguments are different in 11gR1.)

SQL> declare
2 lv_cmp_ratio number;
3 lv_comptype_str varchar2(300);
4 lv_BLKCNT_CMP number;
5 lv_BLKCNT_UNCMP number;
6 lv_ROW_CMP number;
7 lv_ROW_UNCMP number;
8 begin
9 dbms_compression.GET_COMPRESSION_RATIO(
10 SCRATCHTBSNAME=>'USERS',
11 OWNNAME=>'CHANDRA',
12 TABNAME=>'T1',
13 PARTNAME =>null,
14 COMPTYPE =>2, ---2 means OLTP
15 BLKCNT_CMP=>lv_BLKCNT_CMP,
16 BLKCNT_UNCMP=>lv_BLKCNT_UNCMP,
17 ROW_CMP =>lv_ROW_CMP,
18 ROW_UNCMP =>lv_ROW_UNCMP,
19 CMP_RATIO=>lv_cmp_ratio,
20 COMPTYPE_STR=>lv_COMPTYPE_STR);
21 dbms_output.put_line('====================================================');
22 dbms_output.put_line('1. Compression Ratio :'||lv_cmp_ratio);
23 dbms_output.put_line('2. Block Count :'||lv_blkcnt_cmp);
24 dbms_output.put_line('3. Compression Type :'||lv_comptype_str);
25 dbms_output.put_line('4. Blk Count Compressed :'||lv_BLKCNT_CMP);
26 dbms_output.put_line('5. Blk Count Un-compressed:'||lv_BLKCNT_UNCMP);
27 dbms_output.put_line('6. Row Count Compressed :'||lv_row_cmp);
28 dbms_output.put_line('4. Row Count Un-Compressed:'||lv_row_uncmp);
29 dbms_output.put_line('====================================================');
30 end;
31 /
====================================================
1. Compression Ratio :2.38995215311004784688995215311004784689
2. Block Count :836
3. Compression Type :"Compress For OLTP"
4. Blk Count Compressed :836
5. Blk Count Un-compressed:1998
6. Row Count Compressed :256
4. Row Count Un-Compressed:107
====================================================

PL/SQL procedure successfully completed.

According to the above information, if we look at the compressed (836 blocks) and uncompressed blocks(1998) counts (4 and 5 above), we should at least 50% gain!

3. Create a new tablespace with COMPRESS option. Unless I am missing something obvious, I couldn't get the correct syntax in both 11gR1 and R2 documentation. BTW, when I executed the same SQL on 11gR1 - I got - ORA-14464: Compression Type not specified

SQL> create tablespace USER_DATA_COMPRESSED default compress for OLTP datafile '/u01/oracle/oradata/DET01/user_data_compressed.dbf' size 10M autoextend on;

Tablespace created.

4. Create copy of T1 table (our example table) in COMPRESSED tablespace:

SQL> create table chandra.t1_compressed tablespace USER_DATA_COMPRESSED
2 as Select * from chandra.t1;

Table created.

5. Let's see how much space does the compressed table take when compared to the uncompressed/original table:

SQL> select segment_name, bytes, a.blocks, compression, compress_for from dba_segments a,
2 dba_tables b
3 where a.segment_name = b.table_name and a.segment_name in ('T1','T1_COMPRESSED');

SEGMENT_NAME BYTES BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
T1_COMPRESSED 612368384 74752 ENABLED OLTP
T1 1476395008 180224 DISABLED

As theDBMS_COMPRESSION procedure indicated, we did see less than 50% space utilization with the OLTP compression, so far so good. How about performance? I performed such generic tests, involving SELECTS and other DMLs and here are the results:

Any activity involving Full Table Scan, the performance was 4 times faster on compressed tables compared with uncompressed table.
Inserts and Updates took close to twice longer on compressed table compared with uncompressed table and deletes about 10% more.

When we only talk about other benefits of compression - they would be lesser memory utilization, redo generation etc.. comes to my mind. Of course, at the cost of higher CPU consumption.