Re: sizing indexes, does anyone have an accurate script or algorithm?

From: <mzawadzki_at_starnet.lenfest.com>
Date: 1998/04/01
Message-ID: <6ftv6p$jei$2@news1.fast.net>

Appendix A of the Oracle Server Administrators Guide

oradba <oradba_at_erols.com> wrote:

>--------------085240D5E031C4E2C69C38FF
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit

>In terms of sizing a database you have two ways to go depening on your
>priorities.

>option 1 ) Priority is to get the job done quickly, easily and correctly.
>In this case you load a
> representative sample of the data, say 10%. You look to
>see how much space it
> used. Multiply by ten and that how much you need right
>now. If its a production
> system and the data is expected to double over a years
>time then you double the
> number , maybe triple it if you have space and want it
>set up once and for all.

>option 2) Priority is to impress management. Find sone convoluted formula
>and spend a great
> deal of tiime figuring out arcane data structure usage and
>have at least a 50%
> chance of getting it wrong . Spew lots of terminology to
>impress the unknowledgable.
> They will LOVE YOU.

>I'm serious.

>Nick Borrell wrote:

>> Does anyone have an index sizing script for Oracle8 (or 7) which they
>> are happy with, and willing to share? I really find it hard to believe
>> that there isn't an abundance of these scripts. Wouldn't it be logical
>> for Oracle to distribute sizing tools with OEM?
>>
>> I've spent quite some time going thru the Oracle8 documentation and
>> various other resources trying to accurately size or estimate the space
>> requirements for primary keys and unique indexes. The estimates are
>> generally well below the actual number of consumed blocks shown in
>> dba_segments.
>>
>> To test my scripts I've tended to create simple tables, with 500 or 1000
>> rows. Then I create a unique index, with an initial and next extent of
>> one block. Unfortunately, the results aren't very inspiring. For
>> example, if my estimate says I will need 4 blocks for the index, I have
>> found that it will actually consume 7 blocks. Yesterday I created an
>> index that was estimated to need 8 blocks, and actually consumed 18.
>>
>> The only immediate way I can see to improve things (immediately) is to
>> increase the overhead for branches from 10% to more like 80%, but that
>> would be ridiculous wouldn't it?
>>
>> I've repeatedly asked support for either a script or a better algorithm,
>> and got no where. (Although, one support person did tell me that I'd be
>> surprised to know how many people keep asking them about this.) I'm
>> still optimistic that they have the script I want, but that for some
>> reason I haven't given them exactly the right keyword to find it in
>> their database.
>>
>> So, can anyone provide me with a script (or TAR number to get one), that
>> is satisfactorily accurate? And how about sizing tables?
>>
>> Thanks in advance for any help,
>>
>> Nick Borrell
>> DBA/Developer
>> New Zealand Police
>> +64 237 2811 x5459

>--
>Only interested in San Francisco Bay area job opportunities

>--------------085240D5E031C4E2C69C38FF
>Content-Type: text/html; charset=us-ascii
>Content-Transfer-Encoding: 7bit

><HTML>
>In terms of sizing a database you have two ways to go depening on your
>priorities.

>option 1 ) Priority is to get the job done quickly, easily and correctly. 
>In this case you load a
>                 
>representative sample of the data, say 10%.  You look to see how 
>much space it
>                 
>used.   Multiply by ten and that how much you need right now.
>If its a production
>                 
>system and the data is expected to double over a years time then you double
>the
>                 
>number , maybe triple it if you have space and  want it set up once
>and for all.

>option 2) Priority is to impress management.  Find sone convoluted
>formula and spend a great
>                 
>deal of tiime figuring out arcane data structure usage and have at least
>a 50%
>                 
>chance of getting it wrong . Spew lots of terminology to impress the  
>unknowledgable.
>                  
>They will LOVE YOU.

>I'm serious.
>  
>  
>  
>  
>  
>  
>  
>  
>  
>  

>Nick Borrell wrote:
><BLOCKQUOTE TYPE=CITE>Does anyone have an index sizing script for Oracle8
>(or 7) which they
> are happy with, and willing to share? I really find it hard to believe
> that there isn't an abundance of these scripts. Wouldn't it be logical
> for Oracle to distribute sizing tools with OEM?

>I've spent quite some time going thru the Oracle8 documentation and
> various other resources trying to accurately size or estimate the space
> requirements for primary keys and unique indexes. The estimates are
> generally well below the actual number of consumed blocks shown in
> dba_segments.

>To test my scripts I've tended to create simple tables, with 500 or
>1000
> rows. Then I create a unique index, with an initial and next extent
>of
> one block. Unfortunately, the results aren't very inspiring. For
> example, if my estimate says I will need 4 blocks for the index, I
>have
> found that it will actually consume 7 blocks. Yesterday I created an
> index that was estimated to need 8 blocks, and actually consumed 18.

>The only immediate way I can see to improve things (immediately) 
>is to
> increase the overhead for branches from 10% to more like 80%, but that
> would be ridiculous wouldn't it?

>I've repeatedly asked support for either a script or a better algorithm,
> and got no where. (Although, one support person did tell me that I'd
>be
> surprised to know how many people keep asking them about this.) I'm
> still optimistic that they have the script I want, but that for some
> reason I haven't given them exactly the right keyword to find it in
> their database.

>So, can anyone provide me with a script (or TAR number to get one),
>that
> is satisfactorily accurate? And how about sizing tables?

>Thanks in advance for any help,

>Nick Borrell
> DBA/Developer
> New Zealand Police
> +64 237 2811 x5459</BLOCKQUOTE>
> 
><PRE>-- 
>Only interested in San Francisco Bay area job opportunities</PRE>
> </HTML>

>--------------085240D5E031C4E2C69C38FF--

Mark Zawadzki, late of Waynesboro, Va.
'...there is not a sprig of grass that shoots uninteresting to me.'

                                                 Thomas Jefferson, 1790.

Received on Wed Apr 01 1998 - 00:00:00 CST