05. 데이터베이스 Call 최소화 원리 (by feelie) [2016.04.18]
- 01. Call 통계
- 02. User Call vs Recursive Call
- 03. 데이터베이스 Call이 성능에 미치는 영향
- 04. Array Processing 활용
- 05. Fetch Call 최소화
- 06. 페이지 처리의 중요성
- 07. PLSQL 함수의 특징과 성능 부하
- 08. PL/SQL 함수 호출 부하 해소
SQL수행 중에 발생하는 Execute, Fetch Call을 줄이는 방법
데이터베이스 Call을 User Call과 Recursive Call로 나누어 각각을 최소화하는 원리와 방안에대한 설명
01. Call 통계
SQL 트레이스 레포트)
select * from cust_nm, birthday from customer where cust_id = :cust_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5000 0.18 0.14 0 0 0 0
Fetch 5000 0.21 0.25 0 20000 0 50000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 0.39 0.40 0 20000 0 50000
Misses in library cache during parse: 1
- Parse Call :{} 커서를 파싱하는 과정에 대한 통계, 실행계획을 생성하거나 찾는 과정에 관한 정보를 포함
- Execute Call :{} 커서를 실행하는 단계에 대한 통계
- Fetch Call :{} select문에서 실제 레코드를 읽어 사용자가 요구한 결과집합을 반환하는 과정에 대한 통계
DML문 : insert, update, delete, merge
Execute Call 시점에 모든 처리과정을 서버내에서 완료하고 처리결과만 리턴하므로 Fetch Call이 전혀 발생하지 않는다.
delete from objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.21 0.22 0 321 20987 18853
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.21 0.22 0 322 20987 18853
Misses in library cache during parse: 1
insert ... select
클라이언트로부터 명시적인 Fetch Call을 받지 않으며 서버 내에서 묵시적으로 Fetch가 이루어짐
insert into objects
select * from dba_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.03 0 0 0 0
Execute 1 0.15 0.19 3 1563 2946 18853
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.20 0.22 3 1563 2946 18853
Misses in library cache during parse: 1
select 문
Execute Call단계에서는 커서만 오픈하고, 데이터처리는 Fetch 단계에서 발생
select owner, count(*)
from objects
group by owner
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 330 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.01 0 331 0 14
Misses in library cache during parse: 1
For update문
for update 구문을 사용하면 Execute Call 단계에서는 모든 레코드를 읽어 Lock을 설정
select * from
emp for update
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 7 14 0
Fetch 2 0.00 0.00 0 8 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 15 14 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
02. User Call vs Recursive Call
- DB Call 커서 활동 상태에 따른 구분 : Parse, Execute, Fetch
- DB Call 발생 위치에 따른 구분 : User Call, Recursive Call
User Call
- User Call 은 OCI(Oracle Call Interface)를 통해 오라클 외부로부터 들어오는 Call
- User Call 최소화 방법
- Loop 쿼리를 해소하고 집합적 사고를 통해 One-SQL로 구현
- Array Processing : Array 단위 Fetch, Bulk Insert/Update/Delete
- 부분범위처리 원리 활용
- 효과적인 화면 페이지 처리
- 사용자 정의 함수/프로시저/트리거의 적절한 활용
Recursive Call
- Recursive Call은 오라클 내부에서 발생하는 Call을 말한다.
- SQL 파싱과 최적화 과정에서 발생하는 Data Dictionary 조회, PL/SQL로 작성된 사용자 정의 함수/프로시저/트리거 내에서의 SQL 수행이 여기에 해당한다.
- Recursive Call 최소화 방법
- 바인드변수를 적극적으로 사용해 하드파싱 횟수를 줄인다.
- PL/SQL로 작성한 프로그램을 이해하고 시기 적절하게 사용한다.
SQL 트레이스 파일을 TKProf 유틸리티로 포맷팅하면 맨 아래쪽에 아래와 같은 Overall Total 통계가 나온다. 이 중 NON-RECURSIVE 통계가 User Call에 해당하며, 그 아래쪽 RECURSIVE 통계가 Recursive Call에 해당한다.
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.01 0.02 0 1 0 0
Execute 9 0.01 0.09 0 19 69 28
Fetch 4 0.00 0.00 0 16 0 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.03 0.11 0 36 69 56
Misses in library cache during parse: 5
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.01 0 0 0 0
Execute 41 0.04 0.07 0 0 0 0
Fetch 64 0.01 0.03 4 122 0 318
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 113 0.06 0.12 4 122 0 318
Misses in library cache during parse: 7
Misses in library cache during execute: 7
10 user SQL statements in session.
40 internal SQL statements in session.
50 SQL statements in session.
데이터베이스의 종류와 튜닝원리 요약
03. 데이터베이스 Call이 성능에 미치는 영향
기간계 시스템을 정보계 시스템으로 가져오기 위한 ETL 배치 프로그램이 필요
테이블을 생성하고 데이터를 넣는다.
CREATE TABLE 월요금납부실적
AS
SELECT TO_CHAR(OBJECT_ID) 고객번호
, '200903' 납입월
, round(dbms_random.value(1000, 10000), -2) 지로
, round(dbms_random.value(1000, 10000), -2) 자동이체
, round(dbms_random.value(1000, 10000), -2) 신용카드
, round(dbms_random.value(1000, 10000), -2) 핸드폰
, round(dbms_random.value(1000, 10000), -2) 인터넷
FROM ALL_OBJECTS
WHERE ROWNUM <= 30000;
CREATE TABLE 납입방법별_월요금집계 (
고객번호 NUMBER
, 납입월 VARCHAR2(6)
, 납입방법코드 VARCHAR2(1)
, 납입금액 NUMBER
) ;
- PL/SQL 수행
DECLARE
CURSOR C(INPUT_MONTH VARCHAR2) IS
SELECT 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷
FROM 월요금납부실적
WHERE 납입월 = INPUT_MONTH;
REC C%ROWTYPE;
LTYPE VARCHAR2(1);
BEGIN
OPEN C('200903');
LOOP
FETCH C INTO REC;
EXIT WHEN C%NOTFOUND;
IF REC.지로 > 0 THEN
LTYPE := 'A';
INSERT INTO 납입방법별_월요금집계
(고객번호, 납입월, 납입방법코드, 납입금액)
VALUES(REC.고객번호, REC.납입월, LTYPE, REC.지로);
END IF;
IF REC.자동이체 > 0 THEN
LTYPE := 'B';
INSERT INTO 납입방법별_월요금집계
(고객번호, 납입월, 납입방법코드, 납입금액)
VALUES(REC.고객번호, REC.납입월, LTYPE, REC.자동이체);
END IF;
IF REC.신용카드 > 0 THEN
LTYPE := 'C';
INSERT INTO 납입방법별_월요금집계
(고객번호, 납입월, 납입방법코드, 납입금액)
VALUES(REC.고객번호, REC.납입월, LTYPE, REC.신용카드);
END IF;
IF REC.핸드폰 > 0 THEN
LTYPE := 'D';
INSERT INTO 납입방법별_월요금집계
(고객번호, 납입월, 납입방법코드, 납입금액)
VALUES(REC.고객번호, REC.납입월, LTYPE, REC.핸드폰);
END IF;
IF REC.인터넷 > 0 THEN
LTYPE := 'E';
INSERT INTO 납입방법별_월요금집계
(고객번호, 납입월, 납입방법코드, 납입금액)
VALUES(REC.고객번호, REC.납입월, LTYPE, REC.인터넷);
END IF;
END LOOP;
CLOSE C;
END;
- JAVA 프로그램 수행
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();
}
public static void main(String[] args) throws Exception{
long btm = System.currentTimeMillis();
Connection con = getConnection();
execute(con, "200903");
System.out.println("elapsed time : " + (System.currentTimeMillis() - btm));
releaseConnection(con);
}
- PL/SQL문을 수행한 SQL 트레이스 결과
SELECT 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷
FROM
월요금납부실적 WHERE 납입월 = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.03 0 1 0 0
Fetch 30001 0.32 0.25 0 30004 0 30000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30003 0.32 0.29 0 30005 0 30000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
30000 TABLE ACCESS FULL 월요금납부실적 (cr=30004 pr=0 pw=0 time=150156 us)
********************************************************************************
INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 납입방법코드, 납입금액)
VALUES
(:B4 , :B3 , :B2 , :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 150000 3.38 3.14 0 2440 162387 150000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 150005 3.38 3.14 0 2440 162387 150000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41 (recursive depth: 1)
- 총소요시간 : 1377 msc
- 데이터베이스 call : 180008
- 처리해야 할 월요금납부실적이 100만 건이면 이 테이블에 대한 Fetch Call이 100만 번, 납입방법별_월요금집계 테이블로의 insert를 위한 Execute Call이 최대 100만번, 따라서 최대 200만 번의 데이터베이스 Call이 발생하게 된다.
- 그나마 PL/SQL문으로 코딩했기 때문에 네트워크 트래픽 없는 Recursive Call이므로 제법 빠르게 수행된다.
- Java 프로그램을 수행한 SQL 트레이스 결과
SELECT 고객번호, 납입월 , 지로, 자동이체, 신용카드, 핸드폰, 인터넷
FROM
월요금납부실적 WHERE 납입월 = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3001 0.14 0.27 0 3121 0 30000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3003 0.14 0.27 0 3121 0 30000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 41
Rows Row Source Operation
------- ---------------------------------------------------
30000 TABLE ACCESS FULL 월요금납부실적 (cr=3121 pr=0 pw=0 time=60062 us)
********************************************************************************
INSERT INTO 납입방법별_월요금집계 (고객번호, 납입월, 납입방법코드, 납입금액)
VALUES
(:1, :2, :3, :4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 150000 1.35 1.26 0 0 0 0
Execute 150000 8.31 9.56 2 150130 746331 150000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 300000 9.66 10.82 2 150130 746331 150000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41
********************************************************************************
- 총소요시간 : 942519 msc
- 데이터베이스 call : 303,000번
- select 문에서 Fetch Call이 앞에서보다 1/10 수준으로 준 것은 JAVA에서 FetchSize 기본 설정이 10이기 때문이다.
- insert문은 애플리케이션 커서 캐싱 기법을 사용하지 않았으므로 Execute Call과 같은 횟수만큼 Parse Call 발생했다.
- 여기서 중요한것은 순수하게 서버에서 실행된 시간은 10여초에 불과, 나머지는 네트워크구간에서 소비한 시간, 그리고 데이터베이스Call이 발생할 때마다 매번 OS로부터 CPU와 메모리 리소스를 할당받으려고 소비한 시간이다.(User Call이 Recursive Call에 비해 더 심각한 부하를 일으키는 이유가 바로 여기있다.)
- 개선
1. One-SQL로 통합
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.납입월 = '200903'
AND y.NO IN (
DECODE(지로, 0, NULL, 1)
, DECODE(자동이체, 0, NULL, 2)
, DECODE(신용카드, 0, NULL, 3)
, DECODE(핸드폰, 0, NULL, 4)
, DECODE(인터넷, 0, NULL, 5)
) ;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 1.190 1.304 0 1590 3835 150000
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 1.190 1.304 0 1590 3835 150000
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user: IBS_ADM (ID=41)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
150000 NESTED LOOPS (cr=710 pr=0 pw=0 time=600157 us)
5 VIEW (cr=0 pr=0 pw=0 time=96 us)
5 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=87 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=4 us)
150000 TABLE ACCESS FULL 월요금납부실적 (cr=710 pr=0 pw=0 time=450321 us)
2. 소트 머지 조인 또는 해시 조인으로 유도하기 위한 튜닝
INSERT INTO 납입방법별_월요금집계(납입월,고객번호,납입방법코드,납입금액)
SELECT */*+ USE_MERGE(X Y) NO_EXPAND NO_MERGE(X) */* x.납입월, x.고객번호
, CHR(64 + Y.NO) 납입방법코드
, DECODE(Y.NO, 1, 지로, 2, 자동이체, 3, 신용카드, 4, 핸드폰, 5, 인터넷)
FROM (SELECT 1 DUMMY,납입월,고객번호,지로,자동이체,신용카드,핸드폰,인터넷
FROM 월요금납부실적
WHERE 납입월 = '200903') x
,(SELECT 1 DUMMY, LEVEL NO FROM DUAL CONNECT BY LEVEL <= 5) y
WHERE x.DUMMY = y.DUMMY
AND y.NO IN (
DECODE(지로, 0, NULL, 1)
, DECODE(자동이체, 0, NULL, 2)
, DECODE(신용카드, 0, NULL, 3)
, DECODE(핸드폰, 0, NULL, 4)
, DECODE(인터넷, 0, NULL, 5)
) ;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 1.470 1.550 0 1037 3778 150000
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 1.470 1.550 0 1037 3778 150000
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user: IBS_ADM (ID=41)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
150000 MERGE JOIN (cr=142 pr=0 pw=0 time=1004484 us)
5 SORT JOIN (cr=0 pr=0 pw=0 time=115 us)
5 VIEW (cr=0 pr=0 pw=0 time=70 us)
5 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=55 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=4 us)
150000 FILTER (cr=142 pr=0 pw=0 time=854395 us)
150000 SORT JOIN (cr=142 pr=0 pw=0 time=254934 us)
30000 VIEW (cr=142 pr=0 pw=0 time=60074 us)
30000 TABLE ACCESS FULL 월요금납부실적 (cr=142 pr=0 pw=0 time=72 us)
- 총소요시간 : 1초안밖
- 다음절에 나오는 Array Pocessing 기법을 활용하면, JAVA 같은 프로그램에서도 네트워크 트래픽을 획기적으로 줄여 줘 굳이 One-SQL로 작성하지 않더라도 성능개선 효과를 얻을수 있다.
"납입방법별_월요금집계" 테이블을 읽어 "월요금납부실적"을 가공하고자 할 때는 어떻게 하면될까?
- 잘못된 사례
INSERT INTO 월요금납부실적
(고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷)
SELECT K.고객번호, '200903' 납입월
, A.납입금액 지로
, B.납입금액 자동이체
, C.납입금액 신용카드
, D.납입금액 핸드폰
, E.납입금액 인터넷
FROM 고객 K
,(SELECT 고객번호, 납입금액 FROM 납입방법별_월요금집계
WHERE 납입월 = '200903'
AND 납입방법코드 = 'A') A
,(SELECT 고객번호, 납입금액 FROM 납입방법별_월요금집계
WHERE 납입월 = '200903'
AND 납입방법코드 = 'B') B
,(SELECT 고객번호, 납입금액 FROM 납입방법별_월요금집계
WHERE 납입월 = '200903'
AND 납입방법코드 = 'C') C
,(SELECT 고객번호, 납입금액 FROM 납입방법별_월요금집계
WHERE 납입월 = '200903'
AND 납입방법코드 = 'D') D
,(SELECT 고객번호, 납입금액 FROM 납입방법별_월요금집계
WHERE 납입월 = '200903'
AND 납입방법코드 = 'E') E
WHERE A.고객번호(+) = K.고객번호
AND B.고객번호(+) = K.고객번호
AND C.고객번호(+) = K.고객번호
AND D.고객번호(+) = K.고객번호
AND E.고객번호(+) = K.고객번호 ;
- 잘된 사례
INSERT INTO 월요금납부실적
(고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷)
SELECT 고객번호, 납입월
, NVL(SUM(DECODE(납입방법코드, 'A', 납입금액)), 0) 지로
, NVL(SUM(DECODE(납입방법코드, 'B', 납입금액)), 0) 자동이체
, NVL(SUM(DECODE(납입방법코드, 'C', 납입금액)), 0) 신용카드
, NVL(SUM(DECODE(납입방법코드, 'D', 납입금액)), 0) 핸드폰
, NVL(SUM(DECODE(납입방법코드, 'E', 납입금액)), 0) 인터넷
FROM 납입방법별_월요금집계
WHERE 납입월 = '200903'
GROUP BY 고객번호, 납입월 ;
04. Array Processing 활용
1. 한 번의 SQL 수행으로 다량의 로우를 동시에 insert/update/delet 수행
2. 네트워크를 통한 데이터베이스 Call을 감소
3. SQL 수행시간과 CPU 사용량을 획기적으로 줄여준다.
"납입방법_월요금집계" 테이블을 가공하는 사례를 Array Processing을 이용하는 방식 적용
public class JavaArrayProcessing{
public static void insertData( Connection con
, PreparedStatement st
, String param1
, String param2
, String param3
, long param4) throws Exception{
st.setString(1, param1);
st.setString(2, param2);
st.setString(3, param3);
st.setLong(4, param4);
st.addBatch();
}
public static void execute(Connection con, String input_month)
throws Exception {
long rows = 0;
String SQLStmt1 = "SELECT 고객번호, 납입월"
+ " , 지로, 자동이체, 신용카드, 핸드폰, 인터넷 "
+ "FROM 월요금납부실적 "
+ "WHERE 납입월 = ?";
String SQLStmt2 = "INSERT /*+ test3 */ 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();*
}
rs.close();
stmt1.close();
*stmt2.executeBatch();*
stmt2.close();
con.commit();
con.setAutoCommit(true);
}
public static void main(String[] args) throws Exception{
long btm = System.currentTimeMillis();
Connection con = getConnection();
execute(con, "201206");
System.out.println("elapsed time : " + (System.currentTimeMillis() - btm));
releaseConnection(con);
}
SELECT 고객번호, 납입월 , 지로, 자동이체, 신용카드, 핸드폰, 인터넷
FROM
월요금납부실적 WHERE 납입월 = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 31 0.06 0.29 0 169 0 30000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33 0.06 0.29 0 169 0 30000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 41
Rows Row Source Operation
------- ---------------------------------------------------
30000 TABLE ACCESS FULL 월요금납부실적 (cr=169 pr=0 pw=0 time=25 us)
********************************************************************************
INSERT /*+ test3 */ INTO 납입방법별_월요금집계 (고객번호, 납입월,
납입방법코드, 납입금액)
VALUES
(:1, :2, :3, :4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 30 0.63 0.81 2 1142 5106 150000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31 0.63 0.82 2 1142 5106 150000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41
- insert문에 대한 Execute Call이 30회만 발생
insert된 로우 수가 150,000건이므로 매번 5,000건씩 Array Processing
=>커서에서 Fetch되는 각 로우마다 5번씩 insert를 수행하고, 1000 로우마다 한번식 executeBatch를 수행 - select 결과를 Fetch 할 때도 1,000개 단위로 Array Fetch 하도록 조정하여 (JAVA에서 기본값은 10) 3,000건을 읽는데 Fetch Call이 31회만 발생했다.
Bulk Select/Bulk Insert 예제
PL/SQL을 이용해 데이터를 Bulk로 1,000건씩 Fetch해서 Bulk로 insert
create table emp
as
select object_id empno, object_name ename, object_type job
,round(dbms_random.value(1000,5000), -2) sal
,owner deptno, created hirdate
from all_objects
where rownum <= 10000;
create table emp2
as
select * from emp where 1=2;
DECLARE
l_fetch_size NUMBER DEFAULT 1000; -- 1,000건씩 Array 처리
CURSOR c IS
SELECT empno, ename, job, sal, deptno, hirdate
FROM emp;
TYPE array_empno IS TABLE OF emp.empno%type;
TYPE array_ename IS TABLE OF emp.ename%type;
TYPE array_job IS TABLE OF emp.job%type;
TYPE array_sal IS TABLE OF emp.sal%type;
TYPE array_deptno IS TABLE OF emp.deptno%type;
TYPE array_hiredate IS TABLE OF emp.hirdate%type;
l_empno array_empno := array_empno ();
l_ename array_ename := array_ename ();
l_job array_job := array_job ();
l_sal array_sal := array_sal ();
l_deptno array_deptno := array_deptno ();
l_hiredate array_hiredate := array_hiredate();
PROCEDURE insert_t( p_empno IN array_empno
, p_ename IN array_ename
, p_job IN array_job
, p_sal IN array_sal
, p_deptno IN array_deptno
, p_hiredate IN array_hiredate ) IS
BEGIN
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) );
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
END insert_t;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT
INTO l_empno, l_ename, l_job, l_sal, l_deptno, l_hiredate
LIMIT l_fetch_size;
insert_t( l_empno, l_ename, l_job, l_sal, l_deptno, l_hiredate );
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
트레이스 결과
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO, HIRDATE
FROM
EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 11 0.02 0.03 1 82 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.03 0.03 1 83 0 10000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
10000 TABLE ACCESS FULL EMP (cr=82 pr=1 pw=0 time=30 us)
********************************************************************************
INSERT INTO EMP2
VALUES
( :B1 , :B2 , :B3 , :B4 , :B5 , :B6 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.04 0.07 0 151 976 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.04 0.07 0 151 976 10000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 41 (recursive depth: 1)
- SQL 트레이스 결과를 보면, 10,000건을 처리하는데 select문의 Fetch Call과 insert문의 Execute Call이 각각 10번씩만 발생한 것을 알 수 있다.
(select의 Fetch Call이 11번이 발생한 것은 데이터가 더 있는지 확인하기 위한 것임) - EXP, IMP 명령을 통해 데이터를 Export, Import 할 때도 내부적으로 Array Proccessing이 활용
05. Fetch Call 최소화
- 부분범위처리 원리
- OLTP 환경에서 부분범위처리에 의한 성능개선 원리
- ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
- 프로그램 언어에서 Array 단위 Fetch 기능 활용
1. 부분범위처리의 원리
-- 테이블 생성 ( Array Size : 5)
create table t (
x NUMBER not null
, y NUMBER not null ) ;
-- 데이터 insert(랜덤으로)
insert into t
select *
from (
select rownum x, rownum y
from dual
connect by level <= 500000
)
order by dbms_random.value
;
alter table t add
constraint t_pk primary key (x);
alter system flush buffer_cache;
-- 6건 검색
select /*+ index(t t_pk) */ x, y
from t
where x > 0
and y <= 6 ;
X Y
-------- ---------
1 1
2 2
3 3
4 4
5 5 -> 엔터를 치자마자 여기까지 출력하고 잠시 멈춤
6 6 -> 일정시간이 지난 후에 이 라인을 출력하고 수행 종료
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.96 1.06 1040 500546 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.98 1.07 1040 500548 0 6
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
6 6 6 TABLE ACCESS BY INDEX ROWID T (cr=500546 pr=1040 pw=0 time=55 us cost=307 size=312 card=12)
500000 500000 500000 INDEX RANGE SCAN T_PK (cr=1047 pr=1040 pw=0 time=643856 us cost=12 size=0 card=4687)(object id 23893)
2. OLTP 환경에서 부분범위처리에 의한 성능개선 원리
- 첫번째 Fetch Call
1. 오라클 서버는 이 5개 레코드를 아주 빠르게 찾았으므로 지체 없이 전송 명령을 통해 클라이언트에게 전송
2. 클라이언트는 Array 버퍼에 담긴 5개의 레코드를 곧바로 화면에 출력.
*두번째 Fetch Call
1. 두번째 Fetch Call 명령을 받자마자 x=y=6인 레코드를 찾아 Oracle Net으로 내려보낸다.
2. 조건에 더이상 만족하는 레코드가 없다는 사실을 오라클은 모르기 때문에 계속 인덱스를 스캔하면서 테이블을 엑세스해 본다.
끝까지 가 본 후에야 더는 전송할 데이터가 없음을 인식하고 한 건만 전송하도록 Oracle Net에 명령을 보낸다.
- One-Row Fetch
select /*+ index(t t_pk) */ x,y
from t
where x > 0
and y <= 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.57 0.59 0 500545 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.57 0.59 0 500547 0 1
PARSE #215280104:c=31200,e=31689,p=0,cr=75,cu=0,mis=1,r=0,dep=0,og=1,plh=3772518221,tim=3274073163
EXEC #215280104:c=0,e=79,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3772518221,tim=3274074399
FETCH #215280104:c=0,e=71,p=0,cr=4,cu=0,mis=0,[r=1],dep=0,og=1,plh=3772518221,tim=3274074707
FETCH #215280104:c=577203,e=592163,p=0,cr=500541,cu=0,mis=0,[r=0],dep=0,og=1,plh=3772518221,tim=3274667442
select /*+ index(t t_pk) */ x, y
from t
where x > 0
and y <= 6 ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.96 1.06 1040 500546 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.98 1.07 1040 500548 0 6
PARSE #215280104:c=31200,e=30590,p=0,cr=75,cu=0,mis=1,r=0,dep=0,og=1,plh=3772518221,tim=1112506656
EXEC #215280104:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3772518221,tim=1112506951
FETCH #215280104:c=0,e=39,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=3772518221,tim=1112507068
FETCH #215280104:c=0,e=60,p=0,cr=6,cu=0,mis=0,r=5,dep=0,og=1,plh=3772518221,tim=1112507405
FETCH #215280104:c=561604,e=566956,p=0,cr=500536,cu=0,mis=0,r=0,dep=0,og=1,plh=3772518221,tim=1113079488
3. ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
대량 데이터를 내려받을 때 ArraySize를 크게 설정하면
- Fetch Call 횟수가 줄어 네트워크 부하가 감소하고, 쿼리 성능이 향상
- 서버 프로세스가 읽어야 할 블록 개수까지 감소
ArraySize를 조정하는데 왜 블록 I/O가 줄어드는 것일까?
SQL> create table test
as
select * from all_objects;
테이블이 생성되었습니다.
SQL> set autotrace traceonly statistics;
SQL> set arraysize 2;
SQL> select * from test;
47094 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23871 consistent gets
530 physical reads
0 redo size
6247169 bytes sent via SQL*Net to client
259498 bytes received via SQL*Net from client
23548 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
47094 rows processed
ArraySize를 2로 설정하고 47094로우를 가져오게 함.
읽은 블록개수(consistent gets) : 23871
Featch 횟수(SQL*Net roundtrips to/from client) : 23548
===> Fetch할 때마다 2개의 로우(47094/23548 = 1.9999....) 씩 읽는것을 알 수 있다.
SQL 트레이스
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 23548 0.27 1.54 0 23871 0 47094
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23550 0.27 1.58 0 23871 0 47094
ArraySize를 계속 늘려서 위와같은 테스트를 반복해 보자.
ArraySize를 키울수록 Fetch Count는 횟수가 줄고 더불어 Block I/O까지 주는것을 볼수 있다. 즉, 반비례다.
표 참조)
ArraySize를 키운다고 같은 비율로 Fetch Count와 Block I/O가 줄지 않는다.
따라서, 무작정 크게 설정한다고 좋은것만은 아니며, 오히려 리소스 낭비가 된다.
ArraySize가 늘면서 블록I/O가 감소하는 원리는?
10개의 행으로 구성된 3개의 블럭이 있다고 가정한다.
- 총 30개의 레코드이므로 ArraySize를 3으로 설정하면 Fetch 횟수는 10이고, Block I/O는 12번 발생한다.
- 1번블록 : 2~4번째 Fetch에서 반복 엑세스
- 2번블록 : 4~7번째 Fetch에서 반복 엑세스
- 3번블록 : 7~10번째 Fetch에서 반복 엑세스
- 만약 ArraySize를 30으로 설정하면 Fetch횟수는 1로 줄어든다.
4. 프로그램 언어에서 Array 단위 Fetch 기능 활용
PL/SQL을 포함한 프로그램 언어에서 어떻게 ArraySize를 제어하는지 확인하자.
Cursor FOR Loop문을 사용할 경우
- 9i까지 : 한 로우씩 처리 (4절 Array Processing의 Bulk Collect 구문을 사용하지 않는 한)
- 10g : 자동으로 100개씩 Array Processing
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;
/
-- sql트레이스는 동일
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 11 0.00 0.00 0 24 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.00 0.00 0 24 0 1000
- 일반 커서사용*
declare
cursor c is
select object_name
from test where rownum <= 1000;
l_object_name test.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;
-- sql 트레이스 결과
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1001 0.00 0.00 0 1003 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1003 0.00 0.00 0 1003 0 1000
* sys_refcursor 사용
declare
p_cursor sys_refcursor;
o_name dba_objects%rowtype;
begin
open p_cursor for select * from dba_objects where rownum <= 1000;
loop
fetch p_cursor into o_name;
exit when p_cursor%notfound;
dbms_output.put_line(o_name.object_name);
end loop;
close p_cursor;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.10 0.08 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.12 0.09 0 0 0 1
SELECT *
FROM
DBA_OBJECTS WHERE ROWNUM <= 1000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1001 0.01 0.03 0 2104 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1003 0.04 0.07 0 2104 0 1000
위의 결과 Cursor FOR Loop를 사용하지 않으면 Array단위 Fetch가 작동하지 않음을 알 수 있다.
- JAVA 프로그램에서 ArraySize를 조정하는 방법
String sql = "select id,pw from customer";
PreparedStatment stmt = conn.prepareStatment(sql);
Stmt.setFetchSize(100);
ResultSet rs = stmt.executeQuery();
// rs.setFetchSize(100); -- ResultSet에서 조정할 수도 있다.
while(rs.next()){
......
}
rs.close();
stmt.close();
- JAVA에서 FetchSize 기본 값은 10
- 대량 데이터를 Fetch 할 때 이 값을 100~500 정도로 늘려 주면 기본 값을 사용할 때보다 데이터베이스 Call 부하를 1/10 ~ 1/50로 줄일 수 있다.
- FetchSize를 100으로 설정했을 때 데이터를 Fetch 해오는 메커니즘은 아래와 같다.
- 최초 rs.next() 호출 시 한꺼번에 100건을 가져와서 클라이언트 Array 버퍼에 캐싱한다.
- 이후 rs.next() 호출할 때는 데이터베이스 Call을 발생시키지 않고 Array 버퍼에서 읽는다.
- 버퍼에 캐싱 돼 있던 데이터를 모두 소진한 후 101번째 rs.next() 호출 시 다시 100건을 가져온다.
- 모든 결과집합을 다 읽을 때까지 2~3번 과정을 반복한다.
06. 페이지 처리의 중요성
- 클라이언트/서버환경
대용량데이터 조회시 커서를 닫지않고 이벤트가 발생할때마다 결과 집합을 Fetch 하도록 구현.
- 웹 애플리케이션환경
커서를 계속 오픈한 채로 결과집합을 핸들링할 수 없다.
사용자가 다음 패치를 요청하거나, 스크롤 바를 내릴 때마다
개별적인 SQL문을 수행하는 방식으로 페이지 처리를 구현
- 페이지 처리를 하지 않을때 발생하는 부하요인
1. 다량의 Fetch Call 발생
2. 대량의 결과 집합을 클라이언트로 전송하면서 발생하는 네트워크 부하
3. 대량의 데이터 블록을 읽으면서 발생하는 I/O 부하
4. AP 서버 및 웹 서버 리소스 사용량 증가
- 페이지 처리의 부하를 해소 하는 방법
1. 페이지 단위로, 화면에서 필요한 만큼씩 Fetch Call
2. 페이지 단위로, 화면에서 필요한 만큼씩 네트워크를 통해 전송
3. 인덱스와 부분범위처리 원리를 이용해 각 페이지에 필요한 최소량만 I/O
4. 데이터를 소량씩 나누어 전송하므로 AP , 웹 서버 리소스 사용량 최소화
07. PLSQL 함수의 특징과 성능 부하
(1)PL/SQL 함수의 특징
1. 인터프리터 언어다.
SQL> show parameter plsql
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags string
plsql_code_type string INTERPRETED
plsql_compiler_flags string INTERPRETED, NON_DEBUG
plsql_debug boolean FALSE
plsql_native_library_dir string
plsql_native_library_subdir_count integer 0
plsql_optimize_level integer 2
plsql_v2_compatibility boolean FALSE
plsql_warnings string DISABLE:ALL
2. PL/SQL엔진(가상머신, Virtual machine)만 있으면 어디서든 실행가능.
3. native 코드로 완전 컴파일된 내장 함수에 비해 많이 느리다.
4. 매번 SQL 실행엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭이 일어난다.
h2.(2)Recursive Call을 포함하지 않는 함수의 성능 부하
내장함수 TO_CHAR와 사용자정의한 함수를 사용했을때 차이
SQL> create or replace function data_to_char(p_Dt date) return varchar2
2 as
3 begin
4 return to_char(p_dt,'yyyy/mm/dd hh24:mi:ss');
5 end;
6 /
함수가 생성되었습니다.
SQL> create table t (no number, char_time varchar2(21));
테이블이 생성되었습니다.
경 과: 00:00:00.01
-- 내장함수사용
SQL> insert into t
2 select rownum no,
3 to_char(sysdate+rownum, 'yyyy/mm/dd hh24:mi:ss') char_time
4 from dual
5 connect by level <= 1000000;
1000000 개의 행이 만들어졌습니다.
경 과: 00:01:52.21
-- 사용자가만든 함수사용
SQL> insert into t
2 select rownum no,
3 data_to_char(sysdate+rownum) char_time
4 from dual
5 connect by level <= 1000000;
1000000 개의 행이 만들어졌습니다.
경 과: 00:03:27.40
h2.(3) Recursive Call를 포함 하는 함수의 성능 부하
select 문 삽입후 테스트
SQL> create or replace function date_to_char(p_dt date) return varchar2
2 as
3 l_empno number;
4 begin
5 select 1 into l_empno from dual;
6
7 return to_char(p_dt,'yyyy/mm/dd hh24:mi:ss');
8 end;
9 /
함수가 생성되었습니다.
경 과: 00:00:00.03
SQL> delete from t;
2000000 행이 삭제되었습니다.
경 과: 00:01:27.14
SQL> insert into t
2 select rownum no,
3 date_to_char(sysdate+rownum) char_time
4 from dual
5 connect by level <= 1000000;
1000000 개의 행이 만들어졌습니다.
경 과: 00:01:51.39
- 트레이스결과
SELECT 1
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000000 15.09 12.98 0 0 0 0
Fetch 1000000 10.64 8.73 0 0 0 1000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2000001 25.73 21.71 0 0 0 1000000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 73 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1000000 FAST DUAL (cr=0 pr=0 pw=0 time=6174443 us)
1. 사용자 정의함수는 소량의 데이터 조회 시에 사용
2. 대용량 조회 시에는 부분범위처리가 가능한 상황에서 제한적으로 사용
3. 조인, 스칼라 서브쿼리 형태호 변환
h2.(4) 함수를 필터 조건으로 사용할 때 주의 사항
함수를 where절에 필터조건으로 사용할때
SQL> create or replace function emp_avg_sal return number
2 is
3 l_avg_sal number;
4 begin
5 select avg(sal) into l_avg_sal from emp;
6
7 return l_avg_sal;
8 end;
9 /
함수가 생성되었습니다.
SQL> create index emp_x01 on emp(sal);
인덱스가 생성되었습니다.
SQL> create index emp_x02 on emp(deptno);
인덱스가 생성되었습니다.
SQL> create index emp_x03 on emp(deptno, sal);
인덱스가 생성되었습니다.
SQL> create index emp_x04 on emp(deptno, ename, sal);
인덱스가 생성되었습니다.
<케이스1 : 인덱스를 사용하지 않고 Full Scan할때는 읽은 전체 건수만큼 함수호출>
=================================================================================
select /*+ full(emp) */ * from emp
where sal >= emp_avg_sal
-------------------------------------
SELECT AVG(SAL)
FROM
EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 14 0.01 0.02 0 0 0 0
Fetch 14 0.00 0.00 0 98 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29 0.03 0.02 0 98 0 6
=================================================================================
(테스트 결과)
SELECT AVG(SAL)
FROM
EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 12 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.00 0 72 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25 0.00 0.00 0 72 0 12
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 48 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=6 pr=0 pw=0 time=125 us)
14 14 14 TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0 time=84 us cost=3 size=20 card=2)
===========================================================================================
<케이스 2 : 인덱스를 이용하도록 하면 함수호출이 한번일어난다.>
select /*+ index(emp(sal)) */ * from emp
where sal >= emp_avg_sal;
=====================================================================================
SELECT AVG(SAL)
FROM
EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 1 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 48 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=1 pr=0 pw=0 time=86 us)
12 12 12 INDEX FULL SCAN EMP_X01 (cr=1 pr=0 pw=0 time=61 us cost=1 size=20 card=2)(object id 23899)
==============================================================================================
<케이스 3 : 조건절에 deptno = 20 추가, exp_x02인덱스이용하여 조회>
SQL> select /*+ index(emp, emp_x02) */ * from emp
2 where sal >= emp_avg_sal <-- 필터링 건수 3건
3 and deptno =20;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_X02 | 5 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL">="EMP_AVG_SAL"())
2 - access("DEPTNO"=20)
테이블액세스 하는 횟수만큼 5번 함수호출이 일어난걸 확인할수있다.
<케이스 4 : deptno + sal 순으로 구성된 emp_x03 인덱스이용
SQL> select /*+ index(emp(deptno, sal) */ * from emp
2 where sal >= emp_avg_sal
3 and deptno =20;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_X03 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=20 AND "SAL">="EMP_AVG_SAL"() AND "SAL" IS NOT NULL)
sal >= 까지 조건까지 인덱스 조건으로 사용으로 함수호출이 한번 일어난걸확인
<케이스 5 : 조건은 같고, deptno 와 sal 컬럼중간인 ename 컬럼이 낀 emp_x04 인덱스사용
SQL> select /*+ index(emp(deptno, ename, sal)) */ * from emp
2 where sal >= emp_avg_sal
3 and deptno =20;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_X04 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=20 AND "SAL">="EMP_AVG_SAL"())
filter("SAL">="EMP_AVG_SAL"())
첫번째 액세스 단계에서 1번
필터 단계에서 나머지 4건을 찾는동안 4번 , deptno = 20 범위를 넘어 더이상 조건을 만족하는 레코드가
없음을 확인하는 one-plus 스캔과정에서 1번, 하여 총6번의 함수 호출이 일어난다고 하는데 1번만 발생.
<케이스 6 : = 조건이 아닌경우
SQL> select /*+ index(emp(deptno, sal)) */ * from emp
2 where sal >= emp_avg_sal
3 and deptno >= 10;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_X03 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO">=10 AND "SAL">="EMP_AVG_SAL"() AND "DEPTNO" IS NOT NULL)
filter("SAL">="EMP_AVG_SAL"())
인덱스 스캔할 첫 번째 레코드를 액세스하는 단계에서 1번
deptno >= 10 조건을 만족하는 나머지 13건을 스캔하는 동안 13번
하여 총 14번 스캔이 말생한다고 하는데 테스트 결과는 한번만 나왔다.
- 대용량테이블에서 조건절과 인덱스 구성에 따라 성능 차이가 매우 크게나타날 수 있음을 확인할수있다.
h2.(5) 함수와 읽기 일관성
함수 내에서 수행하는 Recursive 쿼리는 메인 쿼리의 시작 시점과 무관하게 그 쿼리가 수행되는 시점을 기준으로 블록을
읽기 때문에 문장수준 읽기 일관서을 보장하지 않는다
** 읽기 일관성을 보장 못하는 예
select a.지수업종코드
,min(a.지수업종명) 지수업종명
,avg(SF_현재가(b.종목코드)) 평균주식가격
,sum(SF_시가총액(b.종목코드)) 시가총액
from 지수업종 a, 지수업종구성종목 b
where a.지수업종유형코드 = '001'
and b.지수업종코드 = ㅁ.지수업종코드
group by a.지수업종코드;
---------------------------------------------
select a.지수업종코드
,min(a.지수업종명)지수업종명
,(select avg(SF_현재가(b.종목코드)) from dual) 평균주식가격
,(select sum(SF_시가총액(b.종목코드)) from dual) 시가총액
from 지수업종 a, 지수업종구성종목 b
where a.지수업종유형코드 = '001'
and b.지수업종코드 = ㅁ.지수업종코드
group by a.지수업종코드;
=============================================================
** 읽기 일관성을 보장하는 예
(조인)
select a.지수업종코드
,min(a.지수업종명)지수업종명
,avg(c.현재가) 평균주식가격
,sum(c.현재가*c.발행주식수) 시가총액
from 지수업종 a, 지수업종구성종목 b, 종목별시세 c
where a.지수업종유형코드 = '001'
and b.지수업종코드 = ㅁ.지수업종코드
and b.종목코드 = c.종목코드
group by a.지수업종코드;
(스칼라 서브쿼리)
select a.지수업종코드
,min(a.지수업종명)지수업종명
,avg(select 현재가 from 종목별시세
where 종목코드 = b.종목코드) 평균주식가격
,sum(select 현재가*발행주식수 from 종목별시세
where 종목코드 = b.종목코드) 시가총액
from 지수업종 a, 지수업종구성종목 b
where a.지수업종유형코드 = '001'
and b.지수업종코드 = ㅁ.지수업종코드
group by a.지수업종코드;
=====================================================================
(6) 함수의 올바른 사용 기준
- Dependency 체인에 의한 라이브러리 캐시 부하를 최소화하려면 가급적 함수/프로시저보다 패키지 사용이 유리
08. PL/SQL 함수 호출 부하 해소
- 사용자정의 함수의 사용범위
1) 소량의 데이터 조회시
2) 대용량 데이터를 조회 할때는 부분범위 처리가 가능한 상황에서 제한적으로
3) 조인 또는 스칼라 서브쿼리 형태로 변환하려는 노력이 필요
4) 어쩔 수 없을 때는 사용하지만, 호출 횟수를 최소화 할수 있는방법을 강구
- 함수호출 부하 해소 방안 요약
1) 페이지 처리 또는 부분범위처리 활용
2) Decode함수 또는 Case문으로 변환
3) 뷰머지 방지를 통한 함수 호출 최소화
4) 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화
5) Deterministic 함수의 캐싱 효과 활용
6) 복잡한 함수 로직을 풀어 SQL로 구현
1) 페이저 처리 또는 부분범위 처리활용
클라이언트에게 데이터를 전송하는 단계에서 함수 호출이 일어나게 처리함으로 속도개선향상
Select *
From (
Select memb_nm(매수회원번호) 매도 회원명
,memb_nm(매수회원번호) 매수회원명
,code_nm('446' , 매도 투자자 구분코드) 매도투자자구분명
,code_nm('446' , 매수 투자자 구분코드) 매수투자자구분명
,code_nm('418' , 체결 유형코드) 체결 유형명
. . . . . . .
From 체결
Where 종목코드 = : 종목코드
And 체결일자 = : 체결일자
And 체결시간 between sysdate-10/21/60 and sysdate
Order by 체결시각 desc
) a
Where rownum <= 30
)
Where no between 21 and 30
)
- 조건절에 부합하는 전체 레코드 건수만큼 함수 호출을 일으키고 그 결과 집합을 Sort Area 또는 Temp 테이블 스페이스 에 저장한다.
그리고 최종 결과집합 10건만 전송한다.
Select memb_nm(매수회원번호) 매도 회원명
,memb_nm(매수회원번호) 매수회원명
,code_nm('446' , 매도 투자자 구분코드) 매도투자자구분명
,code_nm('446' , 매수 투자자 구분코드) 매수투자자구분명
,code_nm('418' , 체결 유형코드) 체결 유형명
. . . . . . .
From (
Select rownum no, a.*
From (
select 매도회원번호, 매수 회원번호,
매도투자자구분코드, 매수 투자자구분코드
. . . . . . . . . . . . .
From 체결
Where 종목코드 = : 종목코드
And 체결일자 = : 체결일자
And 체결시간 between sysdate-10/21/60 and sysdate
Order by 체결시각 desc
) a
Where rownum <= 30
)
Where no between 21 and 30
)
- Order by 와 rownum에 의한 필터 처리 후 사용자에게 전송하는 결과 집합에 대해서만 함수 호출이 일어난다.
2) Decode, Case 함수 문으로 변환
- 체결 테이블 생성
CREATE TABLE 체결(체결일자, 체결번호, 시장코드, 증권그룹코드, 체결수량, 체결금액)
NOLOGGING
AS
SELECT '20090315'
, ROWNUM
, DECODE(SIGN(ROWNUM-100000), 1, 'ST', 'KQ') -- 유가증권, 코스닥
, DECODE(MOD(ROWNUM, 8), 0, 'SS', 1, 'EF', 2, 'EW' -- 주식, ETF, ELW
, 3, 'DR', 4, 'SW', 5, 'RT' -- DR, 신주인수권, 리츠
, 6, 'BC', 7, 'MF') -- 수익증권, 투자회사
, ROUND(DBMS_RANDOM.VALUE(10, 1000), -1)
, ROUND(DBMS_RANDOM.VALUE(10000, 1000000), -2)
FROM DUAL
CONNECT BY LEVEL <= 500000
UNION ALL
SELECT '20090315'
, ROWNUM + 300000
,(CASE WHEN MOD(ROWNUM, 4) < 2 THEN 'SD' ELSE 'GD' END)
,(CASE WHEN MOD(ROWNUM, 4) IN (0, 2) THEN 'FU' ELSE 'OP' END)
, ROUND(DBMS_RANDOM.VALUE(10, 1000), -1)
, ROUND(DBMS_RANDOM.VALUE(10000, 1000000), -2)
FROM DUAL
CONNECT BY LEVEL <= 500000
;
- 업무에 따라 주식 상품을 다르게 분류하고 집계함
- 집계용 쿼리를 작성할 때마다 분류 기준을 적용하기 어려워 함수 정의
CREATE OR REPLACE FUNCTION SF_상품분류(시장코드 VARCHAR2, 증권그룹코드 VARCHAR2)
RETURN VARCHAR2
IS
L_분류 VARCHAR2(20);
BEGIN
IF 시장코드 IN ('ST', 'KQ') THEN -- 유가증권, 코스닥
IF 증권그룹코드 = 'SS' THEN
L_분류 := '주식 현물';
ELSIF 증권그룹코드 IN ('EF', 'EW') THEN -- ETF, ELW
L_분류 := '파생';
ELSE
L_분류 := '주식외 현물';
END IF;
ELSE
L_분류 := '파생';
END IF;
--SELECT 순서 || '. ' || L_분류 INTO L_분류
--FROM 분류순서
--WHERE 분류명 = L_분류;
RETURN L_분류;
END;
/
SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류
, COUNT(*) 체결건수
, SUM(체결수량) 체결수량
, SUM(체결금액) 체결금액
FROM 체결
WHERE 체결일자 = '20090315'
GROUP BY SF_상품분류(시장코드, 증권그룹코드)
ORDER BY 1 ;
*경 과: 00:00:07.31*
- CASE 문으로 변경
SELECT CASE
WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드 = 'SS' THEN '주식 현물'
WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드 NOT IN ('SS', 'EF', 'EW') THEN '주식외 현물'
WHEN 시장코드 IN ('SD', 'GD') OR 증권그룹코드 IN ('EF', 'EW') THEN '파생'
END 상품분류
, COUNT(*) 체결건수
, SUM(체결수량) 체결수량
, SUM(체결금액) 체결금액
FROM 체결
WHERE 체결일자 = '20090315'
GROUP BY
CASE
WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드 = 'SS' THEN '주식 현물'
WHEN 시장코드 IN ('ST', 'KQ') AND 증권그룹코드 NOT IN ('SS', 'EF', 'EW') THEN '주식외 현물'
WHEN 시장코드 IN ('SD', 'GD') OR 증권그룹코드 IN ('EF', 'EW') THEN '파생'
END
ORDER BY 1 ;
*경 과: 00:00:00.68*
- DECODE 문으로 변경
SELECT DECODE( 시장코드||증권그룹코드
, 'STSS', '주식 현물'
, 'KQSS', '주식 현물'
, 'SDFU', '파생'
, 'SDOP', '파생'
, 'GDFU', '파생'
, 'GDOP', '파생'
, 'STEF', '파생'
, 'STEW', '파생'
, 'KQEF', '파생'
, 'KQEW', '파생'
, '주식외 현물' ) 상품분류
, COUNT(*) 체결건수
, SUM(체결수량) 체결수량
, SUM(체결금액) 체결금액
FROM 체결
WHERE 체결일자 = '20090315'
GROUP BY
DECODE( 시장코드||증권그룹코드
, 'STSS', '주식 현물'
, 'KQSS', '주식 현물'
, 'SDFU', '파생'
, 'SDOP', '파생'
, 'GDFU', '파생'
, 'GDOP', '파생'
, 'STEF', '파생'
, 'STEW', '파생'
, 'KQEF', '파생'
, 'KQEW', '파생'
, '주식외 현물' )
ORDER BY 1 ;
*경 과: 00:00:00.87*
- 상품분류를 사용자가 원하는 순서대로 출력하도록 메타정보 관리를 위한 테이블 생성
CREATE TABLE 분류순서(분류명, 순서)
AS
SELECT '주식 현물', 1 FROM DUAL
UNION ALL
SELECT '주식외 현물', 2 FROM DUAL
UNION ALL
SELECT '파생', 3 FROM DUAL ;
- sf_상품분류 함수에 위 테이블을 조회해 출력순서를 위한 숫자값을 앞에 붙이도록 추가
CREATE OR REPLACE FUNCTION SF_상품분류(시장코드 IN VARCHAR2, 증권그룹코드 IN VARCHAR2)
RETURN VARCHAR2
IS
L_분류 VARCHAR2(14);
BEGIN
IF 시장코드 IN ('ST', 'KQ') THEN
IF 증권그룹코드 = 'SS' THEN
L_분류 := '주식 현물';
ELSIF 증권그룹코드 IN ('EF', 'EW') THEN
L_분류 := '파생';
ELSE
L_분류 := '주식외 현물';
END IF;
ELSE
L_분류 := '파생';
END IF;
SELECT 순서 || '. ' || L_분류 INTO L_분류
FROM 분류순서
WHERE 분류명 = L_분류;
RETURN L_분류;
END;
/
SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류
, COUNT(*) 체결건수
, SUM(체결수량) 체결수량
, SUM(체결금액) 체결금액
FROM 체결
WHERE 체결일자 = '20090315'
GROUP BY SF_상품분류(시장코드, 증권그룹코드)
ORDER BY 1 ;
*경 과: 00:00:45.42*
- 분류순서 테이블을 쿼리하는 Recursive Call이 100만번 수행으로 성능 저하
SELECT C.순서 || '.' || B.분류명 상품 분류,
SUM(체결건수) 체결건수,
SUM(체결수량) 체결수량
.....
FROM ( SELECT 시장코드, 증권그룹코드,
COUNT(*) 체결건수,
SUM(체결수량) 체결수량
....
FROM 체결
WHERE 체결일자 = '2090315'
GROUP BY 시장코드, 증권그룹코드 )A, 상품분류 B, 분류순서 C
...........
- 상품분류, 분류순서를 테이블로 관리하고, 조인으로 변경하여 성능 이슈 해결
3) 뷰 머지 방지를 통한 함수 호출 최소화
SELECT SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '1. 주식 현물', 체결수량)) "주식현물_체결수량"
, SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(SF_상품분류(시장코드, 증권그룹코드), '3. 파생', 체결수량)) "파생_체결수량"
FROM 체결
WHERE 체결일자 = '20090315' ;
*경 과: 00:02:13.51*
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
) ;
*경 과: 00:02:13.64*
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT /*+ NO_MERGE */ SF_상품분류(시장코드, 증권그룹코드) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
) ;
*경 과: 00:00:45.34*
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT ROWNUM, SF_상품분류(시장코드, 증권그룹코드) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
) ;
*경 과: 00:00:45.29*
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
AND ROWNUM > 0
) ;
4) 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화
1. 스칼라 서브쿼리를 사용하면 오라클은 그 수행횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시(Query Execution Cache)에 저장해 둔다.
2. 서브쿼리가 수행될 때마다 입력 값을 캐시에서 찾아보고 거기 있으면 저장된 출력 값을 리턴하고, 없으면 쿼리를 수행한 후 입력값과 출력값을 캐시에 저장해 두는 원리이다.
3. 함수를 Dual 테이블을 이용해 스칼라 서브쿼리로 한번 감싸는 것이다.
4. 함수 입력 값의 종류가 적을 때 이 기법을 활용하면 함수 호출횟수를 획기적으로 줄일 수 있다.
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT /*+ NO_MERGE */
(SELECT SF_상품분류(시장코드, 증권그룹코드) FROM DUAL) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
) ;
SELECT 순서||'.'||:B1
FROM
분류순서 WHERE 분류명 = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 725010 11.37 10.39 0 0 0 0
Fetch 725010 17.57 17.63 0 2175030 0 725010
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1450021 28.95 28.03 0 2175030 0 725010
1. 함수 호출 횟수를 20번으로 예상했지만 너무 많은 함수를 호출
2. 해시 충돌이 발생했기 때문
3. 해시 충돌이 발생하면 기존 엔트리를 밀어내고 새로 수행한 입력 값과 출력 값으로 대체할 것 같지만, 오라클은 기존 캐시 엔트리를 그대로 둔채 스칼라 서브쿼리만 한 번 더 수행한다.
4. 8i, 9i에서는 256개 엔트리를 캐싱
5. 10g에서는 입력과 출력 값 크기, _query_execution_cache_max_size 파라미터에 의해 캐시 사이즈가 결정된다(defult : 65536)
*ALTER SESSION SET "_query_execution_cache_max_size" = 2097152;*
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT /*+ NO_MERGE */
(SELECT SF_상품분류(시장코드, 증권그룹코드) FROM DUAL) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
) ;
*경 과: 00:00:00.79*
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 20 0.00 0.00 0 0 0 0
Fetch 20 0.00 0.00 6 60 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 41 0.00 0.00 6 60 0 20
5) Deterministic 함수의 캐싱 효과 활용
1. 10gR2에서 함수를 선언할 때 Deterministic 키워드를 넣어 주면 캐싱 효과가 나타난다.
2. 함수의 입력 값과 출력 값은 CGA(Call Global Area)에 캐싱된다.
3. CGA에 할당된 값은 데이터베이스 Call 내에서만 유효하므로 Fetch Call이 완료되면 그 값은 모두 해제된다.
4. Deterministic 함수의 캐싱 효과는 데이터베이스 Call 내에서만 유효하다.
5. 스칼라 서브쿼리에서의 입력, 출력 값은 UGA에 저장되므로 Fetch Call에 상관없이 그 효과가 캐싱되는 순간부터 끝까지 유지 된다.
create or replace function ACCUM (p_input number) return number
DETERMINISTIC
as
rValue number := 0 ;
call_cnt number := 0;
begin
dbms_application_info.read_client_info(call_cnt);
if call_cnt is null then
call_cnt := 0;
end if;
dbms_application_info.set_client_info(call_cnt + 1);
for i in 1..p_input loop
rValue := rValue + i ;
end loop;
return rValue ;
end;
/
select sum(accum_num)
from (
select accum(mod(rownum, 50)) accum_num
from dual
connect by level <= 1000000
) ;
*경 과: 00:00:01.54*
select sys_context('userenv', 'client_info') from dual;
SYS_CONTEXT('USERENV','CLIENT_INFO')
-----------------------------------------------------------------
50
- DETERMINISTIC 제거 후 재수행
exec dbms_application_info.set_client_info( NULL );
select sum(accum_num)
from (
select accum(mod(rownum, 50)) accum_num
from dual
connect by level <= 1000000
) ;
*경 과: 00:00:13.56*
select client_info
from v$session
where sid = sys_context('userenv', 'sid');
CLIENT_INFO
-------------------------------------------------------
1000000
6) 복잡한 함수 로직을 풀어 SQL로 구현