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