개요
DB생성 시 DB Block Size를 어떤값으로 설정하느냐에 따라 동일한 데이터량이 DB 내에서 차지하는 Block수와 Full scan시 읽어들이는 Block수 차이를 설명합니다.
방법
시나리오
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; ==> 3~4회 수행
SQL
복사
DB Block Size 값별 실행 통계 결과
•
DB Block Size= 2K
◦
Excution stat (실행 통계): DB생성 실패
TBR-21025: Unable to extend segment: maximum number of extensions reached.
SQL
복사
•
DB Block Size = 4K
◦
Total Block Count: 31720
◦
Excution stat (실행 통계):
NAME VALUE
------------------------------ ----------
db block gets 459
consistent gets 61493
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 10000000
SQL
복사
•
DB Block Size = 8K
◦
Total Block Count: 15360
◦
Excution stat (실행 통계):
NAME VALUE
------------------------------ ----------
db block gets 78
consistent gets 30489
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 10000000
SQL
복사
•
DB Block Size = 16K
◦
Total Block Count: 7680
◦
Excution stat (실행 통계):
NAME VALUE
------------------------------ ----------
db block gets 74
consistent gets 15243
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 10000000
SQL
복사
•
DB Block Size = 32K
◦
Total Block Count: 3840
◦
Excution stat (실행 통계):
NAME VALUE
------------------------------ ----------
db block gets 72
consistent gets 7619
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 10000000
SQL
복사
•
DB Block Size= 64K
◦
Excution stat (실행 통계): DB 기동 실패
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)..
SQL
복사