Workload Qualification - Oracle Exadata Recipes: A Problem-Solution Approach - page 90

4-4. Performing a Hybrid Columnar Compression Fit Assessment
Problem
You wish to identify tables that may be suitable for Hybrid Columnar Compression on Exadata to understand the
extent of the benefit you may receive from both a disk capacity and potentially a performance perspective.
Solution
In this recipe, we will provide some guidelines to identify tables that are potential candidates for Hybrid Columnar
Compression on Exadata. Specifically, we will outline steps to measure your segment sizes and capture DML activity
on your candidate tables.
The first step is to understand your data and decide “how large is too large?” Different companies have different
tolerances for table or table partition sizes. In Listing 4-8, let's assume we want to find all of our segments consuming
more than 2% of the total size of the database:
Listing 4-8. lst04-08-hcc-candidate.sql
SQL> select owner,segment_name,
segment_type,partition_name,bytes/1024/1024/1024 gb,
round(100*(bytes/totbytes),2) pctofdb
from ( select owner,segment_name,segment_type,partition_name, bytes,(sum(bytes) over ())
totbytes
from dba_segments order by 4 asc)
where 100*(bytes/totbytes) > 2 --- where size > 2% the size of the database
order by 5 desc
/
Owner Segment SEGMENT_TYPE Partition Size (GB) % DB Size
------- --------------- ------------------ ---------- --------- ----------
MYFACT FACT_DETAIL TABLE PARTITION SYS_P488 206.35 7.19
MYDIM ITEM_LOC_DIM TABLE 185.69 6.47
MYDIM ITEMS_DIM TABLE 185.17 6.45
MYFACT FACT_DETAIL TABLE PARTITION SYS_P469 154.30 5.38
MYFACT FACT_DETAIL TABLE PARTITION SYS_P508 129.17 4.50
MYDIM CIL_INDX_UKY_01 INDEX PARTITION SYS_P41 92.99 3.24
MYFACT FACT_DETAIL TABLE PARTITION SYS_P445 89.16 3.11
... Output omitted for brevity
With this information, you can correlate the tables in the list above with the associated DML by looking at the
view DBA_HIST_SEG_STAT . DBA_HIST_SEG_STAT provides many segment-level statistics, including the number of
physical writes and block changes. Listing 4-9 displays the DML activity for potential HCC candidates.
Listing 4-9. lst04-09-hcc-candidate-segstat.sql
SQL> select seg.owner,seg.segment_name,
seg.segment_type,seg.partition_name,seg.bytes/1024/1024/1024 gb,
round(100*(seg.bytes/seg.totbytes),2) pctofdb,
nvl(sum(segstat.pwrites),0) pwrites, nvl(sum(segstat.blkchanges),0)
blkchanges