SQL Profile

• Automatic Tuning Optimizer 에게 쿼리 최적화를 다시 수행하도록 할 수 있다
• 실제 SQL 수행 시에는 속도를 위해 최적이 아닌 실행계획이라도 만들어야 한다
• Automatic Tuning Optimizer 는 충분한 시간을 가지고 수행 계획을 검토할 수 있다
• 검토 된 최적화 수행 방법 중 하나가 SQL Profile 이다

동작 방식

• SQL 프로파일은 옵티마이저가 효율적인 수행 계획을 찾도록 도와주는 정보가 담긴 오브젝트
• 수행환경, 오브젝트 통계, 통계 보정 정보 제공
• 동작 순서는 다음과 같다

1. 사용자는 SQL 구문을 Tuning advisor 에 최적화 요청
2. Tuning advisor 는 다시 옵티마이저에게 최적화 권고안을 요청
3. 옵티마이저는 초기화 파라메터 및 구문 분석 후 SQL 프로파일을 반환
4. 사용자는 검토 후 승인

SQL 튜닝 어드바이저 사용 방법

• SQL Profile 을 생성, 관리 가능한 툴
• DBMS_SQLTUNE 패키지를 호출하여 사용 가능(EM 도 지원)
  • DBMS_SQLTUNE.CREATE_TUNING_TASK 를 사용하여 프로파일 생성
  • DBMS_SQLTUNE.REPORT_TUNING_TASK 를 사용하여 최적화 분석 보고서를 확인
  • DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 을 사용하여 생성된 프로파일 승인
  • DBMS_SQLTUNE.ALTER_SQL_PROFILE 을 사용하여 생성된 프로파일 변경

텍스트 정규화

• SQL 구문을 직접 수정하는게 아니라 SQL 구문과 생성된 프로파일을 연결해서 사용하므로 SQL 텍스트 정규화 수행 필요
• DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 수행 시 force_match 파라메터를 사용하여 정규화 레벨을 결정할 수 있다
  • false: 공백 무시, 대소문자 구분하지 않음
  • true: 공백 무시, 대소문자 구분하지 않음, 리터럴(변수) 구분하지 않음
• 동일 SQL 이 force_match true/false 의 다른 프로파일을 가질 수 있으나, false 일 경우가 우선

SQL 프로파일 활성화

• SQL Profile Category 는 관리 목적 상 여러 프로파일을 그룹으로 묶는 기능이다
• 초기화 파라메터를 사용하여 시스템/세션 레벨에서 특정 카테고리 활성화 가능하다

alter session set sqltune_category = test ;
-- 다음은 현재 세션이 test 카테고리의 SQL Profile 을 활성화 하도록 한다

• 기본값은 DEFAULT 이므로 프로파일 승인 시 특정 카테고리를 지정하지 않으면 DEFAULT 프로파일이 활성화 된다
• v$sql 의 sql_profile 칼럼을 통해 커서가 사용한 프로파일의 이름을 알 수 있다

SQL Profile 이전

• 단순 복제는 불가능하며
• Profile 을 담는 테이블을 생성 후 데이터 형태로 이동이 가능하다
• 사용되는 패키지는 아래와 같다
  • DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF : SQL Profile 을 담는 스테이징 테이블을 만든다
  • DBMS_SQLTUNE.PACK_STGTAB_SQLPROF: 프로파일을 스테이징 테이블로 복사한다
  • DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF: 스테이징 테이블에 저장된 프로파일의 이름, 카테고리 변경
  • DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF: 스테이징 테이블에 저장된 프로파일을 다시 딕셔너리로 업로드

SQL Profile 삭제

• DBMS_SQLTUNE.DROP_SQL_PROFILE 을 사용하여 삭제

필요 권한

• SQL 프로파일 사용을 위해 11G 이상부터는 administer sql management object 시스템 권한이 필요하다
• SQL 튜닝 어드바이저 사용을 위해서는 advisor 시스템 권한이 필요하다

SQL Tuning advisor 에서 사용되는 힌트

• SQL 프로파일에는 옵티마이저가 수행 할 힌트 목록이 저장되어 있으며
• 이는 모두 SQL 구문에 수동으로 적용 가능하다
• 확인을 위해서는 아래 두 가지 방법이 있다
  • 내부 딕셔너리 테이블 조회: sys.sqlobj$data, sys.sqlobj$ (534p 참조)
  • SQL 프로파일 이행을 위해 사용 되는 스테이징 테이블을 분석 (534p 참조)
• opt_estimate 힌트(문서에 없는) 가 주목할 만 한데, 잘못된 통계 추정치를 보정하여 옵티마이저에게 전달 가능하다

사용 시기

• SQL 튜닝이 필요한데 어플리케이션을 수정할 수 없는 상황이고
• 단순하게 옵티마이저에게 추가 정보를 제공 할 목적에 사용
• 특정 실행 계획을 강제해야 한다면 스토어드 아웃라인이나 SQL 플랜 매니지먼트를 사용해야 함

주의점

• 소스 코드(SQL) 에 의해 제어되지 않으므로 유지보수 및 관리에 어려우니 주의해야 함
• 생성 후 테스트(검증) 시에는 초기화 파라메터에 저장 된 카테고리가 아닌 다른 카테고리를 지정하여 이미 운영 중인 어플리케이션이 영향을 받지 않도록