Re: segment covers more blocks than needed

8MB and 502 blocks (it should be 512 in dba_segments) for small tables is possible with partitioned tables and 16K tablespaces due to _partition_large_extents=true.

On Tue, 28 Jan 2020 at 16:02, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> ASSM formats blocks on demand in chunks of 16 consecutive blocks. But the
> blocks aren't necessarily going to be the ones near the start of the extent
> (and when I've looked - only in passing - at cases where the segment size
> is 1MB or 8MB the first 16 blocks seem to be at, or close to, the end of
> the extent.
>
> The tablescan is then done from the start of segment to the "Low highwater
> mark (LHWM)" in unit of db_file_multiblock_read_count, then in units of
> multiples of 16 blocks s between the LHWM and the High highwater mark
> (HHWM) - where the LHWM is nominally the point up to which the segment is
> formatted with no unformatted gaps below it. (If you're unlucky Oracle
> could think that LHWM is right at the start of the segment when it's really
> thousands of blocks into the segment - but I haven't seen anything that
> extreme recently).
>
> https://jonathanlewis.wordpress.com/2013/07/30/assm-2/
> https://jonathanlewis.wordpress.com/2010/07/19/fragmentation-3/#more-4147
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>
> Sent: 28 January 2020 15:50
> To: Oracle-L_at_freelists.org
> Subject: segment covers more blocks than needed
>
> Hi,
>
> I am no classical DBA, thus this question might be trivia for youl.
> This issue causes serious misestomate when dynamic_sampling >= 4 is used.
> We got a DWH with many small tables where stats are showing 502 blocks in
> DBA_TABLES.
> Empty blocks is 0.
> The Point is that there must be blocks which must (almost?) empty.
> 502 Blocks Matches with the initial size of the Segment.
> We did a testcase. We created am empty table. No blocks when querying
> dba_tables of course.
> After we inserted the first row (deferred_segment_creation = true) , again
> 502 appeared in dba_tables.
> DBMS_SPACE features this Output:
>
> L/SQL-Prozedur erfolgreich abgeschlossen.
> Unformatted Blocks = 486
> Unformatted Bytes = 7962624
> FS1 Bytes (at least 0 to 25% free space) = 0
> FS1 Blocks(at least 0 to 25% free space) = 0
> FS2 Bytes (at least 25 to 50% free space)= 0
> FS2 Blocks(at least 25 to 50% free space)= 0
> FS3 Bytes (at least 50 to 75% free space) = 0
> FS3 Blocks(at least 50 to 75% free space) = 0
> FS4 Bytes (at least 75 to 100% free space) = 262144
> FS4 Blocks(at least 75 to 100% free space)= 16
> Full Blocks in segment = 0
> Full Bytes in segment = 0
>
> Segment_space_managment is AUTO. Tablespace has a uniform extent.
> Runtime Stats show 28 buffers on FTS, proving that the HWM is not set
> after the first block.
> Avg_row_len is 200. Remember this is only 1 row.
>
> What is going on here?
>
>
>
> Regards
>
> Lothar
>
>
>
>
>
>
>
>
>
>
>
> Full Bytes in segment = 0Full Blocks in segment = 0FS4 Blocks(at least 75
> to 100% free space)= 63FS4 Bytes (at least 75 to 100% free space) =
> 1032192FS3 Blocks(at least 50 to 75% free space) = 1FS3 Bytes (at least 50
> to 75% free space) = 16384FS2 Blocks(at least 25 to 50% free space)= 0FS2
> Bytes (at least 25 to 50% free space)= 0FS1 Blocks(at least 0 to 25% free
> space) = 0FS1 Bytes (at least 0 to 25% free space) = 0Unformatted Bytes =
> 7176192Unformatted Blocks = 438
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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