11장. 자동 SQL 튜닝

11장. 자동 SQL 튜닝

 

학습 목표

 

이번 장을 완료하면 다음과 같은 작업을 수행 할 수 있다.

①   문장 프로필을 설명 할 수 있다.

②   SQL Tuning Advisor를 사용 할 수 있다.

③   SQL Access Advisor를 사용 할 수 있다.

④   Automatic SQL Tuning을 사용 할 수 있다.

 

SQL 문장 자동 튜닝

 

자동 SQL 문장 튜닝은 전체 SQL 튜닝 과정을 자동화하는 쿼리 Optimizer의 능력이다. 이 자동화 처리는 복잡하고

반복적이며 많은 시간을 소비하는 직접 SQL 튜닝을 대체한다.

SQL Tuning Advisor는 사용자에게 SQL 튜닝의 기능을 노출한다. 향상된 쿼리 Optimizer는 두 가지 모드를 갖는다.

①   정상 모드(normal mode)에서 Optimizer는 SQL을 컴파일하고 실행 계획을 생성한다. 정상 모드의

Optimizer는 대부분의 SQL 문장에 대하여 적절한 실행 계획을 생성한다. 정상 모드에서 Optimizer는

매우 제한된 시간 제약, 보통 1초 이내에서 좋은 실행 계획을 찾아내야만 한다.

②   튜닝 모드(tuning mode)에서 Optimizer는 정상 모드에서 작성된 실행 계획이 더욱 향상 될 수 있는지 여부를

확인하기 위해 추가적인 분석을 수행한다. 튜닝 모드에서 쿼리 Optimizer의 출력은 실행 계획이 아닌 일련의

액션이며 합리적이고 기대되는 이익(상당히 우수한 실행 계획이 생성되어)을 수반한다. 튜닝 모드의 Optimizer를

ATO(Automatic Tuning Optimizer)라고 하기도 한다. ATO에 의해서 수행되는 작업을 시스템 SQL 튜닝이 라고 부른다.

 

튜닝 모드에서 Optimizer는 단일 문장을 튜닝하기 위해 수 분을 소비 할 수 있다. ATO는 전체 시스템에 중대한 영향을

미치는 복잡하고 과부하 SQL 문장들에 대해 사용 할 의도로 제공되었다.

 

애플리케이션 튜닝의 문제점

그림 11-1

 

과부하 SQL 문장을 식별하고 해당 문장을 튜닝하는 과정은 전문가조차 쉽지 않은 일이다. SQL 튜닝은 데이터베이스 서버의

성능을 관리하는데 매우 중요한 부분 중의 하나일 뿐만 아니라 수행 하기 어려운 작업 중의 하나이다. Oracle Database 10g

에서부터 과부하 SQL 문장을 식별하는 작업은 ADDM(Automatic Database Diagnostic Monitor)에 의해 자동화 되었다.

ADDM에 의해 식별 된 과부하 SQL 문장의 수가 전체 SQL 작업부하의 극히 작은 비율을 차지한다고 하더라도 이러한

문장들을 튜닝하는 것은 여전히 매우 복잡하고 고급 수준의 전문기술을 필요로 한다.

또한, SQL 튜닝 활동은 새로운 애플리케이션 모듈이 배포 될 때였다. SQL 작업부하가 상대적으로 변화되므로 지속적인

작업이 될 수 밖에 없다.

 

Oracle Database 10g에서 소개된 SQL Tuning Advisor는 SQL 문장의 직접 튜닝을 대체하기 위해 고안되었다. CPU, I/O,

임시 공간과 같은 자원을 매우 많이 소비하는 SQL 문장들은 SQL Tuning Advisor의 좋은 대상이 된다. 어드바이저는

입력으로 하나 이상의 문장을 받아서 실행 계획을 최적화하는 방법, 권고안의 이유, 예측된 성능 이득, 권고 사항을

구현하기 위한 실제 명령을 제공 한다. 권고안을 수용함으로서 SQL 문장을 튜닝한다. SQL Tuning Advisor의 등장으로

인하여, 오라클 Optimizer는 사용자를 대신하여 SQL 코드를 튜닝 할 수 있도록 되었다.

 

SQL Tuning Advisor : 개요

그림 11-2

 

SQL Tuning Advisor는 튜닝 작업의 주요 원동력이다. 이것은 ATO(Automatic Tuning Optimizer)를

호출하여 다음과 같은 4가지 형식의 분석을 수행한다.

①   통계 분석 : ATO는 통계가 존재하지 않거나 오래된 각각의 쿼리 객체를 검사하고 관련 통계를 수집하기 위한

권고안을 작성한다. 또한, 권고안이 구현되지 않았을 경우, 누락된 통계를 제공하거나 오래된 통계를 정정 할 수

있는 보조 정보도 수집한다.

②   SQL 프로필 : ATO는 자신의 예측값을 검증하고 예측 오류를 제거 할 수 있는 보조 정보를 수집한다. 또한,

SQL 문장의 이전 실행 이력을 기반으로 first rows 및 all rows와 같은 Optimizer 설정값의 형태로 보조 정보를

수집한다. 이러한 보조 정보를 이용하여 SQL Profile을 구성하고, 이를 생성 할 수 있는 권고안을 작성한다.

SQL Profile이 생성되면, 해당 프로필은 정상 모드에서 쿼리 Optimizer를 활성화하여 잘 튜닝된 실행 계획을 생성 한다.

③   액세스 경로 분석 : ATO는 쿼리에서 각 테이블에 대한 액세스를 향상시키기 위해 새로운 인덱스를 사용

할 수 있는지 탐색하고 그러한 인덱스를 생성할 수 있는 권고안을 작성한다.

④   SQL 구조 분석 : ATO는 나쁜 실행 계획을 발생시키는 SQL 문장을 식별하며, 그 문장을 재구성하도록 권고안을

작성한다. 권고안은 SQL 코드의 문법뿐만 아니라 문법을 변경 할 수도 있다.

 

통계가 오래되었거나 누락된 객체

 

그림 11-3

 

쿼리 Optimizer는 실행 계획을 생성하기 위해 객체 통계에 의존한다. 만약, 이러한 통계가 오래 되었거나 누락되었다면

Optimizer는 필요한 필수 정보를 얻을 수 없으며 준 최적 실행 계획을 작성하게 된다.

 

ATO는 누락되거나 오래된 통계를 가진 각 쿼리 객체를 확인하고 두 종류의 출력을 작성한다.

①   통계가 없는 객체에 대하여 통계 형태의 보조 정보와 오래된 통계를 가진 객체에 대하여 통계 조정 인자

②   통계가 오래되었거나 누락된 객체에 대하여 통계를 수집하기 위한 권고안

 

최적의 결과를 얻기 위해서는 권고안에 따라 통계를 수집하고 ATO를 다시 실행하면 된다. 그러나, 이러한 권고안이

시스템의 다른 쿼리에 영향을 미칠 수 있기 때문에 권고안의 수용을 주저 할 수 도 있다.

 

SQL 문장 프로파일링

그림 11-4

 

SQL 프로파일링이 진행되는 동안, 수행되는 주요 검증 단계는 튜닝된 문장에 대해서 쿼리 Optimizer가 가지고 있는 비용,

선택도, 카디널리티의 예측 값을 검증하는 것이다.

 

SQL 프로파일링이 진행되는 동안, ATO는 자신의 예측 값을 검증하는 단계를 수행한다. 이러한 검증은 데이터의 샘플을

수집하고 샘플에 적절한 조건문을 적용하는 작업으로 구성된다. 새로운 예측 값이 정상적인 예측 값과 비교되며, 그 차이가

매우 큰 경우에는 정정 인자가 적용된다. 예측 값의 검증을 위한 또 다른 방법은 SQL 문장의 일부분을 실행하는 것이다.

부분 실행 방법은 각각의 조건 문이 효율적인 액세스 경로를 제공하는 경우에 샘플링 방법보다 더욱 효율적이다. ATO는

적절한 예측 값 검증 방법을 선택한다.

 

또한, ATO는 올바른 설정 값을 결정하기 위해 SQL 문장의 과거 실행 이력을 사용하기도 한다. 예를 들어, 만약 실행

이력에서 어느 하나의 SQL 문장이 거의 부분 실행으로 나타난다면 ATO는 ALL_ROWS와 상대되는 FIRST_ROWS 최적화를 사용한다.

 

ATO가 Statistics Analysis 또는 SQL 프로파일링이 진행되는 동안 보조 정보를 생성한다면, SQL Profile을 구성한다.

SQL Profile이 구성되면, SQL Profile을 생성하기 위한 사용자 권고안을 생성한다. 이 모드에서 ATO는 SQL Profile을

동작시키기 위해 생성된 SQL Profile의 수용을 권고 할 수 있다.

 

실행 계획 튜닝 흐름 및 SQL Profile 생성

그림 11-5

 

SQL Profile은 SQL 문장의 자동 튜닝이 진행되는 동안, 구성되는 보조 정보의 집합이다. 그러므로, 통계가 테이블 또는

인덱스에 연관된다면 SQL Profile은 SQL 문장과 연관된다. SQL Profile이 생성 되면, 정상 모드의 Optimizer는 기존의

통계와 함께 SQL Profile을 사용하여 해당 SQL 문장에 대한 더 나은 실행 계획을 생성한다. SQL Profile은 데이터

딕셔너리에 영구히 저장된다.

그러나, SQL Profile 정보는 일반 딕셔너리 뷰를 통해 노출되지 않는다. SQL Profile이 생성되면, 정상 모드에서 SQL 문장이

컴파일 될 때였다. 쿼리 Optimizer는 더 나은 실행 계획을 생성하기 위해 SQL Profile을 사용한다.

 

위 그림은 SQL Profile의 생성 및 사용 흐름을 보여준다. 이 과정은 두 개의 분리된 단계로 구성되는데, SQL 튜닝 단계와

정상 최적화 단계이다. 시스템 SQL 튜닝 단계가 진행되는 동안, 시스템 튜닝 대상인 SQL 문장을 선택하고, Database

Control 또는 명령 줄 인터페이스를 사용하여 SQL Tuning Advisor를 실행한다. SQL Tuning Advisor는 ATO를 실행하여

튜닝 권고안과 SQL Profile을 생성한다. 만약, SQL Profile이 구성되면 사용자는 SQL Profile을 수용 할 수 있다.

SQL Profile이 수용되면, SQL Profile은 데이터 딕셔너리에 저장된다. 다음 단계에서 말단 사용자가 동일한 SQL 문장을

실행하면, 쿼리 Optimizer(정상 모드)는 SQL Profile을 사용하여 더 나은 실행 계획을 작성 한다. SQL Profile의 사용은 말단

사용자에게 완전히 투명하고 애플리케이션의 소스 코드를 수정 할 필요도 없다.

 

SQL 튜닝 루프

그림 11-6

 

SQL Profile 내에 포함된 보조 정보는 인덱스의 추가 또는 제거, 테이블의 용량 증가, 데이터베이스 통계의 주기적 수집과

같은 데이터베이스 변경이 발생한 후에도 관련 정보와 연관되어 저장된다. 그러므로, 프로필이 생성된 후, 해당 실행 계획이

고정(아웃라인을 사용하는 것처럼)되지 않는다. 그러나, SQL 프로필은 데이터베이스 내의 대량 변경 또는 오랫동안

변경사항이 누적된 경우에 대해서는 적응하지 못할 수 있다. 그러한 경우, 새로운 SQL Profile로 오래된 프로필을 교체해야 할 필요가 있다.

 

예를 들어, SQL Profile이 너무 오래된 경우, 해당 SQL 문장의 성능은 눈에 띄게 나빠질 수도 있다. 그러한 경우, 해당 SQL

문장은 과부하 또는 최상위 SQL 문장으로 나타나기 시작하여, 다시 시스템 SQL 튜닝의 대상이 된다. 그러한 상황에는

ADDM이 다시 과부하 SQL 문장을 수집한다. 만약, 이러한 상황이 발생한다면, 해당 문장에 대한 새로운 프로필을 재 생성하도록 결정 할 수 있다.

 

액세스 경로 분석

그림 11-7

 

또한, ATO는 인덱스에 대한 권고안을 제공한다. 효율적인 인덱스 사용은 전체 테이블 스캔을 방지하여 SQL 문장의 성능을

상당히 향상시키는 잘 알려진 튜닝 기법이다. ATO에 의해 생성된 인덱스 권고안은 튜닝 할 SQL 문장으로만 한정된다.

그러므로, 단일 SQL 문장과 연관된 성능 문제를 해결하기 위한 신속한 솔루션을 제공한다.

 

ATO는 인덱스 권고안이 전체 SQL 작업 부하에 어떠한 영향을 미칠 것인지를 분석하지 않기 때문에 대표 SQL 작업 부하와

함께 SQL 문장에 대하여 Access Advisor를 실행하도록 권장한다. Access Advisor는 SQL 작업 부하의 각 문장에 대하여

권고안을 수집하고, 통합한 다음, 전체 SQL 작업 부하에 대한 전체적인 권고안을 작성한다.

 

액세스 경로 분석은 다음과 같은 권고안을 작성한다.

①   획기적인 성능 향상이 발생하는 경우에만 새로운 인덱스를 생성한다.

②   애플리케이션 작업 부하를 기반으로 종합적인 인덱스 분석을 수행하기 위해 SQL Access Advisor를 실행한다.

 

SQL 구조 분석

그림 11-8

 

SQL 구조 분석의 목표는 비효율적으로 작성된 SQL 문장을 찾는 것뿐만 아니라 이러한 문장들을 어떻게 재구성 할 것인지

권고하는 것이다. 성능에 부정적인 영향을 미치는 것으로 알려진 구문을 변경 할 수 있다. 이 모드에서 ATO는 여러 규칙을

기반으로 문장을 평가하여, 비효율적인 코딩 테크닉을 식별하고 대체 가능한 문장을 권고 한다.

권고안은 매우 유사 할 수 있지만, 원본 쿼리와 동일하지는 않다. 예를 들어, NOT EXISTS와 NOT IN 구문은 유사하지만,

정확히 동일하지는 않다. 그러므로, 해당 권고안이 유효한지를 결정하여야만 한다.

이런 이유로 ATO는 자동으로 쿼리를 재 작성하지 않고, 권고안을 대신 작성한다.

 

SQL 구조 분석에 의해 탐지된 문제들은 다음과 같이 분류된다.

①   NOT EXISTS 대신 NOT IN, UNION ALL 대신 UNION을 사용하는 것과 같은 SQL 구문 사용

②   인덱스 컬럼의 데이터 타입이 불일치되어 인덱스 사용을 방해하는 조건의 사용

③   카르테시안 프로덕트와 같은 디자인 실수

 

SQL Tuning Advisor : 사용 모델

그림 11-9

 

SQL Tuning Advisor는 하나 이상의 SQL 문장을 입력으로 사용한다. 이 입력에는 다음과 같은 형태가 있다.

①   ADDM에 의해 식별된 과부하 SQL 문장

②   커서 캐시 내에 현재 존재하는 SQL 문장

③   AWR(Automatic Workload Repository) 내의 SQL 문장 : 사용자는 AWR에 의해 수집된 어떠한 SQL

문장의 집합도 선택 할 수 있다. 이것은 스냅샷 또는 기준선(baseline)을 사용하여 수행된다.

④   사용자 정의 작업 부하 : 사용자가 관심 있어 하는 문장들로 구성된 사용자 정의 작업 부하를 생성 할 수

있다. 여기에는 커서 캐시에 존재하지 않는 문장이 있을 수 있으며, ADDM 또는 AWR에 의해 수집된 부하가

많이 걸리지 않는 문장이 있을 수 있다. 그러한 문장의 경우, 사용자 정의 작업 부하를 생성하고, Advisor를

이용하여 해당 문장을 튜닝 할 수 있다. 커서 캐시, AWR, 사용자 정의 작업 부하 내의 SQL 문장은

SQL Tuning Advisor의 입력으로 제공 되기 전에 필터링되며 순위가 부여 될 수 있다.

 

여러 문장들을 입력으로 사용하기 위해 STS(SQL Tuning Set)라고 부르는 새로운 객체가 제공된다.

STS는 해당 문장의 실행 정보와 함께 여러 SQL 문장을 저장한다.

①   실행 문장(Execution context) : 스키마 이름과 바인드 변수 파싱

②   실행 통계(Execution statistics) : 평균 수행 시간 및 실행 횟수

 

참고 : STS 생성을 위해 또 다른 STS가 원본으로 사용 될 수도 있다.

 

Database Control과 SQL Tuning Advisor

그림 11-10

EM에서 SQL Tuning Advisor에 접근하는 가장 쉬운 방법은 Advisor Central 페이지이다. Home 페이지에서

Related Links 영역에 위치한 Advisor Central 링크를 클릭하면 Advisor Central 페이지가 오픈된다.

 

Advisor Central 페이지에서 SQL Advisors 링크를 클릭한다. SQL Advisors 페이지에서 SQL Tuning Advisor

링크를 클릭하면 Schedule SQL Tuning Advisor 페이지가 열린다. 이 페이지에서 여러 다른 페이지로

이동하는 링크를 발견 할 수 있다. Top Activity 페이지를 오픈하기 위해 Top Activity 링크를 클릭한다.

 

SQL Tuning Advisor 실행 : 예

 

그림 11-11

 

Database Control을 사용하여 과부하 또는 최상위 SQL 문장을 식별 할 수 있다. Database Control 에는

SQL Tuning Advisor를 식별된 SQL 문장, 문장들 또는 STS와 함께 여러 위치에서 실행 할 수 있다.

①   ADDM에 의해 식별된 SQL 문장의 튜닝 : ADDM Finding Details 페이지는 ADDM에 의해 식별된

과부하 SQL 문장을 보여준다. 이 과부하 SQL 문장은 CPU 시간, 버퍼 인기, 디스크 인기 등과 같은

하나 이상의 시스템 자원을 상당히 많이 소비하는 것으로 알려져 있다. 이 페이지를 이용하여, 선택된

과부하 SQL 문장에 대해 SQL Tuning Advisor를 실행 할 수 있다.

②  최상위 SQL 문장 튜닝 : 또 다른 SQL 소스는 최상 SQL 문장의 목록이다. 이것은 위의 그림에서

보여진다. 선택된 시간 윈도우를 기반으로 누적된 실행 통계를 검토하여 최상위 SQL 문장의 목록을

식별 할 수 있다. SQL ID로 구분되는 하나 이상의 최상위 SQL 문장을 선택하여, 해당 문장에 대해

SQL Tuning Advisor를 실행한다.

③   STS 튜닝 : 서로 다른 사용자에 의해 생성된 다양한 STS를 확인 할 수 있다. STS는 최상위 SQL 문장의

목록, AWR에 의해 생성된 스냅샷의 범위 내 선택된 SQL 문장들, 사용자 정의 SQL 문장에 의해 생성 될 수 있다.

 

권고안의 구현

 

그림 11-12

 

SQL Tuning Advisor가 실행되면, EM은 자동적으로 튜닝 태스크를 생성한다. 해당 사용자는 해당 작업에 대하여 적절한

ADVISOR 권한을 가지고 있어야 한다. EM은 이전 그림에서 보여진 Schedule SQL Tuning Advisor 페이지에서 자동 디폴트

값으로 튜닝 태스크를 보여준다. 이 페이지에서 해당 사용자는 튜닝 태스크와 관련된 자동 디폴트 값을 변경 할 수 있다.

 

중요한 옵션 중 하나는 튜닝 태스크의 범위를 선택하는 것이다. 만약, Limited 옵션을 선택하였다면 SQL Tuning Advisor는

통계 확인, 액세스 경로 분석, SQL 구조 분석을 기반으로 권고안을 작성한다. No SQL Profile 권고안은 Limited 범위에서

생성된다. 만약, Comprehensive 옵션을 선택 하였다면 SQL Tuning Advisor는 Limited 범위 하의 모든 권고안을 수행하고,

SQL 프로파일링 모드에서 Optimizer를 실행하여 SQL Profile을 구성한다. Comprehensive 옵션을 사용하면 튜닝 태스크에

시간 제한을 지정 할 수 있으며, 디폴트는 30분이다. 또 다른 유용한 옵션은 즉시 튜닝 태스크를 실행하거나 나중에

실행되도록 하는 것이다. Schedule Advisor 페이지에서 튜닝 태스크를 설정 할 수 있으며, 이를 위해서 Schedule SQL

Tuning Advisor 액션을 선택하고 Go를 클릭한다. Top Activity 페이지로 돌아오면, 튜닝된 문장을 클릭하여 SQL Details

페이지를 오픈 할 수 있으며, 튜닝 정보를 확인 할 수 있다. 이것은 사용자에게 완전한 튜닝 태스크를 보여준다. 해당

태스크를 클릭하여 일반적인 SQL Tuning Results를 확인 할 수 있다.

View 버튼을 클릭하여 자세한 정보를 확인 할 수 있다. 보여진 바와 같이 SQL Profile이 생성되며, 새로운 실행 계획을

확인한 다음, SQL Profile을 구현 할 수 있다.

 

SQL Access Advisor : 개요 

그림 11-13

SQL 쿼리를 최적화하기 위해 적절한 액세스 구조를 정의하는 것은 개발자들의 오랜 관심이었다. 그 결과, 이러한 문제를

해결하기 위해 많은 논문과 스크립트가 쓰여졌고, 여러 툴들이 개발되었다. 또한, 파티셔닝과 구체화된 뷰 기술의 개발과

함께 액세스 구조를 결정하는 작업은 더욱더 복잡해졌다. Oracle Database 10g와 11g에서 향상된 관리성의 일부로서

SQL Access Advisor가 이러한 요구에 맞추어 소개되었다. SQL Access Advisor는 SQL 문장의 실행과 관련하여 성능 문제를

식별하고, 인덱스, 구체화 된 뷰, 구체화 된 뷰 로그, 파티션 생성, 삭제, 유지 등의 방법으로 성능 문제를 해결하는데 도움을 준다.

SQL Access Advisor는 Database Control에서 실행하거나 PL/SQL 프로시저를 사용하여 명령줄에서 실행 가능하다.

SQL Access Advisor는 실제 작업 부하를 입력으로 받거나 어드바이저가 스키마로부터 가상적인 작업부하를 만들어 낼 수도 있다.

그런 다음, 더욱 빠른 실행 계획을 위해 액세스 구조를 권장한다.

 

SQL Access Advisor는 다음과 같은 장점을 제공한다.

①   사용자에게 전문 지식을 요구하지 않는다.

②   CBO에 실제 포함된 규칙 기반의 결정을 기반으로 한다.

③   Optimizer 및 향상된 오라클 데이터베이스와 동기화 되어 있다.

④   SQL 액세스 방법의 모든 측면을 고려하는 단일 어드바이저이다.

⑤   단순하고 사용자가 친숙한 GUI 위저드를 제공한다.

⑥   권고안을 구현 할 수 있는 스크립트를 생성한다.

 

 SQL Access Advisor : 사용 모델

 

그림 11-14

 

SQL Access Advisor는 다양한 소스로부터 만들어진 작업 부하를 입력으로 받는다.

①   SQL 캐시, V$SQL의 현재 내용으로부터 가져옴

②   사용자의 디멘전 모델로부터 유사하게 생성된 가상의 작업 부하. 이 옵션은 시스템의 설계 초기에 유용하다.

③   작업 부하 리포지터리로부터 가져온 SQL Tuning Sets

 

또한, SQL Access Advisor는 튜닝 대상을 지정하는데 사용 할 수 있는 강력한 작업 부하 필터를 제공한다.

예를 들어, Optimizer의 비용을 기반으로 가장 자원을 많이 소비하는 30개의 문장에 대해서만 어드바이저가

검토하도록 지정 할 수 있다. 주어진 작업 부하에 대하여 어드바이저는 다음 작업을 수행한다.

①   인덱스 솔루션, 구체화 된 뷰 솔루션, 파티션 솔루션, 이 세 가지의 조합을 동시에 고려한다.

②   생성 및 유지 관리 비용을 위한 저장 구조를 고려한다.

③   부분 작업 부하에 대하여 삭제 권고안은 작성하지 않는다.

④   쿼리 재 작성이 최대화되고 빠른 갱신을 위해 구체화 된 뷰를 최적화한다.

⑤   빠른 갱신을 위해 구체화 된 뷰 로그를 추천한다.

⑥   테이블, 인덱스, 구체화 된 뷰에 대하여 파티셔닝을 추천한다.

⑦   유사한 인덱스들을 단일 인덱스로 결합한다.

⑧   다중 작업 부하 쿼리를 지원하는 권고안을 생성한다.

 

가능한 권고 사항

 

그림 11-15

 

SQL Access Advisor는 권고안의 전체 영향을 주의 깊게 고려하고 오직 알려진 작업 부하와 제공 된 정보만을

사용하여 권고안을 작성한다. 두 개의 작업 부하 분석 방법을 사용 할 수 있다.

①   Comprehensive : 이 접근 방식을 사용하면 SQL Access Advisor는 파티션, 구체화 된 뷰, 인덱스, 구체화

된 뷰 로그의 튜닝에 대하여 모든 사항을 검토한다. 작업 부하에는 애플리케이션 SQL 문장의 완전하고

대표적인 집합이 포함 되었다고 가정한다.

②   Limited : 위 방식의 접근 방법과는 달리, 제한된 작업 부하접근 방식은 해당 작업 부하에 오직 문제가 있는

SQL 문장만 포함되어 있다고 가정한다. 그러므로, 애플리케이션 환경의 일부분에 대한 성능을 향상시킬 수 있는 권고안을 검색한다.

 

Comprehensive 작업 부하 분석이 선택되면, SQL Access Advisor는 전체적으로 더 나은 튜닝 결과를 가져오지만

분석 시간은 길어질 수 있다. 표에서 보는 바와 같이 선택된 작업 부하 접근 방식은 어드바이저가 만들어 낼 수 있는

권고 사항의 종류를 결정한다.

 

참고 : 파티션 권고안은 해당 테이블이 최소한 10,000개의 행을 가지며, NUMBER 또는 DATE 타입의 조건절 또는

조인을 가진 작업 부하에 대해서만 동작한다. 파티션 권고안은 이러한 타입의 컬럼들에서만 생성 될 수 있다. 또한,

파티션 권고안은 단일 컬럼 인터벌(interval) 및 해시 파티션 에서만 생성 가능하다. 인터벌 파티션 권고안은 범위(range)

구문으로 출력될 수 있지만, 인터벌 (interval)이 디폴트이다. 해시 파티션은 오직 파티션 별 조인의 효과를 위해 수행된다.

 

SQL Access Advisor Session : Initial Option

그림 11-16

 

다음의 몇몇 그림은 일반적인 SQL Access Advisor 세션을 보여준다. Database Home 페이지 또는 개별 경보 또는 성능 문제를

해결하기 위한 링크를 포함하는 성능 페이지에서 Advisor Central 링크를 클릭하여 SQL Access Advisor에 접근 할 수 있다.

SQL Access Advisor는 사용자가 제공한 튜닝 대상 SQL 문장과 사용하기를 원하는 액세스 방식의 유형에 따라 여러 단계로 구성된다.

SQL Access Advisor : Initial Options 페이지에서 템플릿 또는 태스크를 선택하여 위저드를 시작하기 전에 디폴트 옵션을 확인

할 수 있다. Continue를 클릭하여 위저드를 시작하거나 Cancel을 클릭하여 Advisor Central 페이지로 돌아 갈 수 있다.

 

참고 : SQL Access Advisor는 권고안을 작성하는 중에도 중지 시킬 수 있으며 해당 결과를 검토할 수도 있다.

그림 11-17

 

만약, Initial Options 페이지에서 Inherit Options from a Task or Template를 선택하였다면, 기존 태스크를

선택하거나 SQL Access Advisor의 옵션을 상속 받기 위해 기존 템플릿을 선택 할 수 있다. 디폴트로

SQL ACCESS_EMTASK 템플릿이 사용된다. 해당 객체를 선택하고 View Options를 클릭하여 태스크

또는 탬플릿에 정의된 여러 옵션을 확인 할 수 있다.

 

SQL Access Advisor : Workload Source

 

그림 11-18

 

다음과 같은 3가지 다른 소스로부터 작업 부하를 선택 할 수 있다.

①   Current and Recent SQL Activity : 이 소스는 SGA(System Global Area) 내에 존재하는 SQL 문장에 해당한다.

②   Use and existing SQL Tuning Set : 사용자의 문장을 포함하는 SQL Tuning Set를 생성하고 사용 할 수도 있다.

③   Hypothetical Workload : 이 옵션은 어드바이저가 디멘전 테이블을 검색하고 작업 부하를 생성 할 수 있도록

스키마를 제공한다. 이 옵션은 스키마를 최초에 설계하는 경우에 매우 유용하다.

 

Filter Options 영역을 사용하여 작업 부하 소스를 필터링 할 수 있다. 필터 옵션은 다음과 같다.

①   Resource Consumption : Optimizer 비용, 버퍼 인기, CPU 시간, 디스크 인기, 수행 시간, 실행 횟수로 정렬 된 문장의 개수

②   Users

③   Tables

④   SQL Text

⑤   Module IDs

⑥   Actions

 

SQL Access Advisor : Recommendation Options

 

그림 11-19

 

Recommendations Options 페이지에서 SQL Access Advisor가 단일 액세스 방식을 기반으로 권고사항을 제한할지 여부를

선택 할 수 있다. Advisor에 의해 추천되는 구조의 유형을 선택 할 수 있다. 만약, 세 가지 가능한 구조 중의 어느

하나라도 선택하지 않으면, Advisor는 새로운 구조를 추천하는 대신 기존 구조를 평가한다. Advisor Mode 영역을 사용하면

두 가지 모드 중의 하나로 Advisor를 실행 할 수 있다. 이 모드들은 권고안의 품질 뿐 만 아니라 처리에 요구되는 시간에

영향을 미친다. Comprehensive Mode 에서 Advisor는 고품질의 권고안을 검색한다. Limited Mode에서 Advisor는

고비용 문장에 대해서만 동작하며 신속하게 실행되고, 후보 권고안들을 제한한다.

 

참고 : Advanced Options를 클릭하여 공간 제한, 튜닝 옵션, 디폴트 저장 위치를 설정 할 수 있는 옵션을 살펴 보거나 감출 수 있다.

 

SQL Access Advisor : Schedule 과 Review

 

그림 11-20

 

스케줄러에 여러 파라메터를 설정하여 새로운 분석 일정을 설정하고 실행 할 수 있다. 사용 가능한 옵션은 위 그림과 같다.

 

SQL Access Advisor : Results

 

그림 11-21

 

Advisor Central 페이지에서 자세한 분석 결과를 확인 할 수 있다. Advisor Central 페이지의 Results 영역에서 태스크

이름을 선택하여 Results for Task Summary 페이지에 접근 할 수 있으며, Access Advisor가 발견한 사항에 대한 개요를

살펴 볼 수 있다. 이 페이지는 전체 작업 부하 성능과 권고안에 의해 향상 될 잠재적인 쿼리 성능을 보여준다. 이 페이지는

실행된 문장의 횟수와 권고 횟수를 보여준다.

 

SQL Access Advisor : Results와 Implementation

 

그림 11-22

 

Access Advisor 태스크에 대한 결과의 다른 측면을 살펴보려면 페이지의 3가지 탭,

Recommendations, SQL Statements, Details 중의 하나를 클릭한다.

Recommendations 페이지에서 권고안의 각 항목을 세부적으로 살펴 볼 수 있다. 각 항목에 대하여

Select Recommendations for Implementation 테이블 내에서 중요 정보를 확인 할 수 있다.

사용자는 하나 이상의 권고안을 선택하고 해당 권고안의 구현 일정을 지정 할 수 있다.

 

만약, 특정 권고안의 ID를 클릭하면, Recommendations 페이지로 이동하고 지정된 권고 사항에 대한 모든 액션이

표시된다. 또한, 해당 문장의 테이블스페이스 이름을 수정 할 수도 있다. 모든 변경 사항이

완료되면 OK를 클릭하여 변경 사항을 적용한다. Recommendations 페이지에서 지정된 액션에 대해 Action 필드의

링크를 클릭하여 액션의 전체 내용을 확인 할 수 있다. Show SQL을 클릭하여 권고안

내의 모든 액션에 대한 SQL을 확인 할 수 있다. SQL Statements 페이지(여기에서는 보이지 않음)는 사용자에게

비용이 가장 많이 절감된 순으로 SQL 문장을 정렬하여 차트와 테이블로 표시해준다. 최상위

SQL 문장은 연관된 권고 사항을 구현함으로써 대부분 향상된다.

 

Details 페이지는 태스크가 생성될 때, 사용된 작업 부하와 태스크 옵션을 보여준다. 이 페이지는 해당 태스크가

실행되는 동안 기록된 모든 저널 엔트리( journal entries)를 보여준다.

 

또한, Schedule Implementation 버튼을 클릭하여 권고안의 구현 일정을 지정 할 수도 있다.

 

SQL 튜닝 루프

 

그림 11-23

 

Oracle Database 10g는 SQL Tuning Advisor를 소개하여 애플리케이션 개발자들이 SQL 문장의 성능을 향상시키는데 도움을 주었다.

Advisor는 비효율적으로 작성된 SQL 문장을 대상으로 한다. 또한, Advisor는 좀더 일반적인 문제를 대상으로 하는데, Optimizer는

정확한 관련 데이터 통계가 부족하면 좋지 않은 실행 계획을 만들어 SQL 문장이 비효율적으로 수행되기 때문이다. 모든 경우에

있어서 Advisor는 SQL 성능을 빠르게 향상시킬 수 있는 특별한 권고안을 작성하지만 해당 권고안의 구현 여부는 사용자에게 남겨둔다.

SQL Tuning Advisor에 추가하여, Oracle Database 10g는 시스템 내의 과부하 SQL 문장을 식별하는 자동화된 프로세스를 가지고

있다. 이 작업은 ADDM에 의해 수행되며, ADDM은 과부하 SQL 문장을 자동으로 식별한다. 그러나, 주요 문제는 여전히 남아있다.

비록 ADDM이 튜닝 할 몇몇 SQL 문장을 식별한 결과가 옳다고 하더라도 사용자들은 ADDM 리포트를 직접 살펴봐야 하고,

튜닝을 위해 해당 리포트에 대해 SQL Tuning Advisor를 실행하여야 한다.

 

자동 SQL 튜닝

 

그림 11-24

 

Oracle Database 11g는 SQL Tuning 프로세스를 더욱 자동화하였다. 이 프로세스는 사용자의 간섭 없이 문제가 있는

SQL 문장을 식별하고, 해당 문장에 대하여 SQL Tuning Advisor를 실행한 다음, 해당 문장을 튜닝하기 위해 SQL 프로필을

구현한다. Automatic SQL Tuning은 디폴트로 매일 저녁에 실행되는 Automatic SQL Tuning이라고 부르는 새로운 태스크를

통해 AUTOTASK 프레임워크를사용한다.

 

다음은 Oracle Database 11g내의 자동화 된 SQL 튜닝 프로세스에 대한 간략한 설명이다.

①   1 단계 : AWR Top SQL 식별을 기반으로 하여(4개의 서로 다른 시간대 : 지난 주, 지난 주의 모든 요일, 지난 주의 모든 시간,

단일 응답 시간에서 최상위 SQL 문장), Automatic SQL Tuning은 자동 튜닝을 목표로 한다.

②   2 단계 및 3 단계 : Automatic SQL Tuning 태스크는 유지 관리 윈도우 동안에 실행되며, 이전에 식별된 SQL 문장들은

SQL Tuning Advisor가 실행되어 자동으로 튜닝된다. 그 결과, 필요한 경우 SQL 프로필이 생성된다. 그러나, 어떠한 결정이라도

하기 전에 새로운 프로필은 주의 깊게 시험되어야 한다.

③   4 단계 : 아무 때라도 이러한 자동 튜닝 활동에 대하여 리포트를 요구 할 수 있다. 튜닝 된 SQL 문장을 검증하거나

생성된 자동 SQL 프로필을 제거 할 수 있는 옵션이 제공된다.

 

자동 튜닝 프로세스

 

그림 11-25

 

튜닝 프로세스가 진행되는 동안, 모든 권고 사항의 유형이 검토되고 보고되지만, SQL 프로필은 자동으로 구현 될 수

있다(ACCEPT_SQL_PROFILE 태스크 파라메터를 TRUE로 설정한 경우). 그렇지 않으면, 오직 SQL 프로필을 생성하는

권고안만 자동 SQL 튜닝 리포트에 기록된다. Oracle Database 11g에서 성능 향상 인자는 SQL 프로필을 구현하기

전에 최소한 3 이상으로 설정되어야 한다. 앞에서 언급한 바와 같이, Automatic SQL Tuning 프로세스는 SQL 프로필만

자동으로 구현한다. 다른 권고안(새로운 인덱스 생성, 오래된 통계 갱신, SQL 문장 재구성)은 SQL 튜닝 프로세스의

일부로서 생성되지만 구현되지는 않는다. 이 부분은 사용자가 직접 검토하고 적절 하다면 직접 구현해야 한다.

 

다음은 자동 튜닝 프로세스에 대한 간단한 설명이다.

 

튜닝은 문장 단위로 수행된다. 오직 SQL 프로필링이 구현 될 수 있기 때문에 그러한 권고안이 전체 작업 부하에 미치는

영향에 대해서는 고려할 필요가 없다. 각 문장에 대하여(중요도 순으로), 튜닝 프로세스는 다음 단계를 각각 수행한다.

1.   SQL Tuning Advisor를 사용하여 문장을 튜닝한다. SQL 프로필을 검색하고, 발견되면 해당 프로필에 대하여

기본 Optimizer 통계가 최신 버전인지 검증한다.

2.   SQL 프로필이 추천되면 다음 사항을 수행한다.  

A.   프로필이 있을 때와 없을 때 각각 해당 문장을 실행하여 새로운 SQL 프로필을 시험한다. 

B.    SQL 프로필이 생성되고, Optimizer가 해당 문장에 대해 다른 실행 계획을 선택한다면, Advisor는

SQL 프로필의 구현을 결정하게 된다. 이 과정은 그림의 플로우차트에 표시되어 있다.

 

여기서, 비록 이득 임계값이 CPU 및 I/O 시간의 합계에 적용될지라도 각 통계에 저하가 발생하면 SQL 프로필은

수용되지 않는다. 즉, CPU 및 I/O 시간 의 합계에 3배 이상의 시간 향상이 있어야 하고, 어느 하나의 통계치라도

더욱 나빠 지면 안된다. 이런 방식으로 해당 문장은 CPU 또는 I/O에 경합이 발생하더라도 프로필이 없는 경우보다 빠르게 실행된다.

3. 만약, 오래됐거나 누락된 통계가 발견되면 GATHER_STATS_JOB으로 이러한 정보를 사용 가능하게 만든다.

 

튜닝 권고안의 자동 구현은 SQL 프로필로 제한되었는데, 그 이유는 위험을 감소시키기 위해서이다. 해당 구현을 되돌리는 것은 어렵지 않다.

 

참고 : 모든 SQL 프로필은 표준 EXACT 모드에서 생성된다. 모든 SQL 프로필들은 CURSOR_SHARING 파라메터의

현재값과 일치되어야 하고 조사된다. 작업 부하에 대하여 CURSOR_SHARING 파라메터를 직접 설정하여야 한다.

 

Automatic SQL Tuning 제어

 

다음은 Automatic SQL Tuning 태스크를 PL/SQL을 이용하여 제어하는 예이다.

 

BEGIN

dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK','LOCAL_TIME_LIMIT', 1400);

dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK','ACCEPT_SQL_PROFILES', 'TRUE');

dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK','MAX_SQL_PROFILES_PER_EXEC', 50);

dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK','MAX_AUTO_SQL_PROFILES', 10002);

END;

 

이 예제에서 ?지링 3개의 파라메터는 Automatic SQL Tuning 태스크에서만 지원된다. 또한, LOCAL_TIME_LIMIT 또는

TIME_LIMIT와 같은 파라메터를 사용 할 수 있으며, 이 파라메터는 전통적인 SQL 튜닝 태스크를 위한 유효한 파라메터이다.

중요한 하나의 예는 TEST_EXECUTE 파라메터를 사용하여 테스트 실행 모드(시간 절약을 위해)를 비활성화하고 성능에 관련된

결정을 위해서만 실행 계획의 비용을 사용하는 것이다. 또한, Automatic SQL Tuning 태스크의 실행 시점과 사용하도록

허용 할 CPU 자원의 양을 제어 할 수 있다.

 

Automatic SQL Tuning 태스크

 

그림 11-26

 

앞에서 언급한 바와 같이, Automatic SQL Tuning은 Automatic SQL Tuning이라고 부르는 자동화된 유지 관리 태스크로서

구현된다. Automated Maintenance Tasks 페이지에서 Automatic SQL Tuning 태스크의 ?지링 실행과 관련된 고급 정보를

확인 할 수 있다. 이 페이지를 오픈하려면 Database Control Home 페이지에서 Server 탭을 클릭한다. Server 탭 페이지가

열리면 Tasks 영역에서 Automated Maintenance Tasks 링크를 클릭한다.

 

Automated Maintenance Tasks 페이지에서 사전에 정의한 태스크를 확인 할 수 있다. 해당 태스크 (그림과 같이)에 대하여

더 많은 정보를 얻으려면 해당 링크를 클릭한다. Automatic SQL Tuning 링크 또는 가장 ?지링 실행 아이콘(시간선의 녹색 영역)을

클릭하면 Automatic SQL Tuning Result Summary 페이지가 오픈된다.

 

Automatic SQL Tuning 구성

 

그림 11-27

 

Automatic SQL Tuning Settings 페이지를 이용하여 다양한 Automatic SQL Tuning 파라메터를 설정 할 수 있다.

해당 페이지를 찾아가려면 Automated Maintenance Tasks 페이지에서 Configure 버튼을 클릭한다. Automated

Maintenance Tasks Configuration 페이지에서 Oracle Database 11g에 포함된 다양한 유지 관리 윈도우를 확인 할 수 있다.

기본적으로 Automatic SQL Tuning은 MAINTENANCE_WINDOW_GROUP 내에서 사전에 정의된 유지 관리 윈도우들을

실행한다. 일주일의 특정 요일에 대하여 Automatic SQL Tuning을 비활성화 할 수 있다. 이 페이지에서 각 Window를

편집하여 해당 특성을 변경 할 수도 있다. Edit Window Group을 클릭해서도 변경 할 수 있다.

Automatic SQL Tuning Settings 페이지를 찾아가려면 Task Settings 영역의 Automatic SQL Tuning에 해당하는 행의

Configure 버튼을 클릭한다.

 

Automatic SQL Tuning Settings 페이지에서 그림에서 보여지는 파라메터들을 설정 할 수 있다. 기본적으로

Automatic Implementation of SQL Profiles는 선택되어 있지 않다.

 

참고 : 만약 STATISTICS_LEVEL을 BASIC으로 설정하였다면 DBMS_WORKLOAD_REPOSITORY를사용하여 AWR 스냅샷을

끄거나, AWR 보존 기간이 7일보다 작다면 Automatic SQL Tuning을 중지시킨다.

 

Automatic SQL Tuning : Result Summary

 

그림 11-28

 

또한, Automatic SQL Tuning Result Summary 페이지는 Automatic SQL Tuning 태스크를 제어 할 수 있도록 다양한 요약

그래프를 포함하고 있다. 예제는 위 그림과 같다. Overall Task Statistics 영역의 첫 번째 차트는 지정된 기간 동안에 발견된

유형을 세분화하여 보여준다. Time Period 목록에 값을 지정하여 원하는 시기에 보고서가 생성하도록 기간을 제어 할 수 있다.

예제에서는 Customized가 사용되었으며, 여기에는 가장 ?지링에 수행된 결과를 보여준다. 지금까지 수행된 모든 태스크를

살펴보려면 ALL을 선택 할 수 있다. Advisor가 가지고 있는 튜닝 이력 내에서 사용자는 과거 어느 시점에서라도 이러한 결과를

요청 할 수 있다. View Report를 클릭하여 리포트를 생성한다.

 

Breakdown by Finding Type 차트에서 구현 가능한 SQL 프로필들을 명확히 확인 할 수 있다. 비록, 많은 프로필들이 추천되지만

앞서 설명한 이유로 인하여 모든 프로필들이 자동으로 구현되지는 않는다. 이와 유사하게 인덱스 생성과 다른 타입에 대한

권고사항은 구현되지 않는다. 그러나, Advisor는 사용자가 해당 권고 사항의 구현을 나중에 희망 할 수 있으므로 모든 권고 사항에

대한 이력 정보를 유지한다. Profile Effect Statistics 영역에서 Tuned SQL DB Time Benefit 차트를 확인 할 수 있으며, 이 차트는

구현된 프로필과 다른 권고 사항에 대해 구현 전 및 후의 DB 시간을 보여준다.

 

Automatic SQL Tuning : Result Details

 

그림 11-29

 

Automatic SQL Tuning Result Details 페이지에서 각각의 자동 튜닝된 SQL 문장들의 중요 정보를 확인 할 수 있다. 여기에는

SQL 텍스트 및 SQL ID, SQL Tuning Advisor에 의해 수행된 권고 사항의 유형, 검증된 이득 퍼센트, 특정 권고 사항이 자동으로

구현되었는지 여부, 권고안의 날짜가 포함된다. 이 페이지에서 해당 SQL ID 링크를 클릭하여 SQL 문장을 자세하게 살펴보거나,

SQL 문장 중에 하나를 선택하여 View Recommendations 버튼을 클릭하여 해당 문장에 대한 구체적인 권고 사항을 확인 할 수 있다. 

 

참고 : 각 권고 사항에 대해서 보여지는 이득 퍼센트는 다음 공식에 의해서 계산된다. bnf% = (time_old – time_new) / (time_old)

이 공식에서 3배의 이득은 66%가 된다(예를 들어, time_old=100, time_new=33이라고 가정). 그래서, 시스템은 66%가 넘는

이득을 갖는 모든 프로필을 구현한다. 이 공식에 의하면 98%는 50배의 이득을 나타낸다.

 

Automatic SQL Tuning Result Details: Drilldown

 

그림 11-30

 

Recommendations for SQL ID 페이지에서 해당 권고 사항을 확인하고 직접 구현 할 수 있다.

SQL Test 링크를 클릭하여 SQL Details 페이지에 접근 할 수 있으며, 여기서 튜닝 이력 뿐만 아니라 SQL 문장과 관련된

실행 계획 제어를 확인 할 수 있다.

이 그림에서 Automatic SQL Tuning에 의해 튜닝된 문장과 자동으로 구현된 관련 프로필을 확인 할 수 있다.

 

Automatic SQL Tuning 고려사항

Automatic SQL Tuning은 시스템에서 발생하는 모든 SQL 성능 문제를 해결하려고 하지 않는다.

이 기능은 다음과 같은 유형의 SQL은 고려하지 않는다.

①   임의(Ad hoc) 또는 거의 반복되지 않는 SQL : 만약, SQL이 동일한 형태로 반복 실행되지 않는다면,

Advisor는 해당 문장을 무시한다. 1주일 동안 반복되지 않는 SQL은 고려하지 않는다.

②   병렬 쿼리

③   수행 시간이 긴 쿼리(프로필 생성 후) : SQL 프로필이 작성된 후, 쿼리의 수행 시간이 너무 길어지면,

시험 실행은 현실적이지 않으므로 Advisor에 의해 무시된다. 이것은 Advisor가 모든 수행 시간이 긴 쿼리를

무시한다는 것을 의미하지는 않는다. 만약, Advisor가 1시간이 소요되는 쿼리를 수 분 내에 실행 할 수 있는

SQL 프로필을 발견 할 수 있다면, 시험 실행은 여전히 가능하기 때문에 해당 문장은 고려된다. Advisor는

이전 실행 계획이 새로운 실행 계획보다 좋지 않다는 것을 결정하기 위해 이전 실행 계획을 오랫동안 수행

할 수도 있으며, 이전 실행 계획이 종료되기까지 대기하지 않고 시험 실행을 종료한다.

④   재귀 SQL 문장

⑤   INSERT SELECT 또는 CREATE TABLE AS SELECT와 같은 DML 완전한 임의 SQL을 제외하고 이러한 제한은

Automatic SQL Tuning에만 적용된다. 그러한 문장들은 직접 SQL Tuning Advisor를 실행하여 튜닝 할 수 있다.