Oracle SQL

    Improvements to HCC with wide tables in 12.2

    Posted on May 4, 2017 by Roger MacNicol Posted in oracle, SmartScan

    HCC Compression Unit Sizing 

    Since the beginning Oracle has provided four compression levels to offer a trade-off between the compression ratio and various other factors including table scans and the performance of single-row retrieval. I can not emphasize enough that the various trade offs mean that YMMV with the different levels and you should always test what works best with your application and hardware when choosing the level. Historically people have rarely used Query Low since the fast compression with reduced compression ratio means that the extra disk I/O is slower than the cost of decompression with Query High. The one time that Query Low makes sense on spinning disks is if you still have a significant number of row retrieval operations (including from index access joins).

    NMVe Flash

    X5 introduced NVMe technology which means that the extra I/O from Query Low is faster than ZLIB decompression which makes Query Low beneficial. So we needed to reassess the sizing of Compression Units. from 11.2.0.1 to 12.1.2.4 the sizing guidelines are as follows:

    Name Target
     Rows
     Target 
     Minimum Size
      Target
     Maximum Size
     Compression
    Query Low 1000 to 8000 32 kb 32 kb LZO
    Query High 1000 to 8000 32 kb 64 kb ZLIB
    Archive Low 8000 64 kb 256 kb ZLIB
    Archive High 8000 256 kb 256 kb BZ2

    So, for example, Compress for Query High aims to pivot around at least 1000 rows and create a minimum compression unit size of 32 kb and a maximum of 64 kb. Using 12.1.2.3 I ran these against a TPC-H Lineitem table than contained between 1 and 6 copies of each column.

    For Query Low fixed 32 kb CUs this gave us the following:

     Additional copies of lineitem Rows per 32 kb CU
     0 2797
     1 580
     2 318
     3 216
     4 162
     5 129

    and for Query High 32 to 64 kb CUs this gave us:

     Additional copies of lineitem Rows per 32 kb CU CU ave size
     0 5031 32
     1 1010 32
     2 936 51
     3 794 63
     4 595 67
     5 476 63

    so we see that the CU size remains as 32 kb as long as we are getting a 1000 rows or more then increases in size to 64 kb to try and fit in at least 1000 rows.

    It became clear that this size range was inadequate for wide tables so to get more efficient compression and longer column runs for faster predicate performance (and also better CELLMEMORY rewrites) we removed the fixed size for Query Low and increased the max:

    Query Low: 32 kb to 64 kb

    Query High:  32 kb to 80 kb

    This will not affect narrow tables at all but wider tables should see better table compression and faster scans at the cost of slightly slower single row retrieval for data loaded by 12.2 RDBMS. If you have HCC wide tables and typically cache them on flash cache you should consider re-evaluating Query Low for data loaded in 12.2 (or use Alter Table Move Compress to recompress existing data).

    Roger 

    Print PDF eBook
    HCC Roger MacNicol SmartScan
    « Examining the new Columnar Cache with v$cell_state
    More tricks with OPT_PARAM »
    photo Sayan Malakshinov

    Oracle Database Developer Choice Award winner
    Oracle ACE Oracle ACE
    Oracle performance tuning expert
    UK / Aylesbury / Transmedia Dynamics
    sayan@orasql.org

    Simple Android Oracle client

    Get it on Google Play

    Recent Posts

    • Where does the commit or rollback happen in PL/SQL code?
    • :1 and SP2-0553: Illegal variable name “1”.
    • ORA exceptions that can’t be caught by exception handler
    • Another interesting troubleshooting case
    • SQL*Plus tips #9: Reading traces and incident files

    Recent Comments

    • Oracle SQL | Shining some light on Database In-Memory vs the Exadata Columnar Cache in 12.1.0.2 on Tracing Hybrid Columnar Compression in an offload server
    • Oracle SQL | More on tracing the offload server on Tracing Hybrid Columnar Compression in an offload server
    • Oracle SQL | DMLs and the Columnar Cache on ADW on How to tell if the Exadata column cache is fully loaded
    • Oracle SQL | DMLs and the Columnar Cache on ADW on What’s new in 12.2 CELLMEMORY Part 3
    • Oracle SQL | Serial Scans failing to offload on When bloggers get it wrong – part 2
    • Oracle SQL | Serial Scans failing to offload on When bloggers get it wrong – part 1
    • Oracle SQL | DMLs and the Columnar Cache on ADW on What’s new in 12.2 CELLMEMORY Part 2
    • Oracle SQL | DMLs and the Columnar Cache on ADW on What’s new in 12.2 CELLMEMORY Part 1
    • Oracle SQL | The beginners guide to Oracle Table Scans on When bloggers get it wrong – part 1
    • Case Study | Oracle Scratchpad on Lateral view decorrelation(VW_DCL) causes wrong results with rownum

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries feed
    • Comments feed
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL