Re: How does Oracle determine how much to extend autoextend datafiles?

Rich,

I just thought the extent management in Oracle in LMT - depending on the size of the segment, Oracle auto determines the next extent size when using automatic space management.

So depending on the size of the segments involved (table, indexes, lobs(?) ) those next extents might have been large.

I think I saw an Oracle support document on the extent size thresholds (based of segment_size) but I can't lay my hands on it at the moment.

Chris

On Thu, Mar 14, 2019 at 12:30 PM Chris Taylor < christopherdtaylor1994_at_gmail.com> wrote:

> Rich,
>
> I don't know the answer to your question but we experienced very odd
> behavior on segments that had their segment header block in an autoextend
> datafile - where the datafile had reached its max size and autoextend on.
> While other datafiles in the same tablespace had plenty of space, Oracle
> continued to try to grab space in the full datafile. Turning off
> autoextend resolved that odd behavior.
>
> I wanted to test it but I haven't had a.) time and b.) a good test case so
> I just have the general observations of what was happening at the time. It
> was exceptionally frustrating though while it was happening until I turned
> off autoextend on the datafiles for the tablespace holding the affected
> segments.
>
> This was also in 12.1.0.2. I have a suspicion that something is 'off'
> about autoextend in 12.1 but nothing definite and nothing concrete to open
> an SR about.
>
> Chris
>
>
> On Thu, Mar 14, 2019 at 10:18 AM Rich J <rjoralist3_at_society.servebeer.com>
> wrote:
>
>> Hey all,
>>
>> In 12.1.0.2, I have a LMT tablespace set to autoallocate with an 8K
>> blocksize and 22 datafiles. Each datafile is set to initial 256M, extend
>> 256M, autoextend to a max of 31GB. The contents are only heap tables with
>> a few inline LOBs.
>>
>> Last night, Oracle decided to autoextend three of the datafiles that
>> aren't maxed, a total of 124 times 256MB, which happens to equal 31GB.
>> That's fine, except it only required less than 2GB, presumably by a table
>> expanding.
>>
>> So, why did Oracle grab a total of 31GB -- suspiciously the exact same as
>> my weird max size of each datafile -- when it only needed maybe 2GB?
>>
>> Thanks,
>> Rich
>>
>

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