(1) 매 3초마다
(2) commit할 때마다
(3) LGWR에 로그 파일 스위치 요청이 있을 대
(4) 로그버퍼가 1/3찼거나 로그버퍼에 리두 로그데이터의 크기가 1MB가 됐을때
※9장. 리두와 언두에서 uncommitted 데이터를 데이터 파일에 기록하는것에대해 상세히 다룸
sys@BWD> alter system set log_buffer=1 scope=spfile;
System altered.
sys@BWD> startup force; --SGA=256m
sys@BWD> select value,isdefault from v$parameter where name = 'log_buffer';
VALUE ISDEFAULT
---------- ---------
2044928 FALSE --1.95MB
--SGA=1512mbsys@BWD> select value,isdefault from v$parameter where name = 'log_buffer';
VALUE ISDEFAULT
---------- ---------
14246912 FALSE --13.5MB
(1) Default pool : 모든 세그먼트 블록들이 일반적으로 캐시되는 공간 buffer_cache_size 로 크기를 조정할 수 있다.
(2) Keep pool : default pool의 대안으로 주기적으로 아주 자주 액세스되는 세그먼트를 여기에 할당, 다른 세그먼트가 공간을 요구하면 오래된 블록은 default pool로 밀려난다. db_default_cache_size 로 크기를 조정할 수 있다.
(3) Recycle pool : default pool의 대안으로 주기적으로 큰 세그먼트를 아주 랜덤하게 액세스해서 다른 많은 블록들이 buffer flushing 되는경우에 그 세그먼트를 여기에 할당한다. 이런 세그먼트를 캐시에 담아 두어도 다른 블록에 의해서 밀려나서 나중에 찾을 경우 없다. Recycle pool에 있는 세그먼트로부터 그런 세그먼트를 분리시키면, 그 세그먼트의 블록이 캐시에서 밀려나지 않도록 할 수 있다. db_recycle_cahce_size 로 크기를 조정할 수 있다.
--demo4.sql
--다섯 개의 'currently hottest blocks'를 찾기위한 쿼리
select tch, file#, dbablk, --touch count, file번호, block번호 v$bh
case when obj = 4294967295 --특별한 블록들을 표시하기위해 사용된 매직넘버
then 'rbs/compat segment'
else (select max( '('||object_type||') ' ||
owner || '.' || object_name ) ||
decode( count(*), 1, '', ' maybe!' ) --object가 하나일땐 '', 하나가아닐때 'maybe!'
from dba_objects --어느 오브젝트에 속해있는지 확인하기위해 dba_objects뷰와 조인
where data_object_id = X.OBJ )
end what
from (
select tch, file#, dbablk, obj
from x$bh
where state <> 0
order by tch desc
) x
where rownum <= 5
/
TCH FILE# DBABLK WHAT
---------- ---------- ---------- -------------------------
89 13 94401 rbs/compat segment
89 39 541737 rbs/compat segment
88 1 9566 (TABLE) SYS.TS$ maybe! --아마 SYS.TS$ 사용
88 31 3586609 rbs/compat segment
88 13 94409 rbs/compat segment
--※인스턴스와 관련된 기초가 되는 블록들 확인
--select * from dba_extents where file_id=<FILE#> and block_id <=<DBABLK> and block_id+blocks-1 >= <DBABLK>
--select * from dba_extents where file_id=13 and block_id <=94401 and block_id+blocks-1 >= 94401 --엄청오래돔
select data_object_id, count(*)
from dba_objects
where data_object_id is not null
group by data_object_id
having count(*) > 1;
DATA_OBJECT_ID COUNT(*)
-------------- ----------
6 3
296 7
8 3
205 3
2 17
29 3
215 2
168 3
514 2
10 3
10 rows selected.
--DBA_OBJECTS에서 DATA_OBJECT_ID가 기본키가 아니기 때문에 X$BH와 DBA_OBJECT를 조인하여 클러스터에 있는 모든 오브젝트들의 리스트를 구할 수 있다.
--쿼리 실행 할 때마다 실제 DUAL 테이블을 액세스 할 것
select tch, file#, dbablk, DUMMY
from x$bh, (select dummy from dual)
where obj = (select data_object_id
from dba_objects
where object_name = 'DUAL'
and data_object_id is not null)
TCH FILE# DBABLK D
---------- ---------- ---------- -
1 1 534 X
1 1 535 X
sys@BWD> /
TCH FILE# DBABLK D
---------- ---------- ---------- -
2 1 534 X
2 1 535 X
sys@BWD> /
TCH FILE# DBABLK D
---------- ---------- ---------- -
2 1 534 X -- TCH가 증가하지 않았다.
2 1 535 X
sys@BWD> /
TCH FILE# DBABLK D
---------- ---------- ---------- -
3 1 534 X
3 1 535 X
sys@BWD> /
TCH FILE# DBABLK D
---------- ---------- ---------- -
4 1 534 X
4 1 535 X
--8i이상에서 블록 버퍼는 블록이 사용되었을 때 더 이상 리스트의 맨 앞으로 이동하지 않고 리스트 원래 있던 위치에 머무르게 하는대신, touch count 수를 증가
--시간이 지나면서 리스트 내에서 블록이 자연스럽게 이동(이동지점만관리)
과거에는 단일 데이터 베이스에 있는 모든 블록들은 크기가 같아야 했고, 다른 븐록 크기를 적용하기위해서는 전체 데이터베이스를 재생성해야만 했다.
오라클 9i부터 하나의 데이터베이스에서 복수의 데이터베이스 블록 크기를 사용할 수 있다. 디폴트 크기를 혼합하여 또 다른 네가지 블록크기로도 변경할 수 있다.
각각의 블록 크기를 갖는 그들만의 영역을 가져야한다. 데이터베이스에 non-default 블록 크기를 정의하기 위해서는 버퍼 pool이 각각의 블록들을 담을 수 있도록 설정해야 된다.
이런 다중 블록 크기는 transportable 테이블스페이스를 지원하기 위해 필요한 기능이다.
트랜잭션 위주의 시스템에서 데이터 파일을 가져올 때 블록 크기가 8kb고, 이동할 데이터 웨어하우스의 블록 크기가 16kb나 32kb일 때 효과적이다.
다중블록 크기는 데이터베이스간 데이터 이전이 주된 목적이고, 아마도 하이브리드 리포팅/ 트랜잭션 시스템에도 유리하다.
--demo5
create tablespace ts_16k
datafile '/tmp/ts_16k.dbf'
size 5m
blocksize 16k;
show parameter 16k
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
sys@BWD> alter system set sga_target = 256m scope=spfile;
System altered.
sys@BWD> alter system set db_16k_cache_size = 16m scope=spfile;
System altered.
sys@BWD> startup force
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 2083368 bytes
Variable Size 104859096 bytes
Database Buffers 159383552 bytes
Redo Buffers 2109440 bytes
Database mounted.
Database opened.
sys@BWD> show parameter 16k
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 32M
sys@BWD>
sys@BWD> select sum(bytes) from v$sgastat where pool = 'shared pool';
SUM(BYTES)
---------- --92m
96476584
sys@BWD> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 80M
-- shared pool은 shared_pool_size 파라미터 범위 외에 많은 다른구조를 포함
-- 오라클10g이상에서 수동 SGA 메모리 관리방식을 사용한다면(shared_pool_size를 직접설정한다면) v$sgastat의 shared pool과 shared_pool_size값이 1:1로 대응된다.
sys@BWD> select * from V$SGAINFO;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 2083368 No
Redo Buffers 2109440 No
Buffer Cache Size 155189248 Yes
Shared Pool Size 100663296 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 268435456 No
Startup overhead in Shared Pool 79691776 No
Free SGA Memory Available 0
11 rows selected.
sys@BWD> create pfile='/tmp/pfile' from spfile;
File created.
sys@BWD> !cat /tmp/pfile;
BWD.__db_cache_size=121634816
BWD.__java_pool_size=4194304
BWD.__large_pool_size=4194304
BWD.__shared_pool_size=100663296
BWD.__streams_pool_size=0
*._b_tree_bitmap_plans=FALSE
*._optim_peek_user_binds=FALSE
*._optimizer_join_sel_sanity_check=true
*._sort_elimination_cost_ratio=10
*._table_lookup_prefetch_size=0
*.audit_trail='DB_EXTENDED'
*.background_dump_dest='/oracle/BWD/saptrace/background'
*.bitmap_merge_area_size=3355443
*.compatible='10.2.0'
*.control_file_record_keep_time=30
*.control_files='/oracle/BWD/sapbackup/cntrlBWD.dbf'
*.core_dump_dest='/oracle/BWD/saptrace/background'
*.create_bitmap_area_size=3355443
*.db_16k_cache_size=16777216
*.db_block_checksum='FALSE'
*.db_block_size=8192
*.db_file_multiblock_read_count=32
*.db_files=254
*.db_name='BWD'
*.db_writer_processes=2
*.dml_locks=2500
*.event='604 trace name errorstack'
*.filesystemio_options='asynch'
*.hash_area_size=1048576
*.java_pool_size=4194304#50000000
*.large_pool_size=4194304
*.log_archive_dest='/oracle/BWD/oraarch/BWDarch'
*.log_archive_max_processes=1
*.log_buffer=1
*.log_checkpoint_interval=0
*.log_checkpoint_timeout=0
*.log_checkpoints_to_alert=true
*.max_dump_file_size='20000'
*.O7_DICTIONARY_ACCESSIBILITY=true
*.open_cursors=800
*.optimizer_mode='choose'
*.os_authent_prefix='ops$'
*.parallel_execution_message_size=16384
*.parallel_max_servers=40
*.parallel_threads_per_cpu=1
*.pga_aggregate_target=256m <==
*.processes=300
*.query_rewrite_enabled='FALSE'
*.remote_os_authent=true
*.replication_dependency_tracking=false
*.sessions=600
*.sga_target=268435456 <==
*.shared_pool_size=83886080
*.sort_area_retained_size=0
*.sort_area_size=1048576
*.star_transformation_enabled='true'
*.timed_statistics=true
*.transactions_per_rollback_segment=20
*.undo_management='auto'
*.undo_retention=150000
*.undo_tablespace='PSAPUNDO'
*.user_dump_dest='/oracle/BWD/saptrace/usertrace'
*.workarea_size_policy='AUTO'