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

Hi,
dba_free_space is an UNION ALL of free space from file free bitmap and "potentially free" joined from recyclebin$ So if you have objects in recyclebin it might show the "old" way of 2 free extents next to each other.
Seems your segment create kicked off a recyclebin purge of some objects.

All what Johnatan said stands.
There no operation to coalesce a free bitmap. (you can't coalesce a sequence of 01000011110000 of having a different number of 0's) Recyclebin purge might change the way things are shown in dba_free_space but this is all unnecessary and managed automatically when needed.
(If i remember correctly- wasn't there a version where recyclebin objects were not counted in dba_free_space?)

Andre

On Thu, Apr 8, 2021 at 6:21 PM Chris Taylor <christopherdtaylor1994_at_gmail.com> wrote:
>
> 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.
>
> But this behavior is what I witnessed and the events for the parallel sessions were all related to some space event (I don't remember the name however)
>
> Again, I have the before and after report of the contiguous free space chunks where my largest contiguous chunk before was 5MB and after it is 745MB.
>
> Details on this tablespace (as a quick reminder of the situation)
> - 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 there are other options here, then I would like to hear them (because it would indicate I missed something)
>
> Chris
>
>
> On Thu, Apr 8, 2021 at 10:10 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:
>>
>> 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