Re: segment covers more blocks than needed

Hello Lothar,
Mikhail is referring to the behavior that is described in MOS ID #1295484.1.

However I am not quite sure what your question is. You said that you are using an uniform extent size - what size is it? Might it be round about 8 MB - this would explain the behavior for non-partitioned tables as well.

Jonathan already explained the FTS behavior with the LHWM / HHWM.

So maybe only one point left: Why does dynamic sampling screw it up?

Do you see something like "DS Failed for ..." in the CBO trace file? AFAIK there is a sanity check - the DS result can be ignored if number of sampled blocks is small and the "filtered sample card." is zero ... or maybe some DS bugs as usual ;-)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Lothar Flatz <l.flatz_at_bluewin.ch> hat am 28. Januar 2020 um 21:02 geschrieben:
>
>
> 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
>
>
> > > 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

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