EHCC Mechanics – Proof that whole CU’s are not decompressed
I saw an interesting post recently where Greg Rahn talked about HCC mechanics. He claimed that an update to a record stored in HCC format did not require decompressing the whole Compression Unit (CU) which consist of several Oracle blocks. I’m assuming by this he meant that all the records contained in the CU did not get written back to storage in a non-HCC format due to a single record being updated. Greg then showed an example proving row migration occurred for an updated record. He didn’t show that the other records had not been decompressed though. So since I was already working on an HCC chapter for the upcoming Apress Exadata book, I thought I would take time off from the book writing to post this (hopefully the editors will forgive me).
Here’s the recipe: Basically we’ll update a single row, see that its rowid has changed, veify that we can still get to the record via its original rowid, and check to see if the TABLE FETCH CONTINUED ROW statistic gets updated when we we access the row via its original rowid, thus proving basic row migration (this is what Greg has already shown). Then we’ll look at block dumps for the original and new block to see what’s there.
-bash-3.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 14 14:16:20 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@SANDBOX1> select rowid, old_rowid(rowid) location from kso.skew_hcc3 where pk_col=16367;
ROWID LOCATION
------------------ --------------------
AAATCBAAIAAF8uSFc9 8.1559442.22333
SYS@SANDBOX1> -- so my row is in file 8, block 1559442, slot 22333
SYS@SANDBOX1>
SYS@SANDBOX1> update kso.skew_hcc3 set col1=col1 where pk_col=16367;
1 row updated.
SYS@SANDBOX1> select rowid, old_rowid(rowid) location from kso.skew_hcc3 where pk_col=16367;
ROWID LOCATION
------------------ --------------------
AAATCBAAHAAMGMMAAA 7.3171084.0
SYS@SANDBOX1> -- Ha! The rowid has changed – the row moved to file 7, block 3171084, slot 0
SYS@SANDBOX1>
SYS@SANDBOX1> -- Let's see if we can still get to it via the original rowid
SYS@SANDBOX1>
SYS@SANDBOX1> select pk_col from kso.skew_hcc3 where rowid = 'AAATCBAAIAAF8uSFc9';
PK_COL
----------
16367
SYS@SANDBOX1> -- Yes we can! – can we use the new rowid?
SYS@SANDBOX1>
SYS@SANDBOX1> select pk_col from kso.skew_hcc3 where rowid = 'AAATCBAAHAAMGMMAAA';
PK_COL
----------
16367
SYS@SANDBOX1> -- That works too! – It’s a migrated Row!
SYS@SANDBOX1> -- Let’s verify with “continued row” stat
SYS@SANDBOX1>
SYS@SANDBOX1> @mystats
Enter value for name: table fetch continued row
NAME VALUE
---------------------------------------------------------------------- ---------------
table fetch continued row 2947
SYS@SANDBOX1> -- select via the original rowid
SYS@SANDBOX1>
SYS@SANDBOX1> select pk_col from kso.skew_hcc3 where rowid = 'AAATCBAAIAAF8uSFc9';
PK_COL
----------
16367
SYS@SANDBOX1> @mystats
Enter value for name: table fetch continued row
NAME VALUE
---------------------------------------------------------------------- ---------------
table fetch continued row 2948
SYS@SANDBOX1> -- Stat is incremented – so definitely a migrated row! |
So the row has definitely been migrated. Now let’s verify that the migrated row is not compressed. We can do this by dumping the block where the newly migrated record resides.
SYS@SANDBOX1> !cat dump_block.sql @find_trace alter system dump datafile &fileno block &blockno; SYS@SANDBOX1> @dump_block TRACEFILE_NAME ------------------------------------------------------------------------------------ /u01/app/oracle/diag/rdbms/sandbox/SANDBOX1/trace/SANDBOX1_ora_5191.trc Enter value for fileno: 7 Enter value for blockno: 3171084 System altered. |
Now let’s look at the trace file produced in the trace directory. Here is an excerpt from the block dump.
Block header dump: 0x01f0630c
Object id on Block? Y
seg/obj: 0x13081 csc: 0x01.1e0574d4 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1f06300 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x002f.013.00000004 0x00eec383.01f2.44 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01f0630c
data_block_dump,data header at 0x2b849c81307c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x2b849c81307c
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f60
avsp=0x1f4c
tosp=0x1f4c
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f60
block_row_dump:
tab 0, row 0, @0x1f60
tl: 32 fb: --H-FL-- lb: 0x1 cc: 5
col 0: [ 4] c3 02 40 44
col 1: [ 2] c1 02
col 2: [10] 61 73 64 64 73 61 64 61 73 64
col 3: [ 7] 78 6a 07 15 15 0b 32
col 4: [ 1] 59
end_of_block_dump |
Notice that there is only one row in the block (nrow=1). Also notice that the object_id is included in the block (in hex format). It is labeled “seg/obj:”. The table has 5 columns. The values are displayed – also in hex format. Just to verify that we have the right block and row we can translate the object_id and the value of the first column as follows:
SYS@SANDBOX1> !cat obj_by_hex.sql
col object_name for a30
select owner, object_name, object_type
from dba_objects
where object_id = to_number(replace('&hex_value','0x',''),'XXXXXX');
SYS@SANDBOX1> @obj_by_hex
Enter value for hex_value: 0x13081
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
KSO SKEW_HCC3 TABLE
SYS@SANDBOX1> desc kso.skew_hcc3
Name Null? Type
----------------------------- -------- --------------------
PK_COL NUMBER
COL1 NUMBER
COL2 VARCHAR2(30)
COL3 DATE
COL4 VARCHAR2(1)
SYS@SANDBOX1> !cat display_raw.sql
col value for a50
select display_raw(replace('&string',' ',''),nvl('&TYPE','VARCHAR2')) Value from dual
/
SYS@SANDBOX1> @display_raw
Enter value for string: c3 02 40 44
Enter value for type: NUMBER
VALUE
--------------------------------------------------
16367 |
As you can see, this is the record that we updated earlier in the SKEW_HCC3 table. Note: display_raw.sql depends on a little function called display_raw() which coincidentally I got from Greg Rahn. Here’s a script to create the function: create_display_raw.sql
Now for a quick look back at the original block (note that in my testing I updated another row in this same block):
=============== tab 0, row 1, @0x32 tl: 5086 fb: --H-F--N lb: 0x3 cc: 1 nrid: 0x0217cb93.0 col 0: [5074] Compression level: 03 (Archive Low) Length of CU row: 5074 kdzhrh: ------PC CBLK: 1 Start Slot: 00 NUMP: 01 PNUM: 00 POFF: 5054 PRID: 0x0217cb93.0 CU header: CU version: 0 CU magic number: 0x4b445a30 CU checksum: 0x982dec03 CU total length: 11403 CU flags: NC-U-CRD-OP ncols: 5 nrows: 32759 algo: 0 CU decomp length: 7266 len/value length: 945436 row pieces per row: 1 num deleted rows: 2 deleted rows: 22333, 30848, START_CU: 00 00 13 d2 1f 01 00 00 00 01 00 00 13 be 02 17 cb 93 00 00 00 4b 44 5a 30 03 ec 2d 98 00 00 2c 8b eb 06 00 05 7f f7 00 0e 6d 1c 01 00 02 00 00 00 00 |
So this little except shows that this is an HCC compressed block (Compression level: 03 (Archive Low) and many CU references). The nrows line shows us that the block contains 32759 rows. It also shows that 2 rows have been deleted from the block (num deleted rows). Notice that one of the deleted rows is the one in slot 22333 (sound familiar). If you’ll look back at the original rowid in the old format (fileno.blockno.slot) you’ll see that it is the row we updated. It was “deleted” from this block when it was migrated to the new block. Of course there is still a pointer left behind.
SYS@SANDBOX1> select old_rowid('AAATCBAAIAAF8uSFc9') location from dual;
LOCATION
-----------------------------------------
8.1559442.22333
SYS@SANDBOX1> select old_rowid(rowid) location, a.* from kso.skew_hcc3 a where rowid = 'AAATCBAAIAAF8uSFc9';
LOCATION PK_COL COL1 COL2 COL3 C
-------------------- ---------- ---------- ------------------------------ --------- -
7.3171084.0 16367 1 asddsadasd 21-JUL-06 Y |
It’s hard to prove a negative, but it does not appear that any records are decompressed other than those that are actually updated. The other rows in the block appear to remain in HCC format.
“Greg then showed an example proving row migration occurred for an updated record. He didn’t show that the other records had not been decompressed though”.
Actually I did (indirectly). If we UPDATE a single EHCC row we get exactly 1 new rowid. If it were true that all the rows in the CU were changed when any one was modified, there would be more than 1 new rowid (1 for every row in the CU), which there is not. Nonetheless, your example provides further evidence of said behavior.
Hi Kerry,
Patiently waiting the publication of EOE (Expert Oracle Exadata).
Feb, Mar, Apr ?
kind regards
Greg – my bad, I didn’t read the SQL statement closely enough to realize what you were actually doing.
Stephen – I think May or June is more like it.
Kerry
[...] Oracle Whitepapers / Presentations « EHCC Mechanics – Proof that whole CU’s are not decompressed [...]