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

Oracle tries to be proactive with tablespace extension (and even some segments' extention - securefiles for example). That's why the Space Management COordinator (SMCO) and its worker slaves (Wnnn) exist. So assuming that there was no user activity (like creating and later dropping/purging a large table), I'd say the SMCO process got a little too proactive with extending datafiles. Maybe you'll find some traces or warnings in alert log or the SMCO tracefile.

There's a _enable_space_preallocation parameter that controls this and also plenty of MOS notes:

• SMCO (Space Management Coordinator) For Autoextend On Datafiles And How To Disable/Enable (Doc ID 743773.1)
• AUTOEXTEND Grows To Full Size Without Reason (Doc ID 1459097.1)
• Master Note: Overview of Oracle Segment Storage (Doc ID 1491960.1) - *search for SMCO*

--
Tanel Poder
https://blog.tanelpoder.com

On Thu, Mar 14, 2019 at 2:16 PM Rich J <rjoralist3_at_society.servebeer.com>
wrote:



> On 2019/03/14 12:40, Chris Taylor wrote:


>

> 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.

>

>

>

> I'd agree that the algorithm might make the next extent "large", but the

> new extent(s) totaled less than 2GB.  Which is why I'm curious what was

> going on that the TS grew by exactly 31GB.

>

> After verifying the top used block in each datafile, I resized them.  Sure

> enough, I got back about 28GB of unused/unallocated space.  The ERP that

> uses this DB doesn't add/drop segments on-the-fly or anything like that.

> The only event that I can see where the datafiles would autoextend is

> segment growth.  And there's a 28+GB difference between how much the

> segment(s) grew and how much the TS grew.  Weird.

>

> Thanks for the feedback, Chris!

>

> Rich

>


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