Text box

Please note that the problems that i face may not be the same that exist on your environment so please test before applying the same steps i followed to solve the problem .

Monday 19 September 2011

Compression in 10g vs shrink and move

1-I wanted to test and see how the compression ratio if the table is shrinked and has no free blocks and the test result was promising as stated below:

SQL> select sum(bytes/1024/1024) from dba_segments where owner='NTS_EA3' and segment_name='CUST_CUSTOMER';

SUM(BYTES/1024/1024)
--------------------
              53.125

Elapsed: 00:00:00.15
SQL> alter table nts_ea3.CUST_CUSTOMER enable row movement;

Table altered.

SQL> alter table nts_ea3.CUST_CUSTOMER shrink space;

Table altered.
SQL> select sum(bytes/1024/1024) from dba_segments where owner='NTS_EA3' and segment_name='CUST_CUSTOMER';

SUM(BYTES/1024/1024)
--------------------
              53.125
SQL> alter table nts_ea3.CUST_CUSTOMER move;

Table altered.

Elapsed: 00:00:03.18
select sum(bytes/1024/1024) from dba_segments where owner='NTS_EA3' and segment_name='CUST_CUSTOMER';

SUM(BYTES/1024/1024)
--------------------
                  54

Elapsed: 00:00:00.08
SQL> alter table nts_ea3.CUST_CUSTOMER move compress;

Table altered.

Elapsed: 00:00:07.12
SQL>  select sum(bytes/1024/1024) from dba_segments where owner='NTS_EA3' and segment_name='CUST_CUSTOMER';

SUM(BYTES/1024/1024)
--------------------
                  28

Elapsed: 00:00:00.16
see how the test result changed from 53M to 28M. i will do the test on a massive amount of data and watch for performance change and i will let you know.

2- Dumpfile Compression

Data Pump Export dump files can contain database metadata objects in a compressed format, allowing dump files to consume less operating system disk space. This metadata is automatically uncompressed during Import.
This feature reduces the amount of disk space consumed by dump files.



No comments:

Post a Comment