Document Type | Technical Information
Category | Monitoring/Inspection
Document Number | TMOTI021
Overview
This explains the difference in the number of blocks occupied by the same amount of data in the DB and the number of blocks read during a full scan depending on what value the DB Block Size is set to when creating the database.
Method
Scenario
create table test_block_size (n number); insert into test_block_size select level from dual connect by level <= 10000000; commit; select sum(blocks) from dba_segments where segment_name='TEST_BLOCK_SIZE'; set autot on set rows off select /*+ FULL(a) */ * from TEST_BLOCK_SIZE a; ==> Perform 3~4 times
Execution Statistics Results by DB Block Size Value
DB Block Size= 2K
- Execution stat (Execution Statistics): DB creation failed
TBR-21025: Unable to extend segment: maximum number of extensions reached.
DB Block Size = 4K
- Total Block Count: 31720
- Execution stat (Execution Statistics):
NAME VALUE ------------------------------ ---------- db block gets 459 consistent gets 61493 physical reads 0 redo size 0 sorts (disk) 0 sorts (memory) 0 rows processed 10000000
DB Block Size = 8K
- Total Block Count: 15360
- Execution stat (Execution Statistics):
NAME VALUE ------------------------------ ---------- db block gets 78 consistent gets 30489 physical reads 0 redo size 0 sorts (disk) 0 sorts (memory) 0 rows processed 10000000
DB Block Size = 16K
- Total Block Count: 7680
- Execution stat (Execution Statistics):
NAME VALUE ------------------------------ ---------- db block gets 74 consistent gets 15243 physical reads 0 redo size 0 sorts (disk) 0 sorts (memory) 0 rows processed 10000000
DB Block Size = 32K
- Total Block Count: 3840
- Execution stat (Execution Statistics):
NAME VALUE ------------------------------ ---------- db block gets 72 consistent gets 7619 physical reads 0 redo size 0 sorts (disk) 0 sorts (memory) 0 rows processed 10000000
DB Block Size= 64K
- Execution stat (Execution Statistics): DB startup failed
Error (-7200) occurred while processing parameter 'DB_BLOCK_SIZE' and value '65536' (DB_BLOCK_SIZE must be a power of 2 and between 2048 and 32768)..