Re: Size estimation

Thanks much Jonathan. Yes the estimation for index size which i did by creating 100K rows manually was by summing up the bytes columns in dba_segments.

I will go through the blog post again as you suggested to see what is the difference between size from dba_segments vs using dbms_space.

On Mon, Feb 22, 2021 at 9:12 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> A couple of points to watch out for - again related to a small model for a
> big object.
> Are you counting leaf blocks in the index to get 4MB or looking at the
> size of the segment ?
>
> At a small scale there could be a big difference - a typical "create
> index" in an ASSM tablespace might start with a few 64KB extents and end up
> with a few 1MB extents, so you could have an index of just over 3MB that
> has allocated 4MB of space. (Unless you're on a very old version of Oracle
> the "explain plan" calculation will allow for the tablespace the index is
> going to be in in exactly the same way:
> https://jonathanlewis.wordpress.com/2009/05/22/index-size/ ).
>
> There is an opposite problem to consider. If your primary key index is
> sequence or time based then it could (in principle) run at 100% efficiency,
> i.e. leaving no free space in any blocks. In many cases, though, because
> rows that are generated with sequence numbers or time stamps are inserted
> slightly out of order, you can easily end up with lots of leaf blocks doing
> 50/50 splits; it also possible (in modern versions of Oracle) to create the
> index using scalable sequence numbers which pretty much guarantee that the
> index will have most of it's leaf blocks with 50% free space (the same is
> true for various manual tricks that people use to avoid contention on the
> "highest value" leaf block). There's a further problem that an odd coding
> feature of index leaf block splits means you could end up with far more ITL
> entries per leaf block that you need - so in the worst case (in older
> versions of Oracle) you could be running at 75% free space; in newer
> versions the problem hasn't gone away completely but the wastage could
> still be in excess of 56%
>
> Bottom line - you need to think a little carefully about how you define
> the index to the "explain plan" feature when you use it to estimate the
> index size. INITRANS, PCTFREE and TABLESPACE could make a big difference
> to what your index is really going to look like.
>
> NB If you read the article you'll note that I didn't need to create a
> table with 100M rows to get an estimate of the index on 100M rows.
>
> Regards
> Jonathan Lewis
>
>
>
> On Mon, 22 Feb 2021 at 08:21, Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Yes this way through explain plan also the size coming as ~4MB, same as I
>> have manually created for 100K rows.
>>
>> Regards
>> Pap
>>
>> On Mon, Feb 22, 2021 at 1:38 PM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>>
>>> if you do an explain plan on an "create index " statement you get a size
>>> estimate (Uncompressed).
>>>
>>> Regards
>>>
>>> Lothar
>>>
>>> Am 22.02.2021 um 01:48 schrieb Jack van Zanen:
>>>
>>> Or
>>>
>>>
>>> Simply create the table and indexes in a test environment and add 100K
>>> dummy records, record the size and multiply to scale.
>>> no need for maths and also fills indexes so you will know the sizes for
>>> those as well :-)
>>>
>>>
>>> Jack van Zanen
>>>
>>>
>>> -------------------------
>>> This e-mail and any attachments may contain confidential material for
>>> the sole use of the intended recipient. If you are not the intended
>>> recipient, please be aware that any disclosure, copying, distribution or
>>> use of this e-mail or any attachment is prohibited. If you have received
>>> this e-mail in error, please contact the sender and delete all copies.
>>> Thank you for your cooperation
>>>
>>>
>>> On Mon, Feb 22, 2021 at 9:35 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:
>>>
>>>> What JL wrote, and you did only ask about the size for the table.
>>>>
>>>>
>>>>
>>>> BUT, since you marked a primary key that is almost certainly supported
>>>> by an index and you may have additional indexes, so you’ll need to tack
>>>> space for indexes on to get total storage requirements.
>>>>
>>>>
>>>>
>>>> mwf
>>>>
>>>>
>>>>
>>>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jonathan Lewis
>>>> *Sent:* Sunday, February 21, 2021 1:29 PM
>>>> *To:* Oracle L
>>>> *Subject:* Re: Size estimation
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> 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