포로젝트를 개발함에 있어 시작부터 끝까지 튜닝에 대한 마인드를 가지고 모든 일을 처리하는 것이다.

튜닝 절차

애플리케이션 형태
애플리케이션 형태를 알면 튜닝 포인트를 찾는데 도움이 된다.

- OLTP(Online Transaction Processing)

대부분의 업무는 Insert/Update 이다.
예) 은행의 온라인 업무, 항공사의 예약 업무, 주문 시스템
데이터의 정확성과 가용성이 중요.

튜닝 항목
롤백 세그먼트, 인덱스, 클러스터, 해싱, 데이터 블록 크기, 테이블 또는 롤백 세그먼트의 동적 할당, 트랜잭션 처리 모니터, 멀티 스레드 서버, 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의 내부 프로시저 상관 관계]







































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

       CLASS                    COUNT

-------------------      -------------

system undo header                2089

system undo block                 633

undo header                       1235

undo block                        942