갱신/삽입/삭제의 SQL를 고속화하는 3개의 기술?

출처 : KACHISORI

 

전회에서는 조건(WHERE 조건)의 유무에 의한 결합 처리나, MView등 Oracle의 기능을 이용한 결합 처리의 튜닝 수법을 설명했습니다. 이번은, WHERE 조건이 없는 DML(INSERT, DELETE, UPDATE) 처리를 고속화하는 튜닝 수법에 대해 설명합니다.

 

■ MERGE문의 이용

MERGE문은 Oracle9i부터 이용할 수 있는 SQL로, 데이터가 존재하는 경우에는 기존 데이터를 갱신(UPDATE)하고, 데이터가 존재하지 않는 경우에는 데이터를 삽입(INSERT)하는 것을 1개의 SQL로 실행할 수 있습니다. 그러므로 지금까지는 어플리케이션으로 분기 처리를 하거나 PL/SQL등에서 반복해 실행하거나 하고 있던 처리가 MERGE문을 1회 실행으로 가능하게 되었습니다.

 

그림 1　PL/SQL와 MERGE문으로의 처리 플로우의 비교

 

그러면 실제로 PL/SQL 처리와 MERGE문 처리를 실행하여 처리 시간, 실행 통계를 비교해 봅시다."ORDERS_TEMP" 테이블에는 10만건, "ORDERS" 테이블에는 5만건의 데이터가 격납되고 있고 "ORDERS_TEMP"테이블의 데이터를 기본으로 「ORDERS」테이블에 대해서 UPDATE, INSERT 처리를 실행합니다.

 

PL/SQL내에서 실행하고 있는 각 SQL의 실행 통계는, 개별적으로 출력됩니다.

그림 2　PL/SQL 처리를 사용했을 경우의 실행 통계

그림 3　MERGE문을 사용했을 경우의 실행 통계

 

그림 2를 보면 PL/SQL의 경우는 각 SQL가 다른 실행 통계로서 출력되므로 실제 처리 시간(elapsed)은 각 실행 통계의 처리 시간을 합계한 「44.56초」걸리는 것을 확인할 수 있습니다. 한편, MERGE문을 사용했을 경우는 「17.92초」이며, PL/SQL의 반이하의 처리 시간이 되고 있습니다.

 

또 그림 2로 그림 3의 액세스 블록수에 큰 차이를 확인할 수 있습니다. 이것은 PL/SQL에서는 몇번이나 같은 테이블에 액세스 하고 있습니다만, MERGE문에서는 1회만의 액세스로 끝나기 때문에 그 결과 MERGE문에서는 대폭적인 퍼포먼스 향상으로 연결되어 있습니다.

 

주의해야 할 점으로서 MERGE문에서는 1개의 SQL로 실행하므로 트랜잭션(transaction)를 나눌 수 없습니다만, PL/SQL에서는 트랜잭션(transaction)를 나누거나 에러 처리를 하는 것이 가능합니다.

예를 들면 열정의보다 데이터 길이의 큰 데이터에 UPDATE 했을 때에 PL/SQL에서는 에러 처리나 사전의 체크 논리를 더하는 것으로 에러 발생 개소의 특정등을 실시하는 것이 가능합니다.그러므로 실행시의 갱신 퍼포먼스와 에러 발생시의 리커버리 처리의 양쪽 모두를 검토, 테스트하고 나서 이용하도록 해야 합니다.

 

■ 다이렉트 로드 인서트의 이용

 

다른 테이블로 부터 대량 데이터를 INSERT 하는 경우에 APPEND 힌트를 이용하면 통상의 INSERT 처리가 아닌 다이렉트 로드 인서트 처리를 실행할 수 있습니다. 이 다이렉트 로드 인서트 처리에서는 버퍼·캐쉬를 경유하지 않고 데이터를 INSERT 하므로 최저한의 REDO 정보만이 생성되 통상의 INSERT 처리보다 크게 퍼포먼스를 개선할 수 있을 가능성이 있습니다.

 

그림 4　다이렉트 로드 인서트의 동작

 

그렇다면 실제로 통상의 INSERT 처리와 다이렉트 로드 인서트 처리를 실행해, 실행 통계, 실행 계획을 비교해 봅시다. 약 90만건의 데이터를 INSERT 하고 있습니다.

 

그림 5　통상 INSERT 처리의 경우의 실행 통계

그림 6　다이렉트 로드 인서트 처리의 경우의 실행 통계

 

또한 기존의 테이블로부터 새롭게 테이블을 작성할 경우에 이용하는 CREATE TABLE AS SELECT 처리에서도 최저한의 REDO 정보 밖에 생성되지 않습니다.그림 7은, 같은 조건으로 새롭게 테이블을 작성했을 경우의 처리 시간, REDO 정보의 생성량이 됩니다.

그림 7　CREATE TABLE AS SELECT 처리로의 처리 시간과 REDO 정보의 생성량

 

그림 5, 그림 6, 그림 7을 비교하면 통상의 INSERT 처리보다 다이렉트 로드 인서트 처리, CREATE TABLE AS SELECT 처리가 처리 시간, REDO 정보의 생성량 모두 큰폭으로 작아지고 있는 것을 확인할 수 있습니다(다이렉트 로드 인서트 처리, CREATE TABLE AS SELECT 처리로 생성된 REDO 정보는, 데이터 딕쇼내리에 대한 갱신분만됩니다).CREATE TABLE AS SELECT 처리에 의한 REDO 정보의 생성량은, AUTOTRACE 기능에서 실행 통계의 취득을 할 수 없기 때문에, 그림 7과 같이 조사할 필요가 있습니다. 또 데이타베이스가 아카이브(archive) 로그 모드로 설정되어 있는 경우는, INSERT처의 테이블의 속성이 「NOLOGGING」로 설정되어 있지 않으면 REDO 정보가 대량으로 생성되어 버리기 때문에 주의가 필요합니다.

 

이러한 결과로 비추어 기존테이블의 데이터를 추가하는 경우에는 다이렉트 로드 인서트 처리의 이용을 검토하여 신규 테이블을 작성하는 경우에는 CREATE TABLE AS SELECT 처리를 이용하는 등 처리에 따라서 사용구분을 실시하는 것이 중요합니다.

 

덧붙여 다이렉트 로드 인서트 처리에서는 이하와 같은 동작이 되기 되므로 미리 주의가 필요합니다.

주의점	내용
하이·워터·마크(이하, HWM) 이후의 블록에 INSERT 된다	통상의 INSERT 처리라면 빈블록에 데이터를 INSERT 하는 것이 가능합니다만, 다이렉트 로드 인서트 처리에서는 HWM 이후의 블록에 데이터가 INSERT 됩니다.그러므로 통상의 INSERT 처리보다 영역의 사용 효율이 저하해 버립니다
테이블 단위의 락이 획득된다	통상의 INSERT 처리에서는 레코드 단위로 락이 획득됩니다만, 다이렉트 로드 인서트 처리에서는 테이블 단위로 락이 획득됩니다.그 때문에 다이렉트 로드 인서트 처리의 실행중에는 다른 세션에 의한 동일 테이블 갱신 처리는 대기가 발생 하게 됩니다. 파티션테이블의 1 파티션에의 다이렉트 로드 인서트 처리에서도 같으므로 주의가 필요합니다
다이렉트 로드 인서트 처리 후, 트랜잭션을 완료할 필요가 있음	다이렉트 로드 인서트 처리의 실행 직후는 반드시 트랜잭션(transaction)를 완료시킬(COMMIT 혹은 ROLLBACK) 필요가 있습니다. 만약 계속해서 같은 테이블에 SELECT를 실행하면, 「ORA-12838: 오브젝트는, 패러렐로 변경된 다음은 독포함/변경할 수 없습니다.」라고 하는 에러가 발생해 버립니다
표 1　다이렉트 로드 인서트 처리로의 주의점

그림 8　다이렉트 로드 인서트 처리로의 주의해야 할 동작

 

■ 패러렐 DML를 이용한다

 

지금까지 설명한 검색 처리의 튜닝·테크닉에서는 패러렐 쿼리 기능을 이용하여 방대한 레코드수를 가지는 테이블의 검색 처리를 고속화할 수 있었습니다. DML 처리에 대해서도 패러렐 DML 기능을 사용하여 방대한 레코드의 삽입, 갱신, 삭제 처리를 고속화할 수 있습니다.

 

Oracle9i R2이전에는 파티션테이블 에서만 패러렐 DML를 실행할 수 있었습니다만, Oracle9i R2에서는 1 파티션내나 비파티션 테이블 (통상의 테이블)에 대해도 패러렐 DML를 실행할 수 있게 되었습니다.

 

DML를 패러렐로 처리하므로 패러렐 쿼리와 같이 복수 CPU가 탑재되어 CPU 사용율 등 system resource에 여유가 있는 환경에서 이용한다면 효과를 기대할 수 있습니다.

 

그림 9　파티션내 및 통상표로의 패러렐 DML의 동작

 

패러렐 DML를 이용하려면  이하의 순서로 설정이 필요합니다.

1．초기화 파라미터 「PARALLEL_MAX_SERVERS」에 적절한 값을 설정한다

 

2．패러렐 DML를 실행하는 세션으로 패러렐 DML의 이용을 가능하게 한다

SQL> alter session enable parallel dml;

 

3．패러렐 DML의 SQL를 실행한다

이하의 SQL에서는 PARALLEL 힌트를 지정해 있습니다만, 대상 테이블에 패러렐도가 설정되어 있는 경우에는 힌트문의 지정은 필요 없습니다.

SQL> DELETE /*+ PARALLEL(lineitem,4) */ FROM lineitem;

테이블의 패러렐도는 이하와 같이 확인할 수 있습니다.

SQL> SELECT table_name,degree   2  FROM user_tables   3  WHERE table_name='LINEITEM'; TABLE_NAME                     DEGREE ------------------------------ -------------------- LINEITEM                                1

DEGREE가 「1」의 경우, 패러렐도가 설정되어 있지 않는 것임

 

4．패러렐 DML의 실행을 확인다음의 동적 퍼포먼스·뷰를 참조하는 것으로, 패러렐 DML가 실행되었는지를 확인할 수 있습니다

SQL> SELECT * FROM v$pq_sesstat;

 

 

그러면 실제로 통상의 DELETE 처리와 패러렐 DML에 의한 DELETE 처리를 실행하여 실행 통계와 실행 계획을 비교해 봅시다.

 

그림 10　통상의 DELETE 처리의 경우의 실행 통계와 실행 계획

그림 11　패러렐 DML에 의한 DELETE 처리의 경우의 실행 통계와 실행 계획

 

그림 10, 그림 11을 보듯 패러렐로 처리되는 패러렐 DML의 처리 시간이 짧아지고 있는 것을 확인할 수 있습니다. 상기예에서는 처리 시간에 그만큼 큰 차이는 볼 수 없습니다만,  검증 환경의 system resource에 의존하는 부분이 크기 때문에 각자의 환경에서 시험해 보면 좋을 것입니다.

 

INSERT SELECT 처리를 실시하는 경우에는 INSERT 처리는 패러렐 DML로 실행하고 SELECT 처리에도 패러렐 쿼리를 조합한다면 각각의 처리가 패러렐화 되어 대폭적인 퍼포먼스 향상을 기대할 수 있습니다.

 

◇

 

이번회까지 다양한 SQL 튜닝 방법을 설명했습니다만 최종회가 되는 다음 번으로는 SQL 튜닝을 실시하는데 필요 최저의 Oracle 튜닝 항목에 대해 설명합니다