[SGA 관련 파라미터]
1. shared_pool
- shared_pool_size (1 granule);동적 변경가능
- shared_pool_reserved_size (default shared_pool_size 의 5%)
2. Data buffer Cache
- db_cache_size (=db_block_buffers --8i 이전) (1 granule);동적 변경가능
3. Redolog buffer
- log_buffer
4. large pool
- large_pool_size (default 0);동적 변경가능
5. JAVA pool
- java_pool_size (default 24)
6. Total SGA 크기 : sga_max_size --9i 부터 등장
[delete 과정]
1. 메모리로 올린다.
2. 롤백세그먼트 이용가능한 장소 확보
4. 락
4. redo buffer에 작업내용기록
5. 수정
[Commit]
1. redo log buffer에 만 기록
--LRU 리스트에서 사라질 때, DBW0가 Dirty_list (=check pont queue)를 보고 db file에 기록
--Check point 가 발생하면, DBW0가 Dirty_list (=check pont queue)를 보고 db file에 기록
[Data Buffer Cashe]
1. 적중률 계산
v$sysstat : session logical reads : A
physical reads : B
physical reads direct : C
physical reads direct (lob) : D
hitratio = 1-(B-C+D)/A) > 90%
*physical reads direct은 어느때 올라가는가?
1)insert into emp2 select * from emp;
2)insert /*+append*/ into emp select * from emp; ->서버프로세스의 메모리에서 copy 작업이 이루어짐, buffer cache에서 작업하지 않음. 이때 올라감.
2. Hit ratio 90% 이하인 경우
a. 다중 버퍼 구성
예)가.20M를 다음과 같이 할당
db_cache_size=16M
db_recycle_cache_size=4M
나.alter table big_temp storage (buffer_pool recycle); --create 할 때하든지..
- Object 전체를 넣을 필요없고, 작업에 필요한 만큼만 잡아야 효과 만땅.
작업 blocks 들은 user_tables의 blocks 컬럼 참고 (<- analyze해야 정확한 정보)
다.자주 사용하는 block을 위해 db_keep_cache_size 영역할당
-alter table big_temp storage (buffer_pool keep); --create 할 때하든지..
-keep할 object들이 모두 들어갈 수 있도록 크지 잡아야 효과 볼수 있다.
b. Cache 옵션 이용
예)full table scan은 MRU쪽으로 이동하지 않는다. 그러나 그래도 MRU쪽에 남게 하려면 alter table table_name cache;이용
c. buffer cache size 증가 고려 : v$db_cache_advice 이용
- size_for_estimate
- estd_physical_reads
[buffer Cache 에 올라와 있는 정보보기]
SQL> select owner#, name , count(*)
from v$cache
where owner# >0
group by owner#,name;
[v#sess_io]
*db block gets : 원본 block에서 읽었을 경우 -> logical reads
*consistent gets : rollback block에서 읽었을 경우 -> 역시 logical reads
[v$buffer_pool_statistics] --v$sysstat이 system 전체라면 이 뷰는 pool 별로 조회
*Keep = 100%
*Recycle < 50%
*Default > 90%
[v$system_event]
*free buffer waits : db buffer cache에 free buffer가 없어 기다릴 때
=>DBWR_is_slaves 지정 : DBWR의 동작을 도와줌 - DBWR프로세스가 db block을 읽어서 slave 1 ..slaveN프로세스가 disk에 기록하는 작업을 함. 단일 CPU에서 권장.
또는 DB_writer_processes : DBWR프로세스가 지정한 수만큼 생성됨. 멀티 CPU에서 권장
*buffer busy waits : 똑 같은 rollback segment을 여러명이 동시에 사용 시, free list(insert 작업시)
[Redolog Buffer]
[기록되는 내용]
1. DDL, DML등 select 제외한 수정 작업 모두 기록
[크기]
1. select value from v$parameter where name='log_buffer'; --파파미터에 지정된 크기
2. select bytes from v$sgastat where name='log_buffer'; --실제 할당된 크기
[튜닝 목표]
:redo buffer에 기록하기위해 waitiing 발생 하지 않도록 해준다.
[리두로그 버퍼에 대한 대기가 일어나고 있는지 확인]
select max(decode(name, 'redo buffer allocation retries', value)) retry,
max(decode(name, 'redo entries', value)) retry,
from v$syssat
where name in ('redo buffer allocation retries','redo entries');
select sid, event, seconds_in_wait, state
from v$session_wait
where event like 'log buffer space%';
select event, total_waits from v$system_event
where event like ''log file switch%;
[Redolog file의 대기 여부 확인]
select event, total_waits, time_waited, average_wait
from v$system_event
where event in ('log file sync','log file parallel write');
[LGWR동작시점]
1. commit 시 동작
2. buffer에 1/3이상 내용이 채워지면 동작
3. Checkpoint 발생시(예, 매 3초마다)
4. DBWR프로세스가 수정된 버퍼를 디스크에 기록할 때(필요한 경우)=DBWR 동작 전
[v$system_event]
*log buffer space :
*log file switch %
[I/O 튜닝]
1. v$filestat, dba_data_files를 활용하여 io가 많은 file을 찾는다.
2. 해당 data file이 속해 있는 tbs를 조회
3. 해당 tbs에 속해 있는 segment_name을 조회
4. 보고 적절히(?) 다른 tbs로 이동
alter table table_name move tablespace tablespace tbs_name; --dept table를 이동
alter index index_name rebuild tablespace tbs_name; --index는 rebuild
5. alter table emp allocate extent (datafile 'data_file_name' size 1M);
[Check point 발생시점]
1. Log switch 발생시
2. Database 종료시 (abort 제외)
3. Alter system checkpoint 명령시
4. alter system switch logfile 명령시
5. 테이블스페이스 offline시
6. 튜닝시 사용되는 초기화 파라미터의 조건을 만족할 때
log_checkpoint_interval = (OS block)*N (default 0)
log_checkpoint_timeout = sec. (default 1800))
fast_start_io_target = dirty block number (8i)
fast_start_mttr_target = sec (9i)
[오늘의 Oracle tips]
1. granule 크기 확인하기
select component, current_size, granule_size
from v$sga_dynamic_components --sga_max_size가 128M 이상은 16M, 그 이하는 4M 고정
2. v$cache를 만들어 주는 sql문 SQL>start ?/rdbms/admin/catparr.sql
3. 다량의 데이터를 복사하기
insert /*+append*/ into emp2 nologging as select * from emp;
잘먹음
비고)insert into emp2 nologging as select * from emp;
무시됨
4. 얼마나 많음 Full table scan이 일어나는 가?
select name, value
from v$sysstat
where name like '%table scans%';
5. Full table scan 작업의 진행률을 파악하는 sql
select sid, serial#, opname, to_char(start_time,'HH24:MI:SS') "start",
(sofar/totalwork)*100 "percent_complete"
from v$session_longops
where totalwork <> 0;
