SQLP

Prefetch라는 용어는 여러가지 의미로 쓰인다. 하지만 여기에서는 테이블 Prefetch와 인덱스 Prefetch를 지칭하는 것이다. 모든 DBMS는 디스크 블록을 읽을 때 곧이어 읽을 가능성이 높은 블록을 미리 읽어오는 Prefetch 기능을 제공한다. 디스크 I/O가 비용이 크기 떄문에 한번의 I/O Call을 통해 다량의 블록을 퍼 올릴 수 있다면 성능향상이 되기 때문이다. MultiBlock I/O도 또한 Prefetch 기능 중 하나라고 볼 수 있다. 테이블 Prefetch와 인덱스 Prefetch와 다른 점은 한 익스텐트에 속한 인접한 블록들을 Prefetch 한다는 점이다. Prefetch는 한 번에 여러 개 Single Block I/O를 동시에 수행하는 것을 이야기하며 MultiBlo..

Call 통계를 보면 버퍼 캐시에 69개 블록을 읽으면서 그 중 64개는 디스크에서 읽었다 버퍼 캐시 히트율은 7.24%이다. 디스크에서 읽은 블록의 개수가 64개라고 I/O Call까지 64번 발생한다고 의미하지 않는다. 읽고자 하는 블록을 버퍼 캐시에서 찾지 못했을 경우 I/O Call을 통해 데이터파일로부터 버퍼 캐시에 적재하는 방식에는 크게 두가지 방식이 존재한다.- Single Block I/O- MultiBlock I/O Single Block I/O는 한번의 I/O Call에 하나의 데이터 블록만 읽어 메모리에 적재하는 것이다. 인덱스를 통해 테이블을 액세스할 때 인덱스와 테이블 블록 모두 이 방식을 사용한다. MultiBlock I/O는 I/O Call이 필요한 시점에 인접한 블록들을 같이..

I/O 효율화 튜닝의 중요성 디스크를 경유한 입출력은 물리적으로 액세스 암이 움직이면서 헤드를 통해 데이터를 읽고 쓰기 때문에 느린 반면 메모리를 통한 입출력은 전기적 신호이기 때문에 매우 빠르다. 그래서 모든 DBMS는 버퍼 캐시를 경유해 I/O를 수행한다. DB 버퍼캐시를 경유한다는 것은, 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾고 찾지 못하면 디스크에서 읽는다. 메모리로부터 블록을 찾아진다면 디스크 상의 데이터파일에서 읽는 것보다 훨씬 나은 성능을 보인다. 물리적인 디스크 I/O가 필요할 떄면 서버 프로세스는 I/O 서브시스템에 I/O Call을 발생시키고 잠시 대기하게 되므로 비용이 크다. 디스크에 발생하는 경합이 심할수록 대기시간도 길어진다. 만약 모든 데이터와 인덱스 블록을 DB 버퍼 캐시에..

데이터베이스 성능 튜닝의 핵심 요소는 아래와 같다.- 라이브러리 캐시 최적화- 데이터베이스 Call 최소화- I/O 효율화 및 버퍼캐시 최적화 DBMS들은 I/O는 블록 단위로 이루어진다. 블록단위로 I/O 한다는 것은, 하나의 레코드에서 하나으 ㅣ컬럼만 읽으려 할 때도 레코드가 속한 블록 전체를 읽게 된다. 하나의 블록을 액세스해 그 안에 저장돼 있는 모든 레코드를 순차적으로 읽어들인다면 무거운 디스크 I/O를 수반하더라도 비효율은 없다. 하지만 레코드 하나를 읽기 위해 블록을 통째로 액세스하면 메모리 버퍼에서 읽더라도 비효율이 존재한다. 우리는 전자를 Sequential 액세스, 후자를 Random 액세스라고 한다. 블록단위 I/O 원리 때문에 아래 두 쿼리를 처리할 때 서버에서 발생하는 I/O 측면..

사용자 정의 함수는 소량의 데이터 조회 시에만 사용하는 것이 좋으며 대용량 데이터를 조회할 떄는 부분범위처리가 가능한 상황에서 제한적으로 사용해야하며 될 수 있으면 조인 또는 스칼라 서브쿼리 형태로 변환하는 것이 좋다. 만약에 함수를 쓸 수 밖에 없는 상황이면 호출 횟수를 최소화 해야한다. 아래는 함수 호출 부하 해소 방안이다.- 페이지 처리 또는 부분범위처리 활용- Decode 함수 또는 Case 문으로 변환- 뷰 머지 방지를 통한 함수 호출 최소화- 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화- Deterministic 함수의 캐싱 효과 활용- 복잡한 함수 로직을 풀어 SQL로 구현페이지 처리 또는 부분범위처리 활용SELECT *FROM ( SELECT ROWNUM NO, ..

PL/SQL 함수의 특징 PL/SQL은 JAVA처럼 인터프리터 언어이기 때문에 Native 코드로 완전 컴파일된 내장 함수에 비해 느리다. 왜냐하면 작성한 함수 실행 시 매번 SQL 실행엔진과 PL/SQL 가상머진 사이에 컨텍스트 스위칭이 일어난다. SQL에서 함수를 호출할 때마다 SQL 실행엔진이 사용하던 레지스터 정보들을 백업했다가 PL/SQL 엔진이 실행을 마치면 다시 복원을 하는 시스템이기 때문이다.Recursive Call를 포함하지 않는 함수의 성능 부하 여기에 가장 흔히 볼 수 있는 사용자 정의 함수 남용 사례이다. 날짜형 데이터를 공통 문자열 포맷으로 변환하려고 함수를 정의하는 경우이다. 오라클 내장함수 to_char와 사용자 정의 함수를 사용할 경우의 수행시간을 비교해보면 0.66초로 수..

대용량 데이터를 조회할 때 커서를 닫지 않은 채 사용자 이벤트가 발생할 때마다 결과 집합을 Fetch 하도록 구현할 수 있었지만 웹 애플리케이션 환경에서는 커서를 계속 오픈한 채로 결과집합을 핸들링할 수 없다. 즉, 데이터베이스와의 연결을 지속하지 않는 웹 환경이기 때문에 사용자가 다음 페이지를 요청하거나 스크롤 바를 내릴 때마다 개별적인 SQL문을 수행하는 방식으로 페이지 처리를 구현해야 한다.i := 0;loop fetch c into l_record; exit when (c%notfound or (i=pageNo * pageSize)); i := i+1; if i > (pageNo - 1) * pageSize then printRow(l_record); end if;end l..

여기에서 할 내용을 아래와 같다.- 부분범위처리 원리- OLTP 환경에서 부분범위처리에 의한 성능개선 원리- ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과- 프로그램 언어에서 Array 단위 Fetch 기능 활용부분범위처리 원리create table t ( x number not null, y number not null);INSERT INTO tSELECT *FROM (SELECT rownum x, rownum y FROM dual connect by level 0 AND y x,y 두 컬럼에는 같은 값을 입력했고, x 컬럼으로만 구성된 인덱스가 생성돼 있다. x>0인 조건으로 인덱스를 스캔하도록 했는데 모든 레코드가 0보다 크기 때문에 ..

Array Processing 기능을 활용하면 한번의 SQL 수행으로 다량의 로우를 동시에 insert,update,delete할 수 있다. 이는 네트워크를 통한 데이터베이스 Call을 감소시켜주고 이를 통해 SQL 수행 시간 및 CPU 사용량을 줄여준다.CREATE OR REPLACE PROCEDURE process_monthly_payment( p_input_month IN VARCHAR2)IS -- 커서 선언 CURSOR c_payment_data IS SELECT customer_no, payment_month, giro_amount, auto_transfer_amount, ..

여기에 두 테이블이 존재한다. '월요금납부실적'테이블은 고객별 납입방법별 납입요금을 칼럼 값으로 입력하고 '납입방법별_월요금집계' 테이블은 납입요금을 납입방법코드별로 하나의 레코드로 입력한다. 기간계 시스템에서는 요금납부실적을 주로 고객별로 조회하기 때문에 흔히 전자처럼 모델링한다. 반면 정보계 시스템에서는 다차원 분석을 많이하기 때문에 후자처럼 납입방법코드를 PK로 끌어 올리는 것이 일반적이다. 이런식으로 기간계와 정보계 시스템이 서로 다른 데이터모델을 사용하기 때문에 발생하는 데이터 가공 요건이 매우 흔하며 '월요금납부실적'테이블을 이용해 '납입방법별_월요금집계'테이블 형태로 가공하는 ETL 배치 프로그램이 필요하다고 가정해보자.기간계시스템 : 핵심 업무를 처리하는 실시간 트랜잭션 시스템(실시간)정보..

데이터베이스 Call을 커서의 활동상태에 따라 Parse, Execute, Fetch로 나누었다. Call이 어디서 발생했느냐에 따라 User Call과 Recursive Call로 나눌 수도 있다. SQL 트레이스 파일을 TKProf 유틸리티로 포멧팅하면 맨 아래쪽에 아래와 같은 Overall Total 통계가 나온다. 이 중 NON-RECURSIVE 통계가 User Call에 해당하며 아래쪽에 RECURSIVE 통계가 Recursive Call에 해당한다. User Call은 OCI를 통해 오라클 외부로부터 들어오는 Call을 말한다. 위 그림에서 User Call이 클라이언트가 아닌 WAS에서 발생하는 이유는, 실제 프로그램 사용자가 어디에 위치하느냐와는 별개로 DBMS 입장에서의 사용자는 WAS이..

SQL 트레이스 레포트에서 Call 통계 부분만 발췌하면 위의 사진과 같다. 이는 커서의 활동상태를 Parse, Execute , Fetch 세 단계로 나누어 각각에 대한 수행통계를 보여준다. Parse Call은 커서를 파싱하는 과정에 대한 통계로, 실행계획을 생성하거나 찾는 과정에 관한 정보를 보여준다. Execute Call은 커서를 실행하는 단계에 대한 통계를 보여준다. Fetch Call은 select문에서 실제 레코드를 읽어 사용자가 요구한 결과집합을 반환하는 과정에 대한 통계를 보여준다. insert, update, delete, merge 등 DML 문은 Execute Call 시점에 모든 처리과정을 서버 내에서 완료하고 처리 결과만 리턴하므로 Fetch Call이 전혀 발생하지 않는다. ..