I'm using 126.96.36.199 (11g R2) and have a series of large partitioned tables which I want to compare non-compressed sizes vs compressed sizes.
I first determined the sizes of all of the tables in non-compressed form.
I then created new tablespaces with clause "DEFAULT COMPRESS FOR OLTP;" included as in the following example:
DATAFILE '/u03/app/oracle/oradata/ORADB1/EIA_SPIDERCLKSTRM_INDEXES_COMP_01.dbf' SIZE 300M AUTOEXTEND
ON NEXT 50M MAXSIZE 500M EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP;
I then altered each partition of the non-compressed table to move it to the new COMPRESS FOR OLTP tablespace.
I've now re-calculated the compression ratio and I'm seeing roughly 15-20% reduction in space usage.
I'm wondering though, if I look in the DBA_TABLES view, the COMPRESS_FOR column is still NULL. This kind of makes sense to me since I never compressed the table but I'm wondering if there is some other way I can tell that the table partitions are compressed properly (beyond comparing sizes before and after). Or is what I have done the best way to check that the table partitions are compressed properly?
Also, I'm wondering if 15-20% is average compression results for folks or if I should expect a higher compression ratio?
I'm just trying to verify that I'm doing everything right.
One other question too:
As I created the compressed tablespaces with the DEFAULT COMPRESS FOR OLTP clause,
in my 10.2.0.5 Grid Server, it shows the compression for the tablespace as
Allocation Type Automatic
Segment Space Management Automatic
Enable logging Yes
Compression Enabled on direct-path INSERT operations only
Block Size (B) 8192
I assume that the Grid Server is just confused because it doesn't know about COMPRESS FOR OLTP but it does make me wonder if the tablespace is really compressed for OLTP or if it's compressed for direct-path INSERT operations only as the Grid Server claims.
I'm not sure if OEM Grid is confused or you are so lets look a little further.
For Advanced Compression you must insert with DIRECT LOAD, for example the APPEND hint.
Secondly when you specify Advanced Compressed Oracle changes PCT_FREE to 0 so you are likely to get 10% reduction even if no compression takes place.
Third Oracle will not do the overhead work of attempting to compress data if it does not believe it can achieve enough compression to have a significant enough impact to make the CPU usage pay for itself.
I'd suggest you run the DBMS_COMPRESSION advisor and figure out what you really have.