by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

11. SQL 최적화 기법




SQL 최적화 기법




  • 엑세스 구조 변경
  • SQL 구문 변경
  • 힌트 사용
  • 실행 환경 변경
  • Stored outline
  • SQL Profile
  • SQL Plan baseline




엑세스 구조 변경




동작 방식


  • 데이터의 엑세스 구조를 파악 후 개선점을 찾는다
    ex) 인덱스 생성, Mview 생성

사용 시기


  • 전반적으로 우선 검토 되어야 함
  • 데이터 엑세스 구조를 변경 가능한 상황

주의점


  • 구조에 대한 변경은 단일 SQL 이 아니라 구조를 참조하는 여러 SQL 에 영향을 준다
    ex) 인덱스 컬럼에 대한 순서 변경 등은 기존에 해당 인덱스를 사용하던 모든 쿼리를 검토 해야 한다



SQL 구문 변경




동작 방식


  • 결과와 목적이 동일한 SQL 이라도 쿼리 옵티마이저가 해석/실행하는 방식은 다를 수 있다
  • 동일한 데이터를 더 효율적인 SQL 로 처리해야 한다

사용 시기


  • SQL 구문을 바꿀 수 있다면 우선적으로 고려

주의점


  • 성능을 위해 유지보수성, 가독성이 떨어지는 SQL 을 써야 할 수도 있다.



힌트




동작 방식


힌트란?


  • 옵티마이저는 모든 실행 계획을 수행 시 고려할 수 없기 때문에 일부 실행 계획을 사전에 제외시킨다
  • 옵티마이저의 수행 환경을 바꾸거나, 특정 기능을 활성화/비활성화 한다
  • 일반적으로는 고려할 실행 계획의 범위를 고정시키는게 목적이다

힌트 지정


  • DELETE, INSERT, UPDATE, SELECT, MERGE 단어 바로 뒤에 사용
  • /*+ HINT */
  • 힌트의 문법 오류는 SQL 오류를 발생시키지 않고 주석으로 간주 됨

힌트의 범주


  • 저자가 선호하는 분류이다
    1) 초기화 파라메터 힌트
  • 시스템/세션 레벨에서 설정 된 이니셜 파라메터의 설정값을 변경
  • ex> first_rows ...
    2) 쿼리 변환 힌트
  • 최적화 시 쿼리 변환 방법 제어
  • ex> no_expand, no_merge ...
    3) 엑세스 패스 힌트
  • 테이블 엑세스 방법 제어
  • ex> full, index ...
    4) 조인 힌트
  • 조인 방법 및 순서 제어
  • ex> ordered, leading ...
    5) 병렬 처리
  • 병렬의 사용 여부, 어떻게 사용할지 제어
  • ex> parallel, pq_distribute ...
    6) 기타
  • 그냥 기타
  • append, qb_name

힌트의 효력


  • 초기화 파라메터는 일반적으로 전체 SQL 구문에 대해 유효하다
  • 대부분의 다른 힌트는 일반적으로 단일 쿼리 블럭에서만 유효하다
  • 글로벌 힌트를 사용 시 . 을 이용하여 다른 쿼리블럭을 지정, 참조 가능하다
    with emps as (
    select dept_no, count(*) as cnt
    from emp
    group by deptno
    )
    select /*+ full(dept) full(emps.emp) */
    dept.dname, emps.cnt
    from dept, emps
    where dept.deptno = emps.deptno
    



  • qb_name 을 사용한 쿼리 블럭 이름을 명시적으로 사용하는 것이 효율적이다
    with emps as (
    select /*+ qb_name(sub) */ dept_no, count(*) as cnt
    from emp
    group by deptno
    )
    select /*+ qb_name(main) full(@main dept) full(@sub emp) */
    dept.dname, emps.cnt
    from dept, emps
    where dept.deptno = emps.deptno
    



  • 사용자가 지정하지 않아도 시스템에서 자동으로 쿼리 블럭 이름을 생성하며, 접두어 + 알파벳/숫자 형식이다

사용 시기


  • 옵티마이저가 효율적인 실행 계획을 자동으로 만들지 못할 때 사용한다
  • 불가피한 경우가 아니면 영구적인 방법으로 힌트를 고려하지 말자

주의점


  • 통계와 엑세스 구조가 어느정도 바뀌더라도 안정적으로 유지되게 힌트를 사용해야 한다
  • 일부 문법을 체크하는 특이한 힌트도 있다 ex> change_dupkey_errpr_index ...
  • 10132 이벤트 트레이스로 힌트가 잘못되지 않은 문법으로 인식 되었는지 판단 가능하다
    --> 힌트가 유요한지에 대해 판단은 불가능하다
  • 테이블 별칭을 사용할 경우 테이블명 대신에 별칭을 힌트에 언급해야 한다
  • 동일 힌트라도 다른 DB 버전에서는 다르게 동작할 수 있다
    --> 세션 레벨에서 _optimizer_ignore_hints = true 로 설정 후 처음부터 다시 검증하는게 가장 바람직
  • 뷰는 다양한 상황에서 사용되므로 뷰 내부에 힌트를 쓸 경우는 주의 필요



실행 환경 변경하기


  • 세션, SQL 구문 레벨에서 환경을 바꾸는게 적정할 수 있다
    ex> OLTP, Batch 가 하나의 DB 에서 수행되는 경우 등

동작 방식


세션 레벨의 환경 변경


  • 대부분의 초기화 파라메터는 alter session 을 사용하여 변경할 수 있다.
  • 특정 사용자에게 특정 환경을 부여하려면 database 트리거의 after logon on database 이벤트를 사용해 설정 가능하다

SQL 구문 레벨


  • 초기화 파라메터의 값을 SQL 구문 레벨에서도 바꿀 수 있으나 이름이 항상 동일하지 않다
  • 500p 참조

참조 가능한 다이나믹 뷰


  • v$sys_optimizer_env - 인스턴스 레벨의 실행 환경에 대한 정보
  • v$sess_optimizer_env - 세션 레벨의 실행 환경에 대한 정보
  • v$sql_optimizer_env - 라이브러리 캐시에 있는 각 차일드 커서의 실행 환경에 대한 정보
    --> 부모 커서의 다른 자식 커서가 서로 다른 실행 환경을 가지는지 확인 가능하다

사용 시기


  • 기본 DB 설정이 일부 어플리케이션이나 사용자에는 좋지 않은데, SQL 구문을 자유롭게 변경하기 힘든 경우

주의점


  • 커넥션 풀을 사용하는 경우라면 서로 다른 환경으로 동작해야 하는 어플리케이션이 같은 풀을 사용하지 않도록 하는게 효율적이다



Stored outline


  • 11G 이상 버전부터는 플랜 매니지먼트로 대체 되었다



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


사용 시기


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

주의점


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



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 에 사용 된 힌트를 플랜 베이스라인에 제한적으로 이식하려 시도



문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.