segment covers more blocks than needed
From: <l.flatz_at_bluewin.ch>
Date: Tue, 28 Jan 2020 16:50:17 +0100 (CET)
Message-ID: <925291326.31988.1580226617046_at_bluewin.ch>
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
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
Date: Tue, 28 Jan 2020 16:50:17 +0100 (CET)
Message-ID: <925291326.31988.1580226617046_at_bluewin.ch>
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)= 16Full 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-lReceived on Tue Jan 28 2020 - 16:50:17 CET