Databases Reference
In-Depth Information
and re-inserted initially as uncompressed. When the newly formatted block becomes full, Oracle transitions the block
to a “block-compressed” block. In addition, DML against rows on HCC tables locks the entire compression unit, or CU.
The impact of migrated rows in the context of HCC tables is similar to the query impact for traditional
tables—extra I/Os are required to access the data when retrieved via a
SELECT
statement. Additionally, if many
updates occur, over time the percentage of compressed blocks will decrease and the segment's compression ratio
will decrease.
Perhaps of more significant importance is the locking mechanics when DML occurs on HCC rows. As
demonstrated in the solution of this recipe, Oracle locks the entire CU when updates are made to a row. Updates to
other rows in the blocks are forced to wait for any pending transactions to commit or roll back. This is certainly not
Oracle's normal locking behavior, in which locks are only held for individual rows. This, of course, means that extra
care must be taken when your application is designed to update HCC segments in order to avoid excessive
lock-related waits.
16-9. Decompression and the Performance Impact
Problem
You wish to measure the performance impact of decompressing data when selecting rows from an HCC segment.
Solution
In this recipe, you will learn how Oracle decompresses HCC data on Exadata, where the decompression takes place
depending on your query conditions, and measure the performance impact related to HCC decompression.
To demonstrate where Oracle decompresses data when accessing rows from HCC segments, we will conduct a
number of test cases and measure HCC-related statistics using the script in Listing 16-13. The tests were executed
on an Exadata Quarter Rack with a set of 20+ million row tables and a flushed database buffer cache between each
execution as summarized:
•
Test #1: Access approximately 1 million rows for a single column in a 20 million row table
compressed for Query High using Smart Scan
•
Test #2: Access approximately 1 million rows for a single column in a 20 million row table
compressed for Archive High using Smart Scan
•
Test #3: Access 1,000 rows for a single column in a 20 million row table compressed for Query
High using Smart Scan
•
Test #4: Access 1,000 rows for a single column in a 20 million row table compressed for Archive
High using Smart Scan
•
Test #5: Access approximately 1 million rows for a single column in a 20 million row table
compressed for Query High
without
smart scans
•
Test #6: Access approximately 1 million rows for a single column in a 20 million row table
compressed for Archive High
without
smart scans
Listing 16-13 provides the script used to collect the results, which are listed in Table
16-3
.
Search WWH ::
Custom Search