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@xxxxxxxxx> 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@xxxxxxxxx> wrote:
10GB and I have the full contiguous space report PRIOR to running the
I'm hesitant to disagree. The index I was rebuilding was something like
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?
61997.1) (From 7.3 onward it says - and yes I know it is old)
In Oracle Support Note:
SMON - Temporary Segment Cleanup and Free Space Coalescing(Doc ID
(*GRANTED* I did *not* check DBA_FREE_SPACE at the time to see if the
It says that SMON is responsible for Free Space Cleanup as well
count was dropping)
sessions were all related to some space event (I don't remember the name
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
however)
chunks where my largest contiguous chunk before was 5MB and after it is
Again, I have the before and after report of the contiguous free space
745MB.
it would indicate I missed something)
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
wrote:
Chris
On Thu, Apr 8, 2021 at 10:10 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
tablespace coalesce. If smon kicked off loads of parallel execution slaves
If this is a locally managed tablespace then there's no such thing as
that's probably something to do with recovery.
probably used extents between the free extents. and if you think there
If you've got a lot of free extents which are very small then there are
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/
christopherdtaylor1994@xxxxxxxxx> wrote:
Regards
Jonathan Lewis
On Thu, 8 Apr 2021 at 14:41, Chris Taylor <
extents by creating an extent that would fail in a tablespace that was
There used to be a "trick" to force SMON to aggressively cleanup
heavily fragmented.
index into this tablespace that was too large to fit in the available
I did this by accident once already by inadvertently rebuilding an
extents and saw SMON ramp up a TON of parallel processes doing space
cleanup.
but still lots of fragmentation (made worse as I move index partitions into
After SMON was finished I now have lots of continguous extents space,
another tablespace).
doesn't clean up any extents (or if it does , I can't find them) and
The alter tablespace coalesce command doesn't really do too much and
returns immediately and smon doesn't do anything special.
while) but still lots of fragments that could be cleaned up [potentially].
So now I have 2 situations: - Plenty of contiguous space (for a
that would fail but the problem is I now have lot and lots of free
I *suspect* I could try rebuilding another index into this tablespace
contigous space so finding an index that would fail on a rebuild could be
problematic.
LMT with autoallocation of extents, it ignores the storage clause of the
I tried creating a segment with storage options but since this is an
segment.
aggressive on the space cleanup in an LMT tablespace?
Does anyone know a trick (or event) that will force SMON to go
5MB)
Before SMON went aggressive:
[sample]
TABLESPACE NAME CONTIGUOUS BYTES
------------------------------ ----------------
EHR_DATA 5,242,880 (largest new extent poss =
TON of 3MB, 1MB, and 64K extents (hundreds of thousands) that I would likeEHR_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
to consolidate (or at least TRY to consolidate using SMON)
Chris
