Hybrid Columnar Compression - Oracle Exadata Recipes: A Problem-Solution Approach - page 478

Solution
In this recipe, you will learn how to use a number of Exadata-specific system statistics and columns from V$SQL to
measure the performance benefits from HCC tables. Start by identifying a HCC table and an uncompressed table with
similar characteristics and sizes; in the following example, we will use the test tables created in Recipe 16-4:

SQL> select table_name,num_rows,blocks,
compression,compress_for
from dba_tables
where table_name in ('MYOBJ_UNCOMP','MYOBJ_COMPQH')
and owner='D14';

Table Rows Blocks Compressed Compressed For
------------------- ------------ ---------- --------------- --------------
MYOBJ_UNCOMP 20423000 289446 DISABLED
MYOBJ_COMPQH 20423000 15188 ENABLED QUERY HIGH
SQL>

Next, run a full table scan against each table and measure its statistics using the query in Listing 16-4. In our test
cases, we selected the MAX(OBJECT_NAME) from each of three tables, without a query predicate.

Listing 16-4. lst16-04-exahcc-mysess.sql

SQL> select stat.name,
sess.value value
from v$mystat sess,
v$statname stat
where stat.statistic# = sess.statistic#
and stat.name in ('physical read total bytes',
'cell IO uncompressed bytes',
'cell physical IO interconnect bytes')
and sess.value > 0
order by 1;

Table 16-2 provides the results of our full table scan tests.

Table 16-2. Query Test Results against Uncompressed and HCC Compressed Tables
Statistic

MYOBJ_UNCOMP MYOBJ_COMPQH MYOBJ_COMPAH MYOBJ_COMPQH

with Smart Scan

Compression Type Uncompressed Query High Archive High Query High

Query Response 1.75 seconds 4.04 seconds 3.06 seconds 1.87 seconds

physical read total bytes 2,364,825,600 123,101,184 84,205,568 123,559,936

cell physical
I/O interconnect bytes

541,298,056 123,101,184 84,205,568 407,969,576

cell I/O
uncompressed bytes

2,367,651,840 0 0 2,181,193,193

CPU used
by this session

145 289 1,009 175