Introduction
The first time I heard of the new oracle 11g compression features was in this thread:
I had a test system running on 11g and did a few small tests on it. The compression seemed to be quite effective.
It would be nice to have a possibility of forecasting the compression factor of a given database. At first I planned to write a small PL/SQL package myself, but then I discovered there already is one: Oracle Advanced Compression Advisor
The good thing is, that it is working on 9i/10g as well. However it won't run with LONG datatypes, if you still have them.
Installing and running the Compression Advisor
Just download to package from the above link and run the scripts in it. I installed it under SYS.
orasid% sqlplus / as sysdba
SQL> start dbmscomp.sql
SQL> start prvtcomp.plbThe package can be run like this (I again use table MARC as example):
SQL> set serveroutput on
SQL> exec DBMS_COMPRESSION.getratio('SAPR3','MARC',10);
Sampling table: SAPR3.MARC
Sampling percentage: 10%
Expected Compression ratio with Advanced Compression Option: 32.12
The package basically will read 10% random tableblocks into a temporary table called DBMS_COMPRESSION_TEMP_UNCMP and compress it into a second table DBMS_COMPRESSION_TEMP_CMP. So be careful, you will need at least 10% space in the users default tablespace of the original table. If you run it under SYS, you don't want to have the objects on the SYSTEM tablespace.
SQL> alter user sys default tablespace psapuser1d;Here are the sizes during my run on MARC:
SQL> select segment_name, (bytes)/1024/1024 "MB" from dba_segments
where segment_name = 'MARC';
SEGMENT_NAME MB
-------------------------------------------------- ----------
MARC 34880
The temp objects:
SQL> select segment_name, (bytes)/1024/1024 "MB" from dba_segments
where owner = 'SYS' and tablespace_name = 'PSAPUSER1D'
SEGMENT_NAME MB
-------------------------------------------------- ----------
DBMS_COMPRESSION_TEMP_UNCMP 2819
DBMS_COMPRESSION_TEMP_CMP 87The result of 32.12 is pretty close the the actual compression I achieved if you remember the SDN thread above. It was something around 30. I like!
I run a second test with only a 1% sample the result was 31.58. So the result is good and the runtime was much faster and needed less space as well. For tables over 1mio blocks even 0.1% seems to be significant enough.
To get rid of the package, just drop it:
SQL> drop package DBMS_COMPRESSION;
Full database estimation
I decided to wrap the main procedure into a custom loop over all tables. I added a check to exclude the LONG types, and some total space calculation. After that i ran it against the large (>2tb test database). I don't post the full procedure here, as it got longer and longer. If anyone is interested in it, I might put it on.
I only select tables with a size larger than 100mb. Here are the results:
- a total of 290 tables was estimated
- the average overall compression factor was 4.1
- the overall size of these tables was 1100gb, the estimated savings with compression are 834gb
- only 28 out of the 290 tables had a compression factor below 1.5, and only 40 had a factor of 8 or higher
- MARC is the table with the best compression factor
Density and Sorting
I was wondering if the density within blocks has an influence on the estimation of the compression factor. I ran the advisor on a table with no deleted rows (full blocks), with every third row deleted (blocks ~60% full) and with every second and third row deleted (blocks ~30% full). The compression factor was always the same, because the advisor first builds a temporary uncompressed table (with all blocks full) and then compresses it. So potential space (also called table fragmentation) within blocks is not included in the estimation. By the way the table fragmentation is one of the main reasons why databases shrink after an export / import.
I already noticed during my first tests, that the order of the rows can be quite important to a compression factor. This is because every block gets compressed independant of the other. So the more similar values within one block the better the compression. I made another test with table MSEG:
estimated compression factor sorted by primary key: 10.3
estimated compression factor actual table distribution: 6.03
estimated compression factor ramdom sorted: 5.81
I used a create table as select * from mseg order by dbms_random.random for the random sorting. So in this example the worst estimated factor is 5.8 and the best is 10.3. So the advisor is also reflecting the sort order of the data.
Conclusion
An average compression factor of 4 is still huge, although I expected a bit more, after the tests on MARC. I am planning to have a 500gb sata disk and the 160gb SSD, so the database will fit on that. I am going to compare the real result to the estimation afterwards.
I have not finally decided on which table to compress yet, I guess I still need to do more tests. For a start I think the tables having compression factors close to 1 should not be compressed. I also made a few test with secure files (lob compression), but I guess the lob data is already compressed, because the results were not very impressive.
At the moment the OLTP compression feature is not yet supported by SAP (as is 11g), but I am sure it will be in the near future. Check SAP note 1289494 - FAQ: Oracle compression for details.
During my research I found this document on Oracle Advanced Compression, recommended for interested readers:
Honey I Shrunk the Data Warehouse!(Oracle Advanced Compression)
I was talking only about table compression, I consider index key compression as well known, you can learn about it in this blog: [Oracle] Index key compression
Stay tuned for more: "Codename SOL" - Intro / TOC
