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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jonathan Lewis
*Sent:* Thursday, April 08, 2021 10:11 AM
*To:* oracle-l@xxxxxxxxxxxxx
*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@xxxxxxxxx> 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
