🖥️ 들어가며
📌 One SQL 구현의 중요성
- 루프를 돌면서 여러 작업을 반복 수행하는 프로그램(Java, C, VB 등)을 One SQL로 구현했을 때 데이터베이스 Call 횟수를 줄여 성능 개선 효과를 얻을 수 있습니다.
- DBMS 내에서 수행되는 사용자 정의 프로시저로 개발하면 네트워크 트래픽이 없는 Recursive Call만 발생하므로 빠르게 수행됩니다.
- 데이터베이스 Call은 개별 프로그램의 수행속도에 큰 영향 + 시스템 전체의 확장성에 영향을 미칩니다.
📌 One SQL 예시
- 메소드를 5번 호출: Parse Call 5번 + Execute Call 5번 발생하는 프로그램이 24시간 돌 때
=> 메소드 1번만 호출하게 수정: 시스템이 5배의 확장성을 갖게 되는 것입니다.
✏️ 1. 데이터베이스 Call 종류
- SQL Cursor 구분 1: 작업 요청별
- (1) Parse Call: SQL 파싱을 요청하는 Call
- (2) Execute Call: SQL 실행을 요청하는 Call
- (3) Fetch Call: SELECT문의 결과 데이터 전송을 요청하는 Call
- SQL Cursor 구분 2: Call 발생위치별
- (1) User Call
- DBMS 외부로부터 요청되는 Call
- 동시 접속자 수가 많은 Peak 시간대에 시스템 확장성을 떨어뜨리는 가장 큰 요인 중 하나입니다.
- 개발자의 기술력 문제, 애플리케이션 설계 문제, 프레임워크 기술 구조의 문제
- Array Processing을 지원하지 않는 프레임워크
- 화면 페이지 처리에 대한 잘못 설계된 표준 가이드
- 사용자 정의 함수/프로시저에 대한 무조건적 제약
- 프로시저 단위 모듈을 지나치게 잘게 쪼개서 SQL을 건건이 호출하도록 설계
=> User Call을 최소화하려는 노력 필요 - User Call을 줄이기 위한 기술 요소
- Loop 쿼리를 해소, 집합적 사고를 통해 One SQL 구현
- Array Processing: Array 단위 Fetch, Bulk Insert/Update/Delete
- 부분범위처리 원리 활용
- 효과적인 화면 페이지 처리
- 사용자 정의 함수/프로시저/트리거의 적절한 활용 (⚠️필요한 때에 일부 허용해야 한다는 말)
- (2) Recursive Call
- DBMS 내부에서 발생하는 Call
- SQL 파싱과 최적화 과정에서 발생하는 데이터 딕셔너리 조회, 사용자 정의 함수/프로시저 내에서의 SQL 수행합니다.
- Recursive Call 최소화 방안
- 바인드 변수를 통한 하드파싱 발생횟수 감소
- 사용자 정의 함수/프로시저의 적절한 사용 (⚠️필요한 때에 일부 허용해야 한다는 말)
- (1) User Call
✏️ 2. Array Processing 활용
- Array Processing 기능을 활용하면 한번의 SQL(Insert/Update/Delete) 수행으로 다량의 레코드를 동시에 처리할 수 있습니다.
- 네트워크 Call 감소, SQL 수행시간, CPU 사용량이 감소합니다.
- Array에 담아 한꺼번에 처리 → Call 횟수를 줄여 성능 개선에 도움됩니다.
- Array Processing 기법을 지원하는 인터페이스는 개발언어마다 다르므로 API를 통해 확인하고 활용해야 합니다.
// Java: Array Processing
1 public class JavaArrayProcessing{
2 public static void insertData( Connection con
3 , PreparedStatement st
4 , String param1
5 , String param2
6 , String param3
7 , long param4) throws Exception{
8 st.setString(1, param1);
9 st.setString(2, param2);
10 st.setString(3, param3);
11 st.setLong(4, param4);
12 st.addBatch(); // Insert할 데이터를 담기만 하다가
13 }
14
15 public static void execute(Connection con, String input_month)
16 throws Exception {
17 long rows = 0;
18 String SQLStmt1 = "SELECT 고객번호, 납입월"
19 + ", 지로, 자동이체, 신용카드, 핸드폰, 인터넷 "
20 + "FROM 월요금납부실적 "
21 + "WHERE 납입월 = ?";
22
23 String SQLStmt2 = "INSERT INTO 납입방법별_월요금집계 "
24 + "(고객번호, 납입월, 납입방법코드, 납입금액) "
25 + "VALUES(?, ?, ?, ?)";
26
27 con.setAutoCommit(false);
28
29 PreparedStatement stmt1 = con.prepareStatement(SQLStmt1);
30 PreparedStatement stmt2 = con.prepareStatement(SQLStmt2);
31 stmt1.setFetchSize(1000); // SELECT 결과집합 Fetch할 때도 1000건씩 하도록 조정
32 stmt1.setString(1, input_month);
33 ResultSet rs = stmt1.executeQuery();
34 while(rs.next()){
35 String 고객번호 = rs.getString(1);
36 String 납입월 = rs.getString(2);
37 long 지로 = rs.getLong(3);
38 long 자동이체 = rs.getLong(4);
39 long 신용카드 = rs.getLong(5);
40 long 핸드폰 = rs.getLong(6);
41 long 인터넷 = rs.getLong(7);
42
43 if(지로 > 0)
44 insertData (con, stmt2, 고객번호, 납입월, "A", 지로);
45
46 if(자동이체 > 0)
47 insertData (con, stmt2, 고객번호, 납입월, "B", 자동이체);
48
49 if(신용카드 > 0)
50 insertData (con, stmt2, 고객번호, 납입월, "C", 신용카드);
51
52 if(핸드폰 > 0)
53 insertData (con, stmt2, 고객번호, 납입월, "D", 핸드폰);
54
55 if(인터넷 > 0)
56 insertData (con, stmt2, 고객번호, 납입월, "E", 인터넷);
57
58 if(++rows%1000 == 0) stmt2.executeBatch(); // 1000건 쌓일 때마다 한번씩 executeBatch 수행
59
60 }
61
62 rs.close();
63 stmt1.close();
64
65 stmt2.executeBatch();
66 stmt2.close();
67
68 con.commit();
69 con.setAutoCommit(true);
70 }
71
72 static Connection getConnection() throws Exception { }
73 static void releaseConnection(Connection con) throws Exception { ...... }
74
75 public static void main(String[] args) throws Exception{
76 Connection con = getConnection();
77 execute(con, "200903");
78 releaseConnection(con);
79 }
80 }-- PL/SQL: Array Processing (1000건씩 Fetch해서 Bulk Insert)
DECLARE
l_fetch_size NUMBER DEFAULT 1000; -- 1,000건씩 Array 처리
CURSOR c IS
SELECT empno, ename, job, sal, deptno, hiredate
FROM emp;
...
BEGIN
OPEN C;
LOOP
FETCH c BULK COLLECT
INTO p_empno, p_ename, p_job, p_sal, p_deptno, p_hiredate
LIMIT l_fetch_size;
FORALL i IN p_empno.first..p_empno.last
INSERT INTO emp2
VALUES ( p_empno (i)
, p_ename (i)
, p_job (i)
, p_sal (i)
, p_deptno (i)
, p_hiredate (i) );
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE C;
✏️ 3. Fetch Call 최소화
- Fetch Call 최소화 방법 1: 부분범위처리
- 부분범위처리의 원리: 쿼리결과집합을 전송할 때, 전체 데이터를 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것입니다.
- 부분범위처리의 예시: ArraySize 100건만 차면 사용자에게 데이터 전달
- SQL Server: 쿼리 분석기 옵션에 설정되어 있는 "네트워크 패키지 크기"로 운반(default 4,096 Byte)
Oracle: 내부적으로는 SDU(Session Data Unit, Session 레이어), TDU(Transport Data Unit, Transport 레이어) 단위로 나누어 전송
*ArraySize를 작게 설정하면 하나의 네트워크 패킷에 담아 전송 / 크게 설정하면 여러 개의 패킷으로 나누어 전송합니다.
- Fetch Call 최소화 방법 2: ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
- 대량의 데이터파일 전송 → ArraySize를 크게 하여 Fetch Call 횟수를 줄여주는 것이 유리하고
적은 양의 데이터만 Fetch 하다가 멈추는 프로그램 → ArraySize를 작게 설정하는 것이 유리합니다. - ArraySize를 증가시키면 → 네트워크 부하 감소, 서브 프로세스가 읽어야 할 블록 개수 감소 효과가 있습니다.
- ArraySize를 키운다고 해서 Fetch Count와 블록 I/O가 같은 비율로 줄지 않습니다.
=> ArraySize를 무조건 크게 설정한다고 좋은 것이 아니고, 일정 크기 이상이면 리소스만 낭비하는 결과를 초래할 수 있습니다.
- 대량의 데이터파일 전송 → ArraySize를 크게 하여 Fetch Call 횟수를 줄여주는 것이 유리하고
-- SQL Plus 프로그램: Array 단위 Fetch 기능 활용 방법 (default arraysize = 100)
set arraysize 100
-- Oracle PL/SQL: 커서를 열고 레코드를 Fetch
-- 9i: 한 번에 한 로우씩만 처리 (Single-Row Fetch)
-- 10g: 자동으로 100개씩 Array Fetch가 일어남 (Cursor For Loop 구문을 이용할 때만)
for item in cursor
loop
...
end lool;
✏️ 4. 페이지 처리 활용
- 페이지 미처리시 발생하는 부하요인
- (1) 다량 발생하는 Fetch Call의 부하
- (2) 대량의 결과집합을 클라이언트에 전송하면서 발생하는 네트워크 부하
- (3) 대량의 데이터 블록을 읽으면서 발생하는 I/O 부하
- (4) AP(Application) 서버 및 웹 서버 리소스 사용량 증가
- 페이지 처리를 통한 부하해소
- (1) 페이지 단위로 화면에서 필요한 만큼만, Fetch Call
- (2) 페이지 단위로 화면에서 필요한 만큼만, 네트워크를 통해 결과 전송
- (3) 인덱스 + 부분범위처리 원리를 이용 = AP 서버 및 웹 서버 리소스 사용량 최소화
=> 조회할 데이터가 일정량 이상이고 수행빈도가 높다면 "필수적으로 페이지 처리"를 구현해야 합니다.
✏️ 5. 분산 쿼리
- 부하 분산, 재해복구, 보안 등 여러가지 목적으로 분산 환경의 데이터베이스를 구축합니다.
- 원격 조인이 자주 문제시 되는데, 네트워크를 통한 데이터 전송을 줄이는 것이 분산 쿼리 튜닝의 핵심 원리입니다.
(⚠️ @DB LINK는 보안에 좋지 않습니다.)
-- 분산 DB간 테이블 조인 예시
select channel_id, sum(quantity_sold) auantity_cold
from order a, sales@lk_sales b -- ☆원격의 sal 테이블을 전송받아 order 테이블과 NL 조인
where a.order_date between :1 and :2
and b.order_no = a.order no
group by channel_id
Rows Row Source Operation
----- ---------------------------------------------
5 SORT GROUP BY
10981 NESTED LOOPS
500000 REMOTE
10981 TABLE ACCESS BY INDEX ROWID ORDER
500000 INDEX UNIQUE SCAN (ORDER_PK)
-- 분산 DB간 성능저하 해결방안
-- order_date 조건에 해당하는 데이터만 원격 전송 조인 + group by 거친 결과 집합만 전송 받음
-- ☆핵심: 네트워크를 통한 데이터 전송량 줄이기☆
select /*+ driving_site(b) */ -- 원격서버가 처리 가능하도록 driving_site 힌트 사용
channel_id, sum(quantity_sold) auantity_cold
from order a, sales@lk_sales b
where a.order_date between :1 and :2
and b.order_no = a.order_no
group by channel_id
Rows Row Source Operation
---- ---------------------------------------------
5 SORT GROUP BY
10981 NESTED LOOPS
939 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER'
939 INDEX (RANGE SCAN) OF 'ORDER_IDX2' (NON-UNIQUE)
10981 REMOTE
✏️ 6. 사용자 정의 함수 / 프로시저의 특징과 성능
- 사용자 정의 함수/프로시저의 특징
- 사용자 정의함수/프로시저는 내장함수처럼 Native 코드로 완전 컴파일된 형태가 아닌 인터프리터 방식입니다.
- 따라서 가상머신(Virtual Machine)같은 별도의 실행엔진을 통해 실행합니다.
- 실행 시마다 컨텍스트 스위칭*이 일어나므로, 내장함수를 호출할 때와 비교해서 성능이 상당히 떨어집니다.
- 아래 함수는 to_char 함수를 바로 호출할 때보다 훨씬 느림
- 메인쿼리가 참조하는 사용자 정의함수에 또다른 쿼리문이 내장되어 있다면 수행성능이 훨씬 나빠짐
- 함수에 내장된 쿼리를 수행할 때마다 Execute Call, Fetch Call이 재귀적으로 발생함(Parse Call은 1번만 수행)
- Recursive Call이 반복적으로 일어남(User Call에 비해 성능부하가 미미하지만, 횟수가 무수히 반복되면 성능 저하) - *컨텍스트 스위칭
- PL/SQL 함수 호출 시 매번 SQL 실행엔진이 사용하던 레지스터 정보를 백업합니다.
- PL/SQL 엔진이 실행을 마치면 다시 복원하는 작업을 반복합니다.
-- 사용자 정의 함수: 문자타입의 일자 데이터 -> 날짜타입으로 변환
create or replace function date_to_char(p_dt date) return varchar2 as
begin
return to_char(p_dt, 'yyyy/mm/dd hh24:mi:ss');
end;
- 사용자 정의함수/프로시저에 의한 성능저하 해소방안
- 소량의 데이터를 조회할 때만 사용합니다. (컨텍스트 스위칭 발생 X)
- 부분범위처리가 가능한 상황에서 제한적으로 사용합니다.
- 가급적 함수를 풀어 조인 또는 스칼라 서브쿼리 형태로 변환합니다.
- 복잡도가 높아 One SQL이 불가능한 경우, 함수 호출을 최소화하여 튜닝합니다.
📒 정리하면
- One SQL 구현
: 반복적인 프로그램 로직을 단일 SQL로 통합하여 데이터베이스 Call 횟수를 줄이면, 성능과 시스템 확장성이 향상됩니다. - 데이터베이스 Call 종류 및 최소화
- 작업 요청별: Parse Call, Execute Call, Fetch Call
- User Call(외부 요청): 네트워크 트래픽을 유발하므로 Loop 쿼리 제거, Array Processing 활용, 부분범위처리, 페이지 처리 등으로 최소화해야 합니다.
- Array Processing 미지원 프레임워크, 잘못된 페이지 처리 표준은 User Call 증가를 유발하므로 주의가 필요합니다.
- Recursive Call(내부 요청): 바인드 변수 사용과 프로시저 적절한 활용으로 줄일 수 있습니다. - Array Processing
- 대량 데이터를 일괄 처리 => 네트워크 Call 횟수, SQL 실행 시간, CPU 사용량을 감소시킵니다.
- Java의 executeBatch()나 PL/SQL의 BULK COLLECT로 구현 가능합니다. - Fetch Call 최소화
- 부분범위처리: 필요한 데이터만 나누어 전송하도록 설계합니다.
- ArraySize 조정: 대량 데이터는 크게 설정해 Fetch 횟수 감소, 소량은 작게 설정해 리소스 낭비를 방지합니다. - 페이지 처리
- 화면에 필요한 데이터만 페이징 처리해 Fetch Call, 네트워크 부하, I/O 블록 접근을 줄입니다.
- 인덱스와 부분범위처리 원리 적용이 핵심입니다. - 분산 쿼리 최적화
- 원격 조인 시 driving_site 힌트로 데이터 전송량을 최소화합니다.
- 네트워크 트래픽 감소가 성능 개선의 핵심입니다. - 사용자 정의 함수/프로시저 제한적 사용
- 인터프리터 방식 실행으로 인한 컨텍스트 스위칭 오버헤드를 줄이기 위해
- 소량의 데이터를 조회할 때만 사용하거나, 복잡한 로직은 조인 또는 스칼라 서브쿼리로 변환합니다. - 사용자 정의 함수/프로시저에 의한 성능 저하 요인 관리
- 대량 결과집합 전송 시 네트워크/AP 서버 리소스 사용량 증가를 방지합니다.
- 페이지 미처리 시 발생하는 I/O 부하를 인덱스 활용으로 해결합니다.
반응형
