Oracle SQL 성능을 고려한 설계 방법 10가지
인덱스(Index) 활용
- 인덱스 설계
- 특정 컬럼을 자주 조회하거나 조인(JOIN)에 사용하는 경우 인덱스를 생성하면 성능이 크게 향상됩니다.
- 인덱스 선택
- 대부분의 경우 B-tree 인덱스가 효과적입니다. 특정 컬럼의 값 분포가 낮거나 범위 검색이 빈번한 경우 비트맵(Bitmap) 인덱스를 고려할 수 있습니다.
- 인덱스 관리
- 사용하지 않는 인덱스를 주기적으로 정리하고, 인덱스가 깨지지 않거나 비효율적이지 않도록 관리해야 합니다.
SQL 플랜정보 분석 및 옵티마이저(Optimizer) 이해
- SQL문 자체를 최적화하고, 데이터베이스가 최적의 실행 계획을 선택하도록 돕는 과정.
- 실행 계획(Execution Plan) 분석
- **`EXPLAIN PLAN`** 또는 **`DBMS_XPLAN`**을 사용하여 SQL문의 실행 계획을 분석하고, 성능 저하의 원인을 파악합니다.
- 옵티마이저 힌트(Hints) 활용
- 옵티마이저가 잘못된 실행 계획을 선택하는 경우, 힌트를 사용하여 올바른 실행 계획을 유도할 수 있습니다. 예: `/*+ USE_NL(a b) */`
- 통계 정보(Statistics) 관리
- 옵티마이저가 최적의 실행 계획을 수립하도록 주기적으로 통계 정보를 수집하고 갱신해야 합니다.
조인(JOIN) 최적화
- 여러 테이블을 결합하는 조인 작업의 효율을 높여 성능을 개선합니다.
- 조인 순서(Join Order)
- 대용량 테이블을 먼저 조인하고, 소량의 테이블을 나중에 조인하는 것이 일반적으로 효율적입니다.
- 조인 방식(Join Method)
- Nested Loop Join, Hash Join, Sort-Merge Join 등 다양한 조인 방식이 존재하며, 데이터의 양과 조인 조건에 따라 적절한 방식을 선택해야 합니다.
데이터 모델링(Data Modeling)
- 데이터베이스 구조 자체를 효율적으로 설계하여 성능을 향상시킵니다.
- 정규화(Normalization): 데이터 중복을 최소화하여 데이터 무결성을 높입니다.
- 그러나 과도한 정규화는 조인을 증가시켜 성능을 저하시킬 수 있습니다.
- 비정규화(Denormalization): 조회 성능 향상을 위해 의도적으로 중복 데이터를 허용합니다.
- 자주 조회하는 데이터를 미리 계산하여 컬럼에 저장하는 등의 방법이 있습니다.
파티셔닝(Partitioning)
- 대용량 테이블을 작은 논리적 단위로 분할하여 관리하는 기법입니다.
- 파티셔닝 전략
- 범위(Range) 파티셔닝, 리스트(List) 파티셔닝, 해시(Hash) 파티셔닝 등 다양한 전략을 데이터 특성에 맞게 적용합니다.
- 파티셔닝 효과
- 특정 파티션만 조회하여 I/O 부하를 줄이고, 대량의 데이터 삭제 및 로드 작업을 빠르게 수행할 수 있습니다.
SQL 작성 방법 개선
- SQL문을 작성하는 방식을 최적화하여 성능을 높입니다.
- `WHERE` 절 최적화
- `WHERE` 절에 인덱스를 사용하지 못하게 하는 함수(예: `UPPER()`, `SUBSTR()`) 사용을 지양합니다.
- `SELECT` 절 최적화
- `SELECT *` 대신 필요한 컬럼만 명시하여 네트워크 전송량과 메모리 사용량을 줄입니다.
캐시(Cache) 및 메모리(Memory) 관리
- 데이터베이스의 메모리 자원을 효율적으로 사용하여 디스크 I/O를 최소화합니다.
- SGA(System Global Area) 및 PGA(Program Global Area) 설정
- SGA는 공유 메모리, PGA는 세션 전용 메모리입니다.
- 이들의 크기를 적절하게 설정하여 성능을 최적화해야 합니다.
- Shared Pool 및 Buffer Cache
- Shared Pool은 SQL 실행 계획을, Buffer Cache는 데이터 블록을 캐싱하여 성능을 향상시킵니다.
트랜잭션(Transaction) 관리
- 데이터베이스의 일관성과 무결성을 보장하면서 성능 저하를 최소화합니다.
- 짧은 트랜잭션
- 긴 트랜잭션은 락(Lock)을 유발하여 다른 세션의 작업을 방해할 수 있으므로, 가능한 짧게 유지합니다.
- 락(Lock) 관리
- 락이 걸리지 않도록 SQL을 작성하고, `COMMIT` 또는 `ROLLBACK`을 명시적으로 사용해야 합니다.
배치(Batch) 처리 최적화
- 대량의 데이터를 처리하는 배치 작업의 성능을 향상시킵니다.
- 배치 작업
- 대용량 데이터 처리 시, 커밋(Commit) 단위를 정하여 일정 단위로 커밋하는 것이 좋습니다.
- 벌크(Bulk) 처리
- `FORALL` 또는 `BULK COLLECT`를 사용하여 한 번의 네트워크 통신으로 여러 건의 데이터를 처리하여 성능을 향상시킬 수 있습니다.
하드웨어 및 시스템 환경 최적화
- 데이터베이스가 구동되는 물리적 환경을 최적화하여 성능을 높입니다.
- 하드웨어
- 충분한 CPU, 메모리, SSD(Solid State Drive)와 같은 고속 스토리지를 사용합니다.
- OS 및 네트워크
- OS(Operating System)의 파라미터 튜닝과 네트워크 대역폭 확보를 통해 시스템 전반의 성능을 개선할 수 있습니다.