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

Ok I isolated the SMON session and its correlated PX processes from dba_hist_active_Sess_history. There are no SQL_IDs for any of them (which is fine).

EVENTS are primarily "wait for a undo record" (tons of these) and some cluster waits and a ton of "DFS Lock Handle" waits and quite a few NULL Events with a file# and block#.

Interesting Factoid that may interest only me:

I isolated the time to the exact time the SMON kicked off the parallel sessions indicating the cleanup start/stop times: (NOTE: I've saved these results to an excel sheet if anyone is interested in taking a look)

• SMON owned 60 parallel sessions per GV$PX_SESSION at the time

So, it took 1 hours 6 minutes to cleanup an online index partition rebuild that only ran for 3 minutes?

Chris

The index build (before it failed) with 8 Parallel only ran for

On Thu, Apr 8, 2021 at 4:05 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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