[ORACLE] ORACLE TUNING의 단계

ORACLE TUNING의 단계

1. 병목 현상의 분석
---> 메모리 경합, 디스크 I/O의 경합
2. ORACLE 제품의 설치 및 환경의 구성
3. APPLICATION의 TUNING
4. 데이타 억세스의 TUNING/SQL statement의 TUNING
---> tkprof및 execution plan을 사용
5. 메모리 관리의 TUNING
---> DATABASE BUFFER와 REDO BUFFER 수의 TUNING
DATA DICTIONARY CACHE MEMORY TUNING
SWAP OUT/PAGE_OUT를 감소시키는 방향으로
6. 디스크 I/O의 TUNING
---> I/O및 APPLICATION의 분산
WRITE수의 TUNING
FRAGMENTATION의 CHECK
7. CPU사용의 TUNING
8. RESOURCE경합의 TUNING

1. ORACLE 제품의 설치 및 환경의 구성

동적 SPACE 관리를 피한다
TABLE SPACE에 대한 정보를 알아보기
---> SQL> select * from dba_tablespaces;
DATA BLOCK내의 SPACE할당을 위해 PCTFREE를 조정한다
PCTINCREASE는 일반적으로 0으로
NEXT는 보통 INITIAL과 같도록
4개의 트랜잭션마다 각각 하나의 ROLLBACK SEGMENT를 작성

2. APPLICATION TUNING

주의. 되도록 이면 VARCHAR2 type을 사용할 것
LONG type은 되도록 이면 다른 DATA와 분리하여 저장하도록 한다
되도록이면 SQL문을 재사용하도록 한다.
(결합 변수상에 각기 다른 값을 할당할 경우 유리,
공용 라이브러리 사용 및 코드의 공용화)

3. DATA ACESS TUNING

SQL TRACE의 SETUP요령
a. SQL*PLUS상에서
SQL> ALTER SESSION SET SQL_TRACE=TRUE ;
또는 INIT.ORA화일에서 SQL_TRACE=TRUE로
b. SQLFORMS30, RUNFORMS30상에서
$ runform30 -s frmfile_name scott/tiger -c vt220
c. PRO*C상에서
EXEC SQL CONNECT :username ;
EXEC SQL ALTER SESSION SET SQL_TRACE=TRUE ;
d. REPORT/WRITER에서
별도의 report column을 만든다.
============================================
COLUMN SOURCE
-------------------------------------------------------------------------
--
TRACE &sql ALTER SESSION SET
SQL_TRACE=TRUE ;
-------------------------------------------------------------------------
--
TRACE FILE이 생성되는 위치
SQLDBA> show parameters user_dump_dest ;
여기에서 나타나는 위치에 .trc라는 화일로 생성된다.
주의. CPU사용량에 대한 정보는 INIT.ORA화일의 parameter
TIMED_STATISTICS=TRUE로 설정이 되어 있어야 얻을 수 있다.
주의. 여기에서 생성되는 *.trc화일을 읽기 위해서는 읽기 쉬운 format으로 변
환하여야 한다.(tkprof)

EX> $ tkprof ora_*.trc test
$ tkprof ora_*.trc test sort=fchqry,fchcu explain=username/passwd
print=20
test 출력되는 *.prf 화일명
sort 지정된 옵션의 오름차순으로 소트하라
explain EXECUTION_PLAN을 발생하라
print 지정된 갯수의 SQL문에 대해서만 TRACE결과를 PRINT하라

===========================================================
파라미터 의미
===========================================================
COUNT OCI procedure가 실행된 횟수
CPU CPU의 실행 시간(초)
ELAPSED EXECUTING에 경과된 시간
DISK PHYSICAL READ의 횟수
QUERY CONSISTENT READ에 의해 소요된 BUFFER의 수
CURRENT 현재의 MODE에서 소요된 BUFFER의 수
ROWS FETCH or EXECUTE CALL에 의해 수행된 ROW의 수
===========================================================

여기에서,
EXECUTE COUNT와 FETCH COUNT가 동일하게 크면 ARRAY FETCH를
사용할 필요가 있다.
FETCHED ROW의 수와 QUERY + CURRENT의 비율이 1:4이하이면
적절한 SQL문이라고 할 수 있다.
만약 1:4이상이라면
INDEX의 사용, 구성 여부
ROWID의 사용
COST_BASED OPTIMIZER의 사용
ARRAY_FETCH의 사용
SORTING을 피할 수 있는 SQL문의 구사
등을 고려해 보아야 한다.
PARSE_COUNT와 EXECUTE_COUNT가 비슷한 경우에는
RELEASE_CURSOR, HOLD_CURSOR option을 사용하도록 한다
QUERY(CR)대 ROWS의 비율이 1.5이상인 경우에는 추가적인 주의가 필요하다.
데이타베이스의 전체적인 성능상의 통계
===> $ $ORACLE_HOME/rdbms/admin/utlbstat.sql
$ $ORACLE_HOME/rdbms/admin/utlestat.sql을 사용

4. MEMORY TUNING

데이타베이스 버퍼 캐쉬의 최적수를 구하는 방법
SQLDBA> select 250*TRUNC(indx/250) +||' to'||
250*TRUNC(indx/250) + "interval" ,
SUM(count) "cache hits"
from SYS.X$KCBRBH
group by TRUNC(indx/250) ;
===> 이 질의문을 통해 최상의 cache hits를 나타내는 buffer의
수 만큼을 추가한다.
데이타 딕셔너리 캐쉬의 최적화
재귀호출이란 ?
사용자 SQL문의 실행시 RDBMS에 의해 작성된 SQL문을
말한다.
SQLDBA> select PARAMETER, COUNT, USAGE, GETS, GETMISSES
from V$ROWCACHE ;
===> 이 질의문을 통해 V$ROWCACHE테이블내의 어느 특정행이 COUNT값보다 휠
씬 낮은(80%정도) USAGE값을 가지고 있는
경우 캐쉬의 수를 감소시켜야 한다.
===> 이 질의문을 통해 GETMISSES대 GETS의 비율이 10--15%를
초과하는 경우 해당 캐쉬내의 엔트리수를 증가하여야 한다.
SWAPPING
전체 프로세스를 디스크로 이동시켜 공간을 확보하는 기능으로서
SWAPPING은 최소화되어야 한다.
$ sar로 확인
===> 불필요한 daemon process와 application process를 실행
하지 말 것
===> 데이타베이스 버퍼의 수를 감소시켜 일부 메모리를 해제
PAGING
프로세스 전체의 이동을 요구하는 SWAPPING과는 달리 PAGING은 메모리의 재확
보를 위해 필요한 프로세스의 해당 페이지 만을
디스크로 이동시킨다.
$ sar -p 또는
$ vmstat로 확인(PAGING을 모니터링한다)
주의. SGA의 규모가 지나치게 큰 경우는 다른 오라클 프로세스 혹은 비 오라
클 프로세스가 사용할 수 있는 메모리의 양이 감소되어 SWAPPING또는 PAGING현
상이 증폭된다.
주의. SGA의 규모가 지나치게 작은 경우는 다른 다른 오라클
프로세스 혹은 비 오라클 프로세스가 사용할 수 있는
메모리의 양은 증가하나 데이타베이스의 I/O가 증가하게 된다.
충분한 SWAPPING공간의 할당
사용된 SWAPPING영역을 알아보기 위해서는
$ pstat -s
$ swap -l 로 확인
지금 현재 사용하고 있는 공용 메모리의 확인
===> $ tstshm 으로 확인
공용 메모리의 세그먼트 수
가상 메모리내의 세그먼트의 위치
단일 세그먼트의 최대의 크기
참고. 공용메모리의 상태를 감시
===> $ ipcs 로 확인
라이브러리 캐쉬의 할당
SQLDBA> select SUM(pins) "Executions" ,
SUM(reloads) "Cache misses while Excuting"
from V$LIBRARYCACHE ;
===> 이 질의문에서
SUM(reloads)는 0에 가까와야 하며 비율 역시 1% 이내
이어야 한다.
===> 라이브러리 캐쉬에 대해 메모리를 더 할당하려는 경우에는
INIT.ORA화일의 SHARED_POOL_SIZE를 증가시켜야 한다.

5. DISK I/O TUNING

REDO LOG를 별개의 디스크 디바이스상에 놓아두도록 하라
디스크수에 해당하는 DB_WRITERS를 할당(DBWR의 수 = 디스크의 수 * 2)
오라클은 LIST I/O와 ASYNCHRONOUS I/O를 사용
a. LIST I/O
다수의 I/O요구를 단일 리스트에 위치시켜 마치 하나의 I/O요구와 같이 처리하
는 기능
LIST I/O기능은 디스크 I/O실행에 필요한 CONTEXT SWITCHES의 수를 감소 시킨
다.
b. ASYNCHRONOUS I/O
하나의 기록이 수행된 후 프로세스가 대기할 필요없이
다음 오퍼레이션을 진행하도록 해주는 메카니즘이다.
ASYNCHRONOUS I/O가 사용되는 경우 오라클은 다중
DBWR를 사용하지 않고 병렬 기록을 행할 수 있도록 해준다.
만일 ASYNCHRONOUS I/O를 사용하는 경우 하나의 DBWR가
사용되도록 해야 한다.
REQUEST_QUEUE가 지나치게 큰 것을 찾도록 하라
요구열이 지나치게 큰 경우 동일한 디스크를 억세스하고자 하는
I/O의 수가 지나치게 많음을 의미한다.
디스크 처리활동을 분석하기 위해서는 $ sar -d 또는 $ iostat를
사용한다.
스트립 기능 ?
큰 테이블의 데이타를 작은 부분으로 나누어 각기 다른 데이타 화일내에 저장
하는 기능이다.
스트립 기능은 다수의 프로세스가 디스크 경합을 초래하지 않고
해당 테이블내의 각기 다른 부분을 동시에 억세스할 수 있도록 해준다.
BUBBLING BLOCK ?
동적 익스턴트 사이에 위치하는 익스턴트의 DROP으로 인해 서로
인접하지 않은 자유 공간에 작은 버블이 생기는 현상
HONEYCOMBING ?
인접한 익스턴트의 DROP이 발생할 때마다 자유 공간이 인접한
부분들로 나누어 진다.

6. CPU USAGE TUNING

$ iostat 를 사용하여 CPU부하를 모니터할 수 있다.
==============================================
파라미터 의미 적정 비율
==============================================
USR 사용자 60%
SYS 시스템 20%
WIO I/O대기 10%
IDLE 유휴상태 10%
==============================================

7. 자원 경합의 TUNING

ROLLBACK SEGMENT내의 SPACE요구가 처리 지연을 야기하는 빈도를 알아 보기 위
해 다음의 SQL명령을 사용할 수 있다.
SQLDBA> select NAME, GETS, WAITS, ((GETS - WAITS)* 100) /GETS
HITS
from V$ROLLSTAT s, V$ROLLNAME n
where s.USN = n.USN ;
===> ROLLBACK SEGMENT의 경합을 줄이는 방법은 ROLLBACK SEGMENT의
추가밖에 없다.

버퍼의 억세스는 LATCH에 의해 규제된다.
SQLDBA상에서 MONITOR LATCH를 통해 LATCH의 경합을 알아볼 것
totals : 해당 LATCH의 총 요구수
timeouts : LATCH실행을 위한 대기 시간동안의 TIMEOUT수
===> timeouts대 totals의 비율이 10 -15%를 초과하는 경우 해당 LATCH에 대
한 경합이 성능 저하를 가져온다.
a. REDO LATCH의 경합을 줄이기 위한 방법
===> INIT.ORA화일의 LOG_SMALL_EXTRY_MAX_SIZE
파라미터를 줄인다.
(단일 프로세스가 LATCH를 처리하는 시간을 최소화
시킨다)
b. 다중프로세스 환경에서 REDO LATCH의 경합을 줄이기 위한
방법
===> LOG_SIMULTANEOUS_COPIES의 값을 증가 시킨다.
(LATCH를 추가한다)

8. 병렬 서버의 자원 경합 TUNING

인덱스 경합의 TUNING
LOCK경합의 TUNING
===> LOCK 경합은 V$SYSSTAT테이블의 필드에 의해 측정된다.
lock_conversion_ration =
consistent get - async lock converts
-------------------------------------
consistent gets
===> 이 비율이 95%이상이 되어야 한다.

* 병렬 서버 상에서 경합의 모니터링
V$SESSION_WAIT
V$SYSTEM_EVENT
V$SQL_AREA
V$CACHE
V$PING