Re: Oracle's automatic extent allocation (next extent) size algorithm - anyone?

Yeah, that would make sense. They ought to highlight in bold and underlined that if your "'tablespace is subject to dropping objects, then please use uniform extent size tablespaces" :)

Chris

On Tue, Apr 13, 2021 at 2:51 PM Powell, Mark <mark.powell2_at_dxc.com> wrote:

> Chris, I believe the exact allocation logic for auto-allocate is not
> documented because it is subject to change with any release, and I am
> pretty sure that at one time it changed to pick up odd free extents. That
> is, if the extent size was 64K and there was 55K at the end of the file it
> was not used on early releases, but later releases would go ahead and use
> the extent. If you check the actual allocation size for all objects in an
> auto-allocate tablespace some of them may vary from the expected size but
> this will also depend on file allocations size/extend sizes. And again
> this could have changed with the newer releases.
>
> Mark Powell
> Database Administration
> (313) 592-5148
>
>
> ------------------------------
> *From:* Chris Taylor <christopherdtaylor1994_at_gmail.com>
> *Sent:* Friday, April 9, 2021 6:36 PM
> *To:* Powell, Mark <mark.powell2_at_dxc.com>
> *Cc:* oracle-l_at_freelists.org <oracle-l_at_freelists.org>
> *Subject:* Re: Oracle's automatic extent allocation (next extent) size
> algorithm - anyone?
>
> Thanks Mark. I knew we had discussed this on the list in the past . I'm
> sort of surprised Oracle hasn't documented the details somewhere for
> general rule of thumb purposes.
>
> Chris
>
>
> On Fri, Apr 9, 2021 at 2:19 PM Powell, Mark <mark.powell2_at_dxc.com> wrote:
>
> The following may be out of date, but I found the following in my notes
>
> -- Connor McDonald
> -- http://www.oracledba.co.uk
> <https://clicktime.symantec.com/3Qd93cAkaEcmttvH3gWWjdT7Vc?u=http%3A%2F%2Fwww.oracledba.co.uk>
> -- taken off metalink 11/19/2002
> --The following appears to cover most (but not guaranteed to be all) cases
>
> -- auto-alloc extents are 16x64k, then 64x1m, then 120x8m, then 64m, when
> the initial_extent is less than 1m
> -- auto-alloc extents are 64x1m, then 120x8m, then 64m, when the
> initial_extent is equal/more than 1m
> --
> -- Jacques Kilchoer [Jacques.Kilchoer_at_quest.com] Oracle-L distr
> 06/24/2004
> -- RE: Next Extent Failure Notification for Locally Managed Tablespaces
> --
> -- There will be 4 extent sizes used, 64K, 1M, 8M, 64M
> -- As long as object allocation is 1M or less, 64K extent sizes are used,
> -- When object allocation is between 1M and 64M, 1M extent sizes are used.
> -- When object allocation is between 64M and 1G, 8M extent sizes are used.
> -- When object allocation is more than 1G, 64M extent sizes are used.
>
>
> Mark Powell
> Database Administration
> (313) 592-5148
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Hemant K Chitale <hemantkchitale_at_gmail.com>
> *Sent:* Friday, April 2, 2021 10:04 AM
> *To:* jlewisoracle_at_gmail.com <jlewisoracle_at_gmail.com>
> *Cc:* Chris Taylor <christopherdtaylor1994_at_gmail.com>;
> oracle-l_at_freelists.org <oracle-l_at_freelists.org>
> *Subject:* Re: Oracle's automatic extent allocation (next extent) size
> algorithm - anyone?
>
>
> I have found Maximum Table Extent Size When Using AUTOALLOCATE (Doc ID
> 1302686.1)
>
> However, it doesn't explain when and how the RDBMS switches from 64K to
> 1M and then 8M and 64M
>
> Hemant K Chitale
>
>
>
>
> On Wed, Mar 31, 2021 at 12:30 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>
> I don't think a complete algorithm has ever been published because there
> are so many possible variations depending on whether it's a single-file or
> multi-file tablespace, depending on what's been created and dropped
> previously and what holes are arround, whether or not the insert is
> parallel or not, and whether you're creating a partitioned object. There
> may be other complications.
>
> In the simplest case in the 8KB block size it seems to be:
> 16 extents at 64KB (8 blocks) - to get to 1MB extent
> 63 extents at 1MB (128 blocks) - to get to a total of 64MB extent
> 120 extents at 8MB (1024 blocks) - to get to 1GB
> -- after that I think it may be 64MB extents all the way, but I have a
> vague memory of someone testing to a very large segment size and finding
> 256MB as the next size.
>
>
> Regards
> Jonathan Lewis
>
>
>
>
>
> On Tue, 30 Mar 2021 at 16:47, Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
> Env: 12.1.0.2
> Linux x86-64
>
> For Oracle segments in Locally Managed Tablespaces with *AUTOMATIC* extent
> allocation (instead of UNIFORM extent allocation) , does anyone have the
> algorithm that Oracle uses to determine the next extent size that is based
> on the size of the segment?
>
> I used to have it where it went something like:
>
> IF segment > xG , then next extent = xGB
> IF Segment > xMB then next extent = xMB
>
> I'm trying to find it but can't quickly locate it (because I'm not sure
> what keywords to search for)
>
> Chris
>
>
>
>
>
>

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