Following up a suggestion from Kerry Osborne that I show how I arrived at the observation I made in an earlier posting about the size of a compression unit, here’s a short note to show you what I did. It really isn’t rocket science (that’s just a quick nod to NASA and Curiosity – the latest Mars rover).
Step 1: you can access rows by rowid in Oracle, so what happens when you try to analyze rowids on Exadata for a table using HCC ? I created a table with the option “compress for archive high” and then ran the following query:
break on report compute sum of count(*) on report compute count of block_no on report select dbms_rowid.rowid_relative_fno(rowid) file_no, dbms_rowid.rowid_block_number(rowid) block_no, count(*) from t1 group by dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) order by dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) ;
This code simply counts the number of rows which appear to share the same file number and block number, and reports them in block address order. With a few simple sets of data, I found that the number of rows per “block” appeared to be around 32,000 – so I created an extremely compressible test case, to get the following results:
create table t1
nologging
compress for archive high
storage (initial 64M next 64M)
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1e4
)
select
lpad('x',10,'x') v1
from
generator v1,
generator v2
where
rownum <= 4 * 1048576
;
FILE_NO BLOCK_NO COUNT(*)
---------- ---------- ----------
5 3983906 32759
5 3983907 32759
5 3983908 32759
...
5 3984033 32759
5 3984034 1152
---------- ----------
count 129
sum 4194304
Note: the storage clause is an attempt to avoid any “starting conditions” in a tablespace declares as locally managed with system managed extent allocation – it (usually) skips the first few 64KB, 1MB and 8MB extent allocations.
As you can see, the best I could do was to cram 32,759 rows into a single block ! Using a slightly less extreme data set (see, for example, the code from my previous CU posting) I ran the same query to show that rather than every reporting rowids in every consecutive block I would see thousands of rows in a single block, followed by a gap of up to something like 32 blocks, then another block with thousands of rows. The obvious inference was that the reported block addresses were the starting blocks of a compression unit – so I dumped one. Here’s the type of thing I found:
data_block_dump,data header at 0x2b98bb454a7c
===============
tsiz: 0x1f80
hsiz: 0x1c
pbl: 0x2b98bb454a7c
76543210
flag=-0------
ntab=1
nrow=1
frre=-1
fsbo=0x1c
fseo=0x30
avsp=0x14
tosp=0x14
r0_9ir2=0x0
mec_kdbh9ir2=0x0
76543210
shcf_kdbh9ir2=----------
76543210
flag_9ir2=--R----- Archive compression: Y
fcls_9ir2[0]={ }
0x16:pti[0] nrow=1 offs=0
0x1a:pri[0] offs=0x30
It’s a normal table block (although the r0_9ir2 entry at line 15 shows that it’s subject to some form of column re-arranging to optimise compression – that’s a detail that arrived in 9.2) holding just one row (nrow = 1 at line 9)
block_row_dump: tab 0, row 0, @0x30 tl: 8016 fb: --H-F--N lb: 0x0 cc: 1 nrid: 0x0156ee23.0 col 0: [8004] Compression level: 04 (Archive High) Length of CU row: 8004 kdzhrh: ------PC CBLK: 33 Start Slot: 00 NUMP: 33 PNUM: 00 POFF: 7664 PRID: 0x0156ee23.0 PNUM: 01 POFF: 15680 PRID: 0x0156ee24.0 PNUM: 02 POFF: 23696 PRID: 0x0156ee25.0 PNUM: 03 POFF: 31712 PRID: 0x0156ee26.0 PNUM: 04 POFF: 39728 PRID: 0x0156ee27.0 PNUM: 05 POFF: 47744 PRID: 0x0156ee28.0 PNUM: 06 POFF: 55760 PRID: 0x0156ee29.0 PNUM: 07 POFF: 63776 PRID: 0x0156ee2a.0 PNUM: 08 POFF: 71792 PRID: 0x0156ee2b.0 PNUM: 09 POFF: 79808 PRID: 0x0156ee2c.0 PNUM: 10 POFF: 87824 PRID: 0x0156ee2d.0 PNUM: 11 POFF: 95840 PRID: 0x0156ee2e.0 PNUM: 12 POFF: 103856 PRID: 0x0156ee2f.0 PNUM: 13 POFF: 111872 PRID: 0x0156ee30.0 PNUM: 14 POFF: 119888 PRID: 0x0156ee31.0 PNUM: 15 POFF: 127904 PRID: 0x0156ee32.0 PNUM: 16 POFF: 135920 PRID: 0x0156ee33.0 PNUM: 17 POFF: 143936 PRID: 0x0156ee34.0 PNUM: 18 POFF: 151952 PRID: 0x0156ee35.0 PNUM: 19 POFF: 159968 PRID: 0x0156ee36.0 PNUM: 20 POFF: 167984 PRID: 0x0156ee37.0 PNUM: 21 POFF: 176000 PRID: 0x0156ee38.0 PNUM: 22 POFF: 184016 PRID: 0x0156ee39.0 PNUM: 23 POFF: 192032 PRID: 0x0156ee3a.0 PNUM: 24 POFF: 200048 PRID: 0x0156ee3b.0 PNUM: 25 POFF: 208064 PRID: 0x0156ee3c.0 PNUM: 26 POFF: 216080 PRID: 0x0156ee3d.0 PNUM: 27 POFF: 224096 PRID: 0x0156ee3e.0 PNUM: 28 POFF: 232112 PRID: 0x0156ee3f.0 PNUM: 29 POFF: 240128 PRID: 0x0156ee40.0 PNUM: 30 POFF: 248144 PRID: 0x0156ee41.0 PNUM: 31 POFF: 256160 PRID: 0x0156ee42.0 PNUM: 32 POFF: 264176 PRID: 0x0156ee43.0 CU header: CU version: 0 CU magic number: 0x4b445a30 CU checksum: 0x210a6a05 CU total length: 264972 CU flags: NC-U-CRD-OP ncols: 8 nrows: 17854 algo: 0 CU decomp length: 262686 len/value length: 2467640 row pieces per row: 1 num deleted rows: 0
But the next part of the block dump shows that the one row is a very funny row consisting of just one column (cc: 1 in line 3 is the column count). If you try to access this “row” by rowid in the normal way you won’t be able to retrieve it, instead will unpack the compression and give the first row from the unit.
Line 3 tells us that the total row length (tl) is 8,016 bytes, but line 4 tells us that the row has chained, and the next row-piece (nrid) is the zeroth row in block
0x0156ee23. Of the 8,016 bytes in the row-piece, the single column comprises 8,004 bytes.
We can see in line 6 that the compression level is “Archive High”.
In line 8 we see that there are 33 continuation blocks (CBLK), and this is followed by a list of the blocks (technically the rowid with block) in order. Notice that the rowid given by PNUM: 00 is consistent with the nrid that is reported in the normal “chained row” part of the row description. The POFF values (pointer offset ?) show the correlation between a row-piece and its starting byte position in the CU.
Line 46 shows us the total length of the compression unit; in this case it’s 264,972 bytes – approximately 259KB. So far most of the dumps I’ve done have show CUs to be a little less than 256KB, but I have seen a few larger ones – but never much more than 256KB.
Line 48 tells us that the underlying rows have 8 columns, and somewhere in the CU there has to be a set of pointers to the first byte for each compressed column. Line 49 tells us that this compression unit holds 17,854 bytes. Line 51 tells us that the data content of the CU (i.e. eliminating overheads) is 262,686 bytes, which has been compressed down from 2.4MB (a compression factor of roughly 9.4).
Line 50 is quite interesting – algo: 0 Is this short for “algorithm”, and does that suggest that Oracle can decide dynamically what compression algorithm it’s going to use based on the data it finds as it starts to build the compression unit ?
The final entry I’ve shown is the list of deleted rows – and there are none in this example. If there are any deleted rows in the CU (and updated rows are deleted from the CU and copied to a new location) they appear in a simple list of number which, in this example, would range from 1 to 17,854 (the number of rows in the CU).
DIGITAL JUICE
No comments:
Post a Comment
Thank's!