Hybrid Columnar Compression: Querying with Predicates and Decompression

In our October 11, 2011 Webinar on the topic of Exadata Hybrid Columnar Compression, we presented several real-world examples of HCC in action.  In one of these, when testing query performance against HCC tables, we got what we thought was an unexpected result when applying a query predicate in a query against a table compressed for query high. The actual details of the test are below:
To summarize results:


Compression Type Table Name Query Time (seconds)
Uncompressed MYOBJ 3.93
Compressed for OLTP MYOBJ_OLTPCOMP 3.78
Compressed for Query Low MYOBJ_QUERYLOW 2.81
Compressed for Query High MYOBJ_QUERYHIGH 2.75
Compressed for Archive Low MYOBJ_ARCHIVELOW 2.89
Compressed for Archive High MYOBJ_ARCHIVEHIGH 9.10
The outlier in our test case in the query against the table that is archive high HCC compressed.   In this blog post I’m going to try to figure out why the discrepancy exists.
Table Sizes
The first thing I want to test is to ensure that the table sizes are as expected.  During the course of preparing for the webinar I did some additional direct path load testing, single-row inserts, and updates against it, and so forth. So the first thing I want to validate is whether, after all these operations, the size of my object increased/decreased enough to warrant the time difference, and if so, if my test cases were done at the wrong time:
As you can see above, MYOBJ_ARCHIVEHIGH does in fact have more blocks and is larger than some of the other tables that were compressed with what should have been a lower compression ration.  However, the size difference alone shouldn’t alone explain the difference in run-time.
Just to re-baseline the test results, let’s re-test the same queries and validate that the same condition presents itself.  First, the query against the uncompressed table:
Next, the query against the OLTP-compressed table:
Next, the query against the table compressed for Query Low:
Next, the query against the table compressed for Query High:
Next, the query against the table compressed for Archive Low:
Next, the query against the table compressed for Archive High:
Summary table:
Statistic Normal OLTP Query Low Query High Archive Low Archive High
Query Time 4.74 4.31 3.46 2.92 3.00 10.18
CG 1874959 1568094 1285813 918585 926170 1081234
PR 1874950 1568030 1285798 918571 926156 1081171
Cell IO interconnect bytes 10084264 299302728 35506016 18365728 10082560 13226704
Logical reads 1875109 1568240 1285963 918731 926316 1081380
CPU used 45 90 55 44 40 66
Flash cache read hits 9 25 25 24 24 31
SS Efficiency 99.35% 97.67% 99.66% 9976% 99.87% 99.85%
So indeed, the test showed the same behavior as what we saw during the webinar on 10/10/2011.  Some interesting things to note about the statistics above:
  • The query against MYOBJ_ARCHIVEHIGH was more than double the query time for any of the other queries.
  • More CPU work was required in our query against MYOBJ_QUERYHIGH, relative to all the other queries against HCC tables, but it was less than required than against the OLTP-compressed table.  Further, the difference in time was small compared to the overall difference in elapsed query time.
  • Logical IO, physical IO, etc. numbers were higher against the table compressed for archive high, but this could be attributed to the fact that the table is actually larger in size and returned more data over the interconnect.
Build a New Table and Re-Test
We still have a bit of a mystery on our hands, so let’s build a new table compressed for archive high and see if we get the same query times:
As we can see, we get roughly the same results.
Remove Predicate 
Let’s try removing the where clause in our test case:
Now we’re getting somewhere!  It seems that adding the query predicate actually slowed it down, which seems counterintuitive, as it reduced the volume of data being transmitted over the interconnect.
Let’s try an equality join in the predicate:
This shows that the performance actually got worse.  Now let’s limit our rows returned/scanned by putting some rownum criteria in the predicate as well:
Clearly, we see an improvement.  But let’s try the same with varying degrees of rows returned and see what we can learn, and keep in mind there are 2,059 rows that match our OBJECT_NAME criteria:
Row limiting criteria Elapsed Time (Seconds)
“rownum < 2” 00.02
“rownum < 100” 01.92
“rownum < 200” 02.37
“rownum < 500” 04.03
“rownum < 1000” 06.13
“rownum < 1500” 08.76
“rownum < 2000” 10.33
“rownum < 2059” 10.38
From the table above, we can see that the query time gradually increases as more rows are processed and there’s not a discernable point at which “x” number of additional rows causes a non-linear increase in response time.
Check Wait Statistics
From the above tests, we know that adding the predicate against the HCC “archive high” compressed table runs longer, so in this section I’ll examine waits statistics by enabling 10046 trace on two different sessions, one for MYOBJ_ARCHIVEHIGH2 and one for MYOBJ_ARCHIVELOW.
In the above output, we can see that the majority of the time difference is that the average wait for “cell smart table scan” is about 3.5 times longer per wait for the HCC archive high table as compared to the HCC archive low table.  From this, we can infer that the time difference doesn’t have much to do with anything on the compute node (database instance node), which would also rule out the potential for incurring a decompression penalty on the compute node side.
Sanity Check: When Does Oracle Decompress HCC Data?
Let’s stop the trial-and-error for a bit and take a step back.  We can assume from the testing above that the increase in performance very likely has to do with decompression overhead on the storage server, so when does Oracle decompress data for HCC tables?
From http://www.oracle.com/technetwork/database/features/availability/311358-…, a paper published by Oracle, the following statement is made:
The key statement is the second bullet: “only the columns and rows being returned to the client are decompressed (in memory )”.  Using this logic, it may stand to reason that the specific columns provided in the predicate could dictate how much decompression work will need to be done by the storage server before returning rows to the compute server.  And not just the specific columns, but also the degree of columnar compression provided by HCC.  Let’s run some tests against MYOBJ_ARCHIVEHIGH (I dropped the initial version of this table and renamed MYOBJ_ARCHIVEHIGH2 to MYOBJ_ARCHIVEHIGH, for frame of reference).
First, let’s check the number of distinct values for each column in our table:
Let’s also show the table/column description so we can see the data types:
Using the above, we’ll run a test query with each column above and measure response times.  For each of these, we’ll select all rows from the table by using a guaranteed no-match predicate condition:

Column Query Time (Seconds)
STATUS 02.55
OWNER 02.79
BIGCOL 04:57.21
The previous section shed light on the “mystery” and in the end, it’s not really a mystery at all but simply related to how HCC decompression works on Exadata:
  • Exadata decompressed data on the storage server when queries are Smart Scan eligible.
  • Decompression incurs a CPU penalty and can impact query response time.
  • Decompression logic is applied, on the storage server, to columns in the select list and query predicate.
  • Decompressing tables compressed for “archive high” is more time consuming than other compression flavors due to the manner in which HCC compression algorithms are applied.
  • If no query predicate exists, Smart Scan will return all rows and in the case of our tests, which did summary functions (i.e., select count(1) …), very little data was returned to the compute node.
  • Further, if no predicate exists, no storage server decompression is required.  The only decompression happens on the column in the SELECT list.
  • More CPU is consumed on the storage server to decompress columns in the predicate that have lower compression ratios.  In other words, the more distinct values, the more work is done during decompression and the longer the queries will run.
  • It’s also evident that decompression is more efficient for numeric data types than date/time data types, which are in turn more efficient than VARCHAR2 data types.  So even if a numeric column has more distinct values and worse overall compression, the CPU penalty for decompression will be less than a VARCHAR2 column with fewer distinct values.
  • Think about the impact to your application – if you have queries with predicates (and who doesn’t) and you want to archive compress your tables, be wary of increased CPU utilization and longer query times.  So it’s just like the documentation says – archive compression is designed for tables or partitions that are infrequently accessed!