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
