Re: Size estimation

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
------------------------------------------------------------

 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