ADDM과 SQL Tuning Advisor

열여덟번째. ADDM과 SQL Tuning Advisor

이제 Oracle Database가 직접 제공하는 SQL 튜닝 서비스를 활용해 보십시오! SQL Profile를 이용하여 쿼리 성능을 향상시키고 ADDM을 통해 일반적인 성능 문제를 쉽고 빠르게 해결하는 방법을 배워보십시오.

오늘은 조용한 하루입니다. 데이타베이스에도 문제가 없어 보입니다. DBA는 긴장을 풉니다. 미루어 두었던 RMAN 매개변수, 블록 사이즈 점검 작업을 하기에 좋은 날입니다.

갑자기 개발자 한 명이 DBA의 자리로 달려 옵니다. 그가 사용하는 SQL 쿼리의 성능이 저하되었습니다. 그가 말합니다. “가능한 한 빨리 해결해 주셨으면 좋겠습니다.”

어쩌면 긴장을 너무 빨리 풀었는지도 모릅니다. 데이타베이스의 성능과 보안을 향상시키려는 기존의 계획은 뒤로 밀리고, 오늘도 급한 불을 끄기 위해 하루를 보내야 합니다.

DBA는 반복적인 잡무에서 벗어나 좀 더 전략적인 과제에 집중할 수 있기를 원합니다. 그렇다면 반복적인 업무를 대신해 줄 조수를 하나 고용하면 좋지 않을까요

Oracle Database 10g에 추가된 Automatic Database Diagnostic Monitor(ADDM)이 바로 이런 역할을 해 줍니다. ADDM은 데이타베이스 성능 통계에 대한 철저한 분석을 통해 성능 병목을 확인하고, SQL 구문에 대한 분석을 통해 성능 향상을 위한 조언을 제공합니다. 또 SQL Tuning Advisor와 연동된 기능을 제공하기도 합니다. 이번 글에서는, ADDM을 이용한 성능 향상 방안에 대해 설명합니다.

Automatic Database Diagnostic Monitor (ADDM)

여섯번째 글에서, Automatic Workload Repository (AWR)에 대해 설명한 바 있습니다. AWR은 데이타베이스로부터 상세한 성능 관련 지표를 주기적으로 수집하여 저장합니다. 스냅샷 생성 작업이 완료될 때마다, ADDM이 호출되어 서로 다른 스냅샷의 데이타와 성능 지표를 비교 분석하고 성능 향상을 위한 조언을 제공합니다. 문제가 발견된 후, ADDM은 다른 어드바이저 툴(SQL Tuning Advisor 등)을 호출하여 해결 방법을 찾아내기도 합니다.

예를 통해 ADDM의 기능을 설명해 보도록 하겠습니다. 원인이 확인되지 않은 성능 문제에 대한 진단 작업에 착수한 경우를 가정해 봅시다. DBA는 문제가 되는 SQL 구문이 무엇인지 확인해 둔 상태입니다. 그러나 실제 환경에서는 이와 같은 유용한 단서도 모를 경우도 있습니다.

10g에서 진단 작업을 수행하는 과정에서, drill-down분석을 위해 적절한 시간 간격을 두고 생성된 스냅샷들을 선택할 수 있습니다. Enterprise Manager 10g의 데이타베이스 홈 페이지에서 “Advisor Central”을 선택하고 “ADDM”을 클릭하면 그림 1과 같은 화면이 표시됩니다.

이 화면에서 ADDM을 이용한 분석 작업을 생성할 수 있습니다. 성능 문제가 오후 11시에 발생했음을 알고 있는 DBA는, “Period Start”와 “Period End”의 값을 설정하여 해당 시간 대의 스냅샷들을 선택합니다. (붉은색 원으로 표시된) 카메라 모양의 아이콘을 클릭하여 스냅샷 시작 시간과 종료 시간을 지정할 수도 있습니다. 시간대를 설정하고 난 뒤 “OK” 버튼을 누르면 그림 2와 같은 화면이 표시됩니다.

ADDM은 해당 시간대에 관련한 두 가지 성능 문제를 발견해 냈습니다. 일부 SQL 구문이 지나치게 많은 CPU 시간을 사용하고 있으며, 이로 인해 데이타베이스의 성능이 전체적으로 저하되었습니다. 발견된 내용을 근거로, ADDM은 해당 구문에 대한 SQL 튜닝을 수행할 것을 권고하고 있습니다.

각각의 문제 항목을 클릭하면 그림 3에서 보여지는 것과 같은 상세 정보를 얻을 수 있습니다.

위 화면에서 문제의 원인이 된 SQL 구문을 확인할 수 있습니다. ADDM은 SQL Tuning Advisor를 이용해 이 SQL 구문에 대한 분석 작업을 수행할 것을 권고하고 있습니다. “Run Advisor Now” 버튼을 클릭하면 SQL Tuning Advisor가 호출되어 분석 작업을 시작합니다.

그림 2 화면의 “View Report” 버튼을 참고하시기 바랍니다. 개별 웹 페이지 별로 권고 사항을 제시하는 것과 별도로, ADDM은 전체 분석 결과에 대한 텍스트 리포트를 생성합니다. Listing 1에서 생성된 텍스트 리포트의 내용을 확인하실 수 있습니다. 텍스트 리포트는 문제가 되는 SQL 구문과 그 hash value 등의 상세한 정보를 제공합니다. 또 텍스트 리포트의 SQL ID 정보를 이용하여 SQL Tuning Advisor 또는 커맨드 라인을 통한 분석 작업을 수행할 수 있습니다.

ADDM은 AWR 스냅샷이 생성될 때마다 호출되고, 가장 최근의 스냅샷과의 비교를 통해 권고사항을 제시합니다. 따라서 비교해야 할 두 스냅샷이 서로 인접한 경우에는 (이미 보고서가 생성되어 있으므로) 별도로 ADDM 태스크를 실행할 필요가 없으며, 스냅샷이 인접해 있지 않은 경우에만 ADDM 태스크를 실행할 필요가 있습니다.

ADDM의 기능이 단순히 SQL 구문의 분석에 한정되지 않는다는 사실을 명심하시기 바랍니다. 과거 연재에서 확인한 것과 같이, ADDM은 메모리 관리, 세그먼트 관리, redu/undo 등의 영역에 대한 다양한 분석 기능을 제공합니다. 제한된 지면을 통해 ADDM의 기능을 모두 설명하는 것은 어차피 불가능하므로, 지금부터는 SQL Tuning Advisor에 초점을 맞추어 설명을 진행하도록 하겠습니다.

SQL Tuning Advisor를 이용한 Access분석

오라클 데이타베이스의 옵티마이저(optimizer)는 가능한 액세스 경로를 여럿 생성한 뒤, 오브젝트 통계정보를 기준으로 가장 적은 비용이 드는 하나를 선택하는 방식으로 runtime optimization을 수행합니다. 하지만 옵티마이저는 (시간의 제약을 받는 만큼) SQL 구문의 튜닝이 필요한지, 통계가 정확한지, 새로운 인덱스를 생성해야 하는지 등의 여부를 판단하지 않습니다. 반면 SQL Tuning Advisor는 일종의 “전문가 시스템”과 같은 역할을 합니다. 옵티마이저가 “현재 가능한 대안 중 최적의 결과를 얻을 수 있는 것은 무엇인가”라는 질문에 대한 답변을 제공한다면, SQL Tuning Advisor는 “사용자상시키기 위해 할 수 있는 일이 무엇인가”라는 질문의 답을 제공합니다.

이러한 “전문가 시스템”으로서의 작업은 CPU 등의 자원을 많이 소모합니다. 이러한 이유로 SQL Tuning Advisor는 데이타베이스가 Tuning Mode로 설정된 경우에만 SQL 구문에 대한 분석작업을 수행합니다. Tuning Mode는 튜닝 태스크를 생성하는 과정에서 SCOPE 및 TIME 매개변수를 설정함으로써 지정됩니다. 사용자에 대한 영향을 최소화하려면 데이타베이스 활동이 적은 시간대를 선택하여 Tuning Mode를 사용하는 것이 바람직합니다.

이제 예를 통해 설명하도록 하겠습니다. 문제가 되는 SQL 구문이 아래와 같습니다:

select account_no from accounts where old_account_no = 11

실제로 튜닝하기 어렵지 않은 구문이지만, 이해를 돕기 위해 간단한 구문을 사용하였습니다. 어드바이저는 Enterprise Manager 또는 커맨드 라인을 통해 실행할 수 있습니다.

먼저, 커맨드 라인을 이용하는 방법을 알아봅시다. 아래와 같은 방법으로 dbms_sqltune 패키지를 호출하고 어드바이저를 실행합니다.

declare   l_task_id     varchar2(20);

   l_sql         varchar2(2000);

begin

   l_sql := 'select account_no from accounts where old_account_no = 11';

   dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');

   l_task_id := dbms_sqltune.create_tuning_task (

      sql_text  => l_sql,

      user_name  => 'ARUP',

      scope      => 'COMPREHENSIVE',

      time_limit => 120,

      task_name  => 'FOLIO_COUNT'

   );

   dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');

end;

/

위 패키지는 FOLIO_COUNT라는 이름의 튜닝 태스크를 생성하고 실행합니다. 다음에는 아래와 같이 입력하여 태스크 실행 결과를 확인합니다.

set serveroutput on size 999999

set long 999999

select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;

실행 결과는 Listing 2에서 확인할 수 있습니다. 어드바이저가 제공하는 권고 사항을 자세히 살펴보시기 바랍니다. 이 경우, 어드바이저는 OLD_ACCOUNT_NO 컬럼에 인덱스를 생성할 것을 권고하고 있습니다. 그 뿐 아니라, 인덱스가 생성된 경우의 비용을 계산하고, 기대되는 성능 향상 효과를 구체적인 형태로 제시하고 있습니다.

예로 든 구문 자체가 단순한 만큼, 굳이 어드바이저를 이용해서 이런 결과를 얻을 필요는 없었을 것입니다. 하지만 보다 복잡한 형태의 쿼리에 성능 문제가 발생한 경우라면, 어드바이저를 통해 수작업으로는 불가능한 성능 개선 효과를 얻을 수 있습니다.

중급 레벨 튜닝: Query Restructuring

조금 더 복잡한 쿼리를 분석하는 경우를 생각해 봅시다:

select account_no from accounts a

where account_name = 'HARRY'

and sub_account_name not in

  ( select account_name from accounts

    where account_no = a.old_account_no and status is not null);

어드바이저는 다음과 같은 권고 사항을 제시하였습니다:

1- Restructure SQL finding (see plan 1 in explain plans section)

----------------------------------------------------------------

  The optimizer could not unnest the subquery at line ID 1 of the execution

  plan.  Recommendation

  --------------

    Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used

    on both sides of the "NOT IN" operator are declared "NOT NULL" by adding

    either "NOT NULL" constraints or "IS NOT NULL" predicates.  Rationale

  ---------

    A "FILTER" operation can be very expensive because it evaluates the

    subquery for each row in the parent query. The subquery, when unnested can

    drastically improve the execution time because the "FILTER" operation is

    converted into a join. Be aware that "NOT IN" and "NOT EXISTS" might

    produce different results for "NULL" values.

이번에는 인덱스 생성과 같은 오브젝트 구조 변경을 제안하는 대신, NOT IN 대신 NOT EXIST를 사용하도록 쿼리를 수정할 것을 제안하고 있습니다. 어드바이저는 근본적인 원인을 통해 권장 내용의 근거를 설명하고, 그 결정을 DBA의 판단에 맡깁니다.

고급 튜닝: SQL Profiles

옵티마이저는 쿼리가 사용하는 오브젝트 통계정보(object statistics)를 점검한 뒤 가장 적은 비용을 사용하는 경로를 선택함으로써 execution plan을 생성합니다. 쿼리가 두 개 이상의 테이블을 참조하는 경우, 옵티마이저는 관련된 테이블의 통계를 모두 점검한 뒤 가장 적은 비용을 사용하는 경로를 선택하지만, 테이블 간의 관계에 대해서는 아무 것도 이해하지 못합니다.

예를 들어, DELIQUENT(연체) 상태가 $1,000 이하의 balance를 갖는 한 account가 있다는 경우를 가정해 봅시다. ACCOUNTS 테이블과 BALANCNES 테이블을 join하는 쿼리에 status가 DELINQUENT인 데이타만을 필터링하는 조건을 추가함으로써, 보다 적은 수의 결과를 얻을 수 있을 것입니다. 옵티마이저는 이러한 테이블 간의 복잡한 관계를 이해하지 못합니다. 하지만 어드바이저의 경우는 다릅니다. 어드바이저는 데이터로부터 이들 관계정보를 “수집”해 낸 뒤 SQL Profile의 형태로 이를 저장합니다. 이제 SQL Profile에 접근할 수 있게 된 옵티마이저는 테이블의 데이타 분포뿐 아니라 데이타 간의 관계까지도 이해할 수 있게 됩니다. 이렇게 추가로 제공되는 정보를 활용하면 보다 뛰어난 수준의 execution plan을 생성하고 성능을 향상시킬 수 있리 힌트를 코드에 삽입하는 형태의 SQL 구문 튜닝이 불필요해집니다. 따라서 SQL Tuning Advisor를 이용하여, 코드에 전혀 손을 대지 않고도 패키지 애플리케이션을 튜닝할 수 있습니다.

오브젝트 통계가 하나 또는 그 이상의 오브젝트에 매핑되는 반면, SQL Profile은 쿼리에 매핑됩니다. 같은 테이블(ACCOUNTS와 BALANCES)을 참조하더라도 쿼리가 다르면 프로파일도 달라집니다.

SQL Tuning Advisor는 실행 과정에서 프로파일을 생성한 뒤 이를 “Accept”할 것을 권고합니다. 사용자가 “Accept”하지 않는 이상 프로파일은 구문에 반영되지 않습니다. 아래와 같이 실행하면 아무 때나 프로파일을 accept할 수 있습니다:

begin

   dbms_sqltune.accept_sql_profile (

      task_name   => 'FOLIO_COUNT',

      name        => 'FOLIO_COUNT_PROFILE'

      description => 'Folio Count Profile',

      category    => 'FOLIO_COUNT');

end;

위 명령은 어드바이저가 생성한 FOLIO_COUNT_PROFILE을 위의 예에서 설명한 FOLIO_COUNT 튜닝 태스크에 연관된 구문에 연결합니다. (DBA가 직접 SQL Profile을 생성할 수는 없으며, 어드바이저만이 SQL Profile을 생성할 수 있음을 참고하시기 바랍니다. 사용자는 SQL Profile의 사용 여부만을 선택할 뿐입니다.)

DBA_SQL_PROFILES 딕셔너리 뷰를 통해 생성된 SQL Profile을 확인할 수 있습니다. SQL_TEXT 컬럼은 프로파일이 할당된 SQL 구문을, STATUS 컬럼은 프로파일의 활성화 여부를 표시합니다. (프로파일이 특정 구문에 연결되어 있는 경우라 하더라도, 활성화하기 전에는 execution plan에 반영되지 않습니다.)

ADDM과 SQL Tuning Advisor의 활용

위에 예로 든 세 가지 경우 이외에도, SQL Tuning Advisor는 쿼리에서 사용하는 오브젝트에 통계가 누락되어 있는지의 여부를 확인해 줍니다. 요약하자면, 어드바이저는 다음과 같은 네 가지 유형의 작업을 수행합니다.

ADDM과 SQL Tuning Advisor가 유용하게 활용되는 경우를, 다음과 같은 세 가지 시나리오를 기준으로 검토해 볼 수 있습니다.

Enterprise Manager의 활용

앞의 예는 SQL Tuning Advisor를 커맨드 라인 모드에서 사용하는 방법을 기준으로 설명되었습니다. 커맨드 라인 모드는 태스크 실행을 위한 스크립트 생성이 용이하다는 장점이 있습니다. 하지만, 사용자에 의해 이미 문제가 보고된 경우라면 Enterprise Manager 10g를 이용하는 방법이 더 효과적일 수 있습니다.

(열세번째) 글에서, 새롭게 개선된 Enterprise Manager 인터페이스에 대해 소개한 바 있습니다. 이번에는 EM을 활용하여 SQL 구문을 진단하고 튜닝하는 방법을 설명해 보겠습니다. 데이타베이스 홈 페이지 하단의 “Advisor Central” 링크를 클릭하면 다양한 어드바이저를 위한 메뉴를 제공하는 화면이 표시됩니다. 이 화면 상단의 “SQL Tuning Advisor”를 클릭합니다 (그림 4 참조).

이제 SQL Tuning Advisor가 실행됩니다. 다음 화면에서 "Top SQL"을 클릭합니다 (그림 5 참조).

그림 6에서 보여지는 것과 같은 화면이 표시되며, 이 화면에서 표시되는 그래프를 통해 다양한 wait class에 관련된 정보를 시간대 별로 확인할 수 있습니다.

그래프의 관심 영역은 회색 사각형으로 표시됩니다. CPU wait이 높게 나타나는 영역에 마우스를 드래그하여 관심영역을 조정합니다. 화면의 하단에는 해당 시간대에 수행된 SQL 구문 관련 정보가 표시됩니다 (그림 7 참조).

상단(붉은색으로 표시된 부분)에는 가장 많은 CPU 자원을 소모하는 SQL 구문들이 표시되고 있습니다. 각 구문의 SQL ID를 클릭하면 그림 8과 같은 화면이 표시됩니다.

위 화면을 통해 문제가 되는 SQL 구문을 확인할 수 있습니다. “Run SQL Tuning Advisor” 버튼을 클릭하면 어드바이저가 실행되고 그림 9와 같은 화면이 표시됩니다.

어드바이저 스케줄러 화면에서, 태스크의 유형과 분석의 심도를 결정할 수 있습니다. 위 화면에서는 “comprehensive” analysis를 선택하고 어드바이저를 바로 실행하도록 설정하였습니다. 어드바이저의 실행이 완료되면 그림 10과 같은 화면을 통해 어드바이저의 권고 사항을 확인할 수 있습니다.

EM을 이용한 SQL Tuning Advisor 실행 방법은, 앞에서 설명한 커맨드 라인 버전을 이용하는 경우와 유사합니다. 하지만, 발생된 문제를 확인하여 드릴다운을 수행하고, 권고사항을 생성한 후 이를 승인하는 전체 과정이, 실제 발생한 문제를 해결하기에 편리한 형태로 구성되어 있다는 점에서 차이가 있습니다.

결론

ADDM은 성능 지표를 자동적으로 분석하고 오라클 전문가가 구현한 베스트 프랙티스 및 방법론을 기반으로 권고사항을 제시해 주는 강력한 성능 관리 툴입니다. 이 기능을 활용하여 발생된 문제와 그 원인을 확인할 수 있을 뿐 아니라, 취해야 할 조치에 대한 조언을 얻을 수 있습니다.

ADDM과 SQL Tuning Advisor에 대한 자세한 정보는 기술백서 Oracle Database 10g: The Self-Managing Database 와 The Self-Managing Database: Guided Application & SQL Tuning, 그리고 Oracle Database 2 Day DBA 매뉴얼의 제 10장 및 Oracle Database Performance Tuning Guide의 6장 and 13장을 참고하시기 바랍니다.