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

I mean, I *guess *that's possible that there was objects in the recyclebin for this tablespace - but thats very unusual (unlikely) .

For example, currently there 0 objects in the recylebin at all.

SQL> select owner, object_name, original_name from dba_recyclebin;

no rows selected

SQL> select count(1) from recyclebin$;

  COUNT(1)

         0

And its been over a week since the table fragmentation issue and no maintenance of segments occurs regularly that would lend itself to putting objects in the recyclebin.

Also recyclebin is OFF

recyclebin                           string      OFF

So I find that very unlikely.

Chris

On Thu, Apr 8, 2021 at 2:13 PM Andre Maasikas <amaasikas_at_gmail.com> wrote:

> 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