Re: SMON "trick" to force cleanup of extent honeycombs/extent fragmentation?

If this is a locally managed tablespace then there's no such thing as tablespace coalesce. If smon kicked off loads of parallel execution slaves that's probably something to do with recovery.

If you've got a lot of free extents which are very small then there are probably used extents between the free extents. and if you think there aren't then it's possible that a lot of those free extents are from segments in the recyclebin, and a "purge recyclebin" or "purge dba_recyclebin" will get rid of the mess. This will delete the segment entries from the seg$ table, and the free space report will change with no further action:
https://jonathanlewis.wordpress.com/2017/05/10/quantum-space/

Regards
Jonathan Lewis

On Thu, 8 Apr 2021 at 14:41, Chris Taylor <christopherdtaylor1994_at_gmail.com> wrote:

> There used to be a "trick" to force SMON to aggressively cleanup extents
> by creating an extent that would fail in a tablespace that was heavily
> fragmented.
>
> I did this by accident once already by inadvertently rebuilding an index
> into this tablespace that was too large to fit in the available extents and
> saw SMON ramp up a TON of parallel processes doing space cleanup.
>
> After SMON was finished I now have lots of continguous extents space, but
> still lots of fragmentation (made worse as I move index partitions into
> another tablespace).
>
> The alter tablespace coalesce command doesn't really do too much and
> doesn't clean up any extents (or if it does , I can't find them) and
> returns immediately and smon doesn't do anything special.
>
> So now I have 2 situations: - Plenty of contiguous space (for a while)
> but still lots of fragments that could be cleaned up [potentially].
>
> I *suspect* I could try rebuilding another index into this tablespace that
> would fail but the problem is I now have lot and lots of free
> contigous space so finding an index that would fail on a rebuild could be
> problematic.
>
> I tried creating a segment with storage options but since this is an LMT
> with autoallocation of extents, it ignores the storage clause of the
> segment.
>
> Does anyone know a trick (or event) that will force SMON to go aggressive
> on the space cleanup in an LMT tablespace?
>
> Before SMON went aggressive:
> [sample]
> TABLESPACE NAME CONTIGUOUS BYTES
> ------------------------------ ----------------
> EHR_DATA 5,242,880 (largest new extent poss = 5MB)
> EHR_DATA 5,242,880
> EHR_DATA 5,242,880
> EHR_DATA 5,242,880
> EHR_DATA 5,242,880
> EHR_DATA 3,145,728
> EHR_DATA 3,145,728
>
>
> AFTER SMON went aggressive:
> [sample]
> TABLESPACE NAME CONTIGUOUS BYTES
> ------------------------------ ----------------
> EHR_DATA 745,013,248
> EHR_DATA 671,088,640
> EHR_DATA 652,214,272
> EHR_DATA 585,105,408
> EHR_DATA 537,919,488
> EHR_DATA 536,870,912
> EHR_DATA 536,870,912
>
> That's just a sample of the contiguous bytes now available - there's a TON
> of 3MB, 1MB, and 64K extents (hundreds of thousands) that I would like to
> consolidate (or at least TRY to consolidate using SMON)
>
>
>
> Chris
>
>

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