5 Replies Latest reply: Oct 24, 2011 9:15 PM by damorgan RSS

    Basic Compression (10gR2) for OLTP

    ashum
      I have configured an OLTP database to use data (tables and indexes) compression and besides I got a huge space saving (64% off), there is not any noticeable performance improvement.

      I know that on 10g this feature is designed for OLAP system, but I have also read that based on the duplicated values removal there should be a improvement specially on the Full Scan Tables.

      Also, I am having a hard time to compare the performance. What exactly should I look for at the execution plan to identify difference on the performance instead of executing the query and see the execution time (usually 1 hour execution time)? I have checked Cost, buffer gets, physical read, etc. Any advice?

      The same query sometimes takes 54 minutes to complete and other 58 (without compression). I would expected slowness the first time (no data cached) and the faster on the further execution, but it is totally irregular and this is a controlled environment where I am the only one using it.

      Regards,
        • 1. Re: Basic Compression (10gR2) for OLTP
          mrmessin
          You can check the indivdual queries with set autotrace on or set autotrace traceonly. Where I asw the performance gains in in the cases where physical I/O was experienced for the query. For logical I/O where the data was gotten from the cache I dod not see performance gains. The larger the physical I/O savings from reading compressed blocks the bigger the performance gain.
          • 2. Re: Basic Compression (10gR2) for OLTP
            ashum
            These are the statistics before and after the compression, but the execution time is the same in both cases (about 55 minutes)

            BEFORE
            Statistics
            ----------------------------------------------------------
            69 recursive calls
            3 db block gets
            287616 consistent gets
            252421 physical reads
            3028 redo size
            26371705 bytes sent via SQL*Net to client
            265192 bytes received via SQL*Net from client
            24066 SQL*Net roundtrips to/from client
            10 sorts (memory)
            0 sorts (disk)
            360973 rows processed


            AFTER
            Statistics
            ----------------------------------------------------------
            48 recursive calls
            0 db block gets
            567462 consistent gets
            184452 physical reads
            752 redo size
            28153499 bytes sent via SQL*Net to client
            265192 bytes received via SQL*Net from client
            24066 SQL*Net roundtrips to/from client
            10 sorts (memory)
            0 sorts (disk)
            360973 rows processed
            • 3. Re: Basic Compression (10gR2) for OLTP
              Luis Moreno Campos
              Basic compression burns a lot of CPU and so you loose the effect of compression on small to medium sized objects.
              See this example of 3 tables in 11g with basic, oltp and no compression, and each with about 800k rows:

              TABLE_NAME COMPRESS COMPRESS_FOR BLOCKS
              ------------------------------ -------- ------------ ----------
              BASIC_COMP ENABLED BASIC 2804
              OLTP_COMP ENABLED OLTP 3520
              UNCOMP DISABLED 8594


              --------------------------------------------------------------------------------
              | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
              --------------------------------------------------------------------------------
              | 0 | SELECT STATEMENT | | 5 | 35 | 2382 (3)| 00:00:29 |
              | 1 | SORT ORDER BY | | 5 | 35 | 2382 (3)| 00:00:29 |
              | 2 | HASH GROUP BY | | 5 | 35 | 2382 (3)| 00:00:29 |
              | 3 | VIEW | | 22 | 154 | 2381 (3)| 00:00:29 |
              | 4 | HASH GROUP BY | | 22 | 286 | 2381 (3)| 00:00:29 |
              |* 5 | TABLE ACCESS FULL| UNCOMP | 826K| 10M| 2347 (1)| 00:00:29 |
              --------------------------------------------------------------------------------

              -----------------------------------------------------------------------------------
              | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
              -----------------------------------------------------------------------------------
              | 0 | SELECT STATEMENT | | 5 | 35 | 994 (4)| 00:00:12 |
              | 1 | SORT ORDER BY | | 5 | 35 | 994 (4)| 00:00:12 |
              | 2 | HASH GROUP BY | | 5 | 35 | 994 (4)| 00:00:12 |
              | 3 | VIEW | | 22 | 154 | 993 (4)| 00:00:12 |
              | 4 | HASH GROUP BY | | 22 | 286 | 993 (4)| 00:00:12 |
              |* 5 | TABLE ACCESS FULL| OLTP_COMP | 555K| 7058K| 970 (2)| 00:00:12 |
              -----------------------------------------------------------------------------------

              ------------------------------------------------------------------------------------
              | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
              ------------------------------------------------------------------------------------
              | 0 | SELECT STATEMENT | | 5 | 35 | 813 (7)| 00:00:10 |
              | 1 | SORT ORDER BY | | 5 | 35 | 813 (7)| 00:00:10 |
              | 2 | HASH GROUP BY | | 5 | 35 | 813 (7)| 00:00:10 |
              | 3 | VIEW | | 22 | 154 | 812 (7)| 00:00:10 |
              | 4 | HASH GROUP BY | | 22 | 286 | 812 (7)| 00:00:10 |
              |* 5 | TABLE ACCESS FULL| BASIC_COMP | 827K| 10M| 777 (3)| 00:00:10 |
              ------------------------------------------------------------------------------------

              In this example the same query would take roughly the same time in each of the 3 cases. You might think it doesn't make sense, but unless you are using Exadata compression, you will always burn the DB Server CPU thus eating up the advantage of doing it in serial mode.

              If I increase the parallel degree of the BASIC compression table, the cost drops down from 813 to 216 and the plan looks like this:

              --------------------------------------------------------------------------------------------------------------------------
              | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
              --------------------------------------------------------------------------------------------------------------------------
              | 0 | SELECT STATEMENT | | 5 | 35 | 226 (7)| 00:00:03 | | | |
              | 1 | PX COORDINATOR | | | | | | | | |
              | 2 | PX SEND QC (ORDER) | :TQ10002 | 5 | 35 | 226 (7)| 00:00:03 | Q1,02 | P->S | QC (ORDER) |
              | 3 | SORT ORDER BY | | 5 | 35 | 226 (7)| 00:00:03 | Q1,02 | PCWP | |
              | 4 | HASH GROUP BY | | 5 | 35 | 226 (7)| 00:00:03 | Q1,02 | PCWP | |
              | 5 | PX RECEIVE | | 5 | 35 | 226 (7)| 00:00:03 | Q1,02 | PCWP | |
              | 6 | PX SEND RANGE | :TQ10001 | 5 | 35 | 226 (7)| 00:00:03 | Q1,01 | P->P | RANGE |
              | 7 | HASH GROUP BY | | 5 | 35 | 226 (7)| 00:00:03 | Q1,01 | PCWP | |
              | 8 | VIEW | | 22 | 154 | 225 (7)| 00:00:03 | Q1,01 | PCWP | |
              | 9 | HASH GROUP BY | | 22 | 286 | 225 (7)| 00:00:03 | Q1,01 | PCWP | |
              | 10 | PX RECEIVE | | 22 | 286 | 225 (7)| 00:00:03 | Q1,01 | PCWP | |
              | 11 | PX SEND HASH | :TQ10000 | 22 | 286 | 225 (7)| 00:00:03 | Q1,00 | P->P | HASH |
              | 12 | HASH GROUP BY | | 22 | 286 | 225 (7)| 00:00:03 | Q1,00 | PCWP | |
              | 13 | PX BLOCK ITERATOR | | 827K| 10M| 216 (3)| 00:00:03 | Q1,00 | PCWC | |
              |* 14 | TABLE ACCESS FULL| BASIC_COMP | 827K| 10M| 216 (3)| 00:00:03 | Q1,00 | PCWP | |
              --------------------------------------------------------------------------------------------------------------------------

              When you have fewer blocks to scan, the compression advantage should kick-in more easily, but be aware that parallelism only compensate for long running queries.

              Hope it helped.

              LMC
              • 4. Re: Basic Compression (10gR2) for OLTP
                user4649694
                Hi Ashum,
                your question is still marked as not answered. Your statistics looks like that compression reduces the amount of physical data:

                Before: 252421 physical reads
                After: 184452 physical reads

                Interestingly consistent gets wen't up:

                Before: 287616 consistent gets
                After: 567462 consistent gets

                So maybe the execution plan changes?

                You should trace your session, check if execution plan will be identically and check the timing in trace files (use tkprof). I would assume that the number of consistent gets (usually also physical reads) will go done. CPU time will increase. Check what kind of wait event is responsible for the 55 minutes (network, I/O, CPU, other?).

                Regards
                Martin
                • 5. Re: Basic Compression (10gR2) for OLTP
                  damorgan
                  Or maybe the test case is flawed by not being controlling for numerous extraneous factors. ;-)