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