데이터 베이스 성능 튜닝 두번째 데이터베이스Call최소화

Call 통계

Parse Call : 커서를 파싱하는 과정에 대한 통계 (실행계획 생성하거나 찾음)

Execute Call : 커서를 실행하는 단계에 대한 통계

Fetch Call : 레코드를 읽어 사용자가 요구한 결과집합을 반환하는 과정에 대한 통계

select : execute 단계에서는 커서만 오픈, 실제 데이터 처리과정은 fetch에서 이루어짐

dml : execute 단계에서 모든 처리, fetch call은 없슴

User Call : 오라클 외부에서 들어오는 Call

Recursive Call : 오라클 내부에서 발생하는 Call

User Call을 줄이려면....

1. One-Sql 구현

2. Array Processing

3. 부분범위처리 활용

4. 페이징 처리

5. 사용자정의함수/프로시져 활용

Recursive Call을 줄이려면...

1. 바인드변수 사용

2. 무분별한 Pl/sql 사용 금지하고 적절한 경우만 사용

3. 대용량 쿼리에서 함수 호출 자재->조인 OR 스칼라 서브쿼리로 대체

PL/SQL은 빈번히 호출시 컨텍스트 스위칭 때문에 성능 저하

Call 구분	User	Recursive
Parse	Parse Call 최소화 -> 애플리케이션 커서 캐싱 (사용자 정의 함수/프로시저에서는 자동으로 기능)
Execute	Array Processing One-Sql 구현 AP 설계 및 표준 가이드	Array Processing 함수 반복 호출 최소화 ->조인.스칼라서브쿼리사용
Fetch	Array 단위 Fetch 부분범위처리 활용 페이지 처리	Cursor For Loop 문 사용(10g)

데이타베이스 CALL을 최소화 하기 위한 방법

1. SELECT 한 후 LOOP 돌면서 INSERT 하는 쿼리는

INSERT INTO ~

SELECT ~

형태로 바꾸는걸 고려

2. 같은 테이블을 탐색하는 다수의 INLINE VIEW

CASE 구문이나 DECODE로 변경

3. 여러개의 테이블에 저장할 때는 INSERT ALL 이나 INSERT FIRST 활용

insert all / first
when sal in (800,1600) then
into emp (empno,ename,job) values (empno,ename,job)
when sal = 3200 then
into emp_1 (empno,ename,job) values (empno,ename,job)
else
into emp_2 (empno,ename,job) values (empno,ename,job)
select empno,ename,job
from emp

INSERT FIRST 첫번째 조건을 만족하면 다음 조건은 보지 않음

INSERT ALL 첫번째 조건을 만족해도 모든 조건을 다봄

Array Processing 활용

java

addBatch, setFetchSize 를 활용해서 array size 늘려줌 (java 기본은 10)

pl/sql

FOR ALL i in p_empno.first..p_empno.last INSERT INTO emp2~~

~~

FETCH  c BULK COLLECT INTO col1, col2 LIMIT l_fetch_size

Fetch Call 최소화

부분범위처리

ArraySize 조정으로 Fetch Call 최소화

->ArraySize를 키울수록 어느 임계점까지 Fetch Count, Block I/O가 줄어들음..

->OLTP 환경에서는 부분으로 보여주는 경우가 많으므로 적게 DW성에서는 전체를 대상으로 하기때문에 많이 잡아주는게 유리

SDU(Session Data Unit) : 세션 레이어 데이터 버퍼에 대한 규격, 네트워크를 통해 전송하기 전에 Oracle Net이 데이터를 담아두려고 사용하는 버퍼

TDU(Transport Data Unit) : Transport 레이어 데이터 버퍼에 대한 규격, 물리적인 하부 레이어로 내려보내기 전에 데이터를 잘개 쪼개어 클라이언트에게 전송되는 도중에 유실이나 에러가 없도록 제어

pl/sql

for item in cusor

loop

....

end loop;

java

stmt.setFetchSize(100); --Statement

rs.setFetchSize(100); --ResultSet

-> rs.next() 시 최초 100건을 가져와서 Client Array 버퍼에 캐싱 이후 100건을 소진시까지 Array버퍼에서 가져옴 100건 이후부터는 다시 100건을 가져옴

pl/sql 함수 사용의 주의점

인터프리터 언어로서 실행시마다 sql 실행엔진과 pl/sql 가상머신 사이에 컨텍스트 스위칭 발생, 대부분 생성시 Recursive Call을 포함하므로  Execute Call, Fetch Call이 기하 급수적으로 늘어남

--> 사용자 정의함수는 소량의 데이터 조회에만 사용하거나 대용량이라도 부분범위 처리가 가능한 환경에서만 사용--> 가급적 조인이나 스칼라 서브쿼리 이용

--> 조건절에 함수 사용시 인덱스 엑세스를 이용하는 조건에 사용해서 호출 회수 줄임

--> 함수내에서 수행되는 Recursive 쿼리는 메인쿼리의 시작 시점과 무관하게 그 쿼리가 수행되는 시점을 기준으로 블록을 읽기 때문에 문장수준 일관성이 보장 안된다.  -> 가급적 조인이나 스칼라 서브쿼리를 이용해야 한다.

--> 변경이 자주 안 일어나는 코드성 테이블에 사용 혹은 코드성 테이블을 생성

--> decode나 case문 활용

--> 뷰머징 방지 (transformer가 쿼리변환으로 뷰머징 했을 경우를 방지) no_merge 힌트나 rownum 삽입

--> (SELECT FN_FUNC() FROM DUAL) 처럼 스칼라 서브쿼리로 만들어 캐싱효과를 주자. 입력값이 많으면 해시탐색에 걸리는 시간이 많으므로 주의해서 사용

--> Deterministic 사용 (스칼라 서브 쿼리는 uga에 입력되서 입/출력값이 db call 에 상관없이 끝까지 유지하지만 Deterministic은 cga에 입력되서 db call 에 한해서만 유지: fetch call 완료되면 소멸) 일반함수처럼 일관성 보장 못하므로 동일 결과를 출력하는 함수만 사용

 

오라클 성능고도화