Re: segment covers more blocks than needed

Hi ,

thanks, I will check the hidden param tomorrow. The tables are of course not partitioned.
But the 16k tablespace is correct. Can you point me to your source? Funny what consquences that illfounded statment had that blocksize >8k would be good for DWH.

Regards

Lothar

Am 28.01.2020 um 17:17 schrieb Mikhail Velikikh:
>
> 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 <mailto: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
> <mailto:oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>> on behalf of
> l.flatz_at_bluewin.ch <mailto:l.flatz_at_bluewin.ch> <l.flatz_at_bluewin.ch
> <mailto:l.flatz_at_bluewin.ch>>
> Sent: 28 January 2020 15:50
> To: Oracle-L_at_freelists.org <mailto: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