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

From: Powell, Mark <mark.powell2_at_dxc.com>
Date: Tue, 13 Apr 2021 18:51:04 +0000
Message-ID: <DM5PR01MB2556F2122D725927A01866C9CE4F9_at_DM5PR01MB2556.prod.exchangelabs.com>

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<mailto: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<mailto: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<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Hemant K Chitale <hemantkchitale_at_gmail.com<mailto:hemantkchitale_at_gmail.com>> Sent: Friday, April 2, 2021 10:04 AM
To: jlewisoracle_at_gmail.com<mailto:jlewisoracle_at_gmail.com> <jlewisoracle_at_gmail.com<mailto:jlewisoracle_at_gmail.com>> Cc: Chris Taylor <christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com>>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> <oracle-l_at_freelists.org<mailto: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<mailto: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<mailto: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 Received on Tue Apr 13 2021 - 20:51:04 CEST

This message: [ Message body ]
Next message: Jeff Chirco: "Re: Backroom process tt00 top process"
Previous message: Laurentiu Oprea: "Re: Backroom process tt00 top process"
Maybe in reply to: Hemant K Chitale: "Re: Oracle's automatic extent allocation (next extent) size algorithm - anyone?"
In reply to Powell, Mark: "Re: Oracle's automatic extent allocation (next extent) size algorithm - anyone?"
Next in thread: Chris Taylor: "Re: Oracle's automatic extent allocation (next extent) size algorithm - anyone?"
Reply: Chris Taylor: "Re: Oracle's automatic extent allocation (next extent) size algorithm - anyone?"
Reply: Chris Taylor: "Re: Oracle's automatic extent allocation (next extent) size algorithm - anyone?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message