문서유형ㅣ기술정보
분야ㅣ모니터링/점검
문서번호ㅣTMOTI021
개요
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회 수행
DB Block Size 값별 실행 통계 결과
DB Block Size= 2K
- Excution stat (실행 통계): DB생성 실패
TBR-21025: Unable to extend segment: maximum number of extensions reached.
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
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
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
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
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)..