튜닝(tuning)이란 불필요한 일들을 최소화하는 것이다.
보다 적은 노력으로 많은 결과를 내고자 하는 활동.
1. 튜닝(Tuning) Overview
최적화 방법
대부분 Cost-Based 방식이 Rule-Based 방식보다 좋은 결과를 내나 사용자가직접 SQL 연산을 튜닝할 때는 Rule-Based 방식이 더 좋은 결과를 낸다.
Rule-Based 방식
Rule-Based 방식은 Cost-Based 방식에 비해 간단하다.
여러 개의 가능한 PATH를 찾아 이미 정해져 있는 Rank를 기준으로 서로의 Cost를 비교하고, 이를 토대로 가장 효율적인 것을 선택한다.
1. ROWID에 의한 단일 행 접근
문장의 WHERE절에 ROWID나 오라클 Precompiler에서 지원하고 있는 확장된 SQL 구문 구조의 CURRENT OF CURSOR에 의해 지정된 Row들이 식별될 수 있을 경우에만 사용이 가능. 문장을 실행하기 위해 ROWID에 의해 테이블을 검색한다.
2, 클러스터 조인에 의한 단일 행 접근
- 문장의 WHERE절에 한 테이블의 클러스터 키의 각 칼럼과 그에 대응하는 다른 테이블의 컬럼을 같게하는 조건절을 가지고 있어야 한다.
- 문장의 WHERE절에 조인이 단지 하나의 행을 리턴하는 것을 보증하는 조건절을 가지고 있어야 한다.
이런 조건은 Unique 하거나 Primary Key인 컬럼의 '=' 조건절과 유사하다.
위 두 조건이 만족할 경우에 사용.
반드시 AND 연산자를 통하여 합쳐져야 한다.
3, Unique Key 또는 Primary Key를 사용하는
Hash Cluster Key에 의한 단일 행 접근
- 문장의 WHERE절이 '='조건절에 해시 클러스터 키의 모든 컬럼을 사용하여야 한다. 합성된 클러스터 키인 경우는 '=' 조건절이 반드시 AND 연산자에 의해 연결되어야 한다.
- 문장은 컬럼이 해시 클러스터 키를 만들 듯이 유일성이나 기본키를 만들어내기 때문에 단지 하나의 데이터 행만을 리턴한다.
4, 유일키 또는 기본키에 의한 단일 행 접근
문장의 WHERE 절이 '=' 조건절에서 컬럼들의 유일성이나 기본키를 사용할 경우 사용될 수 있다. 복합된 키들에 대해 '=' 조건절은 AND 연산자를 통해 연결되어야 한다.
이런 문장을 실행시키기 위해 오라클은 단일 ROWID를 구하기 위해 유일성이나 기본키의 인덱스를 이용해 유일 검색을 한다. 그리고 나서 이 ROWID를 이용해 테이블을 액세스한다.
5, 클러스터 조인
두 Join 테이블이 같은 Cluster에 저장되고, 문장의 WHERE절에 한 테이블의 각 칼럼에 대해, 대응하는 테이블의 칼럼을 같게 하는 조건절을 포함하는 경우 사용될 수 있다. 복합 key에 대해 '=' 조건절은 반드시 AND 연산자에 의해 연결되어야 한다.
6, 해시 클러스터 키
문장의 WHERE절에 Hash Cluster Key인 칼럼의 '=' 조건절이 있을 경우 사용되어질 수 있다. 복합 Cluster Key에 대해 '=' 조건절은 반드시 AND 연산자로 연결되어야 한다. 이런 문장을 실행하기 위해 Oracle은 문장에 기술된 Hash Cluster Key 값을 Cluster의 Hash Function에 적용해서 Hash 값을 구한다. 그리고 나서 Oracle은 이 Hash 값을 이용해 테이블에 대한 Hash Scan을 수행한다.
7, 인덱스 클러스터 키
문장의 WHERE절에 Indexed Cluster Key인 칼럼의 '=' 조건절이 있는 경우 사용될 수 있다. 복합 Key에 대해서는 '=' 조건절은 반드시 AND 연산자로 연결되어 있어야 한다. 이런 문장을 실행하기 위해 Oracle은 Cluster Key에 의해 서술된 Row의 ROWID를 구하기 위해 Cluster Index에 대한 Unique Scan을 수행한다. 그리고 나서 Oracle은 이 ROWID과 Cluster Scan을 이용해 테이블을 액세스한다. 같은 Cluster Key 값을 가지는 모든 Row들이 같은 곳에 저장되기 때문에 Cluster Scan은 그 Row들을 찾기 위해 단지 하나의 ROWID만이 필요하다.
8, 복합 인덱스
WHERE 절에 복합 index 칼럼들의 '=' 조건절이 AND 연산자에 의해 연결 되어있는 경우에 사용될 수 있다. 이런 문장을 실행하기 위해 Oracle은 Index 대한 Range Scan을 수행하여 선택된 Row들의 ROWID를 구하고 이것을 이용해 테이블을 액세스한다.
9, 단일 컬럼 인덱스
문장의 WHERE 절에 하나나 그 이상의 단일 칼럼 Index의 '=' 조건절이 있는 경우에 사용되어질 수 있다. 여러 개의 단일 칼럼 Index에 대해 조건절은 반드시 AND 연산자에 의해 연결되어야 한다.
만약 WHERE 절에 단지 하나의 Index 칼럼을 있을 경우 Oracle은 선택된 Row들의 ROWID를 구하기 위해 Range Scan을 수행함으로써 그 문장을 실행한다. 그리고 나서 구해진 ROWID를 이용해 테이블을 액세스한다.
10, 인덱스 컬럼에서의 바운드 범위 조회
문장의 WHERE 절에 단일 칼럼 Index나 하나나 그이상의, 복합 Key의 한 중요한 부분이 되는 칼럼이 포함된 경우 사용될 수 있다.
11, 인덱스 컬럼에서 언바운드 범위 조회
문장의 WHERE절에 단일 칼럼 Index의 칼럼이나 하나나 그이상의 , 복합 Index의 한 중요한 부분이 되는 칼럼 중 하나의 조건절을 가지고 있을 때 사용 가능하다.
12, 소트 병합 조인
Join 테이블이 같은 Cluster에 저장되지 않고, 문장의 WHERE절에 있는 조건절이 Equailty 조건절이라면, 이 접근 경로가 사용될 수 있다. 이러한 문장을 실행하기 위해 Oracle은 Sort-Merge 연산을 사용한다.
13, 인덱스 컬럼의 최대 또는 최소값
다음의 조건을 만족하는 SELECT 문장에 사용될 수 있다.
- Query에 Single-Column Index나 Composite Index가 있는 칼럼의 최대값이나 최소값을 구하기 위해 MAX나 MIN 함수가 사용되는 경우. 여기서 Cluster Index는 제외.
MAX와 MIN함수의 argument는 칼럼, 상수, + 연산자, || 연산자나 CONCAT 함수 등을 포함하는 어떠한 수식도 올 수 있다.
- Select 절에 다른 수식이 오지 않아야 한다.
- 문장은 WHERE절이나 GROUP BY 절이 없어야 한다.
이런 Query를 실행하기 위해서 Oracle은 Index의 Range Scan을 이용해 최대값이나 최소값을 찾아낸다. 최대값이나 최소값을 찾게된다면 Oracle은 더 이상의 Scan은 하지 않는다.
14, 인덱스 컬럼에서의 ORDER BY 사용
다음의 조건들을 만족하는 select 문에 사용될 수 있다.
- Query에 ORDER BY절이 있어야하고, 그 ORDER BY절에 사용되는 칼럼은 Single-Column Index가 있거나, Composite Index의 Leading Portion이어야 한다.
Cluster Index는 제외.
- 그리고 PRIMARY KEY나 NOT NULL Integrity가 그 Index된 칼럼에 있어야 한다. ORDER BY절에 사용된 칼럼에 NULL이 있어서는 안 된다는 말이다.
- NLS_SORT 매개변수가 BINARY로 설정되어야 한다.
이런 Query를 실행하기 위해 Oracle은 Index의 Range Scan을 사용하여 정렬된 순서에서 해당 Row들의 ROWID들을 구한다. 그리고 나서 Oracle은 이 ROWID들을 이용해 해당 Row를 액세스한다.
15, 테이블의 전체 검색
문장의 WHERE 절과 상관없이 모든 SQL문장에 사용될 수 있다.
Rule-Based 방식의 실행계획
- 수행 가능한 실행계획을 구한다.
- Rank Table에 따라서 순위를 결정한다.
- 가장 낮은 순위를 선택하여 수행한다.
Cost-Based 방식의 실행계획
- Rule-Based 방식이 실행계획을 구하는 것과 같은 방식으로 실행계획을 구한다.
- 각 실행 계획에 대한 Cost를 구한다.
Cost는 실행계획에 사용할 CPU Time, I/O 그리고 메모리를 기준으로 계산한다.
- Cost를 비교해서 가장 낮은 실행계획을 선택하여 수행한다.
ANALYZE 명령어
Cost-Based 방식에 사용할 통계데이터를 모아 준다.
ANALYZE 명령어는 수행될 때마다 다음의 정보를 수정
TABLE
- 테이블의 행수, 사용된 블록 수, 한번도 사용되지 않은 블록 수, 사용 가능한 Space의 평균, Chained Row 수,
컬럼당 Distinct Value의 수, 컬럼당 두 번째로 가장 작은 값, 컬럼당 두 번째로 가장 큰 값.
INDEX
- Index Level, Level 블록 수, Distinct Key 수,
키당 Leaf 블록 수의 평균, 키당 Data 블록 수의 평균, Clustering Factor, 최소 키 값, 최대 키 값.
Hints 사용
Optimizer는 데이터베이스 객체에 대한 얼마나 많고, 정확한 데이터를 가지고 있는가에 대한 의존도가 높아
Optimizer가 더욱 효과적으로 SQL 연산을 수행하도록 사용자가 사용한다.
Hint를 통하여 Optimizer에게 알려 줄 수 있는 사항
- SQL 연산을 위한 Cost-Based 접근 방식의 목표,
접근방식 지정, Index 보다 더 효과적인 Scan 방법, Join 순서, 병렬연산 등급, Join 연산.
Syntax
/* + comment */
comment를 Optimizer에게 알려 준다.
Hint 기능 사용시 Syntax나 기타 오류가 발생하면 Optimizer는 오류 메시지를 사용자에게 전달하지 않고 이를 무시하고, 연산을 수행한다.
SQL TRACE
SQL을 튜닝하기 위해 현재의 SQL 문장의 수행 상태를 정확히 파악해야 하고 튜닝 작업이 의도한 대로되었는지, 효과는 어느 정도인지 알아 볼 필요가 있을 때 SQL Trace를 사용한다.
SQL Trace를 이용해 얻을 수 있는 정보(V$SESSION)
- CPU TIME, 총 수행 시간, 물리적 또는 논리적 I/O, Library Cache, Parse Count, Execute Count, Fetch Count, 수행 Recode 수.
SQL Trace의 초기화
초기 파라미터
TIMED_STATISTICS, MAX_DUMP_FILE_SIZE
USER_DUMP_DEST
세션 초기화
ALTER SESSION SET SQL TRACE = TRUE
사용중인 애플리케이션의 SQL 튜닝
분석 및 오류 찾기
Index
Index 는 Select 연산의 성능을 높이는 요소이나 Update와 Insert 시에는 부하로 작용한다.
현재의 Table에서 Index 들이 정확하게 생성되어 제기능을 다하고 있는지 확인해야 한다.
또한 EXPLAIN PLAN 기능을 사용하여 Index 들이 올바르게 사용되고 있는지 확인해야 한다.
동시 사용자
개발 단계에서 동시 사용자를 생각하지 않을 수 도 있고 Test 해볼 환경이 안될 경우도 있다.
많은 동시 사용자가 하나 혹은 두 개의 Table에 집중되어 있을 경우 개발단계에서는 발견되지 않았던 자원의 경쟁이 일어난다.
이 경우 Disk I/O 집중화 현상이 일어나는 데 우선 여러 Disk에 걸쳐서 데이터 파일을 구성하여 I/O를 분산시키고 크기가 작은 Table은 Table 생성을 다시 하여 블록 당 레코드 수를 줄이는 방법을 사용한다.
병목 현상 찾기
병목 현상을 일으키는 SQL 연산을 찾는 것은 힘든 일이다.
EXPLAIN PLAN 기능을 사용하여 CPU Time, 총 수행 시간, SQL 최적화 상태를 파악하고 액세스 경로를 변경하거나 조회되는 레코드 수를 줄이는 방법 등을 찾는다.
애플리케이션 개발과정의 SQL 튜닝
INDEX
- Index 사용을 고려해야 하는 Table의 특성
Select 연산 결과가 전체 Record 수의 5% 이내인 큰 Table, 중복된 데이터가 적은 Table, Insert, Update, Delete 연산이 적은 Table, Query 수행시 조건이 간략한 Table.
- Index 사용을 피해야 하는 Table의 특성
Select 연산시 결과가 전체 Record 수의 5% 이상인 Table, 중복된 Data가 많은 Table, Select 연산이 주로 일어나는 Table, Query 수행시 조건절이 매우 복잡한 Table.
CLUSTER
장점은 하나의 I/O 연산으로 여러 Table의 정보를 액세스 할 수 있다는 것이다 그러나 Index와 같이 성능을 저하시킬 수 있다.
- Cluster를 사용하지 않아야 할 경우
Insert 연산이 많은 경우, Cluster 구성 Table 중 하나의 Table에 Full Scan이 자주 일어나는 경우, Cluster 구성 Table들이 동시에 액세스되지 않는 경우가 많을 때.
HASH CLUSTER
데이터 블록을 액세스하기 위해 여러 번의 내부 절차를 거쳐야 하는데 해시 클러스터는 한번에 그 일을 다하기 때문에 속도가 가장 빠르다.
그러나 해시 키값이 변하지 않아야 하고 Table이 정적이어야 한다는 단점 때문에 많이 사용하지 않는다.
JOIN
오라클 시스템 자원을 많이 소비하는 연산이다.
- EQUIJOIN
조건절 Equality Condition(=)에 의해 조인이 이루어진다.
Equijoin의 성능을 높이기 위해서는 Index 기능을 사용해야 한다.
자주 사용되는 연산의 경우는 Cluster 기능사용을 고려하는 것도 좋다.
- SELFJOIN
EQUIJOIN과 같으나 하나의 Table에서 조인이 일어나는 것이 다르다.
조건절에 있는 컬럼에 Index를 생성하여 성능을 높여야 한다.
- Outer Join
Equijoin과 비슷하나 결과는 만족하는 레코드뿐 아니라 만족하지 않는 레코드도 출력한다.
Best Throughput
조인 연산시 튜닝의 목표이며, 오라클 Optimizer에게 USE_MERGE Hint를 주어야 한다.
Best Response Time
조인 연산시 튜닝의 목표이며, Optimizer에게 USE_NL Hint를 주어야 한다.
병렬 조회
Single Server Process가 수행하는 하나의 SQL 연산을 동시에 Multiple Server Process가 나누어 수행하는 것이다.
PQP (Parallel Quer Processing) 튜닝
System
I/O 대역폭 용량이 큰 System, CPU 성능이 우수한 System, Sort를 위한 메모리가 충분한 System에는 PQP가 좋은 결과를 가져온다.
I/O
PQP는 데이터 파일이 많은 디스크에 걸쳐서 분산되어 있는 환경에 적합하다.
디스크 분산을 하는 방법 중에는 랜덤 액세스가 많은 애플리케이션 환경에서는 OS 기능을 사용하고 일정량의 특정 부분 데이터를 자주 액세스하는 환경에서는 사용자가 직접하는 방법이 유리하다.
분산되는 최소의 단위는 DB_FILE_MULTIBLOCK_READ_COUNT의 2-3배가 되도록 해야 한다.
Sort를 위한 임시 스페이스는 여러 개의 디스크를 합쳐서 만들어야 PQP 도중 Sort, Merge에 걸리는 시간을 단축할 수 있다.
Parallel Degree
Parallel Degree는 성능에 미치는 영향이 매우 크다.
그러므로 많은 반복적인 시험을 통하여 결정해야 한다.
3. 오라클 인스턴스 튜닝
메모리 튜닝
운영체제 튜닝
운영체제에서 저장장치는 "Real Memory", "Virtual Memory", "Expended Storage", "Disk" 등이 있다.
O/S에 따라 Paging 또는 Swapping이 발생하면 많은 I/O를 동반하기 때문에 이 들이 일어나지 않게 하는 것이 주목적이다.
이를 위해서는 제한적인 메모리로 사용자들에게도 적당한 양을 할당하고 SGA에도 적절하게 할당해야 한다.
UNIX 환경에서는 "sar" 명령어나 모니터링 툴을 이용하여 상태를 확인한다.
SGA 튜닝
Data Dictionary Cache나 Library Cache에서 Miss가 발생하지 않도록 Shared Pool에 충분한 영역을 할당할 수 있도록 한다.
Private SQL, PL/SQL Area 튜닝
튜닝의 목적은 Parse 발생 횟수를 줄이는 것이다.
SQL Trace 기능을 이용해서 Parse 단계에서의 Count 수와 Execute 단계에서의 Count 수가 거의 같은 수로 판단되면 이를 줄여야 한다.
Private SQL, PL/SQL Area의 크기를 증가시키려면 초기 파라미터 파일의 OPEN_CURSORS 값을 증가시킨다.
Shared Pool 튜닝
튜닝의 목적은 Parse Call 수, Execute Call 수 등을 줄이고, Data Dictionary Cache에 대한 Miss를 최소화해서 오라클 성능을 최대한으로 극대화하는데 있다.
Library Cache 튜닝
Library Cache Miss는 Parse 단계와 Execute 단계에서 일어난다.
Cache Hit Rate 가 떨어지면 영역을 키우는 것이 튜닝 방법이다.
+ V$LIBRARYCACHE Table
DBA 권한을 가진 사용자만이 조회가능
SELECT SUM(pins) "Executions"
SUN(reloads) "Cache Misses while Executing"
FROM v$librarycache;
Executions Cache Misses while Executing
--------- -------------------------
320871 549
RELOADS Ratio = (reloads(549)/pins(320871)) * 100 = 0.17%
PINS : Library Cache 내의 항목이 실행된 수
RELOADS : 실행 단계에서의 Library Cache Miss의 수
RELOADS는 0에 가까워야 하고 PINS에서 RELOADS의 비율이 1% 이상이면 Library Cache Miss를 줄인다.
+ Library Cache Miss 줄이는 방법
더 많은 메모리를 할당하고 SQL을 사용할 때 다음 사항을 주의해야 한다.
1, 다음은 다른 것으로 간주하여 Shared SQL Area를 공유할 수 없다.
SELECT * FROM emp;
SELECT * FROM Emp;
2, 다른 사용자가 같은 이름의 Table을 가지고 있을 경우. 이 경우는 소유자를 표시해서 사용한다.
SELECT * FROM BOB.emp;
3, SQL 문장 내에서 바인드 변수는 이름과 데이터 타입이 틀려서는 안 된다.
SELECT * FROM emp WHERE deptno =:department_no;
SELECT * FROM emp WHERE deptno =:d_no;
4, 상수 대신 같은 이름과 데이터 타입을 가진 바인드 변수를 사용한다.
Data Dictionary Cache 튜닝
인스턴스가 Startup 되는 시점에 Data Dictionary Cache에 데이터들이 없기 때문에 Miss가 발생.
사용할 수 있는 메모리를 증가시키기 위해 SHARED_POOL_SIZE를 증가시킨다.
Buffer Cache 튜닝
Cache Hit Ratio를 높이는 것이 튜닝의 목적이다.
V$SYSSTAT
DBA 사용자만 조회가 가능하고 Cache Hit Ratio를 구할 수 있다.
DB BLOCK GETS, CONSISTENT GETS : 합은 데이터를 요구하는 총 횟수.
PHYSICAL READS : 디스크상의 데이터 파일을 액세스한 Request의 총 횟수.
SELECT name, value
FROM v$sysstat
WHERE name IN ('db block gets', 'consistent gets',
'physical reads');
Cache Hit Ratio = 1- (physical reads / (db block gets + consistent reads))
Hit Ratio가 60-70% 보다 작다면 성능 향상을 위해 Cache에서 Buffer 수를 증가시켜야 하는데
DB_BLOCK_BUFFERS 값을 크게 하면 된다.
X$KCBRBH
Buffer 수가 증가함에 따라 Cache Hit Ratio의 변화를 알 수 있는 Table이다.
Buffer 수를 조정한 후에 그 효과를 알아보기는 좋으나 이를 수행함에 따라 부하가 발생한다는 것을 명심해야 한다.
X$KCBCBH
Buffer를 줄임으로서 나타나는 ACM(Additional Cache Miss) 값을 알아보는 Table.
DB_BLOCK_LRU_STATISTIC = TRUE
예) 현재 Buffer가 100이라면 90으로 줄였을 때 ACM?
SELECT SUM(count) acm
FROM sys.x$kcbcbh
WHERE indx >= 90;
INDX : Cache 내의 가능한 Buffer 수
COUNT : INDX에 기인한 ACM
I/O 튜닝
애플리케이션이 시스템의 어떤 자원을 사용하느냐에 따라 I/O Bound 업무와 CPU Bound로 구분할 수 있다.
실제 애플리케이션이 물리적 Read/Write 연산에 많은 시간을 소비하고 있기 때문에 I/O에 대한 튜닝이 중요하다.
디스크 마찰(Disk Contention)
동시에 많은 사용자가 같은 디스크 블록을 Access 하고자 할 때 발생하는 현상.
V$FILESTAT
SELECT name, phyrds, phywrts
FROM V$datafile df, V$filestat fs
WHERE df.file# = fs.file#
+ phyrds : 각 데이터 파일을 읽는 횟수
+ phywrts : 각 데이터 파일에 저장한 횟수
I/O 분산
디스크나 디스크 컨트롤러에 초과되는 Request 처리를 요구받은 디스크들을 찾아내어 이들을 분산시키는 것이다.
- 데이터 파일, 리두 로그 파일 분리
리두 로그 파일은 트랜잭션이 완료되면 그에 대한 로그를 기록하는 파일이다. 또 한 Request를 순차적으로 받아 처리한다.
그러므로 Random 액세스가 주로 일어나는 데이터 파일과 분리하면 그 효과가 크다.
장애에 대비하여 미러링 하는 것이 좋다.
- Striping
하나의 데이터를 세그먼트로 분할하여 여러 개의 디스크 장치로 분산시키는 것.
- Table과 Index 분리
Table에 저장되어 있는 데이터를 access하기 위해서는 Index를 참조하는데 이 들이 한 디스크에 있을 경우는
특정 디스크에 과부하가 발생한다.
- 오라클 사용 디스크 분리
다른 애플리케이션 요소나, OS에서 사용하는 디스크들을 서로 분리하는 것이 오라클에 대한 외부의 영향을 최소화할 수 있다.
데이터 블록
데이터들은 Table 안에 데이터 블록이라는 단위 영역별로 저장된다.
한 데이터 블록에 많은 데이터가 있어야 성능에 도움이 되는 경우가 있고, 적어야 도움이 되는 경우도 있다.
사용중에 데이터 블록이 더 필요해서 계속 할당받으면 성능이 저하될 수 있기 때문에 이를 방지하기 위한 노력이 필요하다.
Update/Insert 연산과 데이터 블록
- Migration : Update가 자주 발생하여 데이터 블록에 더 이상 데이터를 저장할 공간이 없는 경우 오라클은 충분히 큰 데이터 블록을 찾아서 원래 데이터 블록에 있는 내용들을 새로운 곳으로 이동시킨다.
- Chaining Row : 충분히 큰 데이터 블록이 없는 경우 원래 데이터 블록의 데이터를 나누어서 다른 데이터 블록에 분산 저장한다.
Migration과 Chaining을 방지하는 근본적인 방법은 충분히 큰 데이터 블록을 할당해 주는 것이다.
Table 생성시 PCTUSED, PCTFREE 기능을 사용하여 Update, Insert 연산시 필요한 영역을 데이터 블록에 마련하는 방법도 있다.
이미 발생한 Migration과 Chaining의 처리 방법
+ ANALYZE 명령을 이용해 정보를 구한다.
ANALYZE TABLE order_hist LIST CHAINED ROWS;
+ 출력 데이터를 본다.
SELECT OWNER_NAME, TABLE_NAME,
HEAD_ROWID, TIME
FROM chained_rows
WHERE table_name = 'ORDER_HIST';
+ Migrated되거나 Chain된 데이터 행을 가지고 있다면 다음 단계를 거쳐 이러한 행들을 제거한다.
- Migrate되거나 Chain된 데이터 행을 가지고 있는,
실제 Table과 같은 컬럼을 가지고 있는 임시 테이블을 생성한다.
- 실제 Table에서 Migrate되고, Chain된 데이터 행들을 지운다.
- 임시 Table의 데이터 행들을 실제 테이블에 Insert한다.
- 임시 Table을 제거한다.
+ 출력 Table에서 모아진 정보를 지운다.
DELETE FROM chained_row
WHERE table_name = 'ORDER_HIST';
+ 다시 ANALYZE 명령을 이용해 정보를 모으고, 그 정보를 본다.
+ 출력 파일에 Row가 나타나면 데이터 블록의 크기를 증가시켜 이전 Chain된 행을 없앤다.
모든 상황에서 이런 Chaining을 피할 수는 없다.
만약 LONG 컬럼이나 긴 CHAR, VARCHAR2 컬럼을 가지고 있다면 이런 Chaining을 피할 수 없다.
Dynamic Space Management 최소화
DB 객체들의 생성 초기에 할당받은 영역이 사용 중에 부족하면 이들을 증가시켜야 한다.
이런 현상은 과다한 I/O Request를 동시에 발생시키므로 성능에 저해요소가 된다.
그러므로 객체를 생성할 때 예상되는 스페이스를 미리 할당하는 것이 바람직하다.
동적 확장 검사
Recursive Call : 사용자가 수행하는 SQL 연산과 함께 오라클 사용자들이 요구한 SQL 문장을 수행하기 위해 새로운 SQL 문장들을 만들어 수행.
+ Recursive Call이 발생하는 경우
- Data Dictionary Cache에 Miss가 발생했을 경우
- 데이터베이스 트리거 수행
- DDL 문장의 실행
- Stored Procedure, Function, Package 그리고 Anonymous PL/SQL 블록 등이 가지고 있는 SQL 문장의 수행
- Referential Integrity Constraint의 실행
SELECT name, value
FROM v$sysstat
WHERE name = 'recursive calls';
Extent의 확장
큰 Extent 안에 여러 개의 데이터 블록을 한 번에 액세스하는 것이 성능 향상에 좋다.
그러므로 Table 중 성능이 빠른 것은 여러 개의 작은 Extent를 자주 할당받게 하는 것보다 큰 Extent가 한번에 할당되도록 하는 것이 좋다.
Contention 튜닝
Rollback Segment Contention
롤백세그먼트 블록을 포함하는 버퍼를 기다리는 수를 보고 판단할 수 있는데 V$WAITSTAT를 이용해서 rollback segment에 대한 contention이 성능을 감소시키고 있는지 어떤지를 결정할 수 있다.
Default로서 이 테이블은 SYS 유저와 SYSTEM와 같은 SELECT ANY TABLE 시스템 권한을 가진 다른 유저들만이 사용할 수 있다.
SELECT class, count
FROM v$waitstat
WHERE class IN ('system undo header',
'system undo block',
'undo header', 'undo block');
·system undo header : SYSTEM rollback segment의 header block을 포함하는 buffer를 기다리는 수이다.
·system undo block : SYSTEM rollback segment의 header block 이외의 다른 block들을 포함하는 buffer를 기다리는 수이다.
·undo header : SYSTEM rollback segment의 header block 이외의 다른 rollback segment의 header block을 포함하는 buffer들을 기다리는 수이다.
·undo block : SYSTEM rollback segment이외의 다른 rollback segments 의 header block과는 다른 blocks을 포함하는 buffer들을 기다리는 수이다.
위에 나타난 class 별로 기다리는 Buffer 수와 같은 시간 동안 총 Request 수를 비교하면 Contention 정도를 알
수 있다.
SELECT SUM(value)
FROM v$sysstat
WHERE name IN ('db block gets', 'consistent gets');
SUM(VALUE)
------------
929530
이 결과값과 각 Class별 Count값들을 비교해서 1%가 넘으면 롤백 세그먼트의 수를 늘려야 한다.
Redo Log Buffer Contention
리두 로그 파일에 Log Entry를 기록하기 위해서는 리두 로그 버퍼를 거쳐서 내려가야 하는데 Request 가 많을 경우 사용자 프로세스는 대기하는 시간이 길어지게 된다. 이를 단축시키기 위해 리두 로그 버퍼를 늘려 주어야 한다.
SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests';
결과 값이 0에 가까울 때까지 5%씩 리두 로그 버퍼 수를 늘려야 하는데 이는 오라클 초기 파라미터의 LOG_BUFFER의 값을 키우면 된다.
Redo Log Buffer Latch
리두 로그 버퍼 액세스는 Latch에 의해서 조정된다.
- Redo Allocation Latch
리두 로그 버퍼에서 Redo Entry를 위한 공간을 할당하는 것을 제어한다.
Buffer에 공간을 할당할 때는 오라클 사용자 프로세스가 반드시 Redo Allocation Latch를 가지고 있어야 한다.
만약 하나의 Redo Allocation Latch가 있다면 한 번에 단 하나의 사용자 프로세스만 버퍼에 공간을 할당하는 것이 가능하다.
초기 파라미터의 LOG_SMALL_ENTRY_MAX_SIZE값에 따라 정해진다.
Contention을 최소화 하려면
LOG_SMALL_ENTRY_MAX_SIZE값을 감소시킨다.
- Redo Copy Latch
Redo Entry가 너무 커서 Redo Allocation Latch에 복사할 수 없다면, 사용자 프로세스는 버퍼에 Entry를 복사하기 전에 Redo Copy Latch를 갖고 있어야만 한다.
Redo Copy Latch를 잡고 있는 동안 사용자 프로세스는 버퍼에 할당된 공간 안에 Redo Entry를 복사한 후 Redo Copy Latch를 해제하게 된다.
Redo Copy Latch의 수는 초기 파라미터 LOG_SIMULTANEOUS_COPIES에 의해 결정된다.
Contention을 최소화하려면
LOG_SIMULTANEOUS_COPIES의 기본값은 현재 CPU의 수로 되어 있는데 이를 두 배정도 늘리면 성능에 도움이 된다.
Sort 튜닝
대부분 애플리케이션에는 기본 Sort 영역으로도 성능상 무리는 없으나, 필요에 따라서 Sort 영역이 부족하다고 판단되면 이를 늘려야 한다. (오라클 초기 파라미터 SORT_AREA_SIZE 값을 증가)
SELECT name, value
FROM v$sysstat
WHERE name IN('sorts(memory)', '(sorts(disk)');
sorts(memory) : 디스크상의 Temporary Segment와 I/O 없이 Sort 영역에서만 이루어지는 Sort 수.
sorts(disk) : Sort 영역에서만 처리하기에는 데이터가 너무 커서 Temporary Segment와 I/O가 필요한 Sort 수.
Sort 영역을 크게 하면 Sort 관련 작업들은 속도가 향상되나, Private SQL과 PL/SQL을 위한 메모리 등 다른 요소에 영향을 줄 수 있으므로 주의해야 한다.
- NOSORT
Index 생성시 Sort가 발생한다. 반드시 Sort할 필요가 없는 Index는 NOSORT 기능을 사용하여 부하를 줄이는 것이 좋다.
CREATE INDEX emp_index
ON emp(ename)
NOSORT;
Free List Contention 튜닝
동시에 한 Table에 Insert 연산이 많이 발생하는 경우에는 Free List가 부족한 현상이 발생하고 이는 성능 저해 요인이 된다.
SELECT class, count
FROM v$waitstat
WHERE class = 'free list';
Free List 대기 횟수의 비율이 1% 이상이면 충돌감소를 위해 Free List의 추가 배정을 고려한다.
FREELISTS Storage Parameter값을 증가하여 Table을 다시 생성하면 된다.
Checkpoint 튜닝
Checkpoint가 자주 발생하면 만약의 경우에 발생할 인스턴스 Failure와 같은 상황에서 Recovery 시간을 줄여 주지만, 오라클 성능 저하 요인이 되기도 한다.
수행속도가 중요한 환경에서는 Checkpoint 빈도수를 줄이고, 안정성이 중요한 환경에서는 Checkpoint 빈도수를 늘린다.
- Checkpoint 빈도수를 최소화하려면
LOG_CHECKPOINT_INTERVAL값을 가장 큰 Redo Log 파일보다 크게 세팅.
LOG_CHECKPOINT_TIMEOUT값을 0으로 세팅.
- Checkoint 빈도수를 늘리려면
LOG_CHECKPOINT_INTERVAL값을 작게 세팅.
LOG_CHECKPOINT_TIMEOUT값을 작게 세팅.
초기 파라미터에서 CHECKPOINT_PROCESS = TRUE
4. 서버 튜닝
프로세스(Process)
전체 사용자 프로세스 수와 오라클 인스턴스 관련 프로세스 수 그리고 OS 관련 프로세스 수들의 합이 전체 프로세스의 수가된다.
전체 프로세스 수의 10-30% 이상의 프로세스들이 지장 없이 수행될 수 있는 환경을 제공해야 한다.
오라클 인스턴스 프로세스와 오라클 사용자 프로세스는 우선 순위를 같게 해야 한다.
메모리
SGA가 작은 경우 CPU idle 상태가 많이 발생한다.
이는 SGA가 작아서 오라클 인스턴스는 I/O Wait에 많은 시간을 소비하기 때문이다.
NetWare, NT 환경
- 다른 프로세스와 같은 메모리를 사용.
UNIX 환경
- Shared 메모리라는 특수한 종류의 메모리를 사용.
- Page Size Entension(PSE)라는 기능(OS 부팅시 오라클을 위해 미리 많은 양의 메모리를 예약)을 사용하면 성능에 많은 도움이 되나, 이 메모리를 오라클이 다 사용하지 않아도 다른 프로세스는 사용할 수 없다.
I/O
중요한 튜닝 요소는 I/O 대역폭, I/O 메소드, 블록 크기.
블록 크기는 I/O 부하와 많은 관계가 있다.
OLTP, 랜덤 액세스가 많은 환경 - 2k 또는 4k
DSS, 시퀀셜 액세스가 많은 환경 - 4k 또는 8k
OS 블록 크기와 DB 블록 크기를 같게 하거나 OS 블록크기가 DB 블록 크기에 두 배가 되도록 하는 것이 성능을 좋게 하는 요인이 된다.
I/O 메소드 중에는 OS 파일 시스템 버퍼를 사용하는 일방적인 방식이 있고, OS 파일 시스템 버퍼를 사용하지 않는 Raw Device 방식이 있다.
+ Sun Solaris : Filesystem, Raw Device에서 Async I/O 사용가능
+ SCO : Raw Device에서 Async I/O 사용가능
+ Pyramid : Raw Device에서 Async I/O 사용가능
+ Sequent : Raw Device에서 Async I/O 사용가능
Raw Device 방식은 OS의 부하를 줄일 수 있고, I/O 성능을 높일 수 있다.
Asynchronous I/O 방식은 오라클 I/O 성능을 크게 높일 수 있는 방법이다.
O/S 는 Request들을 모아서 디스크 컨트롤러를 요구하고 다른 작업을 할 수 있다.
나중에 디스크 컨트롤러에서 응답이 오면 인터럽트를 받는다.
Asynchronous I/O 방식은 다른 방식과 비교하여 10-30%의 성능을 향상시킬 수 있다.
예) SCO UNIX
$chown oracle /dev/async
$chgrp dba /dev/async
ASYNC_WRITE = TRUE
UnixWare
$chown oracle /dev/async
$chgrp dba /dev/async
USE_ASYNC_TO = TRUE
LGWR_USE_ASYNC_IO = TRUE
Post-Wait Semaphore
오라클이 사용자에게 Sleeping Signal을 줄 수 있게하고, 다시 일을 할 수 있게 해주는 새로운 형태의 Semaphore이다.
CPU의 Idle Cycle을 크게 줄여 성능을 향상시킨다.
Post-Wait Semaphore의 기능을 사용하려면.
- 오라클 초기 파라미터 수정.
USER_POST_WAIT_DRIVER = TRUE
POST_WAIT_DEVICE = /dev/pw
Device 이름은 시스템마다 다를 수 있다.
- Device의 사용 권한을 수정.
%chown oracle /dev/pw
%chgrp dba /dev/pw
- Post-Wait Device가 사용되는지 확인.
%ipcs
5. Performance Parameter
오라클 초기 파라미터 변수들은 오라클 성능에 많은 영향을 준다.
변수들을 이해하고 필요에 따라서 적절하게 수정하고 사용하면 성능 향상에 많은 도움이 된다.
출처 : http://zenos.pe.kr/
보다 적은 노력으로 많은 결과를 내고자 하는 활동.
1. 튜닝(Tuning) Overview
포로젝트를 개발함에 있어 시작부터 끝까지 튜닝에 대한 마인드를 가지고 모든 일을 처리하는 것이다.
튜닝 절차
튜닝 절차
애플리케이션 형태
애플리케이션 형태를 알면 튜닝 포인트를 찾는데 도움이 된다.
- OLTP(Online Transaction Processing)
애플리케이션 형태를 알면 튜닝 포인트를 찾는데 도움이 된다.
- OLTP(Online Transaction Processing)
대부분의 업무는 Insert/Update 이다.
예) 은행의 온라인 업무, 항공사의 예약 업무, 주문 시스템
데이터의 정확성과 가용성이 중요.
튜닝 항목
롤백 세그먼트, 인덱스, 클러스터, 해싱, 데이터 블록 크기, 테이블 또는 롤백 세그먼트의 동적 할당, 트랜잭션 처리 모니터, 멀티 스레드 서버, SGA Shared Pool, SQL 문장 튜닝, 완전 무결성 제약, 프로시저, 패키지, 함수
- DSS (Decision Support System)
예) 은행의 온라인 업무, 항공사의 예약 업무, 주문 시스템
데이터의 정확성과 가용성이 중요.
튜닝 항목
롤백 세그먼트, 인덱스, 클러스터, 해싱, 데이터 블록 크기, 테이블 또는 롤백 세그먼트의 동적 할당, 트랜잭션 처리 모니터, 멀티 스레드 서버, SGA Shared Pool, SQL 문장 튜닝, 완전 무결성 제약, 프로시저, 패키지, 함수
- DSS (Decision Support System)
축적된 대량의 정보를 가공해서 미래의 사실을 유추하는데 목적이 있는 업무이다.
대부분의 작업은 Read와 Join 이다.
튜닝 항목
인덱스, 클러스터, 데이터 블록 크기, 병렬 조회 옵션, 최적화, 쿼리 수행중 오라클 힌트 사용, PL/SQL 함수 사용
- 복잡한 연산 애플리케이션(Scientific Application)
예) 기상청과 같은 대규모의 연산을 수행하는 곳.
튜닝 항목
PL/SQL, 병렬 쿼리 연산, 최적화, 쿼리 사용 중 오라클 힌트 사용.
- 클라이언트/서버 애플리케이션
튜닝 항목
저장 프로시저, 데이터베이스 트리거, 완전 무결성 제약, 배열 프로세싱, 시퀀스.
2. SQL 튜닝 최적화 (Optimization)
데이터를 조작할 수 있는 인터페이스 중에서 대표적인 것이 SQL이다.
Optimization : 어떻게 하면 효율적으로 최소의 자원을 사용하여 원하는 결과를 얻을 것인가.
SQL
SQL이 어떻게 수행되는지를 잘 이해하고 있으면 SQL연산시 무슨 자원이 필요하고 어떻게 튜닝해야 하는지 쉽게 알 수 있다.
[그림. RDBM의 내부 프로시저 상관 관계]
대부분의 작업은 Read와 Join 이다.
튜닝 항목
인덱스, 클러스터, 데이터 블록 크기, 병렬 조회 옵션, 최적화, 쿼리 수행중 오라클 힌트 사용, PL/SQL 함수 사용
- 복잡한 연산 애플리케이션(Scientific Application)
예) 기상청과 같은 대규모의 연산을 수행하는 곳.
튜닝 항목
PL/SQL, 병렬 쿼리 연산, 최적화, 쿼리 사용 중 오라클 힌트 사용.
- 클라이언트/서버 애플리케이션
튜닝 항목
저장 프로시저, 데이터베이스 트리거, 완전 무결성 제약, 배열 프로세싱, 시퀀스.
2. SQL 튜닝 최적화 (Optimization)
데이터를 조작할 수 있는 인터페이스 중에서 대표적인 것이 SQL이다.
Optimization : 어떻게 하면 효율적으로 최소의 자원을 사용하여 원하는 결과를 얻을 것인가.
SQL
SQL이 어떻게 수행되는지를 잘 이해하고 있으면 SQL연산시 무슨 자원이 필요하고 어떻게 튜닝해야 하는지 쉽게 알 수 있다.
[그림. RDBM의 내부 프로시저 상관 관계]
SQL은 기능상 DML과 DDL로 나눌 수 있다.
DML
1단계 : 커서의 생성
커서는 SQL 문장을 수행하기 위해서 오라클이 사용하는 자원이다.
시스템 자원이 충분하면 초기파라미터의 OPEN_CURSORS를 크게 하는 것이 유리하다.
2단계 : 문장의 파스, 질의 프로세싱
Shared Pool에 이전에 사용한 SQL 문장이 있는지 확인하고 없으면 다시 컴파일, 권한검사, 문법오류 등을 검사하여 공유 영역에 올린다. (문장의 파스, 질의 프로세싱)
초기 파라미터의 SHARED_POOL_SIZE를 변경.
3단계 : 설명 결과
4단계 : 정의의 출력
5단계 : 변수의 바인드
6단계 : 문장의 실행
SQL 연산시 임시로 필요한 스페이스가 발생하는 경우가 있다.
사용자가 Default Space와 Temporary Space를 두지 않을 경우 오라클 System Space는 사용자들을 위한 Default Space가 되어 전체 성능을 저해하는 요소가 될 수 있다.
Sort나 Join 등의 연산시는 Space 부족으로 중단되는 경우가 발생한다. 그러므로 Temporary Space를 충분히 확보해 두어야 한다.
Insert, Update, Delete 연산 수행시는 Lock 자원을 사용하므로 주의를 기울여야 한다.
7단계 : 문장의 병렬화
8단계 : 질의 결과 레코드의 FETCH
DDL
데이터를 조작하지는 않지만 Table을 생성, 삭제, 변경 시키는 SQL 문장이다.
DML과 달리 Data Dictionary의 값을 변경시킨다.
DDL 연산을 통하여 데이터의 위치나 스페이스를 변경할 수 있으므로 물리적 I/O 튜닝을 위해서 많이 사용한다.
Shared SQL, PL/SQL
오라클 Library Cache에는 Shared Pool과 PL/SQL 영역이 있다.
성능 위해 자주 사용되는 SQL과 PL/SQL 문장들을 얼마나 많이 장기간 확보하느냐가 중요하다.
LRU(Least Recently Used)
공유 영역은 LRU 알고리즘으로 관리된다.
최근에 사용되지 않은 SQL과 PL/SQL을 위해 자리를 내주어야 한다. 그러므로 같은 결과를 얻기 위해 사용되는 문장은 반드시 같은 폼으로 사용되어야 한다.
V$LIBRARYCACHE
사용자가 얼마나 Library Cache를 잘 사용하는가를 알 수 있는 통계 자료가 있다.
SELECT SUM(pins) "Executions"
SUN(reloads) "Cache Misses while Executing"
FROM v$librarycache;
Executions Cache Misses while Executing
--------- -------------------------
320871 549
RELOADS Ratio = (reloads(549)/pins(320871)) * 100 = 0.17%
PINS : Library Cache 내의 항목이 실행된 수
RELOADS : 실행 단계에서의 Library Cache Miss의 수
RELOADS 값이 상대적으로 PINS 보다 매우 작으면 Cache Hit Ratio가 높다는 것을 의미한다.
Cache Hit Ratio가 낮다는 의미는 공유영역이 부족하거나 애플리케이션에서 같은 SQL, PL/SQL 문장들을 사용하지 않거나 Trigger, Package 기능들을 사용하지 않아서 새로운 아이템들이 Library Cache에 계속 Reload 되기 때문이다.
Library Cache를 위한 메모리 할당
Reload값을 0에 근접하게 만들려면 파라미터값 중에서 SHARED_POOL_SIZE 값을 증가시켜야 한다.
또, 충분한 공유 영역의 이점을 살리기 위해 OPEN_CURSORS 값도 같이 증가시켜야 한다.
Optimization Overview
Optimizer의 목표는 최소한의 프로세싱 시간과 최단 시간의 I/O를 사용하는 것이다.
이를 위해 Optimizer는 실행계획을 세우고 문장을 실행하기 전에 가장 효과적인 계획을 선택한다.
실행 계획 (Execution Plan)
DML문장을 실행하기 위해 Oracle은 많은 내부적 단계를 수행해야 한다.
어떠한 문장을 실행하기 위해 오라클이 사용하는 단계들의 조합을 실행 계획이라 한다.
예)
SELECT ename, job, sal, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND NOT EXISTS
(SELECT * FROM salgrade
WHERE emp.sal BETWEEN losal AND hisal);
ID OPERATION OPTIONS OBJECT_NAME
---------------------------------------------------------
0 SELECT STATEMENT
1 FILTER
2 NESTED LOOPS
3 TABLE ACCESS FULL EMP
4 TABLE ACCESS BY ROWID DEPT
5 INDEX UNIQUE SCAN PK_DEPTNO
6 TABLE ACCESS FULL SALGRADE
실행 계획의 각 단계는 다음 단계나 마지막 단계에서 사용될 행들이나, 사용자나 어플리케이션이 SQL문장을 발행한 것에 대한 답으로서 행들의 집합을 돌려준다. 각 단계에서 반환된 행들의 집합은 행자원(row source)이라고 불린다. 그림은 한 단계에서 다음 단계로 행들의 흐름을 보여주는 계층적 다이어그램이다. 단계의 번호 매김은 우리가 실행 계획을 볼 때 보여지는 순서를 반영하는데, 이것은 일반적으로 각 단계가 실행되는 순서가 아니다.
하나 이상의 행 자원으로부터 행들을 받아들인다.
- 검정 박스로 표현된 각 단계들은 물리적으로 데이터베이스내의 객체로부터 데이터를 검색한다. 그런 단계를 접근 경로(access paths)라고 한다.
1. 단계 3과 6은 각각 EMP와 SALGRADE 테이블의 모든 행들을 읽는다.
2. 단계 5는 PK_DEPTNO 인덱스로 3단계에서 반환된 각각의 DEPTNO값을 찾아본다. DEPT 테이블에서의 관련 행의 ROWIDS를 찾는다.
3. 단계 4는 DEPT테이블에서 5단계에서 반환된 ROWIDS값을 갖는 행들을 검색한다.
- 하얀 박스들로 표현된 각 단계들은 행자원에 대해 작동한다.
1. 단계 2는 3단계와 4단계로부터 행자원을 받아들이고, 단계 3의 자원으로부터 각 행들을 단계 4의 해당 행에
조인하고, 1단계로 결과 행들을 반환하는 중첩 루프(loop) 오퍼레이션을 수행한다.
2. 단계 1은 필터 연산을 수행한다. 그것은 행자원을 2단계와 6단계로부터 받아들인다. 6단계에서 해당 행을 가지고 와서 2단계로부터 행을 추출하여, 2단계로부터 남은 행들을 문장을 발행한 사용자와 어플리케이션에 반환한다.
따라서 실행순서는 3-5-4-2-6-1 이다.
Optimization Mode
Cost-Based, Rule-Based가 있다.
초기 파라미터 파일을 수정
OPTIMIZATION_MODE = COST
OPTIMIZATION_MODE = RULE
ALTER SESSION 명령어 사용
ALTER SESSION SET OPTIMIZER GOAL = COST
DML
1단계 : 커서의 생성
커서는 SQL 문장을 수행하기 위해서 오라클이 사용하는 자원이다.
시스템 자원이 충분하면 초기파라미터의 OPEN_CURSORS를 크게 하는 것이 유리하다.
2단계 : 문장의 파스, 질의 프로세싱
Shared Pool에 이전에 사용한 SQL 문장이 있는지 확인하고 없으면 다시 컴파일, 권한검사, 문법오류 등을 검사하여 공유 영역에 올린다. (문장의 파스, 질의 프로세싱)
초기 파라미터의 SHARED_POOL_SIZE를 변경.
3단계 : 설명 결과
4단계 : 정의의 출력
5단계 : 변수의 바인드
6단계 : 문장의 실행
SQL 연산시 임시로 필요한 스페이스가 발생하는 경우가 있다.
사용자가 Default Space와 Temporary Space를 두지 않을 경우 오라클 System Space는 사용자들을 위한 Default Space가 되어 전체 성능을 저해하는 요소가 될 수 있다.
Sort나 Join 등의 연산시는 Space 부족으로 중단되는 경우가 발생한다. 그러므로 Temporary Space를 충분히 확보해 두어야 한다.
Insert, Update, Delete 연산 수행시는 Lock 자원을 사용하므로 주의를 기울여야 한다.
7단계 : 문장의 병렬화
8단계 : 질의 결과 레코드의 FETCH
DDL
데이터를 조작하지는 않지만 Table을 생성, 삭제, 변경 시키는 SQL 문장이다.
DML과 달리 Data Dictionary의 값을 변경시킨다.
DDL 연산을 통하여 데이터의 위치나 스페이스를 변경할 수 있으므로 물리적 I/O 튜닝을 위해서 많이 사용한다.
Shared SQL, PL/SQL
오라클 Library Cache에는 Shared Pool과 PL/SQL 영역이 있다.
성능 위해 자주 사용되는 SQL과 PL/SQL 문장들을 얼마나 많이 장기간 확보하느냐가 중요하다.
LRU(Least Recently Used)
공유 영역은 LRU 알고리즘으로 관리된다.
최근에 사용되지 않은 SQL과 PL/SQL을 위해 자리를 내주어야 한다. 그러므로 같은 결과를 얻기 위해 사용되는 문장은 반드시 같은 폼으로 사용되어야 한다.
V$LIBRARYCACHE
사용자가 얼마나 Library Cache를 잘 사용하는가를 알 수 있는 통계 자료가 있다.
SELECT SUM(pins) "Executions"
SUN(reloads) "Cache Misses while Executing"
FROM v$librarycache;
Executions Cache Misses while Executing
--------- -------------------------
320871 549
RELOADS Ratio = (reloads(549)/pins(320871)) * 100 = 0.17%
PINS : Library Cache 내의 항목이 실행된 수
RELOADS : 실행 단계에서의 Library Cache Miss의 수
RELOADS 값이 상대적으로 PINS 보다 매우 작으면 Cache Hit Ratio가 높다는 것을 의미한다.
Cache Hit Ratio가 낮다는 의미는 공유영역이 부족하거나 애플리케이션에서 같은 SQL, PL/SQL 문장들을 사용하지 않거나 Trigger, Package 기능들을 사용하지 않아서 새로운 아이템들이 Library Cache에 계속 Reload 되기 때문이다.
Library Cache를 위한 메모리 할당
Reload값을 0에 근접하게 만들려면 파라미터값 중에서 SHARED_POOL_SIZE 값을 증가시켜야 한다.
또, 충분한 공유 영역의 이점을 살리기 위해 OPEN_CURSORS 값도 같이 증가시켜야 한다.
Optimization Overview
Optimizer의 목표는 최소한의 프로세싱 시간과 최단 시간의 I/O를 사용하는 것이다.
이를 위해 Optimizer는 실행계획을 세우고 문장을 실행하기 전에 가장 효과적인 계획을 선택한다.
실행 계획 (Execution Plan)
DML문장을 실행하기 위해 Oracle은 많은 내부적 단계를 수행해야 한다.
어떠한 문장을 실행하기 위해 오라클이 사용하는 단계들의 조합을 실행 계획이라 한다.
예)
SELECT ename, job, sal, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND NOT EXISTS
(SELECT * FROM salgrade
WHERE emp.sal BETWEEN losal AND hisal);
ID OPERATION OPTIONS OBJECT_NAME
---------------------------------------------------------
0 SELECT STATEMENT
1 FILTER
2 NESTED LOOPS
3 TABLE ACCESS FULL EMP
4 TABLE ACCESS BY ROWID DEPT
5 INDEX UNIQUE SCAN PK_DEPTNO
6 TABLE ACCESS FULL SALGRADE
실행 계획의 각 단계는 다음 단계나 마지막 단계에서 사용될 행들이나, 사용자나 어플리케이션이 SQL문장을 발행한 것에 대한 답으로서 행들의 집합을 돌려준다. 각 단계에서 반환된 행들의 집합은 행자원(row source)이라고 불린다. 그림은 한 단계에서 다음 단계로 행들의 흐름을 보여주는 계층적 다이어그램이다. 단계의 번호 매김은 우리가 실행 계획을 볼 때 보여지는 순서를 반영하는데, 이것은 일반적으로 각 단계가 실행되는 순서가 아니다.
하나 이상의 행 자원으로부터 행들을 받아들인다.
- 검정 박스로 표현된 각 단계들은 물리적으로 데이터베이스내의 객체로부터 데이터를 검색한다. 그런 단계를 접근 경로(access paths)라고 한다.
1. 단계 3과 6은 각각 EMP와 SALGRADE 테이블의 모든 행들을 읽는다.
2. 단계 5는 PK_DEPTNO 인덱스로 3단계에서 반환된 각각의 DEPTNO값을 찾아본다. DEPT 테이블에서의 관련 행의 ROWIDS를 찾는다.
3. 단계 4는 DEPT테이블에서 5단계에서 반환된 ROWIDS값을 갖는 행들을 검색한다.
- 하얀 박스들로 표현된 각 단계들은 행자원에 대해 작동한다.
1. 단계 2는 3단계와 4단계로부터 행자원을 받아들이고, 단계 3의 자원으로부터 각 행들을 단계 4의 해당 행에
조인하고, 1단계로 결과 행들을 반환하는 중첩 루프(loop) 오퍼레이션을 수행한다.
2. 단계 1은 필터 연산을 수행한다. 그것은 행자원을 2단계와 6단계로부터 받아들인다. 6단계에서 해당 행을 가지고 와서 2단계로부터 행을 추출하여, 2단계로부터 남은 행들을 문장을 발행한 사용자와 어플리케이션에 반환한다.
따라서 실행순서는 3-5-4-2-6-1 이다.
Optimization Mode
Cost-Based, Rule-Based가 있다.
초기 파라미터 파일을 수정
OPTIMIZATION_MODE = COST
OPTIMIZATION_MODE = RULE
ALTER SESSION 명령어 사용
ALTER SESSION SET OPTIMIZER GOAL = COST
|
옵 션 |
설 명 |
CHOOSE |
Table, Cluster, Index 등이 Optimization을 위한 통계 데이터를 가지고 있으며 Cost-Based 방식으로 Optimization을 하고 통계 데이터가 없으면 Rule-Based 방식으로 한다. |
RULE |
Rule-Based |
ALL_ROWS |
통계 데이터의 유무에 상관없이 Cost-Based 방식의 최적화를 한다. Best Throughput(처리량)이 목적. |
FIRST_ROWS |
Cost-Based Optimization, Best Response Time 목적 |
최적화 방법
대부분 Cost-Based 방식이 Rule-Based 방식보다 좋은 결과를 내나 사용자가직접 SQL 연산을 튜닝할 때는 Rule-Based 방식이 더 좋은 결과를 낸다.
Rule-Based 방식
Rule-Based 방식은 Cost-Based 방식에 비해 간단하다.
여러 개의 가능한 PATH를 찾아 이미 정해져 있는 Rank를 기준으로 서로의 Cost를 비교하고, 이를 토대로 가장 효율적인 것을 선택한다.
|
옵 션 |
Access Path |
|
1 |
ROWID에 의한 단일 행 접근 |
|
2 |
클러스터 조인에 의한 단일 행 접근 |
|
3 |
Unique Key 또는 Primary Key를 사용하는 Hash Cluster Key에 의한 단일 행 접근 |
|
4 |
Unique Key 또는 Primary Key에 의한 단일 행 접근 |
|
5 |
클러스터 조인 |
|
6 |
해시 클러스터 키 |
|
7 |
인덱스 클러스터 키 |
|
8 |
복합 키 |
|
9 |
단일 컬럼 인덱스 |
|
10 |
인덱스 컬럼에서의 바운드 범위 조회 |
|
11 |
인덱스 컬럼에서의 언바운드 범위 조회 |
|
12 |
소트 병합 조인 |
|
13 |
인덱스 컬럼의 최대 또는 최소값 |
|
14 |
인덱스 컬럼에서의 ORDER BY 사용 |
|
15 |
테이블의 전체 검색 |
1. ROWID에 의한 단일 행 접근
문장의 WHERE절에 ROWID나 오라클 Precompiler에서 지원하고 있는 확장된 SQL 구문 구조의 CURRENT OF CURSOR에 의해 지정된 Row들이 식별될 수 있을 경우에만 사용이 가능. 문장을 실행하기 위해 ROWID에 의해 테이블을 검색한다.
2, 클러스터 조인에 의한 단일 행 접근
- 문장의 WHERE절에 한 테이블의 클러스터 키의 각 칼럼과 그에 대응하는 다른 테이블의 컬럼을 같게하는 조건절을 가지고 있어야 한다.
- 문장의 WHERE절에 조인이 단지 하나의 행을 리턴하는 것을 보증하는 조건절을 가지고 있어야 한다.
이런 조건은 Unique 하거나 Primary Key인 컬럼의 '=' 조건절과 유사하다.
위 두 조건이 만족할 경우에 사용.
반드시 AND 연산자를 통하여 합쳐져야 한다.
3, Unique Key 또는 Primary Key를 사용하는
Hash Cluster Key에 의한 단일 행 접근
- 문장의 WHERE절이 '='조건절에 해시 클러스터 키의 모든 컬럼을 사용하여야 한다. 합성된 클러스터 키인 경우는 '=' 조건절이 반드시 AND 연산자에 의해 연결되어야 한다.
- 문장은 컬럼이 해시 클러스터 키를 만들 듯이 유일성이나 기본키를 만들어내기 때문에 단지 하나의 데이터 행만을 리턴한다.
4, 유일키 또는 기본키에 의한 단일 행 접근
문장의 WHERE 절이 '=' 조건절에서 컬럼들의 유일성이나 기본키를 사용할 경우 사용될 수 있다. 복합된 키들에 대해 '=' 조건절은 AND 연산자를 통해 연결되어야 한다.
이런 문장을 실행시키기 위해 오라클은 단일 ROWID를 구하기 위해 유일성이나 기본키의 인덱스를 이용해 유일 검색을 한다. 그리고 나서 이 ROWID를 이용해 테이블을 액세스한다.
5, 클러스터 조인
두 Join 테이블이 같은 Cluster에 저장되고, 문장의 WHERE절에 한 테이블의 각 칼럼에 대해, 대응하는 테이블의 칼럼을 같게 하는 조건절을 포함하는 경우 사용될 수 있다. 복합 key에 대해 '=' 조건절은 반드시 AND 연산자에 의해 연결되어야 한다.
6, 해시 클러스터 키
문장의 WHERE절에 Hash Cluster Key인 칼럼의 '=' 조건절이 있을 경우 사용되어질 수 있다. 복합 Cluster Key에 대해 '=' 조건절은 반드시 AND 연산자로 연결되어야 한다. 이런 문장을 실행하기 위해 Oracle은 문장에 기술된 Hash Cluster Key 값을 Cluster의 Hash Function에 적용해서 Hash 값을 구한다. 그리고 나서 Oracle은 이 Hash 값을 이용해 테이블에 대한 Hash Scan을 수행한다.
7, 인덱스 클러스터 키
문장의 WHERE절에 Indexed Cluster Key인 칼럼의 '=' 조건절이 있는 경우 사용될 수 있다. 복합 Key에 대해서는 '=' 조건절은 반드시 AND 연산자로 연결되어 있어야 한다. 이런 문장을 실행하기 위해 Oracle은 Cluster Key에 의해 서술된 Row의 ROWID를 구하기 위해 Cluster Index에 대한 Unique Scan을 수행한다. 그리고 나서 Oracle은 이 ROWID과 Cluster Scan을 이용해 테이블을 액세스한다. 같은 Cluster Key 값을 가지는 모든 Row들이 같은 곳에 저장되기 때문에 Cluster Scan은 그 Row들을 찾기 위해 단지 하나의 ROWID만이 필요하다.
8, 복합 인덱스
WHERE 절에 복합 index 칼럼들의 '=' 조건절이 AND 연산자에 의해 연결 되어있는 경우에 사용될 수 있다. 이런 문장을 실행하기 위해 Oracle은 Index 대한 Range Scan을 수행하여 선택된 Row들의 ROWID를 구하고 이것을 이용해 테이블을 액세스한다.
9, 단일 컬럼 인덱스
문장의 WHERE 절에 하나나 그 이상의 단일 칼럼 Index의 '=' 조건절이 있는 경우에 사용되어질 수 있다. 여러 개의 단일 칼럼 Index에 대해 조건절은 반드시 AND 연산자에 의해 연결되어야 한다.
만약 WHERE 절에 단지 하나의 Index 칼럼을 있을 경우 Oracle은 선택된 Row들의 ROWID를 구하기 위해 Range Scan을 수행함으로써 그 문장을 실행한다. 그리고 나서 구해진 ROWID를 이용해 테이블을 액세스한다.
10, 인덱스 컬럼에서의 바운드 범위 조회
문장의 WHERE 절에 단일 칼럼 Index나 하나나 그이상의, 복합 Key의 한 중요한 부분이 되는 칼럼이 포함된 경우 사용될 수 있다.
11, 인덱스 컬럼에서 언바운드 범위 조회
문장의 WHERE절에 단일 칼럼 Index의 칼럼이나 하나나 그이상의 , 복합 Index의 한 중요한 부분이 되는 칼럼 중 하나의 조건절을 가지고 있을 때 사용 가능하다.
12, 소트 병합 조인
Join 테이블이 같은 Cluster에 저장되지 않고, 문장의 WHERE절에 있는 조건절이 Equailty 조건절이라면, 이 접근 경로가 사용될 수 있다. 이러한 문장을 실행하기 위해 Oracle은 Sort-Merge 연산을 사용한다.
13, 인덱스 컬럼의 최대 또는 최소값
다음의 조건을 만족하는 SELECT 문장에 사용될 수 있다.
- Query에 Single-Column Index나 Composite Index가 있는 칼럼의 최대값이나 최소값을 구하기 위해 MAX나 MIN 함수가 사용되는 경우. 여기서 Cluster Index는 제외.
MAX와 MIN함수의 argument는 칼럼, 상수, + 연산자, || 연산자나 CONCAT 함수 등을 포함하는 어떠한 수식도 올 수 있다.
- Select 절에 다른 수식이 오지 않아야 한다.
- 문장은 WHERE절이나 GROUP BY 절이 없어야 한다.
이런 Query를 실행하기 위해서 Oracle은 Index의 Range Scan을 이용해 최대값이나 최소값을 찾아낸다. 최대값이나 최소값을 찾게된다면 Oracle은 더 이상의 Scan은 하지 않는다.
14, 인덱스 컬럼에서의 ORDER BY 사용
다음의 조건들을 만족하는 select 문에 사용될 수 있다.
- Query에 ORDER BY절이 있어야하고, 그 ORDER BY절에 사용되는 칼럼은 Single-Column Index가 있거나, Composite Index의 Leading Portion이어야 한다.
Cluster Index는 제외.
- 그리고 PRIMARY KEY나 NOT NULL Integrity가 그 Index된 칼럼에 있어야 한다. ORDER BY절에 사용된 칼럼에 NULL이 있어서는 안 된다는 말이다.
- NLS_SORT 매개변수가 BINARY로 설정되어야 한다.
이런 Query를 실행하기 위해 Oracle은 Index의 Range Scan을 사용하여 정렬된 순서에서 해당 Row들의 ROWID들을 구한다. 그리고 나서 Oracle은 이 ROWID들을 이용해 해당 Row를 액세스한다.
15, 테이블의 전체 검색
문장의 WHERE 절과 상관없이 모든 SQL문장에 사용될 수 있다.
Rule-Based 방식의 실행계획
- 수행 가능한 실행계획을 구한다.
- Rank Table에 따라서 순위를 결정한다.
- 가장 낮은 순위를 선택하여 수행한다.
Cost-Based 방식의 실행계획
- Rule-Based 방식이 실행계획을 구하는 것과 같은 방식으로 실행계획을 구한다.
- 각 실행 계획에 대한 Cost를 구한다.
Cost는 실행계획에 사용할 CPU Time, I/O 그리고 메모리를 기준으로 계산한다.
- Cost를 비교해서 가장 낮은 실행계획을 선택하여 수행한다.
ANALYZE 명령어
Cost-Based 방식에 사용할 통계데이터를 모아 준다.
ANALYZE 명령어는 수행될 때마다 다음의 정보를 수정
TABLE
- 테이블의 행수, 사용된 블록 수, 한번도 사용되지 않은 블록 수, 사용 가능한 Space의 평균, Chained Row 수,
컬럼당 Distinct Value의 수, 컬럼당 두 번째로 가장 작은 값, 컬럼당 두 번째로 가장 큰 값.
INDEX
- Index Level, Level 블록 수, Distinct Key 수,
키당 Leaf 블록 수의 평균, 키당 Data 블록 수의 평균, Clustering Factor, 최소 키 값, 최대 키 값.
Hints 사용
Optimizer는 데이터베이스 객체에 대한 얼마나 많고, 정확한 데이터를 가지고 있는가에 대한 의존도가 높아
Optimizer가 더욱 효과적으로 SQL 연산을 수행하도록 사용자가 사용한다.
Hint를 통하여 Optimizer에게 알려 줄 수 있는 사항
- SQL 연산을 위한 Cost-Based 접근 방식의 목표,
접근방식 지정, Index 보다 더 효과적인 Scan 방법, Join 순서, 병렬연산 등급, Join 연산.
Syntax
/* + comment */
comment를 Optimizer에게 알려 준다.
Hint 기능 사용시 Syntax나 기타 오류가 발생하면 Optimizer는 오류 메시지를 사용자에게 전달하지 않고 이를 무시하고, 연산을 수행한다.
SQL TRACE
SQL을 튜닝하기 위해 현재의 SQL 문장의 수행 상태를 정확히 파악해야 하고 튜닝 작업이 의도한 대로되었는지, 효과는 어느 정도인지 알아 볼 필요가 있을 때 SQL Trace를 사용한다.
SQL Trace를 이용해 얻을 수 있는 정보(V$SESSION)
- CPU TIME, 총 수행 시간, 물리적 또는 논리적 I/O, Library Cache, Parse Count, Execute Count, Fetch Count, 수행 Recode 수.
SQL Trace의 초기화
초기 파라미터
TIMED_STATISTICS, MAX_DUMP_FILE_SIZE
USER_DUMP_DEST
세션 초기화
ALTER SESSION SET SQL TRACE = TRUE
사용중인 애플리케이션의 SQL 튜닝
분석 및 오류 찾기
Index
Index 는 Select 연산의 성능을 높이는 요소이나 Update와 Insert 시에는 부하로 작용한다.
현재의 Table에서 Index 들이 정확하게 생성되어 제기능을 다하고 있는지 확인해야 한다.
또한 EXPLAIN PLAN 기능을 사용하여 Index 들이 올바르게 사용되고 있는지 확인해야 한다.
동시 사용자
개발 단계에서 동시 사용자를 생각하지 않을 수 도 있고 Test 해볼 환경이 안될 경우도 있다.
많은 동시 사용자가 하나 혹은 두 개의 Table에 집중되어 있을 경우 개발단계에서는 발견되지 않았던 자원의 경쟁이 일어난다.
이 경우 Disk I/O 집중화 현상이 일어나는 데 우선 여러 Disk에 걸쳐서 데이터 파일을 구성하여 I/O를 분산시키고 크기가 작은 Table은 Table 생성을 다시 하여 블록 당 레코드 수를 줄이는 방법을 사용한다.
병목 현상 찾기
병목 현상을 일으키는 SQL 연산을 찾는 것은 힘든 일이다.
EXPLAIN PLAN 기능을 사용하여 CPU Time, 총 수행 시간, SQL 최적화 상태를 파악하고 액세스 경로를 변경하거나 조회되는 레코드 수를 줄이는 방법 등을 찾는다.
애플리케이션 개발과정의 SQL 튜닝
INDEX
- Index 사용을 고려해야 하는 Table의 특성
Select 연산 결과가 전체 Record 수의 5% 이내인 큰 Table, 중복된 데이터가 적은 Table, Insert, Update, Delete 연산이 적은 Table, Query 수행시 조건이 간략한 Table.
- Index 사용을 피해야 하는 Table의 특성
Select 연산시 결과가 전체 Record 수의 5% 이상인 Table, 중복된 Data가 많은 Table, Select 연산이 주로 일어나는 Table, Query 수행시 조건절이 매우 복잡한 Table.
CLUSTER
장점은 하나의 I/O 연산으로 여러 Table의 정보를 액세스 할 수 있다는 것이다 그러나 Index와 같이 성능을 저하시킬 수 있다.
- Cluster를 사용하지 않아야 할 경우
Insert 연산이 많은 경우, Cluster 구성 Table 중 하나의 Table에 Full Scan이 자주 일어나는 경우, Cluster 구성 Table들이 동시에 액세스되지 않는 경우가 많을 때.
HASH CLUSTER
데이터 블록을 액세스하기 위해 여러 번의 내부 절차를 거쳐야 하는데 해시 클러스터는 한번에 그 일을 다하기 때문에 속도가 가장 빠르다.
그러나 해시 키값이 변하지 않아야 하고 Table이 정적이어야 한다는 단점 때문에 많이 사용하지 않는다.
JOIN
오라클 시스템 자원을 많이 소비하는 연산이다.
- EQUIJOIN
조건절 Equality Condition(=)에 의해 조인이 이루어진다.
Equijoin의 성능을 높이기 위해서는 Index 기능을 사용해야 한다.
자주 사용되는 연산의 경우는 Cluster 기능사용을 고려하는 것도 좋다.
- SELFJOIN
EQUIJOIN과 같으나 하나의 Table에서 조인이 일어나는 것이 다르다.
조건절에 있는 컬럼에 Index를 생성하여 성능을 높여야 한다.
- Outer Join
Equijoin과 비슷하나 결과는 만족하는 레코드뿐 아니라 만족하지 않는 레코드도 출력한다.
Best Throughput
조인 연산시 튜닝의 목표이며, 오라클 Optimizer에게 USE_MERGE Hint를 주어야 한다.
Best Response Time
조인 연산시 튜닝의 목표이며, Optimizer에게 USE_NL Hint를 주어야 한다.
병렬 조회
Single Server Process가 수행하는 하나의 SQL 연산을 동시에 Multiple Server Process가 나누어 수행하는 것이다.
PQP (Parallel Quer Processing) 튜닝
System
I/O 대역폭 용량이 큰 System, CPU 성능이 우수한 System, Sort를 위한 메모리가 충분한 System에는 PQP가 좋은 결과를 가져온다.
I/O
PQP는 데이터 파일이 많은 디스크에 걸쳐서 분산되어 있는 환경에 적합하다.
디스크 분산을 하는 방법 중에는 랜덤 액세스가 많은 애플리케이션 환경에서는 OS 기능을 사용하고 일정량의 특정 부분 데이터를 자주 액세스하는 환경에서는 사용자가 직접하는 방법이 유리하다.
분산되는 최소의 단위는 DB_FILE_MULTIBLOCK_READ_COUNT의 2-3배가 되도록 해야 한다.
Sort를 위한 임시 스페이스는 여러 개의 디스크를 합쳐서 만들어야 PQP 도중 Sort, Merge에 걸리는 시간을 단축할 수 있다.
Parallel Degree
Parallel Degree는 성능에 미치는 영향이 매우 크다.
그러므로 많은 반복적인 시험을 통하여 결정해야 한다.
3. 오라클 인스턴스 튜닝
메모리 튜닝
운영체제 튜닝
운영체제에서 저장장치는 "Real Memory", "Virtual Memory", "Expended Storage", "Disk" 등이 있다.
O/S에 따라 Paging 또는 Swapping이 발생하면 많은 I/O를 동반하기 때문에 이 들이 일어나지 않게 하는 것이 주목적이다.
이를 위해서는 제한적인 메모리로 사용자들에게도 적당한 양을 할당하고 SGA에도 적절하게 할당해야 한다.
UNIX 환경에서는 "sar" 명령어나 모니터링 툴을 이용하여 상태를 확인한다.
SGA 튜닝
Data Dictionary Cache나 Library Cache에서 Miss가 발생하지 않도록 Shared Pool에 충분한 영역을 할당할 수 있도록 한다.
Private SQL, PL/SQL Area 튜닝
튜닝의 목적은 Parse 발생 횟수를 줄이는 것이다.
SQL Trace 기능을 이용해서 Parse 단계에서의 Count 수와 Execute 단계에서의 Count 수가 거의 같은 수로 판단되면 이를 줄여야 한다.
Private SQL, PL/SQL Area의 크기를 증가시키려면 초기 파라미터 파일의 OPEN_CURSORS 값을 증가시킨다.
Shared Pool 튜닝
튜닝의 목적은 Parse Call 수, Execute Call 수 등을 줄이고, Data Dictionary Cache에 대한 Miss를 최소화해서 오라클 성능을 최대한으로 극대화하는데 있다.
Library Cache 튜닝
Library Cache Miss는 Parse 단계와 Execute 단계에서 일어난다.
Cache Hit Rate 가 떨어지면 영역을 키우는 것이 튜닝 방법이다.
+ V$LIBRARYCACHE Table
DBA 권한을 가진 사용자만이 조회가능
SELECT SUM(pins) "Executions"
SUN(reloads) "Cache Misses while Executing"
FROM v$librarycache;
Executions Cache Misses while Executing
--------- -------------------------
320871 549
RELOADS Ratio = (reloads(549)/pins(320871)) * 100 = 0.17%
PINS : Library Cache 내의 항목이 실행된 수
RELOADS : 실행 단계에서의 Library Cache Miss의 수
RELOADS는 0에 가까워야 하고 PINS에서 RELOADS의 비율이 1% 이상이면 Library Cache Miss를 줄인다.
+ Library Cache Miss 줄이는 방법
더 많은 메모리를 할당하고 SQL을 사용할 때 다음 사항을 주의해야 한다.
1, 다음은 다른 것으로 간주하여 Shared SQL Area를 공유할 수 없다.
SELECT * FROM emp;
SELECT * FROM Emp;
2, 다른 사용자가 같은 이름의 Table을 가지고 있을 경우. 이 경우는 소유자를 표시해서 사용한다.
SELECT * FROM BOB.emp;
3, SQL 문장 내에서 바인드 변수는 이름과 데이터 타입이 틀려서는 안 된다.
SELECT * FROM emp WHERE deptno =:department_no;
SELECT * FROM emp WHERE deptno =:d_no;
4, 상수 대신 같은 이름과 데이터 타입을 가진 바인드 변수를 사용한다.
Data Dictionary Cache 튜닝
인스턴스가 Startup 되는 시점에 Data Dictionary Cache에 데이터들이 없기 때문에 Miss가 발생.
사용할 수 있는 메모리를 증가시키기 위해 SHARED_POOL_SIZE를 증가시킨다.
Buffer Cache 튜닝
Cache Hit Ratio를 높이는 것이 튜닝의 목적이다.
V$SYSSTAT
DBA 사용자만 조회가 가능하고 Cache Hit Ratio를 구할 수 있다.
DB BLOCK GETS, CONSISTENT GETS : 합은 데이터를 요구하는 총 횟수.
PHYSICAL READS : 디스크상의 데이터 파일을 액세스한 Request의 총 횟수.
SELECT name, value
FROM v$sysstat
WHERE name IN ('db block gets', 'consistent gets',
'physical reads');
Cache Hit Ratio = 1- (physical reads / (db block gets + consistent reads))
Hit Ratio가 60-70% 보다 작다면 성능 향상을 위해 Cache에서 Buffer 수를 증가시켜야 하는데
DB_BLOCK_BUFFERS 값을 크게 하면 된다.
X$KCBRBH
Buffer 수가 증가함에 따라 Cache Hit Ratio의 변화를 알 수 있는 Table이다.
Buffer 수를 조정한 후에 그 효과를 알아보기는 좋으나 이를 수행함에 따라 부하가 발생한다는 것을 명심해야 한다.
X$KCBCBH
Buffer를 줄임으로서 나타나는 ACM(Additional Cache Miss) 값을 알아보는 Table.
DB_BLOCK_LRU_STATISTIC = TRUE
예) 현재 Buffer가 100이라면 90으로 줄였을 때 ACM?
SELECT SUM(count) acm
FROM sys.x$kcbcbh
WHERE indx >= 90;
INDX : Cache 내의 가능한 Buffer 수
COUNT : INDX에 기인한 ACM
I/O 튜닝
애플리케이션이 시스템의 어떤 자원을 사용하느냐에 따라 I/O Bound 업무와 CPU Bound로 구분할 수 있다.
실제 애플리케이션이 물리적 Read/Write 연산에 많은 시간을 소비하고 있기 때문에 I/O에 대한 튜닝이 중요하다.
디스크 마찰(Disk Contention)
동시에 많은 사용자가 같은 디스크 블록을 Access 하고자 할 때 발생하는 현상.
V$FILESTAT
SELECT name, phyrds, phywrts
FROM V$datafile df, V$filestat fs
WHERE df.file# = fs.file#
+ phyrds : 각 데이터 파일을 읽는 횟수
+ phywrts : 각 데이터 파일에 저장한 횟수
I/O 분산
디스크나 디스크 컨트롤러에 초과되는 Request 처리를 요구받은 디스크들을 찾아내어 이들을 분산시키는 것이다.
- 데이터 파일, 리두 로그 파일 분리
리두 로그 파일은 트랜잭션이 완료되면 그에 대한 로그를 기록하는 파일이다. 또 한 Request를 순차적으로 받아 처리한다.
그러므로 Random 액세스가 주로 일어나는 데이터 파일과 분리하면 그 효과가 크다.
장애에 대비하여 미러링 하는 것이 좋다.
- Striping
하나의 데이터를 세그먼트로 분할하여 여러 개의 디스크 장치로 분산시키는 것.
- Table과 Index 분리
Table에 저장되어 있는 데이터를 access하기 위해서는 Index를 참조하는데 이 들이 한 디스크에 있을 경우는
특정 디스크에 과부하가 발생한다.
- 오라클 사용 디스크 분리
다른 애플리케이션 요소나, OS에서 사용하는 디스크들을 서로 분리하는 것이 오라클에 대한 외부의 영향을 최소화할 수 있다.
데이터 블록
데이터들은 Table 안에 데이터 블록이라는 단위 영역별로 저장된다.
한 데이터 블록에 많은 데이터가 있어야 성능에 도움이 되는 경우가 있고, 적어야 도움이 되는 경우도 있다.
사용중에 데이터 블록이 더 필요해서 계속 할당받으면 성능이 저하될 수 있기 때문에 이를 방지하기 위한 노력이 필요하다.
Update/Insert 연산과 데이터 블록
- Migration : Update가 자주 발생하여 데이터 블록에 더 이상 데이터를 저장할 공간이 없는 경우 오라클은 충분히 큰 데이터 블록을 찾아서 원래 데이터 블록에 있는 내용들을 새로운 곳으로 이동시킨다.
- Chaining Row : 충분히 큰 데이터 블록이 없는 경우 원래 데이터 블록의 데이터를 나누어서 다른 데이터 블록에 분산 저장한다.
Migration과 Chaining을 방지하는 근본적인 방법은 충분히 큰 데이터 블록을 할당해 주는 것이다.
Table 생성시 PCTUSED, PCTFREE 기능을 사용하여 Update, Insert 연산시 필요한 영역을 데이터 블록에 마련하는 방법도 있다.
이미 발생한 Migration과 Chaining의 처리 방법
+ ANALYZE 명령을 이용해 정보를 구한다.
ANALYZE TABLE order_hist LIST CHAINED ROWS;
+ 출력 데이터를 본다.
SELECT OWNER_NAME, TABLE_NAME,
HEAD_ROWID, TIME
FROM chained_rows
WHERE table_name = 'ORDER_HIST';
+ Migrated되거나 Chain된 데이터 행을 가지고 있다면 다음 단계를 거쳐 이러한 행들을 제거한다.
- Migrate되거나 Chain된 데이터 행을 가지고 있는,
실제 Table과 같은 컬럼을 가지고 있는 임시 테이블을 생성한다.
- 실제 Table에서 Migrate되고, Chain된 데이터 행들을 지운다.
- 임시 Table의 데이터 행들을 실제 테이블에 Insert한다.
- 임시 Table을 제거한다.
+ 출력 Table에서 모아진 정보를 지운다.
DELETE FROM chained_row
WHERE table_name = 'ORDER_HIST';
+ 다시 ANALYZE 명령을 이용해 정보를 모으고, 그 정보를 본다.
+ 출력 파일에 Row가 나타나면 데이터 블록의 크기를 증가시켜 이전 Chain된 행을 없앤다.
모든 상황에서 이런 Chaining을 피할 수는 없다.
만약 LONG 컬럼이나 긴 CHAR, VARCHAR2 컬럼을 가지고 있다면 이런 Chaining을 피할 수 없다.
Dynamic Space Management 최소화
DB 객체들의 생성 초기에 할당받은 영역이 사용 중에 부족하면 이들을 증가시켜야 한다.
이런 현상은 과다한 I/O Request를 동시에 발생시키므로 성능에 저해요소가 된다.
그러므로 객체를 생성할 때 예상되는 스페이스를 미리 할당하는 것이 바람직하다.
동적 확장 검사
Recursive Call : 사용자가 수행하는 SQL 연산과 함께 오라클 사용자들이 요구한 SQL 문장을 수행하기 위해 새로운 SQL 문장들을 만들어 수행.
+ Recursive Call이 발생하는 경우
- Data Dictionary Cache에 Miss가 발생했을 경우
- 데이터베이스 트리거 수행
- DDL 문장의 실행
- Stored Procedure, Function, Package 그리고 Anonymous PL/SQL 블록 등이 가지고 있는 SQL 문장의 수행
- Referential Integrity Constraint의 실행
SELECT name, value
FROM v$sysstat
WHERE name = 'recursive calls';
Extent의 확장
큰 Extent 안에 여러 개의 데이터 블록을 한 번에 액세스하는 것이 성능 향상에 좋다.
그러므로 Table 중 성능이 빠른 것은 여러 개의 작은 Extent를 자주 할당받게 하는 것보다 큰 Extent가 한번에 할당되도록 하는 것이 좋다.
Contention 튜닝
Rollback Segment Contention
롤백세그먼트 블록을 포함하는 버퍼를 기다리는 수를 보고 판단할 수 있는데 V$WAITSTAT를 이용해서 rollback segment에 대한 contention이 성능을 감소시키고 있는지 어떤지를 결정할 수 있다.
Default로서 이 테이블은 SYS 유저와 SYSTEM와 같은 SELECT ANY TABLE 시스템 권한을 가진 다른 유저들만이 사용할 수 있다.
SELECT class, count
FROM v$waitstat
WHERE class IN ('system undo header',
'system undo block',
'undo header', 'undo block');
CLASS COUNT
------------------- -------------
system undo header 2089
system undo block 633
undo header 1235
undo block 942
·system undo header : SYSTEM rollback segment의 header block을 포함하는 buffer를 기다리는 수이다.
·system undo block : SYSTEM rollback segment의 header block 이외의 다른 block들을 포함하는 buffer를 기다리는 수이다.
·undo header : SYSTEM rollback segment의 header block 이외의 다른 rollback segment의 header block을 포함하는 buffer들을 기다리는 수이다.
·undo block : SYSTEM rollback segment이외의 다른 rollback segments 의 header block과는 다른 blocks을 포함하는 buffer들을 기다리는 수이다.
위에 나타난 class 별로 기다리는 Buffer 수와 같은 시간 동안 총 Request 수를 비교하면 Contention 정도를 알
수 있다.
SELECT SUM(value)
FROM v$sysstat
WHERE name IN ('db block gets', 'consistent gets');
SUM(VALUE)
------------
929530
이 결과값과 각 Class별 Count값들을 비교해서 1%가 넘으면 롤백 세그먼트의 수를 늘려야 한다.
Redo Log Buffer Contention
리두 로그 파일에 Log Entry를 기록하기 위해서는 리두 로그 버퍼를 거쳐서 내려가야 하는데 Request 가 많을 경우 사용자 프로세스는 대기하는 시간이 길어지게 된다. 이를 단축시키기 위해 리두 로그 버퍼를 늘려 주어야 한다.
SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests';
결과 값이 0에 가까울 때까지 5%씩 리두 로그 버퍼 수를 늘려야 하는데 이는 오라클 초기 파라미터의 LOG_BUFFER의 값을 키우면 된다.
Redo Log Buffer Latch
리두 로그 버퍼 액세스는 Latch에 의해서 조정된다.
- Redo Allocation Latch
리두 로그 버퍼에서 Redo Entry를 위한 공간을 할당하는 것을 제어한다.
Buffer에 공간을 할당할 때는 오라클 사용자 프로세스가 반드시 Redo Allocation Latch를 가지고 있어야 한다.
만약 하나의 Redo Allocation Latch가 있다면 한 번에 단 하나의 사용자 프로세스만 버퍼에 공간을 할당하는 것이 가능하다.
초기 파라미터의 LOG_SMALL_ENTRY_MAX_SIZE값에 따라 정해진다.
Contention을 최소화 하려면
LOG_SMALL_ENTRY_MAX_SIZE값을 감소시킨다.
- Redo Copy Latch
Redo Entry가 너무 커서 Redo Allocation Latch에 복사할 수 없다면, 사용자 프로세스는 버퍼에 Entry를 복사하기 전에 Redo Copy Latch를 갖고 있어야만 한다.
Redo Copy Latch를 잡고 있는 동안 사용자 프로세스는 버퍼에 할당된 공간 안에 Redo Entry를 복사한 후 Redo Copy Latch를 해제하게 된다.
Redo Copy Latch의 수는 초기 파라미터 LOG_SIMULTANEOUS_COPIES에 의해 결정된다.
Contention을 최소화하려면
LOG_SIMULTANEOUS_COPIES의 기본값은 현재 CPU의 수로 되어 있는데 이를 두 배정도 늘리면 성능에 도움이 된다.
Sort 튜닝
대부분 애플리케이션에는 기본 Sort 영역으로도 성능상 무리는 없으나, 필요에 따라서 Sort 영역이 부족하다고 판단되면 이를 늘려야 한다. (오라클 초기 파라미터 SORT_AREA_SIZE 값을 증가)
SELECT name, value
FROM v$sysstat
WHERE name IN('sorts(memory)', '(sorts(disk)');
sorts(memory) : 디스크상의 Temporary Segment와 I/O 없이 Sort 영역에서만 이루어지는 Sort 수.
sorts(disk) : Sort 영역에서만 처리하기에는 데이터가 너무 커서 Temporary Segment와 I/O가 필요한 Sort 수.
Sort 영역을 크게 하면 Sort 관련 작업들은 속도가 향상되나, Private SQL과 PL/SQL을 위한 메모리 등 다른 요소에 영향을 줄 수 있으므로 주의해야 한다.
- NOSORT
Index 생성시 Sort가 발생한다. 반드시 Sort할 필요가 없는 Index는 NOSORT 기능을 사용하여 부하를 줄이는 것이 좋다.
CREATE INDEX emp_index
ON emp(ename)
NOSORT;
Free List Contention 튜닝
동시에 한 Table에 Insert 연산이 많이 발생하는 경우에는 Free List가 부족한 현상이 발생하고 이는 성능 저해 요인이 된다.
SELECT class, count
FROM v$waitstat
WHERE class = 'free list';
Free List 대기 횟수의 비율이 1% 이상이면 충돌감소를 위해 Free List의 추가 배정을 고려한다.
FREELISTS Storage Parameter값을 증가하여 Table을 다시 생성하면 된다.
Checkpoint 튜닝
Checkpoint가 자주 발생하면 만약의 경우에 발생할 인스턴스 Failure와 같은 상황에서 Recovery 시간을 줄여 주지만, 오라클 성능 저하 요인이 되기도 한다.
수행속도가 중요한 환경에서는 Checkpoint 빈도수를 줄이고, 안정성이 중요한 환경에서는 Checkpoint 빈도수를 늘린다.
- Checkpoint 빈도수를 최소화하려면
LOG_CHECKPOINT_INTERVAL값을 가장 큰 Redo Log 파일보다 크게 세팅.
LOG_CHECKPOINT_TIMEOUT값을 0으로 세팅.
- Checkoint 빈도수를 늘리려면
LOG_CHECKPOINT_INTERVAL값을 작게 세팅.
LOG_CHECKPOINT_TIMEOUT값을 작게 세팅.
초기 파라미터에서 CHECKPOINT_PROCESS = TRUE
4. 서버 튜닝
프로세스(Process)
전체 사용자 프로세스 수와 오라클 인스턴스 관련 프로세스 수 그리고 OS 관련 프로세스 수들의 합이 전체 프로세스의 수가된다.
전체 프로세스 수의 10-30% 이상의 프로세스들이 지장 없이 수행될 수 있는 환경을 제공해야 한다.
오라클 인스턴스 프로세스와 오라클 사용자 프로세스는 우선 순위를 같게 해야 한다.
메모리
SGA가 작은 경우 CPU idle 상태가 많이 발생한다.
이는 SGA가 작아서 오라클 인스턴스는 I/O Wait에 많은 시간을 소비하기 때문이다.
NetWare, NT 환경
- 다른 프로세스와 같은 메모리를 사용.
UNIX 환경
- Shared 메모리라는 특수한 종류의 메모리를 사용.
- Page Size Entension(PSE)라는 기능(OS 부팅시 오라클을 위해 미리 많은 양의 메모리를 예약)을 사용하면 성능에 많은 도움이 되나, 이 메모리를 오라클이 다 사용하지 않아도 다른 프로세스는 사용할 수 없다.
I/O
중요한 튜닝 요소는 I/O 대역폭, I/O 메소드, 블록 크기.
블록 크기는 I/O 부하와 많은 관계가 있다.
OLTP, 랜덤 액세스가 많은 환경 - 2k 또는 4k
DSS, 시퀀셜 액세스가 많은 환경 - 4k 또는 8k
OS 블록 크기와 DB 블록 크기를 같게 하거나 OS 블록크기가 DB 블록 크기에 두 배가 되도록 하는 것이 성능을 좋게 하는 요인이 된다.
I/O 메소드 중에는 OS 파일 시스템 버퍼를 사용하는 일방적인 방식이 있고, OS 파일 시스템 버퍼를 사용하지 않는 Raw Device 방식이 있다.
+ Sun Solaris : Filesystem, Raw Device에서 Async I/O 사용가능
+ SCO : Raw Device에서 Async I/O 사용가능
+ Pyramid : Raw Device에서 Async I/O 사용가능
+ Sequent : Raw Device에서 Async I/O 사용가능
Raw Device 방식은 OS의 부하를 줄일 수 있고, I/O 성능을 높일 수 있다.
Asynchronous I/O 방식은 오라클 I/O 성능을 크게 높일 수 있는 방법이다.
O/S 는 Request들을 모아서 디스크 컨트롤러를 요구하고 다른 작업을 할 수 있다.
나중에 디스크 컨트롤러에서 응답이 오면 인터럽트를 받는다.
Asynchronous I/O 방식은 다른 방식과 비교하여 10-30%의 성능을 향상시킬 수 있다.
예) SCO UNIX
$chown oracle /dev/async
$chgrp dba /dev/async
ASYNC_WRITE = TRUE
UnixWare
$chown oracle /dev/async
$chgrp dba /dev/async
USE_ASYNC_TO = TRUE
LGWR_USE_ASYNC_IO = TRUE
Post-Wait Semaphore
오라클이 사용자에게 Sleeping Signal을 줄 수 있게하고, 다시 일을 할 수 있게 해주는 새로운 형태의 Semaphore이다.
CPU의 Idle Cycle을 크게 줄여 성능을 향상시킨다.
Post-Wait Semaphore의 기능을 사용하려면.
- 오라클 초기 파라미터 수정.
USER_POST_WAIT_DRIVER = TRUE
POST_WAIT_DEVICE = /dev/pw
Device 이름은 시스템마다 다를 수 있다.
- Device의 사용 권한을 수정.
%chown oracle /dev/pw
%chgrp dba /dev/pw
- Post-Wait Device가 사용되는지 확인.
%ipcs
5. Performance Parameter
오라클 초기 파라미터 변수들은 오라클 성능에 많은 영향을 준다.
변수들을 이해하고 필요에 따라서 적절하게 수정하고 사용하면 성능 향상에 많은 도움이 된다.
출처 : http://zenos.pe.kr/
'DataBase > Oracle' 카테고리의 다른 글
| [펌]오라클 날짜처리 함수 (0) | 2009.09.03 |
|---|---|
| [펌]오라클 문자 함수 (0) | 2009.09.03 |
| [펌]COMMENT 컬럼이나 테이블에 코멘트 달기 (0) | 2009.09.02 |
| [펌]테이블스페이스 만들기 (0) | 2009.09.02 |
| [펌]테이블스페이스 관리 (0) | 2009.09.02 |