SQL 튜닝이론 - 데이터베이스 CALL/ ARRAY PROCESSING/ 대량 DML/ 수정가능 조인뷰

SQL 튜닝이론 - 데이터베이스 CALL/ ARRAY PROCESSING/ 대량 DML/ 수정가능 조인뷰

 

데이터베이스 CALL

 

SQL의 실행은 세 단계로 이루어집니다

 

- PARSE CALL : SQL 파싱과 최적화를 수행하는 단계입니다. 캐싱된 SQL과 실행계획을 라이브러리 캐시에서 찾으면 최적화 단계는 생략합니다.

 

- EXECUTE CALL : 새로운 SQL 혹은 캐싱된 SQL을 실행하는 단계입니다. DML 은 이 단계가 끝이지만 SELECT 문은 FETCH 단계를 거칩니다.

 

- FETCH CALL : 데이터를 읽어 사용자에게 결과집합을 전송하는 과정입니다. SELECT 문만 이 과정을 거치고 전송할 데이터가 많은 경우 FETCH CALL이 여러 번 발생합니다.

 

 

CALL은 어디서 발생하느냐에 따라 USER CALL과 RECURSIVE CALL로 나뉩니다.

 

USER CALL은 네트워크를 경유해 DBMS 외부에서 발생한 요청에 의한 CALL입니다. 클라이언트(사용자)의 요청은 WAS 혹은 AP 서버를 거쳐 DB서버에 도착합니다. 즉 3-TIER(PRESENTAION - BUSINESS- DATA)에서 USER CALL 은 WAS 서버에서 발생하는 CALL입니다.

 

RECURSIVE CALL 은 DBMS 내부에서 발생하는 CALL입니다. SQL 파싱과 최적화 과정에서 발생하는 데이터 딕셔너리 조회, 사용자함수/프로시저/트리거에 내장된 SQL을 실행할 때 발생하는 CALL입니다.

 

CALL의 종류와 무관하게 SQL을 실행할 때마다 PARSE, EXCUTE, FETCH 단계를 거칩니다. 데이터베이스 CALL 이 많으면 성능이 저하될 수밖에 없습니다. 특히, 네트워크를 경유하는 USER CALL은 성능에 큰 영향을 줍니다.

ONE SQL의 중요성

 

ONE SQL은 한 번의 CALL로 처리되도록 작성된 SQL을 말합니다.

 

insert into target
select * from source;

 

업무로직이 복잡하다면 절차적(Java, PL/SQL)으로 처리할 수 박에 없지만, 그렇지 않다면 가급적 ONE SQL로 구현하도록 노력해야 합니다.

 

ARRAY PROCESSING 활용

 

절차적 프로그램을 ARRAY PROCESSING으로 처리하면 CALL 부하를 획기적으로 줄일 수 있습니다.

 

declare
    cursor c is select * from source;
    type typ_source is table of c%rowtype;
    l_source type_source;

    l_aaray_szie number default 10000;

    procedure insert_target(p_source in typ_source) is
    begin
        forall i in p_source.first..p_source.last
            insert into target values p_source(i);
        end insert_target;

    begin
        open c;
        loop
            fetch c bulk collect into l_source limit l_array_size;

            insert_target(l_source);
            exit when c%notfound;
        end loop;

        close c;

        commit;
     end;
     /

 

인덱스 및 제약 해제를 통한 대량 DML 튜닝

 

인덱스와 무결성 제약 조건은 DML 성능에 큰 영향을 줍니다. 온라인 트랜잭션 처리 시스템에서 이들 기능을 해제할 순 없습니다. 반면, 동시 트랜잭션 없이 대량 데이터를 적재하는 BATCH 프로그램에서는 이들 기능을 해제함으로써 큰 성능개선 효과를 얻을 수 있습니다.

 

CREATE TABLE SOURCE
AS
SELECT B.NO, A.*
FROM (
    SELECT * FROM EMP WHERE ROWNUM <= 10) 
)A,
(    
    SELECT ROWNUM AS NO FROM DUAL CONNECT BY LEVEL <= 100000)B;
)

CREATE TABLE TARGET
AS
SELECT * FROM SOURCE WHERE 1 = 2;

ALTER TABLE TARGET ADD
CONSTRAINT TARGET_PK PRIMARY KEY(NO,EMPNO);

CREATE INDEX TARGET_x1 ON TARGET(ENAME);

 

PK 제약을 생성하면 UNIQUE 인덱스가 자동으로 생성됩니다. TARGET 테이블에는 이제 2개의 인덱스가 존재합니다. 이때 대량으로 데이터를 추가하면 인덱스가 없을 때의 테이블보다 훨씬 많은 시간이 소요됩니다.

 

TRUNCATE TABLE TARGET;

-- PK 제약을 해제하고 인덱스를 DROP 합니다
ALTER TABLE TARGET MODIFY CONSTRAINT TARGET_PK DISABLE DROP INDEX;

-- 인덱스를 UNUSABLE 상태로 변경합니다.
ALTER INDEX TARGET_X1 UNUSABLE;

-- 인덱스가 UNUSABLE 상태에서 데이터를 입력하려면 아래 파라미터를 TRUE로 설정해야합니다.
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;

 

무결성 제약과 인덱스를 해제함으로써 빠른 INSERT 가 가능합니다. 작업이 끝나면 다시 PK제약을 활성화하고 인덱스를 REBUILD 합니다.

 

alter table target modify constraint target_pk enable NOVALIDATE;

alter index target_x1 rebuild;