불필요하고 반복적인 Call 수행횟수를 최소화하는 것은 데이터베이스 수행속도를 향상시키고 확장성을 높이는 매우 중요한, 핵심적인 튜닝 요소다.
01 Call 통계
Parse Call: 커서를 파싱하는 과정에 대한 통계로서, 실행계획을 생성하거나 찾는 과정에 대한 정보를 포함한다.
Execute Call: 커서를 실행하는 단계에 대한 통계를 보여준다.
Fetch Call: select 문에서 실제 레코드를 읽어 사용자가 요구한 결과집합을 반환하는 과정에 대한 통계를 보여준다.
DML 문은 Execute Call 시점에 모든 처리과정을 서버 내에서 완료하고 처리결과만 리턴하므로 Fetch Call이 전혀 발생하지 않는다.
select 문은 Execute Call 단계에서는 커서만 오픈하고, 실제 데이터를 처리하는 과정은 모두 Fetch 단계에서 일어난다.
02 User Call vs. Recursive Call
데이터베이스의 Call을 커서의 활동상태에 따라 Parse, Execute, Fetch로 나눌 수도 있고,
Call이 어디서 발생하느냐에 따라 User Call, Recursive Call 로 나눌 수도 있다.
User Call: OCI (Oracle Call Interface) 를 통해 오라클 외부로부터 들어오는 Call
동시 접속자 수가 적을 때는 잘 드러나지 않지만 Peak 시간대에 시스템 장애를 발생시키는 가장 큰 주범은 User Call 이다.
User Call 을 최소화하는 방법
1. Loop 쿼리를 해소하고 집합적 사고를 통해 One-SQL 로 구현
2. Array Processing: Array 단위 Fetch, Bulk Insert/Update/Delete
3. 부분범위처리 원리 활용
4. 효과적인 화면 페이지 처리
5. 사용자 정의 함수/프로시저/트리거의 적절한 활용
Recursive Call: 오라클 내부에서 발생하는 Call
SQL파싱과 최적화 과정에서 발생하는 Data Dictionary 조회,
PL/SQL 로 작성된 사용자 정의 함수/프로시저/트리거 내에서의 SQL 수행이 여기에 해당한다.
Recursive Call 을 최소화하려면, 바인드 변수를 적극적으로 사용해 하드파싱 발생횟수를 줄여야한다.
03 데이터베이스 Call이 성능에 미치는 영향
JAVA 같은 프로그램에서도 네트워크 트래픽을 획기적으로 줄여줘 굳이 One-SQL로 작성하지 않더라도 같은 수준의 성능개선 효과를 얻을 수 있다.
이 사실은 One-SQL 로 로직을 통합했을 때 극적으로 성능 개선이 이루어지는 원리가 데이터베이스 Call 횟수를 줄이는 데에 있음을 반증한다.
효율을 고려하지 않은 One-SQL 은 누구나 작성할 수 있으며,
I/O 효율의 핵심은 동일 레코드를 반복 액세스하지 않고 얼마만큼 블록 액세스 양을 최소화할 수 있느냐에 달렸다.
04 Array Processing 활용
Array Processing 기능을 활용하면 한번의 SQL 수행으로 다량의 로우를 동시에 insert/update/delete 할 수 있다.
이는 네트워크를 통한 데이터베이스 call을 감소시켜 주고, 궁극적으로 SQL 수행 시간과 CPU 사용량을 획기적으로 줄여준다.
05 Fetch Call 최소화
- 부분범위처리 원리
- OLTP 환경에서 부분범위처리에 의한 성능개선 원리
- ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
- 프로그램 언어에서 Array 단위 Fetch 기능 활용
(1) 부분범위처리 원리
쿼리 결과집합을 전송할 때, 전체 데이터를 쉼 없이 연속적으로 처리하지 않고
사용자로부터 Fetch Call 이 있을 때마다 일정량씩 나누어서 전송하는 것을 부분범위처리라고 한다.
OSI 7
Application, Presentation, Session, Transport, Network, Data Link, Physical
오라클 서버와 클라이언트는 Application 레이어
SDU (Session Data Unit) Session 레이어 데이터 버퍼에 대한 규격으로서,
네트워크를 통해 전송하기 전에 Oracle Net 이 데이터를 담아 두려고 사용하는 버퍼다
TDU (Transport Data Unit) Transport 레이어 데이터 버퍼에 대한 규격으로,
물리적인 하부 레이어로 내려보내기 전에 데이터를 잘게 쪼개어 클라이언트에게 전송되는 도중에 유실이나 에러가 없도록 제어하는 역할을 한다.
(2) OLTP 환경에서 부분처리에 의한 성능개선 원리
OLTP성 업무에서는 쿼리 결과 집합이 아주 많더라도 그 중 일부만 Fetch하고 멈출 때가 자주 있다.
따라서 출력 대상 건이 많을수록 Array를 빨리 채울 수 있어 쿼리 응답 속도도 그만큼 빨라진다.
인덱스와 부분범위처리 원리를 잘 활용하면 OLTP 환경에서 극적인 성능개선 효과를 얻을 수 있는 원리가 여기에 숨어 있다.
(3) ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
대량 데이터를 내려받을 때 ArraySize 를 크게 설정할수록 그만큼 Fetch Call 횟수가 줄어 네트워크 부하가 감소하고, 쿼리 성능이 향상된다.
그뿐만 아니라 서버 프로세스가 읽어야 할 블록 개수까지 줄어드는 일거양득의 효과를 얻게 된다.
(4) 프로그램 언어에서 Array 단위 Fetch 기능 활용
PL/SQL에서 커서를 열고 레코드를 Fetch하면, Bulk Collect 구문을 사용하지 않는 한
9i까지는 한번에 한 로우씩만 처리, 10g부터는 자동으로 100씩 Array Fetch 가 일어난다.
단 Cursor For Loop 구문을 이용할 때만 작동한다.
06 페이지 처리의 중요성
페이지 처리를 하지 않았을 때 발생하는 부하요인
1. 다량의 Fetch Call 발생 (SQL*Net roundtrips to/from client)
2. 대량의 결과 집합을 클라이언트로 전송하면서 발생하는 네트워크 부하 (bytes sent via SQL*Net to client)
3. 대량의 데이터 블록을 읽으면서 발생하는 I/O 부하
4. AP 서버 및 웹 서버 리소스 사용량 증가
부하를 해소하는 페이지 처리
1. 페이지 단위로, 화면에서 필요한 만큼씩 Fetch Call
2. 페이지 단위로, 화면에서 필요한 만큼씩 네트워크를 통해 전송
3. 인덱스와 부분범위 처리 원리를 이용해 각 페이지에 필요한 최소량만 I/O
4. 데이터를 소량씩 나누어 전송하므로 AP, 웹 서버 리소스 사용량 최소화
결론적으로, 조회할 데이터가 일정량 이상이고 수행빈도가 높다면 필수적으로 페이지 처리를 구현해야 한다.
07 PL/SQL 함수의 특징과 성능 부하
(1) PL/SQL 함수의 특징
P/L SQL은 인터프리터 언어이므로 그것으로 작성한 함수 실행 시 매번 SQL실행엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭이 일어난다.
(2) Recursive Call를 포함하지 않는 함수의 성능 부하
오라클 내장함수보다 사용자 정의 함수 수행시간이 느리다.
(3) Recursive Call를 포함하는 함수의 성능 부하
대용량 조회 쿼리에서 함수를 남용하면 읽는 레코드 수만큼 건건이 함수 호출이 발생해 성능이 극도로 나빠진다.
따라서 사용자 정의 함수는 소량의 데이터 조회 시에만 사용하거나,
대용량 조회 시에는 부분범위처리가 가능한 상황에서 제한적으로 사용해야 한다.
그리고 성능을 위해서라면 가급적 조인 또는 스칼라 서브쿼리 형태로 변환하려고 노력해야 한다.
(4) 함수를 필터 조건으로 사용할 때 주의사항
조건절과 인덱스 구성에 따라 성능 차이가 매우 크게 나타날 수 있다.
(5) 함수와 읽기 일관성
함수/프로시저를 잘못 사용하면 성능을 떨어뜨릴 뿐만 아니라 데이터 정합성까지 해칠 수 있으므로 주의해야 한다.
(6) 함수의 올바른 사용 기준
함수를 가급적 쓰지말라는 뜻이 아니라, 함수를 사용했을 때 성능이 느려지는 원리를 이해하고 잘 활용하라는 뜻이다.
08 PL/SQL 함수 호출 부하 해소 방안
사용자 정의 함수는
1) 소량의 데이터 조회시에만 사용하는 것이 좋다.
2) 대용량 데이터를 조회할 때는 부분범위 처리가 가능한 상황에서 제한적으로 사용해야 하며,
3) 조인 또는 스칼라 서브쿼리 형태로 변환하려는 노력이 필요하다.
4) 어쩔수 없을 때는 함수를 쓰되 호출 횟수를 최소화할 수 있는 방법을 강구해야 한다.
함수 호출 부하 해소 방안 요약
- 페이지 처리 또는 부분범위처리 활용
- Decode 함수 또는 Case 문으로 변환
- 뷰 머지 방지를 통한 함수 호출 최소화
- 스칼라 서브쿼리 해싱 효과를 이용한 함수 호출 최소화
- Deterministic 함수의 캐싱 효과 활용
- 복잡한 함수 로직을 풀어 SQL 로 구현
(1) 페이지 처리 또는 부분범위처리 활용
페이지 처리를 하지 않더라도 부분범위처리가 가능한 상황이라면
클라이언트에게 데이터를 전송하는 맨 마지막 단계에 함수 호출이 일어나도록 함. (맨 바깥쪽 select-list에 함수 기술)
(2) Decode 함수 또는 Case 문으로 변환
함수가 안쪽 인라인 뷰에서 order by 절에 사용된다든가, 전체 결과집합을 모두 출력하거나, insert select 문에서 사용된다면
다량의 함소 호출을 피할 수 없다.
그럴 때는 함수로직을 풀어서 decode, case 문으로 전환하거나 조인문으로 구현할 수 있는지 먼저 확인해야 한다.
(3) 뷰 머지(View Merge) 방지를 통한 함수 호출 최소화
no_merge 힌트 사용하기
뷰 내에 rownum을 사용하면 옵티마이저는 절대 뷰 머지를 시도하지 않는다.
(4) 스칼라 서브쿼리의 캐싱효과를 이용한 함수 호출 최소화
서브쿼리가 수행될 때마다 입력 값을 캐시에서 찾아보고 거기 있으면 저장된 출력 값을 리턴하고,
없으면 쿼리를 수행한 후 입력 값과 출력 값을 캐시에 저장해 두는 원리다.
함수를 dual 테이블을 이용해 스칼라 서브쿼리로 한번 감싸는 것이다.
10g에서는 입력과 출력 값 크기, _query_execution_cach_max_size 파라미터에 의해 캐시사이즈가 결정된다고 한다.
(5) Deterministic 함수의 캐싱 효과 활용
10g R2에서 함수를 선언할 때 deterministic 키워드를 넣어주면 스칼라 서브 쿼리를 덧입히지 않아도 캐싱 효과가 나타난다.
함수의 입력 값과 출력 값은 CGA(Call Global Area)에 캐싱된다.
CGA에 할당된 값은 데이터베이스 Call 내에서만 유효하므로 Fetch Call 이 완료되면 그 값들은 모두 해제된다.
따라서 Deterministic 함수의 캐싱 효과는 데이터베이스 내에서만 유효하다.
반면, 스칼라 서브쿼리에서의 입력, 출력 값은 UGA에 저장되므로 Fetch Call 에 상관없이 그 효과가, 캐싱되는 순간부터 끝까지 유지된다.
(6) 복잡한 함수 로직을 풀어 SQL 로 구현