Trend Oracle Database 11g의 새로운 성능 튜닝 인프라 및 활용 방안 Autumn 2010 �

국적이나 세대, 업종에 관계없이 데이터베이스 관리자에게 있어 영원한 화두는 시스템 최적화와 장애 예방이다. 근본적인 이야기지만 데이

터베이스를 운영할 때 최적화, 안정화된 상태를 유지하기 위해서는 기준이 되는 성능의 베이스라인이 있어야 한다. 또한, 변화에 유연하게

대처하고 문제를 빨리, 그리고 정확하게 식별하여 해결해 나가기 위해서는 과학적인 성능 관리 및 문제 해결 접근법을 반복적으로 적용해

나가는 과정이 반드시 필요하다. 따라서 관리자 스스로 필요한 방법론을 숙지하고 생산성 향상을 위한 신기술을 적절하게 사용할 수 있도

록 꾸준한 관심을 가질 필요가 있다.

실제로, 운영 중인 시스템에 성능 저하가 오는 근본적인 원인은 무척이나 다양하다. 최적기 통계의 변화나 적절하지 않은 접근 경로의 사용,

Oracle성능 관리 방법론

Queueing 이론

오라클의 성능 관리 방법론은 널리 알려진 Queueing 이론에 기반하고 있다. Queueing 이론은 다음과 같은 흐름으로 묘사되

는 소위 Queueing 시스템을 다룬다.

1. 클라이언트들이 시스템에 Random하게 도착한다.

2. 도착한 클라이언트들은 일단 Queue에 들어가 대기 시간이 최소화되도록 최적으로 설정된 우선순위에 따라 클라이언트가

마침내 서비스 차례를 기다리게 된다.

3. 대기 시간이 최소화되도록 최적으로 설정된 우선순위에 따라 클라이언트가 마침내 서비스를 받고 시스템을 떠나게 된다. 일

반적으로 클라이언트/서버 모델이 적용되는 임의의 시스템은 Queueing 시스템으로 볼 수 있으며, 데이터베이스 역시 이에

해당한다고 볼 수 있을 것이다. Queueing 이론은 이러한 Queueing 시스템의 성능을 수학적으로 분석/예상하고자 한다. 하

지만 복잡한 세부 사항들을 뒤로 하면 Queueing 시스템의 성능은 다음과 같은 공식 하나로 깔끔히 요약된다.

클라이언트가 시스템에 도착한 후 떠날 때까지의 전체 시간을 Response Time

으로 정의하면, Response Time은 실제 서비스를 받는데 걸린 Service Time과

서비스를 받기까지 대기해야 했다면 그에 소모된 Wait Time을 합한 것에 해당

한다. 이와 같이 볼 때 Queueing 시스템의 튜닝 목표는 명확하다. 그것은 바로

Response Time을 줄이는 것이다. 이때 가장 큰 병목부터 제거하는 것이 중요하다.

<표 1>은 Wait Time을 줄이는데 우선 주력해야 하는 케이스를 나타내고 있다.

이 상황에서 만일 Service Time을 줄이는데 초점을 맞추어 그것을 반으로 줄인다 하더라도 전체 Response Time은 겨우 10%

가 감소할 뿐이다. 반면에 Wait Time을 반으로 줄일 수 있다면 전체 Response Time은 40%가 감소된다. 물론 이 경우에도

Wait Time 중 가장 큰 비중을 갖는 대기 요인부터 다루도록 해야 할 것이다.

Oracle Database 11g의

새로운 성능 튜닝 인프라 및 활용 방안� 저자 - 강송희 컨설턴트, 한국오라클 DB사업부(songhee.kang@oracle.com)

Response Time = Service Time + Wait Time

<표 1> Response Time의 구성요소

Response Time

WaitTime ServiceTime

80% 20%

� Autumn 2010 Oracle Database 11g의 새로운 성능 튜닝 인프라 및 활용 방안 Trend

잘못된 SQL문의 사용, 자원과 경합에서 오는 문제, 병렬 실행시의 문제 등이 그 예다. 이러한 다양한 원인을 식별하고 해결하여 시스템의

SLA를 충족시키기 위해서 오라클은 이론적으로는 나침반이 될 수 있는 견고한 과학적 접근법을 제시하고 있으며 실무적으로는 성능 튜닝

기반 구조와 다양한 도구들을 제공하고 있다. 본고에서는 오라클의 성능 관리 방법론과 OracleDatabase 11g 새로 도입되었거나 향상된

성능 튜닝 기반 구조 및 그 활용 방안에 대하여 알아보도록 하자.

앞으로의 전개 순서는 먼저 오라클의 성능 관리 방법론을 소개하고, 이를 위해 어떤 통계적 모형이 존재하고 어떤 기반 구조가 구축되어 있

는지를 살펴본 후, OracleDatabase11g 새롭게 향상된 성능 최적기 기능과 그 활용방안을 알아보도록 하겠다.

DBTime

오라클의 성능 관리 방법론이 Queueing 이론에 기반 한다는 말의 정확한 의미는 무엇일까? 그것은 DB의 성능 문제를 언제나

시간의 관점에서 접근해야 한다는 것이다. 여기서 필요한 개념이 바로 DB Time이다. DB Time은 DB를 하나의 Queueing 시

스템으로 바라볼 때 그 Response Time에 해당한다. 따라서 DB를 튜닝한다는 것은 곧 DB Time을 줄여나가는 것을 의미한다.

DB Time을 보다 면밀히 정의하면 사용자 프로세스들이 DB 단에서 CPU I/O 자원을 사용하면서 능동적으로 작업을 수행하

거나(Actively Working) 아니면 능동적으로 대기하면서(Actively Waiting) 소요

한 시간을 의미한다.

DB Time을 이해하기 위해 사용자 관점에서 본 총 Response Time과 DB 자체의

Response Time을 비교해 보기로 하자.

<그림 2>는 사용자 관점에서의 총 Response Time내에서 DB 계층에서 소모되

는 Response Time의 구간을 보여주고 있다. DB 관점에서 보았을 때 Middle-tier

나 Client-tier에서 소요되는 시간과 사용자의 Think Time은 모두 DB Idle Time

이라고 간주할 수 있다.

DB Time의 증감에 대한 일반적인 경향은 다음과 같이 세 가지로 요약할 수 있다.

● 시스템에 걸리는 총 부하가 증가할수록 DB Time도 증가한다. 일반적으로 사용자가 많아질수록 호출 수가 많아져 DB Time

이 증가하고, 트랜잭션이 커질수록 호출 시간이 길어져 DB Time이 증가한다.

● 성능이 저하될수록 DB Time이 증가한다. 일례로, I/O Time이 증가하거나 Application 성능이 저하될 때 대기 시간이 길어

짐에 따라 DB Time도 증가한다.

● 호스트가 CPU-bound일 때 Foreground 프로세스들의 능동적인(Active) Run-queue time을 모두 가산하므로 Wait Event

Time이 실제보다 부풀려 계산될 수 있으며, 따라서 DB Time이 실제보다 늘어날 수 있다.

총 DB Time은 모든 세션의 DB Time을 합한 것으로, 일반적으로 사용자가 느끼는 Wall Clock Time(사용자가 벽시계를 바라

보며 재는 시간에서 유래한 용어)보다 훨씬 커질 수 있다.

<그림2> Application Response Time의 구성요소

ApplicationResponseTime

Database Response TimeClient App Response Time

App Server Response Time

Service Time

UserClicksonWebpage BrowserDisplaysResultService Time Wait Time

Wait Time

App Server Response Time

Client App Response Time

Trend Oracle Database 11g의 새로운 성능 튜닝 인프라 및 활용 방안 Autumn 2010 �

ActiveSession

기본적으로 데이터베이스 시스템을 클라이언트와 서버 모델로 바라 볼 때, 데이터베이스 자원을 사용하려 하는 모든 클라이언

트는 원하는 작업을 처리하기 위해 결과적으로 DB Time을 소비하게 된다. 이러한 클라이언트의 활동을 정량화하여 다루기 위

해 필요한 개념이 Active Session이다. Active Session이란 데이터베이스 호출 내에서 현재 실제로 DB Time을 소모하고 있는

Session을 의미하며, 실제로 DB Time을 소모하고 있다는 것은, 쉽게 풀어 쓰면 CPU를 사용하거나(Actively Working) 혹은

Non-idle wait으로 대기 중(Actively Waiting)이라는 것을 말한다. 따라서 위 정의에 따라 특정 세션의 평균 활동 지수는 단순

히 세션의 총 DB Time을 Wall Clock Time으로 나눔으로써 구할 수 있겠다.

<표 3>은 시간에 따라 Active Session이 사용하는 DB Time을 가시화하여 보여

주고 있으며, 성능 문제를 분석하는 데 가장 근본적인 지표가 되는 DB Time의

흐름을 한 눈에 볼 수 있다.

OraclePerformanceMethodology:DBTimeMethod

이처럼, 오라클의 모든 성능 관리 방법론은 DB Time에 기반을 두고 있다. 주어

진 작업부하에 대하여 시스템을 어떻게 튜닝할 것인가에 관한 질문을 던질 때, 개

략적으로 다음과 같은 논리적인 순서를 생각하라.

● 가장 많은 DB Time을 소모하는 작업을 식별한다.

● 그 중 자원이나 수용 능력과 관련된 병목을 식별해 낸다.

● 해당 작업 부하에 소모된 불필요한 DB Time을 줄여 나간다.

이는 곧 식별, 필터링, 선택과 집중을 통한 해결 방안 도출이 되겠다.

오라클은 공식적으로 위 방식에 따른 Best Practice가 반영된 Workflow를 제

공하는 Enterprise Manager를 성능 관리 툴로 사용하기를 권장하고 있으며,

Enterprise Manager는 아래와 같이 몇 번의 클릭으로 쉽게 성능 저하의 원인을

추적하고 해결할 수 있는 인터페이스를 제공하고 있다. <그림 4>는 Enterprise

Manager의 성능 탭에서 볼 수 있는 첫 화면이다. 여기서는 앞선 내용에서 다룬

이론적 모델에 근거한 그래프를 보여주고 있다.

<그림 4>의 그래프에 대하여 좀 더 부연 설명하자면, 시간에 따른 Active Session

의 모습을 나타내고 있다. 그래프의 특정 색깔을 띤 면적 부분은 DB Time의 양

을 나타낸다. 이러한 DB Time은 관련한 Wait Class에 따라 분류하여 각각 다른 색깔로 표시된다. 성능 분석을 위해 그래프의

가장 큰 면적을 클릭하여 현재 어떤 활동으로 DB Time이 많이 소비되고 있는지, Top-down 방식으로 상세 관련 정보를 드릴

다운할 수 있다.

Oracle성능 관리를 위한 통계적 모형과 기반 구조

앞부분에서 성능 관리를 위해 필요한 핵심적인 개념과 방법론을 다루었다면, 이제는 성능 모니터링과 분석에 필요한 기반 데이

Average Active Sessions= (Total DB Time)/(Wall Clock Time)

<그림 4> Response Time의 구성요소

<표 3> 시간에 따른 Active Session의 모습

User 1User 2User 3User n

t0 t1

Active Sessions over time

4321

� Autumn 2010 Oracle Database 11g의 새로운 성능 튜닝 인프라 및 활용 방안 Trend

터를 확보하게 해 주는 통계적 모형과 이를 바탕으로 오라클 데이터베이스 내부에 구축된 기반 구조에 대해서 알아보도록 하자.

Oracle성능 관리를 위한 통계적 모형의 발전사

먼저 현재 정립된 통계적 모형을 이해하기 위해서 어떠한 과정을 거쳐 왔는지 그 역사를 한 번 살펴보도록 하자.

Pre-Historic(v5)미성숙 단계로서 어떠한 통계적 모형도 존재하지 않았던 시기라 할 수 있다. 기반이 되는 어떠한 이론적 근거

나 방법론도 존재하지 않았으므로 애플리케이션 자체를 디버깅하는데 엄청난 노력이 소요되었다.

DarkAges(v�)흔히 볼 수 있는 간단한 선형적 모델을 사용할 수 있도록 카운터를 도입한 단계이다. Session Logical Reads 혹

은 Physical Reads등과 같은 메트릭을 정의하여 누적값을 뽑아내려는 시도가 일어났다. 초기 단계의 OS에서 타이머는 그 구현

비용이 굉장히 비쌌기 때문에 대안적 장치로서 카운터부터 시작하게 된 셈이다. 카운터를 사용할 때의 문제점은 여러 가지가

있겠지만, 가장 대표적인 것은 유효한 누적값 자체가 인스턴스 시작 이후에 쌓인다는 것이며, 따라서 그 값의 실제적 효용의 범

위가 좁아질 수 밖에 없었다. 이때, BSTAT/ESTAT이라고 불리는 도구들이 개발되기 시작했는데, 이것은 어느 정도 경험이 쌓

인 개발자나 컨설턴트가 벤치마크 시 적용하던 방식이었다. 기본 카운터 값에 Delta 개념을 적용하여 원하는 특정 기간(Period)

동안에 유효한 작업 부하 통계를 산출할 수 있었다. 스크립트 기반의 툴이 개발되었고, SQL Trace등을 활용하였다.

Renaissance(v�)카운터 방식에서 타이머 방식으로 전환되는 과정이라 할 수 있겠다. 이때 현재 우리가 말하는 실제적인

“Timed Statistics”를 사용할 수 있는 빠른 타이머를 내장한 SMP 박스(SUN, Sequent, Pyramid, DEC)가 보급되었다.

Wait Event개념이 처음 소개되었으며, 운영 시스템 하에서 STATSPACK이 개발되었다.

Modernity(v10~)DB Time기반의 튜닝 모델이 정립되었다. 데이터베이스에서 사용하는 DB Time의 기본 개념과 종합적인 튜

닝 방법론이 개발되었고, 이러한 방법론들을 적용하기 위해 필요한 기본적인 Timed Statistics, DB Time Model Statistics를 포

함한 통계적 모형을 종합적으로 제공하는 AWR(Automatic Workload Repository)이라는 기반 구조를 구축하게 되었다.

AWR(AutomaticWorkloadRepository)

이와 같은 과정을 거쳐, 오라클은 미리 정의된 필수적인 통계적 데이터를 수집할

수 있는 기반 구조인 AWR을 데이터베이스 내에 구축하게 되었다.

AWR이란 오라클 데이터베이스에 내장된 작업 부하와 성능 통계를 저장하는 저

장소를 의미한다. 기본 설정에 의해 자동으로 한 시간마다 작업 부하에 관련한 데

이터를 수집하게 되는데 설정에 따라 수동으로 원하는 시점에 수집할 수도 있다.

또한, 기본 설정에 의해 8일간 보관된다. 데이터 보존 기간은 물론 변경할 수 있다.

위 데이터는 모두 SYSAUX 테이블스페이스에 저장되며 오라클 데이터베이스에

의해 자동으로 공간 관리가 가능하다. <그림 5>는 AWR의 구조를 나타내고 있다.

이쯤에서 AWR에 수집되는 데이터에는 어떤 것들이 있는지 궁금해하는 독자들이

있을 것이다. AWR에 수집되는 데이터를 크게 분류하여 정리해 보면 다음과 같다.

● Base Statistics : 다양한 레벨(System, Session, SQL, Segment)에서의 기본 누적 통계

● OS Statistics : CPU, Memory, I/O 등에 대한 통계

● Time Model Statistics : Background가 소비한 시간 및 앞서 언급한 DB Time

● Metrics : 타 통계치에서 2차적으로 도출해 낸 결과로써 대부분 비율의 형태를 띠고 있다. 경보를 발생시키거나 EM상의 그

래프를 그리는데 사용된다.

● Wait Events : 자세한 설명이 소주제 하에 이어진다.

● ASH : 자세한 설명이 소주제 하에 이어진다.

<그림 5> AWR Infrastructure

In-memorystatistics

SGA

Built-in,automaticperformancestatics

datawarehouse

AWR statistics ASH

BG

BG

FG

FG

ADDMfindstopproblems

MMON

DBA DBA_%

SYSAUX

Eightdays

07:00 a.m.

08:00 a.m.

09:00 a.m.

10:00 a.m.

AWRData

Snapshot 1

Snapshot 2

Snapshot 3

Snapshot 4

V$

Trend Oracle Database 11g의 새로운 성능 튜닝 인프라 및 활용 방안 Autumn 2010 10

WaitEvent

앞서 언급했던 DB Time은 다시 다음과 같은 공식으로 간단히 표현할 수 있다.

여기서 Wait Time(대기 시간)이란 작업을 수행하기 위한 필요한 특정 자원을 사용하기 위해 대기하는 데 걸리는 시간을 의미

한다. Wait Event란 대기하는 행위를 나타내며 성능 분석을 위해 관련 정보를 아래와 같이 표현한다.

● 이벤트 이름

● 해당 자원을 표현할 수 있는 세 개의 파라메터(P1, P2, P3)

Wait Event와 관련한 통계에는 Wait Event의 횟수와 Wait Time 등이 있겠다.

모든 Wait Event는 12개의 Wait Event Class로 분류되며 Oracle Database 11g에서는 더욱 세분화되고 방대해졌다.

이러한 모든 Wait Event를 다 알 수는 없더라도, 관리자는 정상적인 시스템 운영 상황에서 어떠한 Wait Event들이 나타나고,

얼마 정도의 시간을 대기하는지에 대해 이해하고 있어야 한다.

Wait Event에 대한 더 자세한 정보는 오라클이 제공하는 Reference Manual을 참고하라.

ASH(ActiveSessionHistory)

AWR에 수집되는 기본 데이터 집합 중 하나인 ASH는, 매초 활성 세션(Active Session)을 메모리에 샘플링한 결과로, 커널 구

조에 직접 접근하여 가져오는 정보이다. 세션의 작업(Activity)에 따라 데이터량이 달라질 수 있고, 10초 이후 디스크로 플러쉬

되어 AWR내에서 스냅샷과 함께 히스토리가 관리된다. ASH에 담긴 정보를 활용하면 AWR 리포트의 Load Profile 섹션에서

시작한 성능 분석을 완성할 수 있다. 특히, 순간적인 혹은 일시적으로 일어나는 문제의 타겟 성능 분석을 가능하게 해 준다.

ASH에 포함되는 정보를 정리해 보면 다음과 같다.

● SID

● SQL ID

● Program, Module, Action

● 상세 Wait Event 정보

이렇게 AWR에 수집되는 모든 통계적 데이터는 성능 분석의 중요한 토대가 되며, 이를 성능 분석에 유용한 정보로 가공하기 위

해 AWR Report라는 보고서를 작성하게 된다.

AWR(AutomaticWorkloadRepository)Report

이처럼 AWR Report는 성능 분석을 위해 필요한 필수적인 툴로써, 두 개의 선별된 AWR 스냅샷 사이의 작업 부하 및 성능 통

계치에 대한 분석 리포트를 의미한다. 미리 정의된 html 형식의 AWR 리포트 군이 제공되며, Enterprise Manager의 인터페이

스를 통해 쉽게 작성할 수 있다.성능 분석의 시작이라 할 수 있는 AWR 리포트의 기본 프로파일의 예는 <표6>과 같다.

<표6>의 Load Profile에서 문제의 소지가 있다고 의심되는 성능 지표가 있을 경우 리포트 하단에서 관련된 상세 정보를 확인할

수 있다. AWR 리포트는 특정 시점 간의 비교 리포트 및 특정 주기 간의 비교 리포트 등의 형태를 띠고 있으며, RAC 환경에서

는 인스턴스 별로 해당 리포트를 생성할 수 있다.

DB Time = CPU Time + Wait Time

11 Autumn 2010 Oracle Database 11g의 새로운 성능 튜닝 인프라 및 활용 방안 Trend

OracleDatabase11g에 새롭게 향상된 성능 최적기 기능과 활용방안

앞 주제에서 다룬 기반 구조를 활용하여, Oracle Database 11g는 통계치를 수집

하고 성능을 튜닝하기 위한 향상된 방법을 제공하고 있다. 본고에서 모든 기능을

다루기에는 무리가 있어, 대표적인 것을 선별해 보았다.

DBMS_STATS

그동안 비용 기반 최적기에 필요한 성능 통계를 수집하는 것이 검증 작업을 제외

하더라도 번거롭거나 많은 시간을 요할 수 있었다. Oracle Database 11g에서는

보다 향상된 통계 수집을 위한 도구를 지원하고 있다.

● 이전 버전에서는 통계치를 수집할 때 DBMS_STATS.SET_PARM 프로시저

를 사용하여 DBMS_STATS.GATHER_*_STATS 프로시저의 기본 파라메터

설정을 바꿔야 했다. Oracle Database 11g에서는 SET_*_PREFS라는 새로운

프로시저가 제공되어 다양한 범위(테이블, 스키마, 데이터베이스, 글로벌) 수

준의 통계치 수집을 지원한다.

● 이전 버전에서는 테이블이나 스키마 레벨로 통계치에 Lock을 걸 수 있도록 지

원하였으나, Oracle Database 11g에서는 파티션 레벨로 통계치에 Lock을 걸

수 있게 되었다.

● Oracle Database 11g에서는 통계치 산출 시 새로운 샘플링 알고리즘을 도입하여 수집 속도의 향상을 가져왔다.

AUTO_SAMPLE_SIZE 지정 시 100%의 정확도로 계산할 때 기존 10% 샘플 수준의 속도로 수집이 가능해졌다.

InvisibleIndexes

11g에서 새로 도입된 Invisible 인덱스를 생성하면 실제로는 해당 인덱스를 사용하지 않지만, 최적기에 의해 잠재적인 접근 경

로로 사용될 수 있는지 검증해 볼 수 있다. 운영 시스템의 인덱스 생성에 조심스럽게 접근해야 할 경우 사용하길 권장한다.

● Invisible 인덱스를 생성하는 방법은 다음과 같다.

CREATE INDEX … INVISIBLE

● 잠재적인 접근 경로가 될 수 있는지 검증하기 위해서는 다음과 같은 세션 레벨의 환경 설정이 필요하다.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

● 인덱스가 유용하겠다는 판단이 섰을 때 해당 인덱스를 실제로 사용하기 위해서는 해당 인덱스를 드랍하거나 다음과 같이

가시화하면 된다.

ALTER INDEX … VISIBLE;

PendingStatistics

DBA는 새로 통계치를 수집하여 적용했을 때 최적기가 특정 작업 부하에 기존과 다른 실행계획을 작성하지 않는지, 새롭게 작

성된 실행 계획이 성능 저하를 가져오지 않는지 반드시 검증해 보아야 할 의무가 있다.

Oracle Database 11g에서는 새로 수집한 통계를 공식적으로 ‘PUBLISH’하기 전에는 해당 통계를 최적기가 사용하지 않도록

설정할 수 있게 되었으며, 이 기능을 Pending Statistics라 통칭한다.

활용 방법은 다음과 같다.

<표 6> AWR Report의 기본 프로파일 섹션

WORK:OADREPOSITORYreportfor

Trend Oracle Database 11g의 새로운 성능 튜닝 인프라 및 활용 방안 Autumn 2010 12

● 대상 테이블 및 스키마의 PUBLISH속성을 FALSE로 설정하고 통계를 수집한다.

DBMS_STATS.SET_TABLE_PREFS(‘SH’,‘SALES’,‘PUBLISH’,‘false’);

● 새 통계치에 대한 기존 작업부하의 실행계획을 검증한다.

ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;

● 검증에 성공한 후 해당 통계치를 PUBLISH한다.

DBMS_STATS.PUBLISH_PENDING_STATS(‘SH’,‘SALES’);

AdaptiveCursorSharing

기존 10g에서 제공하던 Bind Peeking기능은 바인드 값이 달라짐에 따라 최적 실행 계획이 바뀔 수 있다는 점에서 문제가 발생

할 수 있다. Oracle Database 11g에서는 각 바인드 값 집합에 대하여 최적화된 실행계획을 자동 적용할 수 있도록 하는 Adaptive

Cursor Sharing 기능을 제공하고 있다. 이때 가능하면 사용 가능한 Cursor를 최대한 공유할 수 있도록 설계되었다.

IncrementalGlobalStatistics

Oracle Database 11g 이전에는, 새로운 파티션을 추가하거나 혹은 기존의 몇 개 파티션에 대해 데이터를 수정하는 경우에 테이

블 레벨의 통계치를 재수집하기 위해 테이블 전체를 모두 스캔해야 했다. Oracle Database 11g에서는 이 문제를 개선하기 위해

Incremental Global Statistics라는 기능을 제공하며, 사용법은 아래와 같다.

● 대상 파티션 테이블의 Table Preference 중 INCREMENTAL을 TRUE로

설정한다.

DBMS_STATS.SET_TABLE_PREFS(‘SH’,’SALES’,‘INCREMENTAL’,

‘TRUE’);

● 통계치를 수집할 때 GRANULARITY 파라메터를 AUTO로 설정하여 통계를

수집한다.

<표 7>은 Incremental Global Statistics가 이루어지는 과정을 설명하고 있다.

SQLPlanManagement

Oracle Database 11g 이전에는 운영 중에도 예기치 못한 변화로 인하여 실행 계

획이 변경될 수 있었다. 이는, 새로 통계를 수집하고 검증작업을 거치지 않았다

든지, 환경에 변화가 있었다든지, 기반 소프트웨어를 업그레이드 하는 등의 다양

한 원인에서 비롯되었다. 이러한 실행 계획의 변경은 애플리케이션의 성능이 더

최적화되거나 혹은 저하되는 결과를 가져온다. DBA는 데이터 계층에서의 작업

들이 최적화된 성능을 유지할 수 있도록 관리해야 할 책임이 있다. 이를 위해서는

중요한 실행 계획이나 통계치를 항상 최적의 상태로 관리해야 하며, 오라클이 제

시하는 과학적인 성능 튜닝 방법론을 반복적(Iterative)으로 적용하여, 실행계획

자체를 변화에 유연하게 진화시키고 이를 검증할 수 있어야 한다.

이에, Oracle Database 11g부터는 최적기가 자동으로 여러 개의 실행 계획을 집

합으로 묶어 관리하며, 검증된 실행 계획만 실행 가능하도록 설정하는 SQL Plan

Management 기능을 제공하고 있다. SQL Plan Management(SPM)의 원리에 따라 한번 SQL 실행 계획의 베이스라인이 설정

되면 예기치 못한 변화에 의해 새로운 실행 계획이 생성되더라도 검증되기 이전에는 해당 실행 계획을 수행하지 않는다.

SalesTable 1.Partitionlevelstatsaregathered&synopsiscreated

2.Globalstatsgeneratedbyaggregatingpartitionsynopsis

May 18th 2008

May 19th 2008

May 20th 2008

May 21th 2008

May 22th 2008

May 23th 2008

S1

S2

S3

S4

S5

S�

GlobalStatistic

SalesTable

SysauxTablespace

SysauxTablespace

3.Anewpartitionisaddedtothetable&DataisLoadedSalesTable

�.Globalstatsgeneratedbyaggregatingtheoriginalpartitionsynopsiswiththenewone

5.RetrievesynopsisforeachoftheotherpartitionsfromSysaux

May 18th 2008

May 19th 2008

May 20th 2008

May 21th 2008

May 22th 2008

May 23th 2008

May 24th 2008 S�

S1

S2

S3

S4

S5

S�

GlobalStatistic

<표 7> Incremental Global Statistics의 적용 과정

13 Autumn 2010 Oracle Database 11g의 새로운 성능 튜닝 인프라 및 활용 방안 Trend

<그림 8>은 실행 전 생성된 실행 계획들이 Statement Log안에서 어떻게 관리

되는지, 검증을 거친 실행 계획 베이스라인이 어떻게 SQL Plan Management

(SPM)에 의해 받아들여지는지를 나타내고 있다.

SQL Plan Management(SPM)에는 다음과 같은 세 개의 주 구성요소가 있다.

● SQL 실행계획 베이스라인 수집 :

수집된 SQL 실행계획 베이스라인은 SYSAUX 테이블 스페이스 안에 있는

SQL Management Base안에 실행 계획 히스토리로 저장된다.

● SQL 실행계획 베이스라인 선별 :

실행 계획 히스토리 내에서 승인된(Accepted) 실행 계획만 선별한다.

● SQL 실행계획 베이스라인의 진화 :

검증 이후에 성능이 향상된 SQL 실행계획을 승인하여 베이스라인으로 설정해

나가는 과정에서 점점 SQL 실행 계획을 진화시킬 수 있다.

이 기능을 사용하기 위해서는 아래의 두 초기화 파라메터를 적절히 설정해야 한다.

● optimizer_capture_sql_plan_baselines

SQL 실행계획 베이스라인을 자동으로 수집할 것인지의 여부를 결정, 기본 설정은 FALSE

● optimizer_use_sql_plan_baselines

SQL 실행계획 베이스라인을 최적기가 사용하도록 할 것인지를 결정, 기본 설정은 TRUE

위 설정에 따라 SQL 실행계획 베이스라인을 자동으로 수집하지 않을 때, 다양한 대안을 적용하여 SQL 실행계획 베이스라인

을 수집할 수 있다. 정리하면 아래와 같다.

● SQL Tuning Set에서 수집

● 기존에 생성한 Stored Outline에서 SQL 실행계획 베이스라인으로 마이그레이션

마이그레이션을 쉽게 할 수 있도록 지원하는 함수 제공. DBMS_SPM의 MIGRATE_STORED_OUTLINE 참조

● 커서 캐시로부터 해당 실행 계획을 로드

● 타 시스템에서 수집한 플랜을 Staging Table을 거쳐 SPM으로 이관

SQL Plan Management(SPM) 기능을 모니터링 하기 위해서는 Enterprise Manager의 SQL Plan Control 인터페이스를 사

용하거나 DBA_SQL_PLAN_BASELINE 뷰를 활용하도록 하고, 추가 관리가 필요할 경우 역시 Enterprise Manager의 SQL

Plan Control 인터페이스를 사용하거나 DBMS_SPM PL/SQL 패키지를 활용하도록 한다. SQL Plan Management(SPM) 기

능은 특히 업그레이드 시 그 효용을 극대화할 수 있다. 아래에서 업그레이드 시에 어떻게 이 기능을 활용할 수 있는지 좀 더 자

세히 살펴보도록 하자.

SPM을 통한 11g업그레이드

데이터베이스 업그레이드는 DBA에게 있어 중요한 임무 중 하나다. 단순히 데이터베이스 업그레이드 뿐 아니라, 업그레이드

이후에 가능한 데이터베이스 운영 상황에서의 변화를 컨트롤할 수 있어야 한다. 가장 일반적인 성능상의 변화는 실행 계획의

변화에서 온다. 이러한 변화를 쉽게 식별하고 관리하기 위해서, 업그레이드 전 반드시 현재 업무에서 사용하는 실행계획과 최

<그림 8> SQL Plan Management를 통해 실행계획을 받아들이는 과정

GB

HJ

HJ

GB

NL

NL

Plan Acceptable

Plan history

Statement log

SQL

GB

HJ

HJ

Parse ExecuteSQL Plan

Acceptable

Trend Oracle Database 11g의 새로운 성능 튜닝 인프라 및 활용 방안 Autumn 2010 14

적기 통계에 대하여 이해하고 있어야 한다. 또한, 업그레이드 이전에 반드시 새 버전에서 모든 애플리케이션이 정상 작동하는

지를 검증해야 한다. Oracle Database 11g에서는 SQL Plan Management(SPM)를 통해 업그레이드 이후에도 예기치 못하게

실행 계획이 바뀌지 않도록 보장하는 추가적인 안전장치를 확보할 수 있게 되었다. 업그레이드 후의 성능 변화를 관리하기 위

하여 다음과 같은 순서에 따라 SPM을 사용하라.

● 기존 실행계획의 수집

이전 버전의 데이터베이스에서 STS(SQL Tuning Set) 및 Stored Outline 등의 도구를 사용하여 주요 SQL문들에 대한 기존

실행 계획을 수집한다.

● 기존 통계치의 수집

통계치는 오라클의 비용 기반 최적기에 가장 큰 영향을 주므로, 업그레이드 시 반드시 일정하게 유지할 필요가 있다. 따라서

통계치에 대한 백업을 만들어 두길 권장한다.

● 기존 업무 애플리케이션의 테스팅

완벽한 변화 관리를 위해 Oracle Database 11g로 업그레이드하기 전 모든 애플리케이션의 성능을 검증해볼 필요가 있다. 위

단계에서 실행 계획을 수집할 때 중요한 SQL문을 빠뜨릴 위험은 항상 있으므로 전체 애플리케이션 테스트를 수행해 볼 것을

권한다.

● 업그레이드 이전의 체크리스트

업그레이드 이전 반드시 명확한 베이스라인을 확보해 두도록 한다.

▷ 인스턴스 레벨의 피크타임 시 성능 통계치(Statspack 혹은 AWR 데이터, OS 통계 등)를 수집한다.

▷ 모든 미션 크리티컬한 트랜잭션이 베이스라인 수집에 포함되도록 한다.

▷ Statspack을 사용하는 경우 PERFSTAT스키마를 익스포트해 두도록 한다.

▷ 최적기 통계치를 모두 통계치 테이블로 이관하여 익스포트하고, 백업을 만들어 둔다.

▷ 초기 환경 파일인 init.ora 파일을 백업해 둔다.

▷ 주요한 SQL문의 실행계획을 모두 수집해 둔다.

● 업그레이드 이후의 체크리스트

업그레이드를 마치고 애플리케이션을 재시작한 후 서비스하기 이전에, 반드시 SQL Plan Management(SPM)기능을 사용하

여 업그레이드 이전의 실행 계획으로 서비스될 수 있도록 한다. 이때, 11g 최적기에 의한 새로운 실행계획들은 생성되어도 사

용자의 검증 및 확인 이전에는 사용되지 않는다. 이처럼 SPM을 사용하면 운영 환경의 성능에는 영향을 주지 않으면서 11g의

향상된 성능과 기능을 사용할 수 있는 진화된 실행 계획을 차차 적용해 나갈 수 있다.

맺음말

지금까지, 오라클 성능 관리 방법론과 그 기반 구조, Oracle Database 11g에서 새로 소개된 성능 관리를 위한 신기능 및 활용

방안을 소개하였다. 본고에서 다루지 못한 좀 더 깊고 상세한 기술적 내용은 Oracle Technology Network, 사용자 커뮤니티를

이용하거나 혹은 한국오라클의 전문화된 Pre-consulting을 받아 보시기를 권한다.