01. Call 통계
Parse Call - 커서를 파싱하는 과정에 대한 통계로서, 실행계획을 생성하거나 찾는 과정에 관한 정보를 포함한다.
Execute Call - 커서를 실행하는 단계에 대한 통계를 보여준다.
Fetch Call - select문에서 실제 레코드를 읽어 사용자가 요구한 결과집합을 반환하는 과정에 대한 통계를 보여준다.
※inset, update, delete, merge등 DML문은 Excute Call 시점에 모든 처리과정을 서버 내에서 완료하고 처리결과만 리턴하므로
Fetch Call이 전혀 발생 하지 않는다. (insert....select문도 클라이언트로부터 명시적인 Fetch Call을 받지 않으며 서버 내에서 묵시적으로 Fetch가 이루어진다.)
- select문일 때 Execute Call 단계에서는 커서만 오픈하고, 실제 데이터를 처리하는 과정은 모두 Fetch 단계에서 일어난다.
02. User Call vs. Recursive Call
-> NON-RECURSIVE STATEMENTS : USER CALL에 해당
-> RECURSIVE 통계 : RECURSIVE CALL에 해당
--> CALL이 어디서 발생하느냐에 따라 나눌 수 있다.
- User Call : OCI(Oracle Call Interface)를 통해 오라클 외부로부터 들어오는 Call을 말한다. 그림에서 User Call이 클라이언트가
아닌 WAS에서 발생하는 이유는, 실제 시스템 사용자가 어디에 위치하느냐와는 별개로 DBMS 입장에서의 사용자는 WAS이기 때문
이다.
- Peak 시간대에 시스템 장애를 발생시키는 가장 큰 주범은 User Call이다. 개발자의 기술력에 의해서도 좌우되지만 많은 경우
애플리케이션 설계와 프레임워크 기술구조에 기인한다.
<User Call을 최소화하기 위한 기능과 기술>
1. Loop 쿼리를 해소하고 집합적 사고를 통해 One_SQL로 구현
2. Array Procession : Array 단위 Fetch, Bulk Insert/Update/Delete
3. 부분범위처리 원리 활용
4. 효과적인 화면 페이지 처리
5. 사용자 정의 함수/프로시저/트리거의 적절한 활용
- Recursive Call : 오라클 내부에서 발생하는 Call을 말한다. SQL 파싱과 최적화 과정에서 발생하는 Data Dictionary 조회, PL/SQL
로 작성된 사용자 정의 함수/프로시저/트리거 내에서의 SQL 수행이 여기에 해당한다.
- Recursive Call을 최소화하려면, 바인드 변수를 적극적으로 사용해 하드파싱 발생횟수를 줄여야 한다.
- Recursive Depth가 깊어지도록 프로그래밍하는 것은 바람직하지 않다. PL/SQL은 가상머신상에서 수행되는 인터프리터 언어이
므로 빈번한 호출 시 컨텍스트 스위칭 때문에 성능이 매우 나빠진다. 성능을 위해서라면 PL/SQL 프로그램에 대한 지나친 모듈화는
지양해야 한다.
- 대용량 데이터를 조회할 때는 함수를 부분범위처리가 가능한 상황에서 제한적으로 사용해야 하며, 될 수 있으면 조인 또는 스칼라
서브쿼리 형태로 변환하려는 노력이 필요하다.
<데이터베이스 Call의 종류와 튜닝 원리를 요약한 표>
03. 데이터베이스 Call이 성능에 미치는 영향
-> '월요금납부실적' 테이블은 고객별 납입방법별 납입요금을 컬럼 값으로 입력하고, '납입방법별_월요금집계' 테이블은 납입요금을
납입방법코드별로 하나의 레코드로 입력하도록 하고 있다.
-> 기간계 시스템에서는 요금납부실적을 주로 고객별로 조회하므로 흔히 좌측과 같이 모델링하는 반면, 정보계 시스템에서는 다차원
분석을 많이 하므로 우측과 같이 납입방법코드를 PK로 끌어올리는 것이 일반적이다.
--> 여기서 '월요금납부실적' 테이블을 이용해 '납입방법별_월요금집계' 테이블 형태로 가공하는 ETL 배치 프로그램이 필요하다고
가정하자.
<대용량 데이터를 가공해 본 경험이 적은 개발자>
->개발 초기에는 소량의 테스트용 데이터만으로 프로그램을 수행하므로 무리 없이 돌아가지만 프로젝트가 통합 테스트 단계로 접어
들어 실 데이터들이 이관되기 시작하면 갑자기 문제가 심각해진다.
-> 과도한 데이터베이스 Call때문에 아침까지 끝나지 않는 프로그램들이 부지기수다.
-> 그나마 위처럼 PL/SQl문으로 코딩하면 네트워크 트래픽 없는 Recursive Call이므로 제법 빠르게 수행된다.
<월요금납부실적 테이블에 30,000건의 데이터 삽입>
<PL/SQL로 수행 시>
-> PL/SQL문으로 수행했을 때 7.25초 소요, select와 insert를 합쳐 총 18만 번가량의 데이터베이스 Call이 발생
<Java로 수행했을 시>
-> 126.82초가 소요, 총 303,000번 가량의 데이터베이스 Call이 발생했다.
--> select문에서 Fetch Call이 앞에서보다 1/10 수준으로 준 것은 Java에서 FetchSize 기본 설정이 10이기 때문이다.
그리고 Java에서 insert문은 애플리케이션 커서 캐싱 기법을 사용하지 않았으므로 Execute call과 같은 횟수만큼 Parse Call이
발생했다. PL/SQL에서는 자동으로 커서를 캐싱하므로 Parse Call이 5번에 그쳤다.
★ Java에서 총 소요시간이 126.82초인데 반해 서버에서의 일량은 그에 훨씬 못 미친다.
-> 순수하게 서버에서 처리한 시간은 10여 초에 불과하고 나머지는 네트워크 구간에서 소비한 시간, 그리고 데이터베이스 Call이
발생할 때마다 매번 OS로부터 CPU와 메모리 리소스를 할당받으려고 소비한 시간이다.
-> User Call이 Recursive Call에 비해 더 심각한 부하를 일으키는 이유가 바로 여기에 있다.
<One-SQL로 통합>
-> 1초가 채 걸리지 않는다.
<데이터양이 많을시 위 쿼리를 소트 머지 조인 또는 해시 조인으로 유도하기 위한 튜닝>
※Array Processing 기법을 활용하면, DBMS 외부에서 수행되는 Java 같은 프로그램에서도 네트워크 트래필을 획기적으로 줄여 줘
굳이 One-SQL로 작성하지 않더라도 같은 수준의 성능개선 효과를 얻을 수 있다. 이 사실은 One-SQL로 로직을 통합했을 때
극적으로 성능 개선이 이루어지는 원리가 데이터베이스 Call 횟수를 줄이는 데에 있음을 반증한다.
<'납입방법별_월요금집계' 테이블을 읽어 '월요금납부실적'을 가공하고자 할 때 I/O 효율 고려 안한 경우>
-> I/O효율의 핵심은 동일 레코드를 반복 액세스하지 않고 얼마만큼 블록 액세스 양을 최소화할 수 있느냐에 달렸다.
<I/O효율을 고려한 One-SQL>
-> Cross-Table(또는 Pivot)을 만드는 쿼리
<상품 5개 선택 화면>
-> Parse Call 5번, Execute Call 5번 발생.
04 Array Processing 활용
- Array Processing 기능을 활용하면 한 번의 SQL 수행으로 다량의 로우를 동시에 insert/update/delete 할 수 있다.
이는 네트워크를 통한 데이터베이스 Call을 감소시켜 주고, 궁극적으로 SQL 수행시간과 CPU 사용량을 획기적으로 줄여준다.
<Java 프로그램에서 Array Processing을 이용하는 방식>
->15,000(=30,000*5)건을 insert 하는데 단 1.21초 만에 수행을 완료.
<Trace 결과>
-> insert문에 대한 Execute Call이 30회만 발생하였다.
-> insert된 로우 수가 150,000건이므로 매번 5,000건씩 Array Processing한 것을 알 수 있다.
-> 커서에서 Fetch되는 각 로우마다 5번씩 insert를 수행하는데, 1,000 로우마다 한 번씩 executeBatch를 수행하기 때문에 얻게 된
결과다.
-> 참고로 select 결과를 Fetch 할 때도 1,000개 단위로 Array Fetch 하도록 조정하였다.(30,000건을 읽는데 Fetch Call이 31회만
발생)
※ Java에서 FetchSize를 조정하지 않으면 기본적으로 10개 단위로 Array Fetch를 수행한다.
-> Array Processing의 효과를 극대화하려면 연속된 일련의 처리과정이 모두 Array 단위로 진행돼야 한다.
앞선 단계에서 Array 단위로 수천 건씩 아무리 빠리게 Fetch 하더라도 다음 단계에서 수행할 insert가 건건이 처리된다면 그
효과가 크게 반감되며, 반대의 경우도 마찬가지다. 이것은 병렬 프로세싱에서 '병렬로부터 직렬' 또는 '직렬로부터 병렬'로
처리되는 부분이 병목을 일으키는 것과 같은 이치다.
<PL/SQL을 이용해 데이터를 Bulk로 1,000건씩 Fetch해서 Bulk로 insert하는 예제 프로그램>
<트레이스 결과>
-> 10,000건을 처리하는데 select문의 Fetch Call과 insert문의 Execute Call이 각각 10번씩 발생
(Fetch Call이 1번 더 발생 한 것은 데이터가 더 있는지 확인하기 위한 것)
※ EXP, IMP 명령을 통해 데이터를 Export, Import 할 때도 내부적으로 Array Processing이 활용되며, 그만큼 대용량 데이터를
처리하는 데 있어 Array Processing은 필수적인 요소다. Array Processing을 지원하는 인터페이스가 프로그램 언어별로 각기
다르므로 API를 통해 확인하고 이를 잘 활용해야 한다.
05 Fetch Call 최소화
< Fetch Call 최소화 원리 요약 >
- 부분범위처리 원리
- OLTP 환경에서 부분범위처리에 의한 성능개선 원리
- ArraySize 저정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
- 프로그램 언어에서 Array 단위 Fetch 기능 활용
(1) 부분범위처리 원리
-> 2초정도 수행했다.... 책에서는 33초정도 걸린다고 함.
-> 1억 건짜리 BIG_TABLE을 쿼리하는 데 1초가 채 걸리지 않는다. 이것은 ARRAY 단위 FETCH 때문에 나타나는 효과다.
물론 1억 건 전체를 읽고 처리한 것은 아니며, rs.next()를 한 번만 호출하고 곧바로 ResultSet과 Statement를 닫아 버렸다.
rs.next()를 호출하는 순간 오라클은 FetchSize(자바에서 기본값은 10)만큼을 전송했고, 클라이언트는 그 중 한 건만 콘솔에
출력하고는 곧바로 커서를 닫은 것이다. 추가 요청 없이 그대로 커서를 닫아버리면 오라클은 데이터를 더는 전송하지 않고
바로 일을 마친다.
-> 이처럼 쿼리 결과집합을 전송할 때, 전체 데이터를 쉼 없이 연속적으로 처리하지 않고 사용자로부터 Fetch Call이
있을 때마다 일정량씩 나누어서 전송하는 것을 '부분범위 처리'라고 한다.
- Array Size를 5로 설정하면, 서버 측에서는 Oracle Net으로 데이터를 내려 보내다가 5건당 한 번씩 정송 명령을 날리고는
클라이언트로부터 다시 Fetch Call이 올 때까지 대기한다. 클라이언트 측에는 서버로부터 전송받은 5개 레코드를 담을 Array
버퍼가 할당되며, 그곳에 서버로부터 받은 데이터를 담았다가 한 건씩 꺼내 화면에 출력하거나 다른 작업들을 수행한다.
< SDU, TDU >
- Array 버퍼는 클라이언트 측에 위치하며, 서버 측에서는 SDU에 버퍼링이 이루어진다.
< Array Fetch를 수행하는 내부 메커니즘 >
- 오라클에서 데이터를 전송하는 단위는 ArraySize에 의해 결정된다. 하지만 내부적으로 데이터는 네트워크 패킷 단위로 단편화되어
여러 번에 걸쳐 나누어 전송된다.->부분범위처리 내에 또 다른 부분범위 처리가 작동하는 것이다.
- 네트워크를 통해 큰 데이터를 전송할 때는 작은 패킷들로 단편화해야 하며, 그래야 유실이나 에러가 발생했을 때 부분 재전송을
통해 복구할 수 있다.
- 오라클 서버와 클라이언트는 Application 레이어에 위치하며, 그 아래에 있는 레이어를 통해 서로 데이터를 주고 받는다.
- SDU(Session Data Unit)는 Session 레이어 데이터 버퍼에 대한 규격으로서, 네트워크를 통해 전송하기 전에 Oracle Net이
데이터를 담아 두려고 사용하는 버퍼다.
--> 서버 측에서는 Oracle Net으로 데이터를 내려보내다가 5건당 한 번씩 전송 명령을 날리고는 매번 클라이언트로부터 다음 Fetch
Call을 기다리는데, Oracle Net이 서버 프로세스로부터 전송명령을 받을 때까지 데이터를 버퍼링하는 곳이 SDU다.
- Oracle Net은 서버 프로세스로부터 전송요청을 받기 전에라도 SDU가 다 차면 버퍼에 샇인 데이터를 전송하는데, 이때는 클라이언트로부터 Fetch Call을 기다리지 않고 곧이어 데이터를 받아 SDU를 계속 채워 나간다.
- TDU(Transport Data Unit)는 Transport 레이어 데이터 버퍼에 대한 규격이다. 물리적인 하부 레이어로 내려보내기 전에 데이터를
잘게 쪼개어 클라이언트에게 전송되는 도중에 유실이나 에러가 없도록 제어하는 역할을 한다.
- SDU와 TDU 사이즈는 TNSNAMES.ORA, LISTENER.ORA 파일에서 아래와 같이 설정 가능하며, 이들의 기본 설정 값은 2KB이다.
- 예를 들어, 결과집합이 18건이고 각 로우당 900바이트를 차지한다고 가정하자. 그러면 총 16,200바이트를 전송해야 하는데, 만약
ArraySize를 5로 설정하면 한번 Fetch 할 때마다 4,500(=900*5)바이트씩 3번을 전송하고 4번째는 2,700(=900*3) 바이트를 전송하게
된다.
★ 각 패킷은 헤더 정보를 포함하므로 패킷 단편화를 줄이면 네트웤 트래픽도 줄어들게 된다.
(2) OLTP 환경에서 부분범위처리에 의한 성능개선 원리
- 'x > 0 and y <= 6' 조건으로 쿼리를 수행하면, 첫 번째 Fetch Call(1)에서는 인덱스를 따라 x 컬럼 값이 1~5인 5개 레코드를 전송
받아 Array 버퍼에 담는다. x와 y 컬럼 값을 같게 입력해 놓았으므로 이들 5개 레코드는 테이블 필터 조건인 y<=6조건도 만족한다.
- 문제는 두 번째 Fetch Call(2)에서 발생한다. 두 번째 Fetch Call 명령을 받자마자 x=y=6인 레코드를 찾아 Oracle Net으로 내려보
낸다. 이제 'x>0 and y<=6'조건을 만족하는 레코드가 더 없다는 사실을 오라클은 모르기 때문에 계속 인덱스를 스캔하면서 테이블
을 액세스해 본다. Oracle Net은 한 건만 담은 패킷을 클라이언트에게 전송한다. 클라이언트는 Array 버퍼가 다시 채워지기를 기다
리면서 30여 초 이상을 허비했지만 결국 한 건밖에 없다는 신호를 받고 이를 출력한 후에 커서를 닫는다.
-> 1.87초 걸린다. 책에서는 37초 걸렷다.
- 부분범위 처리 원리 때문에 OLTP 환경에서는 결과집합이 많을수록 오히려 성능이 더 좋아진다.
1. SELECT /*+ index(t t_pk)*/ *
FROM T2
WHERE x > 0 and mod(y, 10) = 0;
2. SELECT /*+ index(t t_pk)*/ *
FROM T2
WHERE x > 0 and mod(y, 10000) = 0;
--> ArraySize가 10일 때, 1번 쿼리는 500건을 스캔할 때마다 한 번씩 전송 명령을 날리지만 2번 쿼리는 500,000건당 한 번씩이므로
그만큼 클라이언트 측 Array 버퍼를 채우는 데 시간이 많이 소요되어 출력 도중 끊기는 현상이 생기는 것이다.
출력 대상 건이 많을수록 Array를 빨리 채울 수 있어 쿼리 응답 속도도 그만큼 빨라진다.
※ One-Row Fetch
- SQL*Plus에서 테스트 해 보면 'x>0 and y<=6' 조건으로 쿼리를 수행했을 때, 첫 번째 Fetch Call에서 5개를 빠르게 전송 받기 때문에 대기 없이 곧바로 출력이 이루어 진다. 그렇다면 'x>0 and y<=5' 조건을 사용했을 때 Array 버퍼가 첫 번째 Fetch Call에서 금방 채워지므로 쿼리를 시작하자마자 5건이 바로 출력되어야 하는데 'x>0 and y<=1' 조건을 사용했을 때처럼 한참 후에야 5건을 출력하고 수행을 마친다.
- 이것은 오라클 서버와는 무관하게 SQL*Plus에서 나타나는 특징인데, SQL*Plus에서 쿼리를 수행하면 첫 번째 Fetch에서는
항상 한 건만 요청하고, 두 번째부터 ArraySize 만큼을 요청하기 때문에 생기는 현상이다.
- 첫 번째 Fetch에서 1건을 빠르게 리턴 받지만 곧바로 출력하지 않은 이유는, Fetch 해 오는 방식과는 무관하게 Array 버퍼로는
5개를 할당하기 때문이며 클라이언트는 이 버퍼가 다 채워져야 출력을 시작한다.
- 'x>0 and y<=1' 조건으로 수행할 때는 첫 번째 Fetch Call에서 대기할 것 같지만 실상은 두 번째 Fetch Call에서 대기한다.
첫 번째 Fetch에서 이미 1건을 가져왔지만 클라이언트 쪽 Array 버퍼 크기가 5이므로 나머지 4개를 마저 채우려고 두 번째 Fetch Call을 날리고 기다리는 거이다.
<오렌지에서 SQL 트레이스에서 1건만 가져오는데 2번의 Fetch Call이 발생>
<'x>0 and y<=6'조건>
-> 첫 번째 Fetch에서는 1건만 가져오고, 두 번째 Fetch에서 5건을 가져와 클라이언트 측 Array 버퍼를 채운다. 5개가 채워졌으므로
일단 화면에 출력하고, 남은 1건 외에 4건을 더 가져와 Array 버퍼를 채우려고 세 번째 Fetch를 수행하는데, 이 때 지연이 발생
하게 된다.
( SQL*Plus나 Orange가 아닌 다른 쿼리 툴에서는 Fetch Call이 두 번만 발생하며, 첫 번째 Fetch에서 5건, 두 번째 Fetch에서
1건을 가져온다.)
-> 어떤 클라이언트 툴을 사용하든 서버 측에서는 항상 Array 단위로 전송한다.
(3) ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
- 대량 데이터를 내려받을 때 ArraySize를 크게 설정할수록 그만큼 Fetch Call 횟수가 줄어 네트워크 부하가 감소하고, 쿼리 성능이
향상된다. 그 뿐만 아니라 서버 프로세스가 읽어야 할 블록 개수까지 줄어드는 일거양득의 효과를 얻게 된다.
<Array Size에 따른Fetch Count와 Block I/O>
-> ArraySize를 키운다고 해서 같은 비율로 Fetch Count와 Block I/O가 줄지는 않는다. 무작정 크게 설정한다고 좋은 것만은
아니며 일정 크기 이상이면 오히려 리소스만 낭비하게 된다. 데이터 크기에 따라 다를 텐데, 위 데이터 상황에서는 100정도로
설정하는 게 적당해 보인다.
<ArraySize가 늘면서 블록 I/O까지 감소하는 원리>
-> 10개의 행으로 구성된 3개의 Block이 있다고 하자. 총 30개 레코드이므로 ArraySize를 3으로 설정하면 Fetch 횟수는 10이고,
이때 Block I/O 는 12번이나 발생하게 된다. 왜냐하면, 10개 레코드가 담긴 블록들을 각각 4번에 걸쳐 반복 액세스해야 하기
때문이다.
- 첫 번째 Fetch에서 읽은 1번 블록을 2~4번째 Fetch에서도 반복 액세스하게 된다.
- 만약 ArraySize를 10으로 설정한다면 3번의 Fetch와 3번 블록 I/O로 줄일 수 있다. 그리고 30으로 설정하면 Fetch횟수는 1로 준다.
(4) 프로그램 언어에서 Array 단위 Fetch 기능 활용
- PL/SQL을 포함한 프로그램 언어에서 어떻게 ArraySize를 제어하는지 확인해 보자.
- 10g부터는 자동으로 100개씩 Array Fetch가 일어난다. 단, 아래처럼 Cursor FOR Loop 구문을 이용할 때만 작동한다.
for item in cursor
loop
......
end loop;
- Cursor FOR Loop 문은 커서의 Open, Fetch, Close가 내부적으로 이루어지는 것이 특징이며, Implicit Cursor FOR Loop와
Explicit Cursor FOR Loop 두가지 형태가 있다. 둘 다 Array Fetch 효과를 얻을 수 있다.
-- Implicit Cursor FOR Loop
DECLARE
l_object_name big_table.object_name%type;
BEGIN
for item in ( select object_name from big_table where rownum <= 1000 )
loop
l_object_name := item.object_name;
dbms_output.put_line(l_object_name);
end loop;
END;
/
-- Explicit Cursor FOR Loop
DECLARE
l_object_name big_table.object_name%type;
cursor c is select object_name from big_table where rownum <= 1000;
BEGIN
for item in c
loop
l_object_name := item.object_name;
dbms_output.put_line(l_object_name);
end loop;
END;
/
<Implicit, Explicit SQL 트레이스 결과>
-> 둘 다 Array Fetch 효과를 얻을 수 있다.
< Cursor FOR Loop이 아닌 일반 커서를 이용>
DECLARE
cursor c is
select object_name
from big_table where rownum <= 1000;
l_object_name big_table.object_name%type;
BEGIN
open c;
loop
fetch c into l_object_name;
exit when c%notfound;
dbms_output.put_line(l_object_name);
end loop;
close c;
END;
/
-> Fetch 횟수가 1,001로 늘어난 것을 통해 Array 단위 Fetch가 작동하지 않았음을 알 수 있고, 이는 sys_refcursor를 사용해도
마찬가지 결과가 나타난다.
-> Array 단위 Fetch가 작동하지 못함으로 인해 블록 I/O까지 25에서 1,003으로 40배가량 늘어났다.
<Java에서 ArraySize를 조정하는 방법>
-> setFetchSize 메서드를 이용해 FetchSize를 조정한다.
-> Java에서 FetchSize 기본 값은 10이다.
-> 대량 데이터를 Fetch 할 때 이값을 100~500 정도로 늘려 주면 기본 값을 사용할 때보다 데이터베이스 call 부하를 1/10 ~ 1/50로
줄일 수 있다.
< FetchSize를 100으로 설정했을 때 데이터를 Fetch 해오는 매커니즘 >
1. 최초 rs.next(0 호출 시 한꺼번에 100건을 가져와서 클라이언트 Array 버퍼에 캐싱한다.
2. 이후 rs.next(0 호출할 때는 데이터베이스 Call을 발생시키지 않고 Array 버퍼에서 읽는다.
3. 버퍼에 캐싱 돼 있던 데이터를 모두 소진한 후 101번째 rs.next() 호출 시 다시 100건을 가져온다.
4. 모든 결과집합을 다 읽을 때까지 2~3번 과정을 반복한다.