Oracle DROP TABLE (Partition Table) Performance Issue

---

Oracle DROP TABLE (Partition Table) Performance Issue

대량의 파티션(저자는 약 10,000 개 이상) 이 존재하는 테이블을 DROP 하는 경우 속도 이슈가 발생할 수 있다. 저자는 DROP TABLE명 → PURGE DBA_RECYCLEBIN 프로세스로 작업을 한 경험이 있었는데 19c EXADATA 장비에서 하루 이상 시간이 소요되었다.

 

---

 

대량의 파티션 테이블 삭제 시 속도가 지연되는 이유

 

 11g 이후 버전부터 1024개 이상의 파티션을 가진 파티션 테이블 PURGE 시 multi-commit (1000개 파티션을 단위로 PURGE 되는 시점마다 한번 COMMIT 을 수행) 을 해주는 원리가 도입 (이는 과거 하나의 Long DDL 트랜젝션이 실패 또는 작업 취소를 대비하여 나온 기능) 되었고 이 기능은 _drop_table_optimization_enabled 파라미터가 true 일 때 활성화 된다. 디자인 상 RECYCLEBIN 영역에서 PURGE TABLE 테이블명 또는 PURGE DBA_RECYCLEBIN 으로 수행 시 multi-commit 기능을 사용할 수 없으며,
아래의 예제와 같은 RECYCLEBIN 영역에서 DROP TABLE "schema_owner"."BIN$......" PURGE 방식 또는 DROP TABLE schema_owner.table_name PURGE 로 삭제해야 multi-commit 기능을 사용할 수 있다.

 

• DROP table_name PURGE : 대량의 파티션을 삭제하여 multi-commit 기능을 사용해도 삭제 시간이 오래 걸릴 수 있다.
• DROP table_name : RECYCLEBIN 에 DROP 한 대량의 파티션 테이블이 존재하여 딕셔너리 테이블 조회 시 성능 이슈가 발생할 수 있다. 또한 RECYCLEBIN 영역에서 DROP TABLE "schema_owner"."BIN$......" PURGE 방식으로 삭제하여 multi-commit 기능을 사용해도 삭제 시간이 오래 걸릴 수 있다.

 

위의 내용을 참고하여 multi-commit 기능을 사용한 파티션 테이블에 대한 DROP 진행 시 속도 이슈가 발생하지 않는다면 위의 방식으로 처리하며, 만약 동일하게 속도가 지연된다고 하면 아래의 방식으로 삭제할 것을 추천한다.

 

대량의 파티션 테이블 삭제 시 속도 이슈 해결 방법

해당 테이블 중 하나의 파티션을 제외하고 모든 파티션을 수동으로 삭제 (서브파티션 작업 동일) → 한 개의 파티션이 남은 테이블 DROP 하여 RECYCLEBIN 영역으로 이동 → DROP TABLE "schema_owner"."BIN$....." PURGE 실행하여 남은 파티션까지 모두 삭제한다.

-- Target Schema Owner : ORA
-- Target Table Name : TEST

-- 저자는 DROP PARTITION 에서 제일 마지막 파티션을 삭제에서 제외
SELECT 'ALTER TABLE '||table_owner||'.'||table_name||' DROP PARTITION '||partition_name||';'
  FROM DBA_TAB_PARTITIONS
 WHERE TABLE_OWNER = 'ORA'
   AND TABLE_NAME = 'TEST'
   AND ROWNUM < (SELECT COUNT(*)
                   FROM DBA_TAB_PARTITIONS
                  WHERE TABLE_OWNER = 'ORA'
                    AND TABLE_NAME = 'TEST');

-- 위의 결과 스크립트를 실행
ALTER TABLE ORA.TEST DROP PARTITION PL_19850101;
ALTER TABLE ORA.TEST DROP PARTITION PL_19850102;
ALTER TABLE ORA.TEST DROP PARTITION PL_19850103;
ALTER TABLE ORA.TEST DROP PARTITION PL_19850104;
ALTER TABLE ORA.TEST DROP PARTITION PL_19850105;
ALTER TABLE ORA.TEST DROP PARTITION PL_19850106;
...............
ALTER TABLE ORA.TEST DROP PARTITION PL_20221230;

-- 파티션이 PL_19850101 ~ PL_20221231 까지 있다고 했을 때 PL_20221231 파티션이 1개 남아있다.

-- 테이블 DROP (RECYCLEBIN 영역으로 이동)
DROP TABLE ORA.TEST;

-- RECYCLEBIN 에서 해당 테이블 DROP (Double Quotation 사용함으로 대소분자 구분)
ALTER SESSION SET RECYCLEBIN = OFF; --해당 옵션을 꼭 OFF 해야한다.
DROP TABLE "ORA"."BIN$.....로 시작하는 TEST 테이블" PURGE;
ALTER SESSION SET RECYCLEBIN = ON;