대량의 데이터를 변경해야 하는 작업은 그 자체만으로도 부담으로 다가온다. 대량의 데이터를 변경해야 하는 작업의 특성상 SQL tuning만으로 성능을 개선할 여지는 많지 않을 뿐더러 개선한다고 해도 극적인 효과를 기대하기는 어렵다. 필연적으로 발생하는 redo, undo 데이터로 인한 추가적인 부하는 더욱 부담으로 느껴진다.
대량의 DELETE
월 단위 혹은 특정 기간 단위의 데이터를 주기적으로 삭제하는 업무는 어디든 항상 존재한다. 대량의 delete 작업은 table full scan이나 index scan 등과 같은 해당 테이블을 access 하는 방법에 의한 비효율보다는 필연적으로 발생할 수 밖에 없는 redo, undo 데이터 생성과 더불어 해당 테이블에 인덱스가 존재한다면 인덱스의 개수에 따라 추가적인 overhead가 발생하는 것이 성능 저하의 주된 이유이다.
인덱스에서 발생하는 추가적인 overhead란, 삭제 대상이 되는 데이터를 인덱스에서도 삭제해야하는 것과 동시에, 이 과정 역시 redo, undo 데이터가 생성됨을 의미한다.
개선 방법
- partition table 구성
주기적으로 삭제해야 하는 기간이 정해져 있다면, 해당 칼럼을 key로 하는 range partition 을 구성하고, 삭제 대상이 되는 기간에 해당하는 partition table을
ALTER TABLE partition_test DROP PARTITION partition_test1; 명령어를 통해 DML이 아닌 DDL로 작업을 대체할 수 있다.
DDL로의 delete 작업의 대체는, redo 데이터가 생성되지 않고 단순히 해당 partition을 drop 하는 작업만 수행하므로 속도의 차이는 비교할 수 없을 만큼 크고, 많은 양의 redo / undo 데이터가 발생되지 않음으로 down time 최소화에도 큰 기여를 할 수 있다.
대량의 INSERT
Insert는 delete 작업에 비해 성능 개선 여지가 많은 편이다. 데이터 입력 대상 테이블의 속성변경(nologging)과 힌트 (/+append/) 만으로 direct path insert를 유도하며 redo 발생을 억제하는 방법이 있기 때문이다.
- Insert 쿼리에
/*+append*/힌트를 추가 - 테이블에
nologging추가(alter table xxx nologging; => 리두 로그에 쌓이지 않음
위의 두 경우에는 /*+append*/ 가 의미가 없는 듯 하다.
- NOARCHIVELOG 모드 데이터베이스가 사용되고 있는 경우
- NOLOGGING으로 표시된 테이블을 대상으로 작업하고 있는 경우
대량의 INSERT의 제일 큰 문제점이 있다면 그건 인덱스의 존재다.
대량의 insert 작업 시 데이터가 테이블에 엽력되는 작업보다도 인덱스의 개수와 인덱스의 성격에 따라 성능이 좌우된다고 해도 과언이 아니다.
대량의 데이터를 입력할 때, 해당 테이블의 인덱스가 많으면 성능 저하 및 속도가 느려지는 것을 알 수 있다.
array processing
대량의 DML 작업은 데이터를 load 하거나 update 할 때 일반적으로 LOOP 문을 사용하여 건건이 처리하는 경우가 많다. 이 방법은 매 LOOP 마다 1회씩 DML 작업이 수행되어 그만큼 DBMS CALL이 발생하기 때문에 성능상 불리하다.
하지만 array processing을 이용한 BULK SQL을 사용하면 LOOP 없이 단 한번의 SQL 수행만으로 처리가 가능하다. 즉, 대량의 DML을 단 한번에 처리할 수 있으므로 DBMS CALL을 감소시켜 loop 로 처리되던 방법에 비해 큰 성능개선 효과를 볼 수 있다.
