ORACLE7 FOR WINDOWS NT TUNING GUIDE

Explanation
-----------
I. ORACLE7 RDBMS FOR WINDOWS NT의 구조

NT에서 동작하는 ORACLE7은 Microsoft 32 API를 사용하는 완전한 32bit
프로그램 으로서 하드웨어와 완전하게 결합되어 동작한다.
NT상의 ORACLE7은 NT의 메모리 모델에 맞도록 SINGLE PROCESS/MULTI
THREADED 구조를 갖도록 설계되었다.

UNIX에서 동작하는 ORACLE7과는 달리 SINGLE PROCESS상에서 MULTI THREAD
를 갖기 때문에 단일 주소 공간하에서 메모리를 공유하게 된다. ORACLE7은
NT가 제공하는 선점형 스케쥴링과 여러 개의 CPU를 최대한 활용하는 LOAD
BALANCING 기능을 활용하고 있다.

NT상의 ORACLE INSTANCE는 SGA(System Global Area)와 여러개의
BACKGROUND THREADS로 구성된다. SGA는 하나의 ORACLE INSTANCE를 위한 다양
한 정보를 갖고 있다. 최고의 퍼포먼스를 내기 위해서는 SGA는 주어진 메모
리 한도 내에서 충분한 크기를 갖어야 한다. 만약 SGA를 제외한 나머지 메모
리가 너무 작게 되면 PAGING과 SWAPPIG이 계속 일어나게 되므로 퍼포먼스를
크게 저하시키게 되므로 SGA 영역이 과도하게 커서도 안된다.

1)SGA는 다음과 같은 부분들로 이루어져 있다.

- DATABAES BUFFER CACHE

데이타 화일에서 읽어들인 데이타 블럭이 저장되는 곳으로 일종의
CACHE 역할을 한다. 디스크에 기록되기 이전의 데이타가 저장되는 버퍼
의 기능도 하는데 적절한 크기로 세팅함으로써 DISK I/O를 줄여서 퍼포
먼스를 증가시킬 수 있다.

- LOG BUFFER AREA

데이타 블럭의 이전 정보와 변경되고 난 후의 정보가 기록되는 곳이다.
LOG_BUFFER 파라미터가 이 영역의 크기를 결정한다(BYTE 단위). 이 영
역의 크기가 너무 작으면 LOG WRITER는 디스크에 WRITE 작업을 자주 해
야 하기 때문에 퍼포먼스가 저하하게 된다. MULTIPROCESSOR MACHINE에서
는 LOG_SIMLUTANEOUS_COPIES 파라미터를 CPU 갯수의 2배로 설정함으로써
REDO COPY LATCH에 대한 경합을 줄일 수 있다.

- SHARED POOL AREA

SHARED_POOL_SIZE(BYTE 단위) 파라미터로 그 크기가 결정되는데 SHARED
SQL/PLSQL 명령을저장하고 DICTIONARY CACHE 역할을 한다. SQL문의
PARSING 정보가 기록이 되므로 같은 명령은 REPARSING 없이 바로 실행될
수 있다.

- CURSORS

커서는 SQL 명령과 관련된 메모리 영역을 지시하는 HANDLE이다. 대부분
의 경우 커서는 내부적으로 생성되는데 직접 사용자가 커서를 생성하여
명령을 수행할 수도 있다.

2) BACKGROUND THREADS는 다음과 같이 이루어져 있다.

- DBWR(DATABASE WRITER) THREAD : DBWR는 BUFFER CACHE의 변경된 데이타
블럭을 디스크로 쓰는 작업을 한다.

- LGWR(LOG WRITER) THREAD: LGWR는 REDO LOG ENTRY를 디스크에 기록하는
작업을 한다.

- CKPT(CHECKPOINT) THREAD: LOG SWITCH가 일어나면 DBWR는 버퍼 내의 모
든 변경된 블럭을 디스크에 WRITE하며 CKPT는 CONTROL FILE과 DATA FILE
을 UPDATE한다. 만약 CKPT가 ENABLE되어 있지 않으면 LGWR가 CKPT의 기능
까지 함게 수행한다. 퍼포먼스 증대를 위해서는 CKPT 를 ENABLE 시켜 두
는 것이 좋다.

- SMON(SYSTEM MONITOR) THREAD: INSTANCE STARTUP시에 INSTANCE RECOVERY
를 수행한다.

- PMON(PROCESS MONITOR) THREAD: USER PROCESS가 비정상적으로 끊어지는
등 FAILURE가 발생했을 경우 PROCESS RECOVERY를 수행하여 점유한
RESOURCE 를 RELEASE한다.

- RECO(RECOVERY) THREAD: NETWORK/SYSTEM FAILURE 등으로 인해 발생하는
분산 트랜잭션의 PENDING 문제를 해결한다.

- ARCH(ARCHIVER) THREAD: ONLINE REDO LOG FILE이 재사용 되기 전에 다른
곳으로 복사하는 역할을 한다.


II. PERFORMANCE BOTTLENECK을 찾는 방법과 SYSTEM RESOURSE

시스템의 다음과 같은 부분에서 퍼포먼스 저하가 많이 발생하게 된다.

SYSTEM MEMORY, CPU, DISK I/O SUBSYSTEM, NETWORK

위의 사항들을 최적 상태로 튜닝함으로써 ORACLE의 퍼포먼스를 증대시킬
수 있다. 메모리는 그 크기가 한정되어 있으므로 PROCESS 들은 한정된 메모
리를 서로 이용하려고 하기 때문에 PAGING과 SWAPPING이 일어나는 경우가
생긴다. 여러명의 사용자가 동시에 디스크를 읽거나 쓰고자 할 경우 DISK
CONTENTION이 발생하게 된다. 마찬가지로 여러개의 프로그램이 CPU를 동시
에 사용하고자 하면CPU CONTENTION이 발생한다. PHYSICAL MEMORY가 부족하
게 되면 CPU TIME의 많은 부분이 PAGING 과 SWAPPING에 할당되게 되므로 퍼
포먼스가 저하하게 된다. 한정된 BANDWIDTH에 다다르도록 NETWORK TRAFFIC
이 증가하게 되면 NETWORK BOTTLENECK이 발생한다.

1) PERFORMANCE MONITOR를 통한 CPU BOTTLENECK 검사

PERFORMANCE MONITORING은 응용프로그램이 CPU TIME과 MEMORY와 같은 시스
템 리소스를 얼마나 사용하는 가를 측정하는 것을 말한다. 따라서
CPU/MEMORY BOTTLENECK을 알아보는데 있어서 매우 유용하게 사용되는 방법
이다.

CPU는 프로세스들간에 CONTENTION이 일어나는 가장 중요한 시스템 요소의
하나로 만약 시스템안에 CPU가 여러개가 존재한다면 다른 레벨의
CONTENTION이 일어날 것이다. PERCENTAGE PROCESSOR TIME 카운터는 하나의
PROCESS 내의 모든 THREADS가 명령을 수행하기 위해서 소요된 시간 백분율
을 알려준다. 만약 이 값이 85%를 초과하게 된다면 CPU에서 BOTTLENECK이
발생하고 있음을 알 수 있다.

PERCENTAGE PRIVILEGED TIME와 PERCENTAGE USER TIME의 합은 PROCESSOR
TIME 전체를 알려준다. PERCENTAGE PRIVILEGED TIME은 CPU가 응용프로그램
을 위해서 사용된 시간이 아닌 OS 를 위해서 사용된 시간을 나타낸다. 이
시간의 대부분은 디스크에서 데이타를 가져오고 저장하는데 사용된다.

WINDOWS NT는 각각의 SUBSYSTEM을 분리하기 위하여 PROCESS BOUNDARY를 사
용한다. 모든 응용프로그램 코드는 사용자의 SUBSYSTEM에 존재하며 % USER
TIME COUNTER를 통하여 알아볼 수 있다. 만약 시스템이 효율적으로 작동한
다면 대부분의 시간이 USER PROCESS에 할당되어 있을 것이므로 % USER TIME
이 매우 높은 값을 나타낼 것이다. 하지만 CPU를 많이 사용하는 작업을 수
행하고 있고, 이 COUNTER의 값이 높다면 응용프로그램의 튜닝이 요구된다.
이상의 사항들은 각각의 PROCESS와 THREADS 별로 CPU 사용현황을 알려주는
WINDOWS PERFORMANCE MONITOR를 통하여 알 수 있다.


III. ORACLE7 SERVER의 퍼포먼스에 영향을 끼치는 사항들

1) RAW PARTITION

ORACLE7은 I/O 작업이 많은 경우 퍼포먼스를 증가시키기 위하여
RAW PARTITION을 사용할 수 있다. RAW PARTITION은 FILE SYSTEM
OVERHEAD가 없으므로 I/O 성능이 우수하다.
그러나 RAW PARTITION은 FILE SYSTEM에 비해서 유연성이 떨어지
기 때문에 관리하는데 있어 많은 주의와 노력이 요구된다. 적절한
크기로 설정된 충분한 갯수의 RAW PARTITION을 확보하는 것이 쉬
운 작업이 아니기 때문에 RAW PARTITION을 이용하여 DATABASE
CONFIGURATION을 잡는 것은 까다로운 문제에 속한다. 하나의 데
이타 화일은 하나의 RAW PARTITION에 해당된다. 만약 RAW
PARTITION을 사용한다면 RAW PARTITION의 크기를 모두 같게 유지
함으로써 I/O BALANCING과 관리상의 유연성을 가질 수 있다.

2) ORACLE7 SERVER BACKGROUND THREAD PRIORITY 높이기

ORACLE7 SERVER와 BACKGROUND THREAD는 NORMAL PRIORITY CLASS
상태에서 수행되도록 세팅되어 있다. 이 CLASS에서는 SCHEDULLER
는 PROIRITY를 1에서 15 사이에서 동적으로 변화를 시킨다. 하지
만 16~31 사이에 해당되는 REAL-TIME PRIORITY CLASS로는 올라가
지 못한다.
ORACLE은 NORMAL PRIORITY CLASS 상태에서 가장 좋은
PERFORMNACE를 보인다는 사실이 알려져 있다. 이 경우는 주로
OLTP 와 같은 성격의 트랜잭션일 때에 대한 것으로 WINDOWS NT의
REGISTRY에 저장된 ORACLE_PRIORITY 파라미터를 변경함으로써
ORACLE7 PROCESS와 관련 THREADS 의 PRIORITY를 변경할 수 있다.
한편 각 BVACKGROUND THREAD의 PRIORITY를 하나씩 별도로 지정할
수도 있다. 하지만 모든 BACKGROUND THREADS의 PRIORITY를 동일
한 레벨에 설정하는 것이 가장 바람직하다. 만약 보다 높은
PRIORITY를 갖는 THREAD가 CPU를 요구할 경우 그보다 낮은
PRIORITY를 갖는 THERAD는 CPU를 할당 받지 못하게 된다.
ORACLE_PRIORITY 값을 조정하여 안정성에 문제가 발생하는 경우에
는 이 값을 다시 원래대로 돌려놓으면 된다.
오라클의 CONFIGURATION PARAMETER 는 REGISTRY 상의
\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 에 저장되어 있는데
REGIOSTRY EDITOR인 REGEDT32.EXE 이용하여 변경이 가능하다.
REGISTRY 변경 후에는 ORACLE7 의 SERVICE와 DATABASE를 다시 기
동하여야만 새로운 값이 적용된다.

3) OPTIMIZATION

WINDOWS NT SERVER의 최적화는 기본적으로 MAXIMIZE THROUGHPUT
FOR FILE SHARING으로 세팅되어 있다. 만약 시스템이 데이타베이
스 작업을 위해서 주로 사용된다면 CONTROL PANEL/NETWORK/SERVER
에서 MAXIMIZE THROUGHT FOR NETWORK APPLICATIONS 로 변경하면
된다.

WINDOWS NT SYSTEM CACHE는 DISK I/O를 위한 BUFFER를 가지고
있다. 32MB RAM을 가진 시스템에서는 NT는 보통 4MB 를 SYSTEM
CACHE로 할당한다. ORACLE7 DATABASE는 독자적인 BUFFERING
MECHANISM을 갖고 있기 때문에 DISK에 기록시 SYSTEM CACHE를
거치지 않게 된다. REGISTRY의
\HKEY_LOCAL_MACHINE\Control\Session Manager\Memory Management
에서 LargeSystemCache 값을 0으로 변경함으로써 WINDOWS NT OS
의 SYSTEM CACHE가 0이 되어 오라클에 맞게 시스템을 최적화 할
수 있다.

4) SERVICES

ORACLE7 DATABSAE와 관련된 NETWORK LISTENER 들은 WINDOWS NT의
SERVICE로 이루어져 있다. 사용되지 않는 SERVICE 들도 START 되
어 있으면 시스템에 부하를 주게 되므로 이들을 STOP 시킴으로써
퍼포먼스를 증가시킬 수 있다.

5) TUNING DISK I/O

- SEQUENTIAL I/O

REDO LOG FILE은 순차적으로 사용된다. 퍼포먼스를 증가시키려면
LOG FILE 들은 I/O가 별로 일어나지 않는 디스크에 두는 것이 좋
다. 그리고 ARHIVING THREAD가 기동되면 각각의 LOG FILE이 서
로 다른 디스크 상에 있어야지만 LGWR가 LOG FILE을 복사하거나
ARCHIVER THREAD가 CLOSED REDO LOG FILE을 읽을 때 DISK
CONTENTION이 일어나지 않는다.

- RANDOM I/O

OLTP 또는 DECISION SUPPORT 환경하에서는 I/O는 디스크와 디스
크 콘트롤러 상에 걸쳐서 골고루 일어나야 최고의 퍼포먼스를 얻
을 수 있다. RAID 0 DISK ARRAY를 사용하는 것이 관리도 편하고
I/O LOAD BALANCING도 잘 이루어진다고 알려져 있다.
DISK ARRAY CONTROLLER는 I/O의 LOAD BALANCE를 유지함으로써
디스크에 대한 병렬접근이 가능하게 한다. SQL*DBA의 MONITOR
FILE I/O는 ORACLE에 의해서 사용되는 모든 화일과 I/O 활동상황
에 관한 통계정보를 제공한다.
한 디스크에 대한 전체 I/O는 READ/S와 WRITE/S의 합으로 계산
된다. 각 디스크에 대한 이들 값을 계산한 다음 이를 디스크의
I/O CAPACITY와 비교하여 DISK CONTENTION 상태를 알 수 있다.

- 과도한 DISK FRAGMENTATION의 점검

화일 크기와 디스크 사용계획을 세울 때 앞으로 확장될 상황을 예
상하여 결정하여야 한다. 그렇지 않으면 데이타화일이 불연속적인
EXTENT 들로 구성됨에 따라서 I/O 에 많은 시간이 걸리게 된다.
DATABASE FRAGMENTATION은 퍼포먼스를 저하시키므로
EXPORT/IMPORT를 수행함으로써 FRAGMENTATION을 제거하여야 한다.
그리고 SORT_AREA_SIZE 파라미터가 작게 설정된 가운데 이 크기에
넘치는 소팅작업을 실행하게 되면 I/O가 많이 발생하여 퍼포먼스
가 저하된다.

6) 메모리 튜닝

- SGA 영역의 튜닝

DISK에서 데이타를 읽는 것 보다 메모리에서 읽는 것이 수천,수
만 배 빠르기 때문에 메모리에 대한 튜닝은 매우 중요하다.

SGA 영역은 반드시 PHYSICAL MEMORY 내에 존재해야 한다. 그렇지
않으면 PAGING에 의해서 퍼포먼스가 크게 저하된다. SQLDBA에서
SHOW SGA 명령을 통하여 SGA에 할당된 메모리를 확인할 수 있다.
INIT.ORA 에 PRE_PAGE_SGA=TRUE 라고 세팅함으로써 SGA 영역
을 항상 PHYSICAL MEMORY 상에 고정시킬 수 있다. 이는 DATABASE
의 STARTUP에 더 많은 시간을 요구하지만 DATABASE 사용중에 발
생하는 CACHE MISS를 줄여 준다.

- BUFFER CACHE의 튜닝

DB_BLOCK_BUFFER 파라미터는 시스템의 퍼포먼스에 큰 영향을 미친
다. 여기에는 TABLE,INDEX,ROLLBACK SEGMENTS 의 블럭내용을 보관
하고 있으며 HIT RATIO는 몇 퍼센트의 데이타가 이곳에서 사용되
었는 지를 알려주는 값이다. 계산방법은 다음과 같다.

HIT RATIO = (LOGICAL READS - PHYSICAL READS) / (LOGICAL READS)

BSTAT/ESTAT 스크립트를 수행함으로써 HIT RATIO을 알아낼 수 있
다. 만약 이 값이 0.95 이하라면 DB_BLOCK_BUFFER 값을 늘려주도
록 한다.

WINDOWS NT 상의 ORACLE7의 DB_BLOCK_SIZE 값은 기존적으로 2K
BYTES이다. 이 값은 WINDOWS NT의 PAGE SIZE와 같은 값이다. 가
능한 OS의 PAGE SIZE의 정수배로 DB_BLOCK_SIZE를 결정하는 것
이 바람직하다. 만약 응용프로그램이 OLTP 와 같은 작업 위주라면
DB_BLOCK_SIZE를 2K BYTES 로 하도록 하고 응용프로그램이 FULL
TABLE SCAN과 같은 작업을 많이 한다면 DB_BLOCK_SIZE를 8K
BYTES 정도로 키워주는 것이 퍼포먼스를 크게 증대시키는 요인이
될 수 있다.

- 사용자 메모리

사용자 프로그램에서 요구하는 메모리는 WINDOWS NT의
PERFORMANCE MONITOR나 PVIEW.EXE(WINDOWS NT RESOURCE KIT에 있
음)를 통하여 알아낼 수 있다.
응용프로그램이 실행되기 전과 후의 남아있는 메모리 양을 검사함
으로써 응용프로그램에서 사용하는 메모리양을 알아 낼 수 있다.
ORACLE의 SHADOW THREADS는 ORACLE WORKING SET에 할당된 메모
리를 사용한다. WINDOWS NT의 특성상 WORKING SET의 크기는 최대
256MB를 넘을 수 없도록 제한되어 있다.

- WINDOWS NT PAGING

WINDOWS NT상에서 오라클을 운영하기 위해서는 최소 32MB 이상의
메모리를 확보하는 것이 좋다. 만약 SGA 영역에 나무 많은 메모리
가 할당되어서 남은 메모리가 너무 적게 되면 PAGING이 발생하게
되며 과도한 PAGING/SWAPPING은 시스템의 퍼포먼스를 크게 저하
시키게 된다. 이 문제를 해결하려면 보다 많은 PHYSICAL MEMROY
를 갖도록 시스템을 확장하거나 아니면 SGA영역을 줄이거나, 동
시 사용자수를 줄여야 한다.

PAGE FAULTS/SEC은 PROCESS의 PAGE FAULT 횟수를 보여준다. PAGE
FAULT는 한 PROCESS가 자신의 PHYSICAL MEMORY내의 WORKING SET
에 들어있지 않는 PAGE(VIRTUAL MEMORY PAGE)를 요구하는 경우에
발생한다. PAGE FAULT가 발생하더라도 해당 PAGE가 STANDBY LIST
상에 있어서 MAIN MEMORY 상에 남아 있는 경우라면 실제의 PAGING
은 일어나지 않는다. 잘 튜닝된 시스템에서는 PAGING이 거의 일
어나지 않아야 한다.

7) 지원되는 SQL*NET PROTOCOLS

WINDOWS NT 상의 ORACLE에서 지원되는 NETWORK PROTOCOL로는
TCP/IP, NAMED PIPES, SPX/IPX 등이 있다. CLIENT/SERVER 환경하
에서는 TCP/IP 를 NETWORK TRANSPORT로 사용하는 경우 가장 좋은
퍼포먼스를 얻을 수 있다고 알려져 있다.

8) BENCHMARKING TIPS

- LOADING DATA

SQL*LOADER를 사용할 때에는 DIRECT PATH OPTION을 사용하도록
한다.

- PROGRAMMATIC INTERFACE

OCI(ORACLE CALL INTERFACE)는 PRO*C 에 비해서 ORACLE에 대한
보다 섬세한 제어가 가능하다. 만약 OCI를 사용한다면 DEFERRED
LINKING과 PARSING을 사용하면 좋은데, 이 OPTION은
CLIENT/SERVER 환경에서 NETWORK TRIP을 줄여줌으로써 퍼포먼스
를 높여주게 된다.

- CLIENT APPLICATION의 튜닝

INDEX 생성을 빠르게 하려면 SORT_AREA_SIZE 값을 키워주면 된다.
게다가 SORT 작업과 SORT/MERGE 작업 또한 PHYSICAL MEMORY상에
서 일어나게 되므로 DISK I/O를 줄여주는 장점도 있다.
DISCRETE_TRANSACTIONS_ENABLED=TRUE로 세팅하면 길이가 짧고 분
산 트랜잭션외의 다른 트랜잭션의 퍼포먼스를 증대시켜 준다. 이
OPTION을 사용하면 트랜잭션이 COMMMIT 명령을 만나기 전까지 모
든 변경된 데이타가 저장되지 않고 기다리게 된다. 그리고 UNDO
INFORMATION이 발생하지 않는다.
STORED PROCEDURE, DATABASE TRIGGER, ARRAY PROCESSING 또한
NETWORK TRAFFIC을 줄여주는 등 퍼포먼스 증가에 큰 영향을 줄
수 있다.

9) DIAGNOSTIC TOOLS

EXPLAIN PLAN과 TKPROF 유틸리티를 사용하여 OPTIMIZER의
EXECUTION PLAN을 점검할 수 있다. COST-BASED OPTIMIZER가 디폴
트 이므로 이를 사용하는 것이 좋다. OPTIMIZER는 해당 OBJECT에
관련된 통계정보를 이용하여 최적의 EXCUTION PATH를 선정하여
준다.

10) ROLLBACK SEGMENTS

OLTP TRANSACTION의 특징은 처리건수는 많으나 TRANSACTION 하나
하나의 크기는 작다는 데 있다. 이때에는 각 TRANSACTION에 10K
BYTES 정도의 매우 작은 ROLLBACK SEGMENT 를 할당하는 것이 매우
효과적이다. 한편으로는 BENCHMARKING APPLICATION과 같이 큰 데
이타를 다루는 경우라면 역시 ROLLBACK SEGMENT도 큰 것을 사용
하는 것이 좋다.

from otn