Autumn 2010 Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Trend 잘못된 SQL문의사용, 자원과경합에서오는문제, 병렬실행시의문제등이그예다. 이러한다양한원인을식별하고해결하여시스템의 SLA를충족시키기위해서오라클은이론적으로는나침반이될수있는견고

Size: px

Start display at page:

Download "Autumn 2010 Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Trend 잘못된 SQL문의사용, 자원과경합에서오는문제, 병렬실행시의문제등이그예다. 이러한다양한원인을식별하고해결하여시스템의 SLA를충족시키기위해서오라클은이론적으로는나침반이될수있는견고"

규환 전
5 years ago
Views:

1 Trend Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Autumn 2010 Oracle Database 11g 의새로운성능튜닝인프라및활용방안 저자 - 강송희컨설턴트, 한국오라클 DB 사업부 (songhee.kang@oracle.com) 국적이나세대, 업종에관계없이데이터베이스관리자에게있어영원한화두는시스템최적화와장애예방이다. 근본적인이야기지만데이터베이스를운영할때최적화, 안정화된상태를유지하기위해서는기준이되는성능의베이스라인이있어야한다. 또한, 변화에유연하게대처하고문제를빨리, 그리고정확하게식별하여해결해나가기위해서는과학적인성능관리및문제해결접근법을반복적으로적용해나가는과정이반드시필요하다. 따라서관리자스스로필요한방법론을숙지하고생산성향상을위한신기술을적절하게사용할수있도록꾸준한관심을가질필요가있다. 실제로, 운영중인시스템에성능저하가오는근본적인원인은무척이나다양하다. 최적기통계의변화나적절하지않은접근경로의사용, Oracle 성능관리방법론 Queueing 이론오라클의성능관리방법론은널리알려진 Queueing 이론에기반하고있다. Queueing 이론은다음과같은흐름으로묘사되는소위 Queueing 시스템을다룬다. 1. 클라이언트들이시스템에 Random하게도착한다. 2. 도착한클라이언트들은일단 Queue에들어가대기시간이최소화되도록최적으로설정된우선순위에따라클라이언트가마침내서비스차례를기다리게된다. 3. 대기시간이최소화되도록최적으로설정된우선순위에따라클라이언트가마침내서비스를받고시스템을떠나게된다. 일반적으로클라이언트 / 서버모델이적용되는임의의시스템은 Queueing 시스템으로볼수있으며, 데이터베이스역시이에해당한다고볼수있을것이다. Queueing 이론은이러한 Queueing 시스템의성능을수학적으로분석 / 예상하고자한다. 하지만복잡한세부사항들을뒤로하면 Queueing 시스템의성능은다음과같은공식하나로깔끔히요약된다. Response Time = Service Time + Wait Time Response Time 80% 20% Wait Time Service Time < 표 1> Response Time의구성요소 클라이언트가시스템에도착한후떠날때까지의전체시간을 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 중가장큰비중을갖는대기요인부터다루도록해야할것이다.

2 Autumn 2010 Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Trend 잘못된 SQL문의사용, 자원과경합에서오는문제, 병렬실행시의문제등이그예다. 이러한다양한원인을식별하고해결하여시스템의 SLA를충족시키기위해서오라클은이론적으로는나침반이될수있는견고한과학적접근법을제시하고있으며실무적으로는성능튜닝기반구조와다양한도구들을제공하고있다. 본고에서는오라클의성능관리방법론과 Oracle Database 11g 새로도입되었거나향상된성능튜닝기반구조및그활용방안에대하여알아보도록하자. 앞으로의전개순서는먼저오라클의성능관리방법론을소개하고, 이를위해어떤통계적모형이존재하고어떤기반구조가구축되어있는지를살펴본후, Oracle Database 11g 새롭게향상된성능최적기기능과그활용방안을알아보도록하겠다. DB Time 오라클의성능관리방법론이 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 의증감에대한일반적인경향은다음과같이세가지로요약할수있다. Client App Response Time User Clicks on Web page App Server Response Time < 그림 2> Application Response Time 의구성요소 Application Response Time Database Response Time Service Time Service Time Wait Time Wait Time App Server Client App Response Time Response Time Browser Displays Result 시스템에걸리는총부하가증가할수록 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( 사용자가벽시계를바라 보며재는시간에서유래한용어 ) 보다훨씬커질수있다.

Trend Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Autumn 2010 Active Session 기본적으로데이터베이스시스템을클라이언트와서버모델로바라볼때, 데이터베이스자원을사용하려하는모든클라이언트는원하는작업을처리하기위해결과적으로 DB Time을소비하게된다.

3 Trend Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Autumn 2010 Active Session 기본적으로데이터베이스시스템을클라이언트와서버모델로바라볼때, 데이터베이스자원을사용하려하는모든클라이언트는원하는작업을처리하기위해결과적으로 DB Time을소비하게된다. 이러한클라이언트의활동을정량화하여다루기위해필요한개념이 Active Session이다. Active Session이란데이터베이스호출내에서현재실제로 DB Time을소모하고있는 Session을의미하며, 실제로 DB Time을소모하고있다는것은, 쉽게풀어쓰면 CPU를사용하거나 (Actively Working) 혹은 Non-idle wait으로대기중 (Actively Waiting) 이라는것을말한다. 따라서위정의에따라특정세션의평균활동지수는단순히세션의총 DB Time을 Wall Clock Time으로나눔으로써구할수있겠다. Average Active Sessions= (Total DB Time)/(Wall Clock Time) t0 t1 User 1 User 2 User 3 User n < 표 3> 은시간에따라 Active Session 이사용하는 DB Time 을가시화하여보여 주고있으며, 성능문제를분석하는데가장근본적인지표가되는 DB Time 의 흐름을한눈에볼수있다 Active Sessions over time < 표 3> 시간에따른 Active Session 의모습 Oracle Performance Methodology : DB Time Method 이처럼, 오라클의모든성능관리방법론은 DB Time에기반을두고있다. 주어진작업부하에대하여시스템을어떻게튜닝할것인가에관한질문을던질때, 개략적으로다음과같은논리적인순서를생각하라. 가장많은 DB Time 을소모하는작업을식별한다. 그중자원이나수용능력과관련된병목을식별해낸다. 해당작업부하에소모된불필요한 DB Time 을줄여나간다. 이는곧식별, 필터링, 선택과집중을통한해결방안도출이되겠다. < 그림 4> Response 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 성능관리를위한통계적모형과기반구조 앞부분에서성능관리를위해필요한핵심적인개념과방법론을다루었다면, 이제는성능모니터링과분석에필요한기반데이

4 Autumn 2010 Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Trend 터를확보하게해주는통계적모형과이를바탕으로오라클데이터베이스내부에구축된기반구조에대해서알아보도록하자. Oracle 성능관리를위한통계적모형의발전사 먼저현재정립된통계적모형을이해하기위해서어떠한과정을거쳐왔는지그역사를한번살펴보도록하자. Pre-Historic(v5) 미성숙단계로서어떠한통계적모형도존재하지않았던시기라할수있다. 기반이되는어떠한이론적근거나방법론도존재하지않았으므로애플리케이션자체를디버깅하는데엄청난노력이소요되었다. Dark Ages(v6) 흔히볼수있는간단한선형적모델을사용할수있도록카운터를도입한단계이다. Session Logical Reads 혹은 Physical Reads등과같은메트릭을정의하여누적값을뽑아내려는시도가일어났다. 초기단계의 OS에서타이머는그구현비용이굉장히비쌌기때문에대안적장치로서카운터부터시작하게된셈이다. 카운터를사용할때의문제점은여러가지가있겠지만, 가장대표적인것은유효한누적값자체가인스턴스시작이후에쌓인다는것이며, 따라서그값의실제적효용의범위가좁아질수밖에없었다. 이때, BSTAT/ESTAT 이라고불리는도구들이개발되기시작했는데, 이것은어느정도경험이쌓인개발자나컨설턴트가벤치마크시적용하던방식이었다. 기본카운터값에 Delta 개념을적용하여원하는특정기간 (Period) 동안에유효한작업부하통계를산출할수있었다. 스크립트기반의툴이개발되었고, SQL Trace등을활용하였다. Renaissance(v7) 카운터방식에서타이머방식으로전환되는과정이라할수있겠다. 이때현재우리가말하는실제적인 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(Automatic Workload Repository) 이와같은과정을거쳐, 오라클은미리정의된필수적인통계적데이터를수집할수있는기반구조인 AWR을데이터베이스내에구축하게되었다. AWR이란오라클데이터베이스에내장된작업부하와성능통계를저장하는저장소를의미한다. 기본설정에의해자동으로한시간마다작업부하에관련한데 Built-in,automatic performance statics data warehouse MMON SYSAUX AWR Data ADDM finds top problems 이터를수집하게되는데설정에따라수동으로원하는시점에수집할수도있다. 또한, 기본설정에의해 8일간보관된다. 데이터보존기간은물론변경할수있다. 위데이터는모두 SYSAUX 테이블스페이스에저장되며오라클데이터베이스에의해자동으로공간관리가가능하다. < 그림 5> 는 AWR의구조를나타내고있다. 이쯤에서 AWR에수집되는데이터에는어떤것들이있는지궁금해하는독자들이있을것이다. AWR에수집되는데이터를크게분류하여정리해보면다음과같다. BG BG FG FG AWR statistics In-memory statistics SGA < 그림 5> AWR Infrastructure ASH V$ DBA 07:00 a.m. 08:00 a.m. 09:00 a.m. 10:00 a.m. DBA_% Snapshot 1 Snapshot 2 Snapshot 3 Snapshot 4 Eight days 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 Trend Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Autumn Wait Event 앞서언급했던 DB Time 은다시다음과같은공식으로간단히표현할수있다. DB Time = CPU Time + Wait 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(Active Session History) AWR에수집되는기본데이터집합중하나인 ASH는, 매초활성세션 (Active Session) 을메모리에샘플링한결과로, 커널구조에직접접근하여가져오는정보이다. 세션의작업 (Activity) 에따라데이터량이달라질수있고, 10초이후디스크로플러쉬되어 AWR내에서스냅샷과함께히스토리가관리된다. ASH에담긴정보를활용하면 AWR 리포트의 Load Profile 섹션에서시작한성능분석을완성할수있다. 특히, 순간적인혹은일시적으로일어나는문제의타겟성능분석을가능하게해준다. ASH에포함되는정보를정리해보면다음과같다. SID SQL ID Program, Module, Action 상세 Wait Event 정보 이렇게 AWR 에수집되는모든통계적데이터는성능분석의중요한토대가되며, 이를성능분석에유용한정보로가공하기위 해 AWR Report 라는보고서를작성하게된다. AWR(Automatic Workload Repository) Report 이처럼 AWR Report는성능분석을위해필요한필수적인툴로써, 두개의선별된 AWR 스냅샷사이의작업부하및성능통계치에대한분석리포트를의미한다. 미리정의된 html 형식의 AWR 리포트군이제공되며, Enterprise Manager의인터페이스를통해쉽게작성할수있다. 성능분석의시작이라할수있는 AWR 리포트의기본프로파일의예는 < 표6> 과같다. < 표6> 의 Load Profile에서문제의소지가있다고의심되는성능지표가있을경우리포트하단에서관련된상세정보를확인할수있다. AWR 리포트는특정시점간의비교리포트및특정주기간의비교리포트등의형태를띠고있으며, RAC 환경에서는인스턴스별로해당리포트를생성할수있다.

11 Autumn 2010 Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Trend Oracle Database 11g에새롭게향상된성능최적기기능과활용방안앞주제에서다룬기반구조를활용하여, Oracle Database 11g 는통계치를수집하고성능을튜닝하기위한향상된방법을제공하고있다. 본고에서모든기능을다루기에는무리가있어, 대표적인것을선별해보았다.

6 11 Autumn 2010 Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Trend Oracle Database 11g에새롭게향상된성능최적기기능과활용방안앞주제에서다룬기반구조를활용하여, Oracle Database 11g 는통계치를수집하고성능을튜닝하기위한향상된방법을제공하고있다. 본고에서모든기능을다루기에는무리가있어, 대표적인것을선별해보았다. WORK:OAD REPOSITORY report for DBMS_STATS 그동안비용기반최적기에필요한성능통계를수집하는것이검증작업을제외하더라도번거롭거나많은시간을요할수있었다. Oracle Database 11g 에서는보다향상된통계수집을위한도구를지원하고있다. 이전버전에서는통계치를수집할때 DBMS_STATS.SET_PARM 프로시저를사용하여 DBMS_STATS.GATHER_*_STATS 프로시저의기본파라메터설정을바꿔야했다. Oracle Database 11g 에서는 SET_*_PREFS라는새로운프로시저가제공되어다양한범위 ( 테이블, 스키마, 데이터베이스, 글로벌 ) 수준의통계치수집을지원한다. 이전버전에서는테이블이나스키마레벨로통계치에 Lock을걸수있도록지원하였으나, Oracle Database 11g 에서는파티션레벨로통계치에 Lock을걸수있게되었다. < 표 6> AWR Report 의기본프로파일섹션 Oracle Database 11g 에서는통계치산출시새로운샘플링알고리즘을도입하여수집속도의향상을가져왔다. AUTO_SAMPLE_SIZE 지정시 100% 의정확도로계산할때기존 10% 샘플수준의속도로수집이가능해졌다. Invisible Indexes 11g 에서새로도입된 Invisible 인덱스를생성하면실제로는해당인덱스를사용하지않지만, 최적기에의해잠재적인접근경 로로사용될수있는지검증해볼수있다. 운영시스템의인덱스생성에조심스럽게접근해야할경우사용하길권장한다. Invisible 인덱스를생성하는방법은다음과같다. CREATE INDEX INVISIBLE 잠재적인접근경로가될수있는지검증하기위해서는다음과같은세션레벨의환경설정이필요하다. ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE; 인덱스가유용하겠다는판단이섰을때해당인덱스를실제로사용하기위해서는해당인덱스를드랍하거나다음과같이가시화하면된다. ALTER INDEX VISIBLE; Pending Statistics DBA는새로통계치를수집하여적용했을때최적기가특정작업부하에기존과다른실행계획을작성하지않는지, 새롭게작성된실행계획이성능저하를가져오지않는지반드시검증해보아야할의무가있다. Oracle Database 11g 에서는새로수집한통계를공식적으로 PUBLISH 하기전에는해당통계를최적기가사용하지않도록설정할수있게되었으며, 이기능을 Pending Statistics라통칭한다. 활용방법은다음과같다.

7 Trend Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Autumn 대상테이블및스키마의 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 ); Adaptive Cursor Sharing 기존 10g에서제공하던 Bind Peeking기능은바인드값이달라짐에따라최적실행계획이바뀔수있다는점에서문제가발생할수있다. Oracle Database 11g 에서는각바인드값집합에대하여최적화된실행계획을자동적용할수있도록하는 Adaptive Cursor Sharing 기능을제공하고있다. 이때가능하면사용가능한 Cursor를최대한공유할수있도록설계되었다. Incremental Global Statistics Oracle Database 11g 이전에는, 새로운파티션을추가하거나혹은기존의몇개파티션에대해데이터를수정하는경우에테이블레벨의통계치를재수집하기위해테이블전체를모두스캔해야했다. Oracle Database 11g 에서는이문제를개선하기위해 Incremental Global Statistics라는기능을제공하며, 사용법은아래와같다. Sales Table May 18 th 2008 May 19 th 2008 May 20 th 2008 May 21 th 2008 May 22 th Partition level stats are gathered & synopsis created S1 S2 S3 S4 S5 2. Global stats generated by aggregating partition synopsis Global Statistic 대상파티션테이블의 Table Preference 중 INCREMENTAL을 TRUE로설정한다. DBMS_STATS.SET_TABLE_PREFS( SH, SALES, INCREMENTAL, TRUE ); 통계치를수집할때 GRANULARITY 파라메터를 AUTO로설정하여통계를수집한다. May 23 th 2008 S6 Sysaux Tablespace < 표 7> 은 Incremental Global Statistics 가이루어지는과정을설명하고있다. Sales Table May 18 th 2008 May 19 th 2008 May 20 th 2008 May 21 th 2008 May 22 th 2008 May 23 th 2008 May 24 th A new partition is added to the table & Data is Loaded Sales Table S1 S2 S3 S4 S5 S6 S7 6. Global stats generated by aggregating the original partition synopsis with the new one Global Statistic 5. Retrieve synopsis for each of the other partitions from Sysaux Sysaux Tablespace < 표 7> Incremental Global Statistics 의적용과정 SQL Plan Management Oracle Database 11g 이전에는운영중에도예기치못한변화로인하여실행계 획이변경될수있었다. 이는, 새로통계를수집하고검증작업을거치지않았다 든지, 환경에변화가있었다든지, 기반소프트웨어를업그레이드하는등의다양 한원인에서비롯되었다. 이러한실행계획의변경은애플리케이션의성능이더 최적화되거나혹은저하되는결과를가져온다. DBA 는데이터계층에서의작업 들이최적화된성능을유지할수있도록관리해야할책임이있다. 이를위해서는 중요한실행계획이나통계치를항상최적의상태로관리해야하며, 오라클이제 시하는과학적인성능튜닝방법론을반복적 (Iterative) 으로적용하여, 실행계획 자체를변화에유연하게진화시키고이를검증할수있어야한다. 이에, Oracle Database 11g 부터는최적기가자동으로여러개의실행계획을집 합으로묶어관리하며, 검증된실행계획만실행가능하도록설정하는 SQL Plan Management 기능을제공하고있다. SQL Plan Management(SPM) 의원리에따라한번 SQL 실행계획의베이스라인이설정 되면예기치못한변화에의해새로운실행계획이생성되더라도검증되기이전에는해당실행계획을수행하지않는다.

8 13 Autumn 2010 Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Trend < 그림 8> 은실행전생성된실행계획들이 Statement Log 안에서어떻게관리 되는지, 검증을거친실행계획베이스라인이어떻게 SQL Plan Management (SPM) 에의해받아들여지는지를나타내고있다. SQL Plan Management(SPM) 에는다음과같은세개의주구성요소가있다. SQL Parse GB HJ Execute Plan Acceptable HJ SQL 실행계획베이스라인수집 : 수집된 SQL 실행계획베이스라인은 SYSAUX 테이블스페이스안에있는 SQL Management Base안에실행계획히스토리로저장된다. SQL 실행계획베이스라인선별 : 실행계획히스토리내에서승인된 (Accepted) 실행계획만선별한다. SQL 실행계획베이스라인의진화 : 검증이후에성능이향상된 SQL 실행계획을승인하여베이스라인으로설정해나가는과정에서점점 SQL 실행계획을진화시킬수있다. Statement log Plan history GB Plan Acceptable NL GB NL HJ HJ SQL 이기능을사용하기위해서는아래의두초기화파라메터를적절히설정해야한다. < 그림 8> SQL Plan Management 를통해실행계획을받아들이는과정 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에게있어중요한임무중하나다. 단순히데이터베이스업그레이드뿐아니라, 업그레이드이후에가능한데이터베이스운영상황에서의변화를컨트롤할수있어야한다. 가장일반적인성능상의변화는실행계획의변화에서온다. 이러한변화를쉽게식별하고관리하기위해서, 업그레이드전반드시현재업무에서사용하는실행계획과최

Trend Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Autumn 2010 14 적기통계에대하여이해하고있어야한다. 또한, 업그레이드이전에반드시새버전에서모든애플리케이션이정상작동하는지를검증해야한다.

9 Trend Oracle Database 11g 의새로운성능튜닝인프라및활용방안 Autumn 적기통계에대하여이해하고있어야한다. 또한, 업그레이드이전에반드시새버전에서모든애플리케이션이정상작동하는지를검증해야한다. 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을받아보시기를권한다.

Oracle Database 10g: Self-Managing Database DB TSC

Oracle Database 10g: Self-Managing Database DB TSC Agenda Overview System Resource Application & SQL Storage Space Backup & Recovery ½ Cost ? 6% 12 % 6% 6% 55% : IOUG 2001 DBA Survey ? 6% & 12 % 6% 6%