반응형
Oracle 데이터베이스에서 쿼리 성능을 분석하기 위해 X플랜을 사용하는 방법에 대해 설명합니다. 이 과정은 쿼리의 실행 계획과 성능 통계를 수집하고 분석하는 데 유용합니다. 다음은 X플랜을 사용한 쿼리 성능 분석의 단계별 절차입니다.
1. 힌트 추가
먼저, 실행 계획 통계를 수집하기 위해 쿼리에 힌트를 추가합니다. 이 힌트는 옵티마이저의 특정 동작을 제어하는 데 사용됩니다.
- 옵티마이저 힌트 추가: _OPTIMIZER_PUSH_PRED_COST_BASED 힌트는 옵티마이저가 비용 기반으로 조건을 푸시하도록 지시합니다. 이는 복잡한 쿼리에서 성능을 향상시킬 수 있습니다.
/*+ OPT_PARAM('_OPTIMIZER_PUSH_PRED_COST_BASED' 'TRUE') */
- 실행 계획 통계 수집 힌트 추가: gather_plan_statistics 힌트는 실행 계획과 함께 성능 통계를 수집합니다.
/*+ gather_plan_statistics */
예제:
SELECT /*+ OPT_PARAM('_OPTIMIZER_PUSH_PRED_COST_BASED' 'TRUE') gather_plan_statistics */
column1, column2
FROM table_name
WHERE condition;
2. 쿼리 실행
힌트를 추가한 쿼리를 실행하여 실행 계획 통계를 수집합니다.
SELECT /*+ OPT_PARAM('_OPTIMIZER_PUSH_PRED_COST_BASED' 'TRUE') gather_plan_statistics */
column1, column2
FROM table_name
WHERE condition;
3. X플랜 정보 조회쿼리가 실행된 후, 다음 쿼리를 사용하여 실행 계획과 성능 통계를 확인합니다.
SELECT *
FROM table(dbms_xplan.display_cursor(null, null, 'allstats last -rows +alias +outline +predicate'));
이 쿼리는 마지막으로 실행된 쿼리의 실행 계획과 함께 다양한 통계 정보를 제공합니다.
4. 결과 분석
실행 계획과 통계를 분석하여 쿼리 성능을 개선할 수 있는 방법을 찾습니다. 주요 분석 포인트는 다음과 같습니다:
- 메모리 처리량과 디스크 처리량이 많은 부분을 중심으로 개선을 시도합니다.
- 인덱스 생성, 힌트 추가, 쿼리 재구성 등을 통해 성능을 최적화합니다.
예제 결과:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--| 0 | SELECT STATEMENT | | 1 | 1 | 00:00:00.08 | 6414 | 1 | | | |
--| 30 | SORT AGGREGATE | | 1 | 1 | 00:00:00.08 | 6297 | 1 | | | |
--|*31 | TABLE ACCESS BY INDEX ROWID | PSTK01MT | 1 | 1489 | 00:00:00.04 | 1828 | 1 | | | |
--|*32 | INDEX RANGE SCAN | XPKPSTK01MT| 1 | 3634 | 00:00:00.01 | 62 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
결과 분석 및 최적화
- 메모리 처리량과 디스크 처리량이 많은 부분을 확인합니다.
- 예제에서는 SORT AGGREGATE와 TABLE ACCESS BY INDEX ROWID 단계에서 많은 메모리와 디스크 처리가 발생했습니다.
- 해당 부분의 처리량을 줄이는 방안을 검토합니다.
- 인덱스를 생성하거나 재구성합니다.
- 쿼리 구조를 재설계합니다.
- 힌트를 사용하여 옵티마이저의 동작을 변경합니다.
예제 분석
예제에서 주요 문제가 되는 부분은 다음과 같습니다:
--| 30 | SORT AGGREGATE | | 1 | 1 | 00:00:00.08 | 6297 | 1 | | | |
--|*31 | TABLE ACCESS BY INDEX ROWID | PSTK01MT | 1 | 1489 | 00:00:00.04 | 1828 | 1 | | | |
--|*32 | INDEX RANGE SCAN | XPKPSTK01MT| 1 | 3634 | 00:00:00.01 | 62 | 0 | | | |
- SORT AGGREGATE (Id 30): 이 단계에서 많은 메모리와 디스크 처리가 발생합니다.
- TABLE ACCESS BY INDEX ROWID (Id 31): 특정 테이블에 대한 인덱스를 사용한 접근에서 많은 리소스를 사용합니다.
- INDEX RANGE SCAN (Id 32): 인덱스 스캔 단계에서 비교적 적은 리소스를 사용하지만, 연관된 테이블 접근이 성능에 영향을 미칩니다.
결론
Oracle X플랜을 사용하여 쿼리의 실행 계획과 성능 통계를 분석하면, 쿼리 성능을 저하시키는 요소를 쉽게 식별할 수 있습니다. 이를 통해 적절한 인덱스 생성, 힌트 추가, 쿼리 재구성 등의 최적화 작업을 수행하여 성능을 개선할 수 있습니다.
주요 포인트:
- 힌트 사용: OPT_PARAM 힌트로 옵티마이저 동작을 제어하고 gather_plan_statistics 힌트로 실행 계획 통계를 수집합니다.
- 쿼리 실행 후 X플랜 조회: dbms_xplan.display_cursor를 사용하여 실행 계획과 성능 통계를 확인합니다.결과 분석 및 최적화: 메모리와 디스크 처리량이 많은 부분을 중심으로 인덱스 생성, 쿼리 재구성 등의 최적화를 진행합니다.
반응형