[오라클 튜닝] instance 튜닝1

 

[오라클 튜닝] instance 튜닝1

SQL PLAN 테이블에서 확인

SQL 문이 실행되면서 공유 풀 영역에 저장된다.( hash_value, address 확인

select sql_text, hash_value, address from v$sqlarea where sql_text like 'select * from dept%';

sql plan 확인

 

set linesize 180
col id format 999 newline
col operation format a20
col options format a15
col object_name format a22 trunc
col optimizer format a3 trunc

 

select id, lpad('  ', depth) || operation operation, options, object_name, optimizer, cost from v$sql_plan where hash_value = &1 and address = '&2' start with id = 0 connect by (prior id = parent_id and prior hash_value = hash_value  and prior child_number = child_number) order siblings by id, position;

* 라이브러리 캐쉬 확인
- 히트율(gethitratio) 확인 => 90% 이하이면 튜닝 검토
select namespace, gets, gethits, gethitratio from v$librarycache where namespace = 'SQL AREA';

 

- Reload 비율 확인 => 1% 이상이면 튜닝 검토
select sum(pins), sum(reloads), sum(reloads)/sum(pins) from v$librarycache where namespace = 'SQL AREA';

 

- 객체 변경 빈도 확인(analyze, alter, drop) => invalidations 값이 계속 증가하면 튜닝 검토
select invalidations from v$librarycache where namespace = 'SQL AREA';

 

- 데이터 딕셔너리 캐쉬 확인 => 2% 이상이면 튜닝 검토
select sum(gets), sum(getmisses), round(sum(getmisses)/sum(gets), 2) miss_ratio
from v$rowcache;

* PL/SQL 캐싱
- 조회
select name, type, kept  from v$db_object_cache where type in ('PACKAGE', 'PROCEDURE', 'TRIGGER', PACKAGEBODY') and owner = 'SCOTT' and kept = 'YES';

 

- 등록
execute DBMS_SHARED_POOL.KEEP('SCOTT.CHECK_SAWON');

* 데이터 버퍼 캐쉬 확인 => 90% 이하이면 튜닝 검토
select phy.value phy, lob.value lob, dir.value dir, ses.value ses, round((phy.value-lob.value-dir.value)/ses.value,

 "CACHE HIT RATIO" from v$sysstat phy, v$sysstat lob, v$sysstat dir, v$sysstat ses
 where phy.name = 'physical reads' and lob.name = 'physical reads direct (lob)'
 and dir.name = 'physical reads direct' and ses.name = 'session logical reads';

* 멀티 버퍼 캐쉬 영역 확인
select name, round((physical_reads/(db_block_gets+consistent_gets)), " Multiple Ratio" from v$buffer_pool_statistics
where db_block_gets+consistent_gets > 0;

 

-> KEEP : 100%에 가까울수록 최적
-> RECYCLE : 50%가 최적
-> DEFAULT : 90% 이상이 가장 최적

* 로그 버퍼 캐쉬 확인
- 경합여부 확인 => state값이 waiting이면 경합 발생
select sid, event, seconds_in_wait, state from v$session_wait where event = 'log buffer space%';

 

- 버퍼 할당 비율 확인 => 90% 이하이면 튜닝 검토
select redo.value redo, redo_alloc.value redo_alloc, round(redo_alloc.value/redo.value, 2) "ALLOC RATIO"
from v$sysstat redo, v$sysstat redo_alloc where redo.name = 'redo entries' and redo_alloc.name = 'redo buffer allocation retries';

* 리두로그 래치 확인 => 1% 이상이면 튜닝 검토
- WILLING_TO_WAIT 타입

select b.name, round(decode(a.gets,0,0,a.misses/a.gets),2) "MISSES/GETS" from v$latch a, v$latchname b
where b.name in ('redo allocation', 'redo copy') and b.latch# = a.latch#;

 

- IMMEDIATE 타입
select b.name, round(decode(a.immediate_gets,0,0,a.immediate_misses/a.immediate_gets),2) "MISSES/GETS"
from v$latch a, v$latchname b where b.name in ('redo allocation', 'redo copy') and b.latch# = a.latch#;

* Free 블록 경합
- 세그먼트 확인 => count, time 값이 계속 증가하면 INSERT문 성능 저하
select class, count, time from v$waitstat where class = 'segment header';

 

- 경합 발생하는 객체 찾아 튜닝

 

1) FILE과 블록 주소 확인

 

select event, p1, p2, p3 from v$session_wait where event = 'buffer busy waits';

 

2) 테이블 확인
select * from dba_extents where file_id = [p1] and [p2] between block_id and block_id+blocks-1;

3) 해당 테이블의 FREE-LIST 개수 확인
select segment_name, freelists from dba_segments where segment_name = [segment명] and segment_type = [타입];

 

4) FREE-LIST 개수 늘림
alter table [테이블명] storage (freelists [n]);

* DBWR 백그라운드 프로세스 경합 확인
- 조회
select total_waits, time_waited, average_wait from v$system_event where event in ('write complete waits', 'free buffer waits');

 

=> total_waits 값이 계속 증가하면 튜닝 검토
-> show parameter db_writer_processes;
-> alter system set db_writer_processes = 8;