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

Correct - parallelism on the operation and it was a partition index rebuild with parallel 8.

So like so:

• this is the one that failed on the index it was rebuilding where I had negelected to set the correct tablespace for the move 'alter index ehr.'||rec.segment_name||' rebuild partition '||rec.partition_name||' tablespace ehr_data online parallel 8';

Since I was going to loop through the top N index partitions in the offended tablespace, I don't remember or have the first one that was picked up in this operation.

The loop looks like this:

 for rec in
 (
select owner, segment_name, segment_type, partition_name, bytes/1024/1024/1024 as size_gb
 from dba_segments
 where segment_type = 'INDEX PARTITION'
 and tablespace_name = 'EHR_DATA'
 and owner = 'XXXXXXX' --redacted
 order by bytes desc
 fetch first 50 rows only
 ) LOOP The largest index partition we had at the time was 10G (slightly larger than 10G but less than 11G)

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