Re: Size estimation

Mikhail,

I don't know - I think it was just a glitch in mental arithmetic. I didn't use bbed - I didn't realise it could still be trusted with anything newer than 10.x,

create table t1(n1 number);
insert into t1 values(0);
commit;

Flush the buffer cache, dump the block, check avsp and subtract 11 (minimum row allocation).
How I got to 8066 from 0x1f7b + 11 I don't know.

I'm very surprised that I forgot the row overhead, though.

Regards
Jonathan Lewis

On Mon, 22 Feb 2021 at 13:32, Mikhail Velikikh <mvelikikh_at_gmail.com> wrote:

> Hi Jonathan,
>
> Then you have to allow for block size, which means 8,066 bytes available
>> from an 8KB block size with pctfree 0, initrans 2 (default) and ASSM
>>
>
> Can you please explain why it is 8,066 bytes? Based on my tests, it is
> 8,070 bytes in 19.9.
>
> I did:
> create tablespace ts1
> datafile '/tmp/1.dbf' size 10m
> extent management local
> segment space management auto;
>
> create table test(c1 varchar2(10))
> pctfree 0
> tablespace ts1;
>
> insert into test values ('test123');
> commit;
>
> alter system checkpoint;
>
> select dbms_rowid.rowid_relative_fno(rowid),
> dbms_rowid.rowid_block_number(rowid) from test;
>
> The BBED dump:
>
> BBED> map /v
> File: /tmp/1.dbf (37)
> Block: 135 Dba:0x09400087
> ------------------------------------------------------------
> KTB Data Block (Table/Cluster)
>
> struct kcbh, 20 bytes _at_0
> ub1 type_kcbh _at_0
> ub1 frmt_kcbh _at_1
> ub2 wrp2_kcbh _at_2
> ub4 rdba_kcbh _at_4
> ub4 bas_kcbh _at_8
> ub2 wrp_kcbh _at_12
> ub1 seq_kcbh _at_14
> ub1 flg_kcbh _at_15
> ub2 chkval_kcbh _at_16
> ub2 spare3_kcbh _at_18
>
> struct ktbbh, 72 bytes _at_20
> ub1 ktbbhtyp _at_20
> union ktbbhsid, 4 bytes _at_24
> struct ktbbhcsc, 8 bytes _at_28
> sb2 ktbbhict _at_36
> ub1 ktbbhflg _at_38
> ub1 ktbbhfsl _at_39
> ub4 ktbbhfnx _at_40
> struct ktbbhitl[2], 48 bytes _at_44
>
> struct kdbh, 14 bytes _at_100
> ub1 kdbhflag _at_100
> sb1 kdbhntab _at_101
> sb2 kdbhnrow _at_102
> sb2 kdbhfrre _at_104
> sb2 kdbhfsbo _at_106
> sb2 kdbhfseo _at_108
> sb2 kdbhavsp _at_110
> sb2 kdbhtosp _at_112
>
> struct kdbt[1], 4 bytes _at_114
> sb2 kdbtoffs _at_114
> sb2 kdbtnrow _at_116
>
> sb2 kdbr[1] _at_118
>
> ub1 freespace[*8057*] _at_120
>
> ub1 rowdata[11] _at_8177
>
> ub4 tailchk _at_8188
>
> We need to add: kcbh(20 bytes) + ktbbh(72 bytes) + kdbh(14 bytes) + kdbt(4
> bytes) + tailchk (4 bytes)=114
> kdbh starts at offset 100 rather than 92=(72+20), so that we need to add
> extra 8 bytes to get 122=(114+8)
> Thit leaves us with 8,070 bytes (8192-122).
>
> I inserted one row (test123) that is 7 bytes + 1 byte (length) + 3 bytes
> (flag, lock, cc=col count)=11 bytes in total.
> It has one slot in the row directory - 2 bytes. Overall, it consumes 13
> bytes (rowdata + row directory).
> Thus, the freespace became 8,070-13=*8,057* - it matches the freespace
> precisely.
>
> If I insert another 7-byte row, the freespace becomes 8,044 bytes as
> expected:
> [image: image.png]
>
>
> Max rows = trunc(8066 / 471) = 17 rows per block,
>>
>
> I think we should add 5 bytes to each row.
> Each row consumes one slot in the row directory (2 bytes) + 3 bytes (flag,
> lock, cc bytes).
> Having added 5 bytes, we will get 476 bytes in total, which results in 16
> rows per block. It matches the result of DBMS_SPACE_ADMIN.CREATE_TABLE_COST
> (
> https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_space.htm#ARPLS68103),
> however, it does not take ASSM into account.
>
> Best regards,
> Mikhail Velikikh
>
>
>
> On Sun, 21 Feb 2021 at 18:29, Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> The number(15,0) will take at most 9 bytes
>> The number(13,0) will take at most 8 bytes each
>> So your estimate should be 496 - 13 - 14 - 14 = 455
>> Then you need to add one byte per column to get 471.
>>
>> Then you have to allow for block size, which means 8,066 bytes available
>> from an 8KB block size with pctfree 0, initrans 2 (default) and ASSM
>> Max rows = trunc(8066 / 471) = 17 rows per block,
>> At 100M rows that's 5,882,353 data blocks.
>>
>> If you create the table using a large extent size *8MB min) you get 1
>> bitmap block for every 128 blocks allocated so your block requirement goes
>> up by 128/127,
>> so a total of 5,928,671 blocks. Round that up to the nearest 64MB
>> (assumed extent size) - 5,931,008 blocks = 45.25GB.
>>
>> So even with several errors on the way you got pretty close to the
>> "right" answer.
>>
>> Realistically, though, you're unlikely to fill all those 40 and 50
>> character columns, and unless you're very carefull with setting pctfree
>> (and maybe playing around with the Hakan factor) you're probably going to
>> run into problems with getting too many rows into a block on the initial
>> insert and running into problems with row migration.
>>
>> There's also the question of multi-byte character sets - are you
>> thinking of your varchar2(N) declarations N bytes (the default assumption)
>> or N characters (which, depending on character set could mean up to 4N
>> bytes).
>>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Sun, 21 Feb 2021 at 17:03, Pap <oracle.developer35_at_gmail.com> wrote:
>>
>>>
>>> Hi Listers, It's Oracle RDBMS version 11.2.0.4 exadata. We have a table
>>> with structure as below which is going to be created as part of a new
>>> project. And we want to predict the storage/space requirement for this. It
>>> may not be the exact size but at least we want to estimate the AVG and
>>> MAXIMUM space requirement for the table , if all the columns filled with
>>> not null values with max column length being occupied/filled for each of
>>> the columns.
>>>
>>> So to estimate the maximum space requirement , is it correct to Just
>>> add the length of the column as it is in bytes and multiply it with the
>>> projected number of rows. Something as below.
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l




image/png attachment: image.png