2 Replies Latest reply: Apr 16, 2010 5:52 AM by damorgan RSS

    Question about tablespace compression

    JSebastian
      All,

      I'm using 11.2.0.1 (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:

      CREATE SMALLFILE
      TABLESPACE "EIA_SPIDERCLKSTRM_INDEXES_COMP"
      LOGGING
      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.

      Thanks,

      John
        • 1. Re: Question about tablespace compression
          JSebastian
          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

               
          Storage
          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.
          • 2. Re: Question about tablespace compression
            damorgan
            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.
            http://www.morganslibrary.org/reference/dbms_compression.html