OracleDB Call최소화 : ArraySize조절을 통한 Fetch Call 최소화

1. Call통계

① Parse Call : SQL 처리 루틴 생성 (캐싱 되어 있으면 그것을 사용)
② Execute Call : SQL 실행 요청
③ Fetch Call : 데이터 전송 요청
④ Misses in library cashe during parse : 하드파싱 횟수

2. User Call과 Recursive Call

▶ User Call

네트워크를 통해 외부에서 들어오는 Call을 말하며, DBMS입장에서 User Call은 WAS와 API서버를 말함.
동시 접속자 수가 적을 때는 잘 느끼지 못하지만, PeakTime에 시스템 장애를 발생시키는 주범
집합적 사고를 통해 Loop-Query를 해소하고 One-SQL로 구현해서 해소해야 함
Array Processing기능 활용

▶ Recursive Call

SQL파싱과 최적화 과정에서 발생하는 Data Dictionary 조회
Function, Procedure, Trigger내에서 수행되는 SQL
부분범위처리가 가능한 상황에서 제한적으로 사용하거나, 조인 또는 Scalar Subquery 형태로 변환해야 함

3. Database Call 최소화 방안 요약

4. 직접 눈으로 보자!! : 오라클 성능고도화 1권 p366참고하여 테스트 진행

▶ Array Processing 활용 테스트

→ 테스트 Source 테이블 생성

 drop table emp purge ; 
 
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 hiredate
from   all_objects  ,  (select rownum  rn from dual  connect by  level <= 15 )
where rownum <= 1000000 ;

→ Target 테이블 생성

 drop table emp2 purge ; 
create table emp2
as
select * from emp where 1 = 2 ;

→ 테스트 시작 : Arraysize 10으로 Insert 수행

 DECLARE
  l_fetch_size NUMBER DEFAULT 10;  -- 10건씩 Array 처리
 
  CURSOR c IS 
    SELECT empno, ename, job, sal, deptno, hiredate 
    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.hiredate%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;
END;
/

→ Arraysize 10의 Trace결과

 SELECT EMPNO, ENAME, JOB, SAL, DEPTNO, HIREDATE FROM EMP
 
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.003          0         61          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch    10001    0.031        0.187        570      10737          0     100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total    10003    0.031        0.190        570      10798          0     100000
 
 
INSERT INTO EMP2 VALUES ( :B1 , :B2 , :B3 , :B4 , :B5 , :B6 )
 
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute  10000    5.016        4.952          0       1824      18585     100000
Fetch        0    0.000        0.000          0          0          0          0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total    10001    5.016        4.952          0       1824      18585     100000

→ Arraysize 100으로 Insert 수행

 truncate table emp2 ; 
 
DECLARE
  l_fetch_size NUMBER DEFAULT 100;  -- 100건씩 Array 처리
 
  CURSOR c IS 
    SELECT empno, ename, job, sal, deptno, hiredate 
    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.hiredate%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;
END;
/

→ Arraysize 100의 Trace결과

 SELECT EMPNO, ENAME, JOB, SAL, DEPTNO, HIREDATE FROM EMP
 
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch     1001    0.047        0.061          0       1806          0     100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     1003    0.047        0.061          0       1806          0     100000
 
INSERT INTO EMP2 VALUES ( :B1 , :B2 , :B3 , :B4 , :B5 , :B6 )
 
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute   1000    0.547        0.595          2       1791       8553     100000
Fetch        0    0.000        0.000          0          0          0          0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     1001    0.547        0.595          2       1791       8553     100000

→ 위 Trace결과의 비교 분석

Array Processing 활용으로 Fetch Call이 감소 되었음
Array Processing의 효과를 극대화 하려면 연속된 일련의 처리과정이 모두 Array 단위로 진행되어야 함
예를들면, 최적의 Array단위로 앞서 Fetch를 하더라도, Insert단계에서 건건히 처리된다면 그 효과는 크게 반감됨
그렇다면 Array단위는 항상 크게 하는것이 좋을까?

→ ArraySize별 Fetch / IO갯수 확인

 set autotrace traceonly statistics;
set timing on 
 
set arraysize 2
select * from emp ; 
 
set arraysize 5
select * from emp ; 
 
set arraysize 10
select * from emp ; 
 
set arraysize 15
select * from emp ; 
 
set arraysize 100
select * from emp ; 
 
set arraysize 5000
select * from emp ;

→ ArraySize 별 Fetch Count와 Block IO갯수 변화량

대량의 데이터를 Fetch할 때 ArraySize를 크게 설정하면 Fetch Call과 Block IO도 감소
Fetch사이즈가 작으면 한번의 Fetch 완료 후 다시 데이터를 읽을 때 이 전에 읽었던 블록에 아직 읽어야 할 데이터가 있을 경우 동일 블록을 반복해서 읽기 때문
ArraySize와 Block IO는 반비례하지만, 일정량이 넘어가면 더이상 감소하지 않음
일정수치가 넘어갈 경우 IO감소는 되지 않은 채 한번에 처리해야하는 양이 많아져 메모리부하 발생

저작자표시

'DB' 카테고리의 다른 글

OracleDB IOT로 MariaDB/MySQL 인덱스 이해하기 (0)	2022.06.19
Oracle Index Skip Scan의 효용성 (0)	2022.06.12

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

minyDB

OracleDB Call최소화 : ArraySize조절을 통한 Fetch Call 최소화

'DB' 카테고리의 다른 글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

	drop table emp purge ;

	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 hiredate
	from all_objects , (select rownum rn from dual connect by level <= 15 )
	where rownum <= 1000000 ;

	drop table emp2 purge ;
	create table emp2
	as
	select * from emp where 1 = 2 ;

	DECLARE
	l_fetch_size NUMBER DEFAULT 10; -- 10건씩 Array 처리

	CURSOR c IS
	SELECT empno, ename, job, sal, deptno, hiredate
	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.hiredate%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;
	END;
	/

	SELECT EMPNO, ENAME, JOB, SAL, DEPTNO, HIREDATE FROM EMP

	Call Count CPU Time Elapsed Time Disk Query Current Rows
	------- ------ -------- ------------ ---------- ---------- ---------- ----------
	Parse 1 0.000 0.003 0 61 0 0
	Execute 1 0.000 0.000 0 0 0 0
	Fetch 10001 0.031 0.187 570 10737 0 100000
	------- ------ -------- ------------ ---------- ---------- ---------- ----------
	Total 10003 0.031 0.190 570 10798 0 100000


	INSERT INTO EMP2 VALUES ( :B1 , :B2 , :B3 , :B4 , :B5 , :B6 )

	Call Count CPU Time Elapsed Time Disk Query Current Rows
	------- ------ -------- ------------ ---------- ---------- ---------- ----------
	Parse 1 0.000 0.000 0 0 0 0
	Execute 10000 5.016 4.952 0 1824 18585 100000
	Fetch 0 0.000 0.000 0 0 0 0
	------- ------ -------- ------------ ---------- ---------- ---------- ----------
	Total 10001 5.016 4.952 0 1824 18585 100000

	truncate table emp2 ;

	DECLARE
	l_fetch_size NUMBER DEFAULT 100; -- 100건씩 Array 처리

	CURSOR c IS
	SELECT empno, ename, job, sal, deptno, hiredate
	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.hiredate%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;
	END;
	/

	set autotrace traceonly statistics;
	set timing on

	set arraysize 2
	select * from emp ;

	set arraysize 5
	select * from emp ;

	set arraysize 10
	select * from emp ;

	set arraysize 15
	select * from emp ;

	set arraysize 100
	select * from emp ;

	set arraysize 5000
	select * from emp ;

OracleDB Call최소화 : ArraySize조절을 통한 Fetch Call 최소화

'DB' 카테고리의 다른 글

'DB' Related Articles

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역