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

Yes, clearing the bits is part of (really) dropping segments.

In an autoallocate tablespace each bit in the space map in the file header blocks corresponds to 64KB so there could be a lot of bits to clear. I don't suppose there is much information about exactly who updates the bitmaps and how - whether it's one extent at a time, or all the bits for the whole segment in as bulky a method as possible - and it might depend on version and how the segment was dropped anyway. It's probably possible to work this out by dump log files. At a guess I'd say Oracle plays safe and identifies one extent in the segment header, deletes its entry from the segment header, clears its bits and then logs the change.

You could try aggregating ASH over the interval - the samples in dba_hist might be a bit thin by now, though, so see what SMON and any PX slave processes were waiting on if you were curious to see what was actually happening in the interval. The SQL_IDs of any smon queries might also be something that could be found in MOS on someone's blog.

Regards
Jonathan Lewis

On Thu, 8 Apr 2021 at 20:36, Chris Taylor <christopherdtaylor1994_at_gmail.com> wrote:

> Question -would the bitmaps have to be updated to account for new free
> space that wasn't there previously? I assume there has to be some kind of
> maintenance of bitmaps or does every bit represent an extent with a 1 or 0
> depending on if its free or not? If that is true what is responsible for
> setting/toggling the bitmap to 1 or 0 when an extent is used or freed
> (specifically freed in this case)
>
> Chris
>
>
> On Thu, Apr 8, 2021 at 2:46 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> From the MOS note your referenced:
>>
>> Coalescing free space is not necessary for locally managed tablespaces because bitmaps automatically track adjacent free space.
>> Please refer to:
>> Oracle9i Database Administrator's Guide
>> Release 2 (9.2)
>>
>>
>>
>> I'd forgotten you had 1,024 files in your tablespace; and I'll guess that
>> you used parallel execution to rebuild the index - if so, what degree?
>> So here's one reason why you might have seen smon / PX slaves working
>> very hard for a while.
>>
>> When you create the index in parallel every slave creates a separate
>> segment and when they've all finished each PX slave has a segment slave
>> with a list of extents which they pass to the query coordinator. The QC
>> picks one and makes it the final segment header, copies the lists from the
>> others into the one, and reformats the other segment headers. This means
>> you might end up with 16 * DOP extents of 64KB in one segment, then 63 *
>> DOP 1MB extents before you get any 8MB extents.
>>
>> If the rebuild failed, or your interrupted it, then the PX segments have
>> to disappear. Oracle won't necessary make you wait while it clears up the
>> mess; it can just flag temporary segments as "to be cleared" - and that's
>> one of SMON's periodic jobs.
>>
>> If you had a high degree of parallelism, which means a lot of TEMP
>> segments, and given 1,024 files, it's possible that smon would have decided
>> to run some of its "what segments extents do I have to sort out" in
>> parallel, and that may be what you saw.
>>
>> I don't think Oracle will report "temporary" segments in the datafiles
>> (i.e. the segments allocated for creating permanent objects that will
>> eventually get proper names). Once upon a time you could query dba_segments
>> and see segments with names like 7.34265 which meant a "temporary" segment
>> in file 7 starting at block 34265 that needed clearing up. You might still
>> be able to find segments in sys.seg$ with a query like:
>>
>> select type#, count(*) from seg$ where ts# = {tablespace number of your
>> tablespace} group by type#;
>>
>> I think type 3 is 'TEMPORARY';
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>
>>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l