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

I'm hesitant to disagree. The index I was rebuilding was something like 10GB and I have the full contiguous space report PRIOR to running the rebuild that failed. (And I have the contiguous freespace report AFTER SMON cleaned up the failed index rebuild that shows the coalesced space - when SMON was doing a TON of work (much more so than what is normally done with a failed index rebuild). Again, I'm EXTREMELY hesitant to disagree, but I don't know of any other explanation other than SMON was doing free space consolidation?

In Oracle Support Note:

     SMON - Temporary Segment Cleanup and Free Space Coalescing(Doc ID 61997.1) (From 7.3 onward it *says - and yes I know it is old)*

It says that SMON is responsible for Free Space Cleanup as well  (*GRANTED* I did *not* check DBA_FREE_SPACE at the time to see if the count was dropping)

 How to identify whether SMON is coalescing

    o. Check whether there are a large number of free extents that might

       be being coalesced by running the following query a few times:

         SELECT COUNT(*) FROM DBA_FREE_SPACE;

       If the count returned is dropping while SMON is working, it is
       likely that SMON is coalescing free space.

- EHR_DATA has 1024 datafiles (max # of datafiles) - roughly 30TB
- "Free Space" at the time was nearly 1 TB
- Largest Possible Extent creation was 5MB
- After consoldation/cleanup largest possible extent creation is ~745MB

> 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