이 글에서는 오라클(Oracle®)에서 SQL 프로파일(SQL Profile)과 SQL 플랜 베이스라인(SQL Plan Baseline)의 차이점을 살펴보고, 쿼리 튜닝 시 이 두 요소가 어떻게 작동하는지 자세히 설명합니다.
옵티마이저, 프로파일, 베이스라인의 관계
먼저 세 가지 핵심 요소가 어떻게 상호작용하는지 개괄적으로 살펴보겠습니다.
쿼리 옵티마이저(Query Optimizer): 시스템 통계, 바인드 변수, 컴파일 정보 등을 활용해 쿼리 실행에 최적의 플랜을 도출합니다. 하지만 입력 정보에 결함이 있으면 차선(sub-optimal)의 플랜을 선택할 수 있습니다.
SQL 프로파일: 옵티마이저의 이러한 한계를 보완하는 보조 정보를 담고 있습니다. 잘못된 추정을 최소화하여 옵티마이저가 최적의 플랜을 선택하도록 돕습니다.
SQL 플랜 베이스라인: 특정 SQL 문장에 대해 승인된(accepted) 플랜들의 집합으로 구성됩니다. 문장 파싱 후 옵티마이저는 승인된 플랜 중 최적의 것을 선택합니다. 비용 기반 옵티마이저가 더 나은 새로운 플랜을 발견하면 해당 플랜을 플랜 히스토리에 추가하지만, 현재 승인된 플랜보다 성능이 더 좋다는 검증이 완료되기 전까지는 새 플랜을 사용하지 않습니다.
쉽게 비유하자면 다음과 같습니다. SQL 프로파일은 옵티마이저에게 정보를 제공해 최적의 플랜 선택을 '도울' 뿐, 특정 플랜을 강제하지 않습니다. 반면 SQL 플랜 베이스라인은 옵티마이저의 플랜 선택 범위를 승인된 플랜 집합으로 '제한'합니다. 비용 기반 플랜도 고려 대상에 포함하고 싶다면 해당 플랜을 승인된 베이스라인 집합에 추가해야 합니다.
따라서 옵티마이저가 최신 통계를 반영한 최소 비용 플랜을 사용하길 원한다면 SQL 프로파일을, 특정 플랜 집합 중 하나를 사용하도록 제어하고 싶다면 베이스라인을 사용하는 것이 좋습니다. 만약 SQL 플랜 베이스라인이 승인된 플랜 집합 안에서 최적의 플랜을 찾지 못한다면, 그때 SQL 프로파일을 활용하면 됩니다.
SQL 플랜 관리(SPM)란?
SQL 플랜 관리(SQL Plan Management, SPM)는 다음 세 가지 구성 요소로 이루어집니다.
- 플랜 캡처(Plan Capture)
- 플랜 선택(Plan Selection)
- 플랜 진화(Plan Evolution)
SPM 플랜 캡처
SQL 문장을 실행하면 시스템이 하드 파싱(hard parse)을 수행하고, 사용 가능한 SQL 프로파일을 기반으로 비용 플랜을 생성합니다. 비용 기반 플랜이 선택되면 SQL 플랜 베이스라인에 있는 플랜들과 비교합니다. 생성된 플랜이 승인된 플랜 중 하나와 일치하면 그대로 사용하고, 일치하지 않으면 해당 플랜을 '미승인(unaccepted)' 플랜으로 베이스라인에 추가합니다.
SPM 플랜 선택
베이스라인 플램이 있는 SQL 문장을 실행하면, 시스템은 해당 SQL에 가장 적합한 플랜을 선택합니다. 옵티마이저도 동일한 프로세스를 거치며, 사용 가능한 SQL 프로파일 역시 각 플랜의 예상 비용 산정에 영향을 주어 그에 따라 플랜이 선택됩니다.
SPM 플랜 진화
SPM의 마지막 단계는 미승인 플랜의 진화(evolution)입니다. 이 과정에서는 미승인 플랜을 승인된 플랜과 비교·검증합니다. 쿼리 소요 시간과 필요한 CPU 리소스를 고려해 최적의 플랜을 평가하고, 쿼리 비용 기준으로 가장 우수한 플랜을 승인합니다. SQL 프로파일이 존재하면 예상 비용 산정에도 영향을 미칩니다.
프로파일 vs 베이스라인 비교
아래 표는 SQL 프로파일과 SQL 플랜 베이스라인을 항목별로 비교한 것입니다.
SQL 플랜 베이스라인 아키텍처
다음 이미지는 SQL 플랜 베이스라인의 아키텍처를 보여줍니다.
이미지 출처: https://ittutorial.org/sql-plan-management-using-sql-plan-baselines-in-oracle-oracle-database-performance-tuning-tutorial-14/)
SQL 플랜 베이스라인 로드하기
SQL 플랜 베이스라인을 로드하는 방법에는 두 가지가 있습니다.
이미지 출처: https://ittutorial.org/sql-plan-management-using-sql-plan-baselines-in-oracle-oracle-database-performance-tuning-tutorial-14/
첫 번째 방법(자동 캡처): 초기화 파라미터 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES를 TRUE로 설정하면 자동 플랜 캡처가 활성화됩니다. 이 파라미터는 기본값이 FALSE이므로, 아래 예시와 같이 TRUE로 변경해야 합니다.
두 번째 방법(수동 관리): DBMS_SPM 패키지를 사용해 SQL 플랜 베이스라인을 수동으로 관리할 수 있습니다. 아래 예시와 같이 SQL 튜닝 세트(SQL Tuning Set)에서 플랜을 로드합니다.
SQL 플랜 베이스라인 수동 로드
플랜 베이스라인을 수동으로 로드하려면 다음 명령어를 사용합니다.
SQL 플랜 베이스라인 사용 여부 확인
플랜 베이스라인을 로드한 후에는 실제 SQL을 실행해 옵티마이저가 해당 베이스라인을 사용하는지 확인해야 합니다. 아래와 같이 SQL_TEXT와 플랜 이름을 조건으로 SQL 플랜 베이스라인을 조회할 수 있습니다.
SQL 플랜 베이스라인 조회
다음 쿼리를 실행하면 SQL 플랜 베이스라인 목록을 확인할 수 있습니다.
SQL 플랜 베이스라인 삭제
SQL 플랜 베이스라인을 삭제하려면 먼저 아래 쿼리로 옵티마이저가 현재 어떤 SQL 플랜을 사용 중인지 확인합니다.
사용 중인 플랜을 확인한 뒤, 다음 명령어로 베이스라인을 삭제합니다.
오라클 SQL 프로파일
SQL 튜닝 어드바이저(SQL Tuning Advisor)는 오라클 엔터프라이즈 매니저(OEM) 또는 명령줄 쿼리를 통해 실행할 수 있으며, SQL 문장에 대한 프로파일을 생성합니다. 이 프로파일에는 해당 문장에 관한 추가 정보가 포함됩니다.
실습 예제
다음 예제에서는 먼저 sql_id를 대상으로 SQL 튜닝 어드바이저를 실행한 후, SQL 프로파일에 대한 각종 작업을 수행합니다.
1단계: SQL 튜닝 어드바이저 실행
sql_id 6dkrnbx1zdwy38에 대해 아래 SQL 튜닝 어드바이저 코드를 실행합니다.
권장 사항(recommendation)을 확인하려면 다음 DBMS_SQLTUNE.report_tuning_task를 실행합니다.
2단계: SQL 프로파일 승인(Accept)
다음 코드를 실행해 SQL 프로파일을 승인합니다.
3단계: SQL 프로파일 이름 확인
다음 쿼리로 SQL 프로파일 이름을 확인합니다.
4단계: SQL 프로파일 비활성화
다음 코드를 실행해 SQL 프로파일을 비활성화합니다.
반대로 다시 활성화하려면 값을 DISABLED에서 ENABLED로 변경하면 됩니다.
5단계: SQL 프로파일 삭제
다음 코드를 실행해 SQL 프로파일을 삭제합니다.
결론
SQL 문장을 실행하면 옵티마이저가 실행 계획을 생성해 쿼리를 파싱하고, 하드 디스크에서 데이터를 읽어 메모리에 적재합니다. SQL 프로파일과 플랜 베이스라인은 옵티마이저가 시간 및 CPU 비용 관점에서 가장 저렴한 플랜을 선택하도록 안내하는 역할을 합니다. 잘 설계된 SQL 플랜은 쿼리를 효율적으로 실행하여 원하는 결과를 더 빠르게 제공합니다.
데이터베이스 서비스에 대해 더 알아보세요.
피드백 탭을 통해 의견을 남기거나 질문할 수 있으며, 언제든 저희와 대화를 시작할 수 있습니다.