[Oracle] 파티셔닝(Partitioning) 성능 튜닝과 관리 팁

Oracle Partitioning을 적용하면 대용량 데이터에 대한 성능을 크게 향상시킬 수 있지만, 적용 후에도 지속적인 성능 튜닝과 관리가 필요합니다. 조금 전에 올린 포스트 이후로 이번 포스트에서는 Partitioning을 적용한 후 성능을 극대화하는 방법과 관리 시 주의할 점을 중심으로 설명하겠습니다.

 

1. Partition Pruning을 최대한 활용하기

Partition Pruning은 쿼리를 수행할 때 필요한 파티션만 스캔하여 성능을 극대화하는 기능입니다. 파티션이 나뉜 테이블에서 전체를 스캔하지 않고, 필요한 파티션만 조회하도록 쿼리 구조를 최적화하는 것이 중요합니다.

-- Partition Pruning이 일어나는 쿼리 예시
SELECT * FROM sales_data
WHERE sale_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
                   AND TO_DATE('2023-12-31', 'YYYY-MM-DD');

위 쿼리는 sale_date 기준으로 파티션이 나뉜 테이블에서 2023년의 데이터만 조회하므로, p2023 파티션만 스캔하게 됩니다. 이를 통해 쿼리 성능이 대폭 향상됩니다.

 

※ WHERE 절에 파티션 키(column)를 사용해야 Partition Pruning이 제대로 작동합니다. 파티션 키가 아닌 다른 조건을 사용할 경우 전체 파티션을 스캔하게 되어 성능 저하가 발생할 수 있습니다.

 

2. Global vs. Local Index의 선택

Partitioning을 적용한 테이블에서는 인덱스 관리도 매우 중요합니다. Oracle에서는 두 가지 인덱스 옵션을 제공합니다:

• Local Index: 각 파티션에 인덱스를 독립적으로 생성합니다.
• Global Index: 테이블 전체에 걸친 인덱스를 생성합니다.

Local Index는 파티션이 추가되거나 삭제될 때 유지 관리가 쉬운 장점이 있지만, Global Index는 특정 쿼리에서 더 나은 성능을 제공할 수 있습니다.

-- Local Index 생성 예시
CREATE INDEX idx_sales_data ON sales_data (sale_date, amount)
LOCAL;

-- Global Index 생성 예시
CREATE INDEX idx_sales_global ON sales_data (sale_date, amount);

 

※ 데이터 업데이트가 빈번한 경우에는 Local Index가 더 유리합니다. 왜냐하면 파티션 단위로 관리되어 특정 파티션에 대한 변경 작업이 일어날 때 전체 인덱스를 재구성할 필요가 없기 때문입니다.

 

※ 조회 쿼리 성능을 극대화하려면 상황에 따라 Global Index를 고려할 수 있습니다.

 

3. Parallel Query를 활용한 성능 최적화

대용량 데이터를 처리하는 경우 Parallel Query를 활용하면 쿼리 성능을 크게 향상시킬 수 있습니다. Parallel Query는 여러 프로세스를 동시에 실행하여 쿼리를 병렬 처리하는 방식입니다.

-- Parallel Query 적용 예시
SELECT /*+ PARALLEL(sales_data, 4) */ * 
FROM sales_data
WHERE sale_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') 
                   AND TO_DATE('2023-12-31', 'YYYY-MM-DD');

 

위 예시에서는 PARALLEL 힌트를 사용해 sales_data 테이블을 4개의 프로세스로 병렬 처리하게 했습니다. 이는 대용량 데이터에 대한 조회 시 특히 유용합니다.

 

※ Parallel Query는 테이블 크기나 시스템 성능에 따라 적절한 프로세스 수를 설정하는 것이 중요합니다. 너무 많은 프로세스를 할당하면 오히려 성능이 저하될 수 있습니다.

 

4. Partition 관리 - MERGE, SPLIT, DROP

Partitioning된 테이블에서 특정 데이터 범위가 변경되거나, 더 이상 필요하지 않은 데이터를 삭제하는 경우 파티션 관리 명령어를 통해 효율적으로 작업할 수 있습니다.

• MERGE: 여러 파티션을 하나로 병합할 때 사용합니다.
• SPLIT: 기존 파티션을 두 개로 나눌 때 사용합니다.
• DROP: 특정 파티션을 삭제하여 데이터를 삭제할 수 있습니다.

-- MERGE 파티션 예시
ALTER TABLE sales_data
MERGE PARTITIONS p2022, p2023 INTO PARTITION p2022_2023;

-- SPLIT 파티션 예시
ALTER TABLE sales_data
SPLIT PARTITION p2022 AT (TO_DATE('2022-06-30', 'YYYY-MM-DD'))
INTO (PARTITION p2022_h1, PARTITION p2022_h2);

-- DROP 파티션 예시
ALTER TABLE sales_data
DROP PARTITION p2022;

 

※ 파티션을 삭제(DROP)할 때는 해당 파티션 내 데이터가 완전히 제거되므로 신중해야 합니다. 이 작업은 주로 오래된 데이터를 아카이브하거나 삭제할 때 사용됩니다.

 

5. Statistics 수집과 업데이트

Partitioning된 테이블에서도 Oracle의 Optimizer가 최적의 실행 계획을 수립할 수 있도록 Statistics를 주기적으로 수집하고 업데이트하는 것이 중요합니다. 이 작업을 통해 쿼리 성능을 최적화할 수 있습니다.

-- Statistics 수집 예시
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'SALES_DATA', PARTNAME => 'P2023');
END;

Statistics를 최신 상태로 유지하면 Optimizer가 최신 데이터를 기반으로 효율적인 실행 계획을 수립할 수 있습니다.

 

※  데이터의 변화가 클 때는 수시로 Statistics를 수집해야 성능 저하를 방지할 수 있습니다.

 

실무에서 Partitioning의 이점을 최대한 활용해 성능을 극대화 하여 사용하시기 바래요!!!