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

1. What Jonathan wrote
2. IF you have a highly skewed work rate around the clock such that the bit of coalescence between allocated segments is worthwhile, the trick from Stu Goss’ 1990 paper should still (mostly) work: Run a report on free segments to discover the contiguous sizes of free extents and create junk tables, largest contiguous space first. Then drop and purge them. Because you can no longer exactly specify the initial extent this now relies on uniformity, so the purging and coalescence won’t be exact. AND this relies on no autoextend, since that might just make you a big chunk when you try to fill all free space.
3. IF you believe this is a significant problem, you’re probably better off using the Terascape (patent expired unless I missed EMC renewing it) algorithm and relocating your existing objects to two or more new tablespaces now densely packed afresh.
4. Nearly everything too big to move probably needs lifecycle management. Administratively rotating amongst time/purpose named tablespaces for time based partitions is probably the best solution except where time does not fit your entity relationship design (which is sometimes true of, perhaps, inventory levels or bank accounts and an unbounded list smaller than the list of things where time partitioning does make sense. Proofless conjecture.)

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