Skip to main content

Search

Welcome to Tibero GTS!

Difference in Number of Stored Blocks According to DB Block Size (Execution Statistics, Execution stat)

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)..
 
 
 

Related to