SQL 플랜 매니지먼트

• 공식적인 목적은 실행 계획의 안정화
• 어플리케이션 수정 없이 실행 계획 강제 가능

동작 방식

• 쿼리 옵티마이저가 주어진 SQL 구문에 대해 특정 실행 계획을 일관되게 생성하도록 강제
• SQL Plan baseline 은 SQL manaegment base 에 저장되고 옵티마이저는 자동으로 선택

1. SQL 구문은 기존 방식으로 파싱 됨
2. 옵티마이저는 SQL 텍스트를 정규화
3. 정규화에 의해 나온 해시값을 SQL Management base 에서 조회
4. 기존 방식으로 파싱 된 실행 계획이 SQL Plan baseline 에 동일하지 않으면
5. 저장된 힌트들을 사용하여 다른 실행 계획을 사용
6. Plan baseline 이 제공한 정보로 생성된 실행계획이 예상했던 실행 계획을 재현하는지 점검(?) 후 사용 결정

SQL Plan baseline 캡처

자동 캡처

1. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 초기화 파라메터 true 설정 시 옵티마이저가 자동으로 캡처
2. 두 번 째 실행되는 SQL 에 대해 저장
3. SQL 이 세 번 째 실행될 때 현재 생성한 수행 계획과 저장된 수행 계획을 비교하여 일치하지 않으면 미 승인 상태로 추가 저장
4. 옵티마이저는 플랜 베이스라인에 생성 된 계획을 사용하도록 강제하므로 2) 에서 생성 된 실행계획을 사용

라이브러리 캐시에서 읽기

• DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 를 사용하여 수동으로 로드할 수 있다
• 로드 된 실행 계획은 승인 상태로 저장되며, 옵티마이저는 즉시 사용 가능하다

SQL 튜닝 세트에서 읽기

DBMS_SPM.LOAD_PLANS_FROM_SQLSET 을 사용하여 SQL 튜닝 세트에서 SQL 플랜을 로드할 수 있다

SQL Plan baseline 조회

• DBA_SQL_PLAN_BASELINES 뷰를 통해 일반적인 정보를 조회할 수 있다
• DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE 함수를 사용하여 상세 정보를 조회할 수 있다
  • 11.2 버전부터 힌트 목록도 출력이 가능하다

SQL Plan baseline evolution

• 옵티마이저가 플랜 베이스라인 캡처 시 기존 저장 된 플랜과 다른 플랜이 나오면 미 승인 상태로 저장
• DBMS_SPM.EVOLE_SQL_PLAN_BASELINE 함수를 사용해 어떤 플랜이 더 효율적인지 확인, 수정 가능

SQL Plan baseline 변경

• DBMS_SPM.ALTER_SQL_PLAN_BASELINE 을 통해 속성 변경 가능

SQL Plan baseline 활성화

• OPTIMIZER_USE_SQL_PLAN_BASELINES 초기화 파라메터가 TRUE 로 설정된 경우에만 사용(기본이 True)

SQL Plan baseline 이전

• SQL Profile 과 동일하게 스테이징 테이블을 만들어 데이터 형태로 로드/언로드 가능하다
  • DBMS_SPM.CREATE_STGTAB_BASELINE: 스테이징 테이블 생성
  • DBMS_SPM.PACK_STGTAB_BASELINE: 딕셔너리에서 스테이징 테이블로 플랜 베이스라인 복사
  • DBMS_SPM.UNPACK_STGTAB_BASELINE: 스테이징 테이블에서 딕셔너리로 플랜 베이스라인 로드

SQL Plan baseline 제거

• DROP_SQL_PLAN_BASELINE 을 통해 제거

필요 권한

• dbms_sqm 패키지는 administer sql management object 시스템 권한이 있어야 사용 가능하다

사용 시기

• 어플리케이션의 수정이 불가능할 때
• 어떤 경우에든 플랜이 불안정해서 고정하고 싶을 때
• 엔터프라이즈 에디션을 쓸 수 있을 때(스텐다드 에디션은 스토어드 아웃라인을 사용해야 한다)

주의 사항

• SQL Profile 과 마찬가지로 소스코드(SQL) 과 별도로 관리되어 유지관리에 어려움
• SQL Text 에 의해 사용이 결정되므로 같은 이름의 테이블이 여러 스키마에 있을 경우에도 동일한 플랜 베이스라인이 사용 가능함
• 원격 DB 를 사용하는 SQL 에서는 지원되지 않음
• 11.2.0.2 까지는 Active data guard 환경에서 사용이 제한
• SYSAUX 의 테이블스페이스에서 기본 10% 용량까지 베이스라인 저장에 할당
  • DBA_SQL_MANAGEMENT_CONFIG 에서 설정값 조회 가능
  • 임계값을 초과하면 alert log 에 기록
  • DBMS_SPM.CONFIGURE 로 1~50% 사이 값으로 변경 가능
• 플랜 베이스라인과 스토어드 아웃라인을 동시에 가진 SQL 이면 스토어드 아웃라인이 우선 사용 됨
• 플랜 베이스라인과 SQL Profile 을 동시에 가진 SQL 이면 SQL Profile 에 사용 된 힌트를 플랜 베이스라인에 제한적으로 이식하려 시도