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

Some embedded comments – those were the days…..

Clay Jackson

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Mark W. Farnham Sent: Friday, April 2, 2021 7:36 AM
To: hemantkchitale_at_gmail.com; jlewisoracle_at_gmail.com Cc: 'Chris Taylor' <christopherdtaylor1994_at_gmail.com>; oracle-l_at_freelists.org Subject: RE: Oracle's automatic extent allocation (next extent) size algorithm - anyone?

CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

I have a vague memory that (64M) was the limit for the default block size of 8KB but that it might be different for a larger database block size.

But that may have been deprecated before it was ever deployed with the recommended strategy that objects that big should get individual attention using uniform. It all seems murky and I doubt that it was a purely engineering decision because if memory serves it took place during one of the more aggressive “automate everything and you won’t need no stinkin’ DBAs” marketing campaigns.

Mostly I was annoyed because they remove the syntax that made it possible to create a dummy object on each datafile of placement and the exact size required to actually align database extents on device boundaries (Hank <last name escapes me> documented why most folks never did that correctly, but it could be done until that change with a bit of effort until that change.) That could really matter on spinning rust that was protected from “other” jobs for huge monthly operations. I suppose it might still matter if you could figure out page boundaries on SSD exactly, but that is intentionally deeply behind many veils, ‘cause nobody thinks anyone can actually manage that correctly. Anyway, without the ability to either intervene in the volume manager so header stuff appears on one boundary and “data” blocks still align correctly you can’t really do it anymore. We actually did this for a system I helped design while at a Wireless Provider (based on MWF’s presentation). We had Oracle Parallel Server (Version 7!) set up with one node writing call detail records to “spinning rust” and one node READING the records; but always (at least) one PHYSICAL “extent” behind the writer. It was VERY obvious (1.5-3x jump in throughput) when we finally “got it right”. Took LOTS of experimentation and “tweeking”, and we also had to be sure that when the admins replaced disks, they paid attention to the GEOMETRY of the new disks.

For some version regarding how to physically order when you couldn’t use direct mode, I documented the automatic block placement order, which was some sort of spiral pattern that looked like it was oriented toward inserts rather than access. DIRECT does not do that at all, which is one of the reasons DIRECT combined with attribute clustering (or other methods of physical ordering data) cannot be compared with non-DIRECT methods.

I don’t know if that presentation is still available on da net.

mwf

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hemant K Chitale Sent: Friday, April 02, 2021 10:04 AM
To: jlewisoracle_at_gmail.com<mailto:jlewisoracle_at_gmail.com> Cc: Chris Taylor; 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 Fri Apr 02 2021 - 17:04:34 CEST