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@xxxxxxxxxxxxxxxxxxxxx>
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
