SMON "trick" to force cleanup of extent honeycombs/extent fragmentation?
Date: Thu, 8 Apr 2021 09:41:22 -0400
Message-ID: <CAP79kiThCffDO0xf3b6mWBuMSah8nu1jqGPq3wDSz-XpOfT4rg_at_mail.gmail.com>
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-lReceived on Thu Apr 08 2021 - 15:41:22 CEST