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

From: Powell, Mark <mark.powell2_at_dxc.com>
Date: Fri, 9 Apr 2021 18:18:54 +0000
Message-ID: <BN6PR01MB25477BBCDD31223FE484E645CE739_at_BN6PR01MB2547.prod.exchangelabs.com>

The following may be out of date, but I found the following in my notes

• Connor McDonald
• http://www.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<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 Fri Apr 09 2021 - 20:18:54 CEST