Well I turned in the HCC chapter on the Exadata book last week and of course as is usually the case, I immediately ran across something kind of cool on the topic I just finished writing about. (we still have several editing passes though, so I can add it later). Anyway, although I don’t have time to post much these days, I thought this one would be a quick little snippet. So here it is.
The Compression Advisor is part of the DBMS_COMPRESSION package. Specifically it is the GET_COMPRESSION_RATIO procedure. This procedure is worthy of a separate post but I won’t discuss it here except to say that as of 18.104.22.168 you can use it to test HCC compression ratios on non-Exadata platforms. That’s pretty cool, but what I wanted to tell you about is a handy little function in the same package called GET_COMPRESSION_TYPE. This function can tell you exactly what level of compression has been applied to a single row. This can come in handy for investigating the inner workings of HCC (or OLTP or BASIC compression for that matter).
As you probably already know, HCC is only applied to records loaded via direct path writes. Any updates cause rows to be migrated out of that storage format into blocks flagged for OLTP compression. Of course OLTP compression on a block only kicks in when a block is “full”. On top of this, altering a table to change it’s compression does not actually change the storage format of any existing records (unless you use the MOVE keyword). So you could load some data and then change the designation (say from QUERY LOW to QUERY HIGH). Rows that are inserted after the change will be stored in the new format (assuming the records are loaded via direct path writes of course). So why am I telling you all this. Well, because I ran across a statement in some Oracle documentation that said you can check to see what compression method a table is stored with by looking at the COMPRESS_FOR column in the DBA_TABLES view. This column does reveal what the table designation is. However, the setting actually only tells you how rows inserted in the future will be compressed. It tells you absolutely nothing about the way current rows are stored.
As for the mechanics, it appears that each row has a bitmask associated with it showing what compression format is being used. So I wrote a little script to give me what I want to see (check_row_comp.sql) using the DBMS_COMPRESSION.GET_COMPRESSION_TYPE function. Here’s an example of its use.
== Note this listing has been updated to fix the bitmask as suggested by Greg in the comments (I had it wrong initially) SYS@SANDBOX1> !cat check_row_comp.sql col old_rowid for a20 /* There is a bit mask that indicates level of compression 10000000 (1) = no compression 01000000 (2) = BASIC/OLTP 00100000 (4) = QUERY HIGH 00010000 (8) = QUERY LOW 00001000 (16) = ARCHIVE HIGH 00000100 (32) = ARCHIVE LOW */ select old_rowid(rowid) old_rowid, decode( DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( '&owner', '&table_name', '&rowid'), 1, 'No Compression', 2, 'Basic/OLTP Compression', 4, 'HCC Query High', 8, 'HCC Query Low', 16, 'HCC Archive High', 32, 'HCC Archive Low', 'Unknown Compression Level') compression_type from dual; SYS@SANDBOX1> select rowid from kso.skew_hcc3 where pk_col = 444444; ROWID ------------------ AAAWbXAAIAAGAfiBdz 1 row selected. SYS@SANDBOX1> @check_row_comp Enter value for owner: KSO Enter value for table_name: SKEW_HCC3 Enter value for rowid: AAAWbXAAIAAGAfiBdz OLD_ROWID COMPRESSION_TYPE -------------------- ------------------------- 1.929.0 HCC Archive Low 1 row selected. SYS@SANDBOX1> update kso.skew_hcc3 set col1 = col1*2 where pk_col=444444; 1 row updated. SYS@SANDBOX1> commit; Commit complete. SYS@SANDBOX1> select rowid, old_rowid(rowid) old_rowid from kso.skew_hcc3 where pk_col = 44444; ROWID OLD_ROWID ------------------ -------------------- AAAWbXAAIAAF7aUAAA 8.1554068.0 1 row selected. SYS@SANDBOX1> -- row migrated to file 8 SYS@SANDBOX1> SYS@SANDBOX1> @check_row_comp Enter value for owner: KSO Enter value for table_name: SKEW_HCC3 Enter value for rowid: AAAWbXAAIAAF7aUAAA OLD_ROWID COMPRESSION_TYPE -------------------- ------------------------- 1.929.0 No Compression 1 row selected.
I did a previous post (Proof That Whole CU’s Are Not Decompressed) where I showed row migration to a block flagged for OLTP compression, but of course the OLTP compression would not be applied until the block was sufficiently full. As you can see in the example, the function showed that the record was not actually compressed, even though it had been migrated to a block flagged for OLTP compression.
So the GET_COMPRESSION_TYPE function is handy for showing us how an individual record is actually stored. Of course you can see this by dumping blocks as well, but I think this function is much easier than looking at block dumps. You could also write a script to sample some percentage of the rows in a table to get a feel for how well compressed the rows are, but I will leave that as an exercise for the reader. (please let me know if you undertake that task as it is something I will eventually want to do as well) ;)