Kerry Osborne's Oracle Blog

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.