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

Mark,

That's part of the whole problem with this LMT. It's setup to AUTOMATIC EXTENT ALLOCATION instead of UNIFORM :/ I've got hundreds of thousands of 64k, 1M, 3M, 4M etc extents strewn throughout the tablespace.

We *are* reorganizing objects out of that tablespace into a BIGFILE tablespace focusing on index partitions first as no downtime is needed.

Thanks,
Chris

On Thu, Apr 8, 2021 at 10:58 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> a) What Jonathan wrote
>
> b) 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.
>
> c) 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.
>
> d) 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.)
>
>
>
> good luck. Honeycomb freespace is probably NOT a real problem. In the rare
> edge case it might be a concurrency killer and space waster. With
> allocation being uniform it should never be a real problem, with “SYSTEM”
> increasing extents I suppose it could be.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jonathan Lewis
> *Sent:* Thursday, April 08, 2021 10:11 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* 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