@markdown
# 데이터베이스 Call과 네트워크 부하
- 서버 프로세스에게 작업을 지시하거나 결과집합을 요청할 때 모든 명령은 데이터베이스 Call을 통해 전달된다.
## 데이터베이스 Call 종류
____
### SQL 커서에 대한 작업 요청에 따른 구분
- 1) Parse Call : SQL 파싱을 요청하는 Call
- 2) Execute Call : SQL 실행을 요청하는 Call
- 3) Fetch Call : SELECT문의 결과 데이터 전송을 요청하는 Call
### Call 발생 위치에 따른 구분
#### 1) User Call
- DBMS 외부로부터 요청되는 Call
- User Call이 많은 Peek 시간대에 시스템 확장성을 떨어뜨리는 가장 큰 원인
- User Call을 줄이기 위한 기술요소
- ① Loop 쿼리를 해소하고 집합적 사고를 통해 One SQL로 구현
- ② Array Preocessing : Array 단위 Fetch, Bulk `Insert` / `Update` / `Delete`
- ③ 부분범위처리 원리 활용
- ④ 효과적인 화면 페이지 처리
- ⑤ `사용자 정의 함수` / `프로시저`/ `트리거`의 적절한 활용
#### 2) Recursive Call
- DBMS 내부에서 발생하는 Call
- SQL 파싱과 최적화 과정에서 발생하는 데이터 딕셔너리 조회, 사용자 정의함수/프로시저 내에서의 SQL수행
- Recursive Call 최소화방안
- ① 바인드 변수 적극 활용하여 하드파싱 발생횟수를 줄인다.
- ② 사용자 정의 함수와 프로시저를 내부적으로 어떻게 수행되는지 잘 이해하고 사용
</br>
## 데이터베이스 Call과 성능
____
### One SQL 구현의 중요성
- Loop를 돌면서 여러 작업을 반복 수행하는 프로그램을 One SQL로 구현했을 때 데이터베이스 Call 횟수를 줄여 성능 개선 할 수 있다.
- DBMS 내에서 수행되는 사용자 정의 프로시저로 개발하면 네트워크 트래픽없는 Recursive Call만 발생하므로 빠르게 수행됨
#### Java Loop Query
<pre><code class="java" style="font-size:14px">public class JavaLoopQuery{
public static void insertData( Connection con
, String param1
, String param2
, String param3
, long param4) throws Exception{
String SQLStmt = "INSERT INTO 납입방법별_월요금집계 "
+ "(고객번호, 납입월, 납입방법코드, 납입금액) "
+ "VALUES(?, ?, ?, ?)";
PreparedStatement st = con.prepareStatement(SQLStmt);
st.setString(1, param1);
st.setString(2, param2);
st.setString(3, param3);
st.setLong(4, param4);
st.execute();
st.close();
}
public static void execute(Connection con, String input_month) throws Exception {
String SQLStmt = "SELECT 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷 "
+ "FROM 월요금납부실적 "
+ "WHERE 납입월 = ?";
PreparedStatement stmt = con.prepareStatement(SQLStmt);
stmt.setString(1, input_month);
ResultSet rs = stmt.executeQuery();
while(rs.next()){
String 고객번호 = rs.getString(1);
String 납입월 = rs.getString(2);
long 지로 = rs.getLong(3);
long 자동이체 = rs.getLong(4);
long 신용카드 = rs.getLong(5);
long 핸드폰 = rs.getLong(6);
long 인터넷 = rs.getLong(7);
if(지로 > 0) insertData (con, 고객번호, 납입월, "A", 지로);
if(자동이체 > 0) insertData (con, 고객번호, 납입월, "B", 자동이체);
if(신용카드 > 0) insertData (con, 고객번호, 납입월, "C", 신용카드);
if(핸드폰 > 0) insertData (con, 고객번호, 납입월, "D", 핸드폰);
if(인터넷 > 0) insertData (con, 고객번호, 납입월, "E", 인터넷);
}
rs.close();
stmt.close();
}
</code></pre>
- 처리해야할 납부실적이 10만건이라고 하면 Insert를 위한 Parse Call 50만번, Execute Call 50만번으로 최대 110만번의 데이터베이스 Call이 발생함
</br>
#### Java One SQL
<pre><code class="java" style="font-size:14px">public class JavaOneSQL{
public static void execute(Connection con, String input_month) throws Exception {
String SQLStmt = "INSERT INTO 납입방법별_월요금집계"
+ "(납입월,고객번호,납입방법코드,납입금액) "
+ "SELECT x.납입월, x.고객번호, CHR(64 + Y.NO) 납입방법코드 "
+ " , DECODE(Y.NO, 1, 지로, 2, 자동이체, 3, 신용카드, 4, 핸드폰, 5, 인터넷) "
+ "FROM 월요금납부실적 x, (SELECT LEVEL NO FROM DUAL CONNECT BY LEVEL <= 5) y "
+ "WHERE x.납입월 = ? "
+ "AND y.NO IN ( DECODE(지로, 0, NULL, 1), DECODE(자동이체, 0, NULL, 2) "
+ " , DECODE(신용카드, 0, NULL, 3) , DECODE(핸드폰, 0, NULL, 4) "
+ " , DECODE(인터넷, 0, NULL, 5) )" ;
PreparedStatement stmt = con.prepareStatement(SQLStmt);
stmt.setString(1, input_month);
stmt.executeQuery(); stmt.close();
}
static Connection getConnection() throws Exception { ...... }
static void releaseConnection(Connection con) throws Exception { ...... }
public static void main(String[] args) throws Exception{
Connection con = getConnection();
execute(con, "200903");
releaseConnection(con);
}
}
</code></pre>
- Loop Query를 One SQL로 SQL Statement에 기술하여 실행한다면 위의 최대 110만번 데이터베이스 Call을 단 2회(Parse Call 1회, Execute Call 1회)로 줄일 수 있다.
- 그 이유는 대부분 네트워크 구간에서 오랜 시간이 소비되거나 OS로부터 CPU와 메모리 리소스를 할당 받으려고 기다리면서 소비가 이루어지는데에 비해, 이 부담을 DBMS의 사용자 정의 프로시저로 구현하여 수행함으로써 성능개선 효과를 볼 수 있다.
</br>
### Array Processing 활용
- 데이터베이스 Call은 개별 프로그램의 수행속도와 시스템 전체의 확장성에 영향을 미치기 때문에 중요하다.
- Array Processing 기능을 활용하면 한 번의 SQL(INSERT?UPDATE/DELETE) 수행으로 다량의 레코드를 동시에 처리할 수 있다.
- 네트워크 Call 감소, SQL 수행시간 감소, CPU 사용량 감소 효과를 볼 수 있다.
<pre><code class="java" style="font-size:14px">public static void execute(Connection con, String input_month) throws Exception {
long rows = 0;
String SQLStmt1 = "SELECT 고객번호, 납입월"
+ ", 지로, 자동이체, 신용카드, 핸드폰, 인터넷 "
+ "FROM 월요금납부실적 "
+ "WHERE 납입월 = ?";
String SQLStmt2 = "INSERT INTO 납입방법별_월요금집계 "
+ "(고객번호, 납입월, 납입방법코드, 납입금액) "
+ "VALUES(?, ?, ?, ?)";
con.setAutoCommit(false);
PreparedStatement stmt1 = con.prepareStatement(SQLStmt1);
PreparedStatement stmt2 = con.prepareStatement(SQLStmt2);
stmt1.setFetchSize(1000);
stmt1.setString(1, input_month);
ResultSet rs = stmt1.executeQuery();
while(rs.next()){
String 고객번호 = rs.getString(1);
String 납입월 = rs.getString(2);
long 지로 = rs.getLong(3);
long 자동이체 = rs.getLong(4);
long 신용카드 = rs.getLong(5);
long 핸드폰 = rs.getLong(6);
long 인터넷 = rs.getLong(7);
if(지로 > 0)
insertData (con, stmt2, 고객번호, 납입월, "A", 지로);
if(자동이체 > 0);
insertData (con, stmt2, 고객번호, 납입월, "B", 자동이체);
if(신용카드 > 0)
insertData (con, stmt2, 고객번호, 납입월, "C", 신용카드);
if(핸드폰 > 0)
insertData (con, stmt2, 고객번호, 납입월, "D", 핸드폰);
if(인터넷 > 0)
insertData (con, stmt2, 고객번호, 납입월, "E", 인터넷);
if(++rows%1000 == 0) stmt2.executeBatch();
}
}
</code></pre>
- 위의 Java 코드는 INSERT할 데이터를 계속 Array에 담기만 하다가 1,000건 쌓일 때마다 한 번씩 executeBatch를 수행한다.
- SELECT 결과집합을 Fetch할 때도 1,000개 단위로 Fetch하도록 조정
- 대용량 데이터 처리를 위한 Array Processing 사용 효과를 극대화하려면 연속된 일련의 처리과정이 모두 Array 단위로 진행돼야 한다.
</br>
## Fetch Call 최소화
____
### 부분범위처리 원리
- 쿼리 결과 집합을 전송할 때, 전체 데이터를 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을때마다 일정량씩 나누어서 전송하는 것
- SQL Server : 쿼리분석기 옵션에 설정되어있는 "네트워크 패키지 크기"로 운반 (default 4,096 바이트)
- ORACLE : 내부적으로는 SDU(Session Data Unit,Session 레이어), TDU(Transport Data Unit,Transport 레이어) 단위로 나누어서 전송
- array 사이즈를 작게 설정하면 하나의 네트워크 패킷에 담아 전송하겠지만, 크게 설정하면 여러개의 패킷으로 나누어 전송
<pre><code class="java" style="font-size:14px">set arraysize 100
call count cpu elapsed disk query current rows
----- ----- ----- ------- ----- ----- ----- ------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.02 2 2 0 0
Fetch 301 0.14 0.18 9 315 0 30000
----- ----- ----- ------- ----- ----- ----- ------
total 303 0.14 0.20 11 317 0 30000
</code></pre>
</br>
### ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
- 대량 데이터 파일을 전송할 때 ArraySize를 크게하여 Fetch Call 횟수를 줄여주는 것이 유리
- 반대로 적은량의 데이터만 fetch 하다가 멈추는 프로그램이라면 arraysize를 작게 설정하는것이 유리
- ArraySize를 증가시키면 네트워크 부하감소 및 서버 프로세스가 읽어야할 블록 갯수 감소 효과
- 위의 그림 처럼 10개 행으로 수성된 3개의 블록가 총 30 레코드이므로 ArraySize를 3으로 설정하면 Fetch 횟수는 10, 블록 I/O는 12번이 발생하게 된다.
- 이때 ArraySize를 10으로 설정한다면 3번의 Fetch와 3번의 블록 I/O로 줄일 수 있고, 이때 ArraySize와 Fetch Count는 반비례 관계를 보인다.
#### Java Fetch 사이즈 조정 후 Fetch 매커니즘
<pre><code class="java" style="font-size:14px">String sql = "select custid, name from customer";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setFetchSize(100); -- Statement에서 조정
ResultSet rs = stmt.executeQuery();
// rs.setFetchSize(100); -- ResultSet에서 조정할 수도 있다.
while( rs.next() ) {
int empno = rs.getInt(1);
String ename = rs.getString(2);
System.out.println(empno + ":" + ename);
}
rs.close();
stmt.close();
</code></pre>
- 1.최초 rs.next() 호출 시 한꺼번에 100건을 가져와서 클라이언트 Array 버퍼에 캐싱한다.
- 2.이후 rs.next() 호출할 때는 데이터베이스 Call을 발생시키지 않고 Array 버퍼에서 읽는다.
- 3.버퍼에 캐싱 돼 있던 데이터를 모두 소진한 후 101번째 rs.next() 호출 시 다시 100건을 가져온다.
- 4.모든 결과집합을 다 읽을 때까지 2~3번 과정을 반복한다.
</br>
## 페이지 처리 활용
____
### 페이지 처리를 하지 않았을 때
- 1.다량 발생하는 Fetch Call의 부하
- 2.대량의 결과 집합을 클라이언트에 전송하면서 발생하는 네트워크 부하
- 3.대량의 데이터 블록을 읽으면서 발생하는 I/O 부하
- 4 .AP 서버 및 웹 서버 리소스 사용량 증가
### 페이지 처리를 통한 부하해소
- 1. 페이지 단위로, 화면에서 필요한 만큼만 Fetch Call
- 2. 페이지 단위로, 화면에서 필요한 만큼만 네트워크를 통해 결과 전송
- 3. 인덱스와 부분범위처리 원리를 이용해 각 페이지에 필요한 최소량만 I/O
- 4. 데이터를 소량씩 나누어 전송하므로 AP웹 서버 리소스 사용량 최소화
- <b>결론 : 조회할 데이터가 일정량 이상이고 수행빈도가 높다면 필수적으로 페이지 처리를 해야한다.</b>
</br>
## 분산 쿼리
____
### 분산 DB 간 테이블을 조인할 경우
<pre><code class="SQL" style="font-size:14px">select 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
500000 REMOTE
10981 TABLE ACCESS BY INDEX ROWID ORDER
500000 INDEX UNIQUE SCAN (ORDER_PK)
</code></pre>
- 원격에 있는 salses 테이블을 전송받아 order 테이블과 NL 방식으로 조인하고 있는 상황
- 50만 건이나 되는 sales 테이블의 데이터를 네트워크를 통해 전송받기 때문에 성능은 당연히 나쁨
</br>
### 분산 DB 간 성능저하 해결방안
<pre><code class="java" style="font-size:14px">select /*+ driving_site(b) */ 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
</code></pre>
- 이를 해결하기 위해 sales 테이블보다 데이터가 적은 order 테이블의 order_date 필터조건에 해당하는 데이터만 원격으로 sales로 보내 조인과 group by를 거친 결과집합을 받으면 큰 성능 개선 효과를 볼 수 있다.
- 원격 서버가 쿼리를 처리하도록 driving_site 힌트를 지정할 수 있는 방법이 있다.
- 이를 통해 939건의 order 테이블의 데이터를 원격으로 보냈고, 거기서 처리가 완료된 5건만 전송 받은 것을 확인 할 수 있다.
- <b>분산 쿼리의 성능을 높이는 핵심 원리는 네트워크를 통한 데이터 전송량을 줄이는데 있다.</b>
## 사용자 정의 함수 / 프로시저의 특징과 성능
____
### 사용자 정의 함수 / 프로시저의 특징
- 가상머신과 같은 별도의 실행엔진을 통해 실행됨
- 실행시마다 Context Switching이 일어나므로, 내장함수를 호출할 때와 비교해 성능이 상당히 떨어진다.
#### 문자타입의 일자 데이터를 날짜 타입으로 변환해주는 사용자 정의 함수
<pre><code class="SQL" style="font-size:14px">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;
/
</code></pre>
- `to_char` 함수를 바로 호출하는것 보다 훨씬 느림
- 메인쿼리에 참조하는 사용자 정의 함수에 또 다른 쿼리문이 내장되어 있다면 수행 성능이 훨씬 나빠짐
- Recursive Call이 반복적으로 일어난다.
</br>
### 사용자 정의 함수 / 프로시저에 의한 성능저하 해소 방안
- 대용량 조회 쿼리에서 함수 호출하면 Recursive Call이 반복되기 떄문에 소량 데이터를 조회할 때 사용한다.
- 부분범위 처리가 가능한 상황에서 제한적으로 사용한다.
- 가급적 함수를 풀어 조인 또는 스칼라 서브쿼리 형태로 변환
> SQL 전문가 가이드 : 과목3. SQL 고급 활용 및 튜닝, 1장 SQL 아키텍처 기반 튜닝 원리 - 제3절 데이터베이스 Call과 네트워크 부하(p.499 ~ p.515)
> 사진 출처 : 구루비넷
'SQLP 자격증' 카테고리의 다른 글
| SQLP - Lock (0) | 2018.04.29 |
|---|---|
| SQLP - 데이터베이스 I/O 원리 (0) | 2018.04.28 |
| SQLP - SQL 파싱 부하 (0) | 2018.03.05 |
| SQLP - 데이터베이스 아키텍처(2) (0) | 2018.03.03 |
| SQLP - 데이터베이스 아키텍처(1) (0) | 2018.02.07 |