HYBRID COLUMNAR COMPRESSION - OVERVIEW

I couldn't find much from Oracle Documentation.However there are very good bits and pieces on the net. Here are my takeaways from these readings.

1). HYBRID COLUMNAR COMPRESSION [HCC] is only for EXADATA.There seems to be some technical difficulties in implementing it for the traditional storage (Don't know what it is!).

2). Concept:

Compression is very effficient for a data which is quite repetetive. With repetitive values, we can replace the Values with the smaller values (eg Symbols/Special Characters) and create the metadata to map these values; this metadata can be used to decompress the data.In a nutshell ,Compression saves space at the cost of processing power (CPU Usage).

Below diagram shows a typical Oracle Data block Stores the Data.C1-5 are Columns.

Values with Lower Cardinality (more duplicated elements) are the potential candidates for the efficient compression. With this, Columns are set to have more duplicated values than the rows.

So with HCC, Columns are grouped together in a logical area called Compression Unit.Which is a logical grouping of multiple datablocks.

So the compression unit would look like.

3). There are 2 types of HCC

- Warehouse Compression - On Average, 10 Times Space Savings.

- Archive Compression - On Average, 15 Times Space Savings(70% at some customer sites)

4). Warehouse Compression

-   Syntax: 

                 CREATE TABLE X

                                        COMPRESS FOR QUERY [LOW  | HIGH]

                                        AS

                                        SELECT * FROM Y;

                   Where   HIGH | LOW - Specifies the Extent of Compression.

                - Warehouse Compression is optimized for Query Performance.

5). Archive Compression

-   Syntax: 

                 CREATE TABLE X

                                        COMPRESS FOR ARCHIVE [LOW  | HIGH]

                                        AS

                                        SELECT * FROM Y;

                   Where   HIGH | LOW - Specifies the Extent of Compression.

                 - Archive Compression is optimised for Storage savings.

6). When a session places lock in the particular row HCC locks the whole Compression Unit Resulting in multiple row lock. Hence it may not be suitable for OLTP application where frequent update/insert occurs.

7). A Partition Table can have different type of HCC based on the Partition .For example. The latest partition can have COMPRESS BY QUERY whereas the older partition which hardly being queried can create using COMPRESS BY ARCHIVE.

8). The existing table can be converted to get benefit from HCC.However only newly added rows will be compressed .To compress the existing rows you need to do ALTER TABLE MOVE ;

9). You can check the characteristics of a row using DBMS_COMPRESSION package. To know more about the package click here

10)CPU Consumption for compression and decompression of the data is offset'd by less logical reads. Archive Compression consumes relatively more CPU compared to Warehouse Compression.

PART 2 :EXADATA V2 - Overview

1).Infiband

• Only Storage Network Supported by Exadata
• Provides Highest Possible performance available - 40Gb/Sec each direction.
• Uses ZDP Protocol (Zero Data Loss UDP Protocol) - Open Source Developed by Oracle Corp
• Can also be used as RAC interconnect.
• Each Infiband Link is able to carry the full data bandwidth of the entire Cell

2).Exadata comes to 2 DISK Type - SAS and SATA.

• SAS - For Maximum Performance
• SATA -For Maximum Capacity

3).Sun Database Machine doesn't include any software licence.

4).Sun Database Storage Server Details :

• RAM - 72 GBP(18 x4 )
• Disk Drives - 584 GB ( 4 x 2.5 146 GB)
• Oracle Enterprise Linux
• Oracle Database 11.2

5).A Full Rack Exadata can have 8 Database Servers,14 exadata cells,3 Infiband Switches

6).SQL Offloading is done by Database Server using iDB(Oracle Data Transfer Protocol)

7).CELLSRV component of Exadata storage server is used for Smart scan processing.CELLSERV Scans the blocks from storage and extract the relevant columns and send it back to the database server.

8).Database Server can break the SQL query and send the small portion of it to each exadata cells and upon retrieving the
results ,Database Server constructs the results and required rows are returned to clients.

9).We can use the below query to find the list of functions which can be offloaded to the Exadata.

Select * from v$sqlfn_metadata where offloadable='YES'

10).Exadata Storage server can perform Smart scans on encrypted tablespace and Columns.

11).With Performance of incremental Backup performance is significantly improved as the changes are tracked from individual block level rather than the large group of blocks.

PART 1 :EXADATA V2 - Overview

1).Based on SunFire X4275 Server.Processor is Intel Xeon E5440 Processor(64 Bit).

2).Exadata V2 requires 11G R2 (alteast 11.2.01)

3).Works with Single Instance /RAC

4).Exadata can offload some database processing to the Exadata Storage Server.This ability is known as Smart Scan.

For Example below operations can be offloaded to Exadata Storage Server.

• Table Scan Predicate Filters.
• Join Processing
• CPU Intensive Decompression and Decryption.
• File Creation Operations.

5).Exadata V2 Introduces HYBRID COLUMNAR COMPRESSION.Which mainly Reduces no of IO's

6).Uses Infiband network which has much throughput than conventional storage networks.

7).Exadata cell

• A Computer with CPU,Memory,Bus,disks,Host Channel Adapter.
• OS is Linux.
• Building Block for Storage.
• Houses Disk Storage and Exadata Storage Software Provided by Oracle.
• Add more cells for More Storage and More I/O Capacity.

8).The Database Server and Exadata Cells Communicate using Infiband Network.

9).Cell is mainly for storing Database Files.You can use it for storing Business Files using Database File System.

10).All the software's are preinstalled.Oracle Enterprise Linux is used as OS for Database server.