Computer >> 컴퓨터 >  >> 프로그래밍 >> 데이터베이스

Oracle SQL 플랜 베이스라인으로 실행 계획 다른 인스턴스로 옮기는 방법

동일한 SQL 쿼리가 한 데이터베이스(예: 운영 환경)에서는 성능이 저하되지만, 다른 데이터베이스(예: 개발 환경)에서는 정상적으로 동작하는 경우가 있습니다. 이는 같은 쿼리라도 각 인스턴스에서 서로 다른 실행 계획(execution plan)을 사용하기 때문에 발생할 수 있습니다. 이 글에서는 Oracle® Database®가 11g 버전부터 도입한 SQL 플랜 베이스라인(SQL Plan Baseline) 기능을 활용해, 쿼리가 잘 동작하는 인스턴스의 실행 계획을 성능 문제가 있는 인스턴스로 전송하는 방법을 소개합니다.

SQL 플랜 관리(SPM)란 무엇인가

SQL Plan Management(SPM)는 Oracle Database에서 특정 쿼리의 과거 실행 계획 이력을 모두 저장하는 기능입니다. SPM에 저장된 실행 계획 중 성능이 좋은 플랜을 기준선(baseline)으로 만들고, 이를 활성화하면 옵티마이저가 기준선에 포함된 좋은 플랜만 선택하도록 강제할 수 있습니다.

이 기능을 활용하려면 먼저 한 인스턴스에서는 잘 수행되지만 다른 인스턴스에서는 느린 쿼리의 sql_id를 확인해야 합니다. 또한 쿼리가 정상 동작하는 인스턴스에서 해당 쿼리의 좋은 실행 계획 ID인 plan_hash_value를 함께 확보해야 합니다.

SQL 플랜 베이스라인을 인스턴스 간 복사하는 절차

소스(source) 인스턴스에서 타겟(target) 인스턴스로 SQL 플랜 베이스라인을 복사하려면 아래 단계를 순서대로 진행합니다.

  1. 쿼리가 정상 동작하는 소스 인스턴스에서 해당 쿼리를 실행하여 커서 캐시(cursor cache)에 실행 계획이 존재하도록 합니다.
  2. 소스 인스턴스에서 커서 캐시의 SQL 실행 계획을 SPM에 베이스라인으로 로드합니다.
  3. 소스 인스턴스에 스테이징 테이블(staging table)을 생성합니다. 이 테이블은 실행 계획을 소스에서 타겟으로 이전할 때 사용됩니다.
  4. 소스 인스턴스의 스테이징 테이블에 실행 계획(베이스라인)을 패킹(pack)합니다.
  5. export/import 유틸리티를 사용해 스테이징 테이블을 소스 인스턴스에서 타겟 인스턴스로 전송합니다.
  6. 타겟 인스턴스에서 스테이징 테이블로부터 SQL 플랜을 언패킹(unpack)하여 SPM에 등록합니다.
  7. 타겟 인스턴스에 생성된 베이스라인이 fixed 및 accepted 상태인지 확인하여, 다음 실행 시 해당 플랜이 선택되도록 합니다.
  8. 타겟 인스턴스에서 성능 문제가 있던 SQL을 테스트하고, 전송된 베이스라인을 사용하는지 검증합니다.

실제 실행 예제

위 절차를 실제로 수행하면 다음과 같은 결과를 확인할 수 있습니다.

1단계: 소스 인스턴스에서 쿼리 실행

소스 인스턴스에서 SQL을 실행하고 sql_id와 plan_hash_value를 식별합니다. 커서 캐시를 조회하면 값을 확인할 수 있습니다. 이 예제에서는 다음 값들을 사용합니다.

  • sql_id: 9xva48wpnsmp6
  • plan_hash_value: 1572948408

소스 인스턴스에서 아래 쿼리를 실행합니다:

SQL> select distinct plan_hash_value from v$sql where sql_id='9xva48wpnsmp6';

PLAN_HASH_VALUE
---------------
1572948408

2단계: SPM에 플랜 로드

아래 PL/SQL 블록을 실행하면 커서 캐시에 있는 좋은 실행 계획을 SPM에 베이스라인으로 로드할 수 있습니다:

SQL> set serveroutput on
SQL> declare
2   ret binary_integer;
     l_sql_id varchar2(13);
3
4   l_plan_hash_value number;
5   l_fixed varchar2(3);
6   l_enabled varchar2(3);
7   Begin
8   l_sql_id := '&&sql_id';
9   l_plan_hash_value := to_number('&&plan_hash_value');
10   l_fixed := 'Yes';
11   l_enabled := 'Yes';
12   ret := dbms_spm.load_plans_from_cursor_cache(
13       sql_id=>l_sql_id,
14       plan_hash_value=>l_plan_hash_value,
15       fixed=>l_fixed,
16       enabled=>l_enabled);
17   end;
18  /

Enter value for sql_id: 9xva48wpnsmp6
old   8:  l_sql_id := '&&sql_id';
new   8:  l_sql_id := '9xva48wpnsmp6';

Enter value for plan_hash_value: 1572948408
old   9:  l_plan_hash_value := to_number('&&plan_hash_value');
new   9:  l_plan_hash_value := to_number('1572948408');

PL/SQL procedure successfully completed.

아래 쿼리를 실행하여 소스 인스턴스에 SQL 베이스라인이 정상적으로 생성되었는지 확인합니다. 이후 단계에서 참조해야 하므로 SQL_HANDLE과 PLAN_NAME 값을 기록해 둡니다.

SQL> select count(*) from dba_sql_plan_baselines ;

COUNT(*)
--------
  1

SQL> select SQL_HANDLE, PLAN_NAME from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_d344aac395f978a4           SQL_PLAN_d6j5asfazky54868c96c3

3단계: 소스 인스턴스에 스테이징 테이블 생성

아래 명령을 실행하여 소스 인스턴스에 스테이징 테이블을 생성합니다:

SQL> sho user
USER is "SYS"
SQL> BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
  table_name      => 'SPM_STAGETAB',
  table_owner     => 'APPS',
  tablespace_name => 'SYSAUX');
END;

2    3    4    5    6    7
8  /

PL/SQL procedure successfully completed.

4단계: 베이스라인 패킹

아래 명령을 실행하여 소스 인스턴스의 스테이징 테이블에 베이스라인을 패킹합니다:

SQL> DECLARE
2      my_plans number;
3      BEGIN
4        my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
         table_name => 'SPM_STAGETAB',
         enabled => 'yes',
5
6
7        table_owner => 'APPS',
8        plan_name => 'SQL_PLAN_d6j5asfazky54868c96c3',
9      sql_handle => 'SQL_d344aac395f978a4');
10   END;
11  /

PL/SQL procedure successfully completed.

5단계: 스테이징 테이블을 소스에서 타겟 인스턴스로 전송

먼저 소스 인스턴스에서 스테이징 테이블을 export 백업합니다:

exp file=SPM_STAGETAB.dmp tables=APPS.SPM_STAGETAB log=SPM_STAGETAB.log compress=n
Export: Release 11.2.0.4.0 - Production on Sun Jun 3 13:14:50 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system/*******

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to APPS
. . exporting table             SPM_STAGETAB	         1 rows exported
Export terminated successfully without warnings.

다음으로 export 백업 파일을 타겟 인스턴스 호스트로 옮긴 후, 타겟 인스턴스에서 아래 명령으로 테이블을 import 합니다:

imp system file=SPM_STAGETAB.dmp log=imp_SPM_STAGETAB.log fromuser=apps touser=apps

Import: Release 11.2.0.4.0 - Production on Sun Jun 3 14:16:25 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing APPS's objects into APPS
. . importing table           "SPM_STAGETAB"   	       1 rows imported
Import terminated successfully without warnings.

6단계: 베이스라인 언패킹

아래 명령을 실행하여 스테이징 테이블에서 베이스라인을 언패킹하고 타겟 인스턴스의 SPM에 등록합니다. 예제에서는 언패킹 전후로 건수를 확인하여 베이스라인이 타겟에 제대로 반영되었는지 검증합니다.

SQL> select count(*) from dba_sql_plan_baselines;

COUNT(*)
--------
  2

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2      l_plans_unpacked  PLS_INTEGER;
3         BEGIN
4         l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
5               table_name      => 'SPM_STAGETAB',
6               table_owner     => 'APPS');
7
8            DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
9      END;
10  /
Plans Unpacked: 1

PL/SQL procedure successfully completed.

SQL> select count(*) from dba_sql_plan_baselines;

COUNT(*)
--------
  3

7단계: 베이스라인 검증

타겟 인스턴스에서 아래 명령을 실행하여 베이스라인이 accepted 및 fixed 상태인지 확인합니다.

SQL> SELECT sql_handle, plan_name, enabled, accepted, fixed, origin FROM dba_sql_plan_baselines;

SQL_HANDLE            PLAN_NAME                      ENA ACC FIX ORIGIN
--------------------- ------------------------------ --- --- --- ------------
SQL_d344aac395f978a4  SQL_PLAN_d6j5asfazky54868c96c3 YES YES NO  MANUAL-LOAD

SQL>

위 출력 결과를 보면 베이스라인은 타겟 인스턴스에 import 되었지만 아직 fixed가 NO 상태입니다. 아래 쿼리를 실행해 베이스라인을 fixed로 변경하면, 옵티마이저가 이 플랜만 선택하게 됩니다.

SQL> DECLARE
2    l_plans_altered  PLS_INTEGER;
3  BEGIN
4    l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
5      sql_handle      => 'SQL_d344aac395f978a4',
6      PLAN_NAME       => 'SQL_PLAN_d6j5asfazky54868c96c3',
7      ATTRIBUTE_NAME  => 'fixed',
8      attribute_value => 'YES');
9
10    DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
11  END;
12  /

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted, fixed, origin FROM   dba_sql_plan_baselines;

SQL_HANDLE            PLAN_NAME                      ENA ACC FIX ORIGIN
--------------------- ------------------------------ --- --- --- ------------
SQL_d344aac395f978a4  SQL_PLAN_d6j5asfazky54868c96c3 YES YES YES MANUAL-LOAD

SQL>

8단계: 타겟 인스턴스에서 SQL 쿼리 테스트

타겟 인스턴스에서 아래 명령을 실행하여 새 베이스라인이 실제로 사용되는지 확인합니다:

SQL> select SQL_PLAN_BASELINE from v$sql where sql_id='9xva48wpnsmp6';

SQL_PLAN_BASELINE
------------------------------
SQL_PLAN_d6j5asfazky54868c96c3

SQL 플랜 선택 방식

다음 이미지는 베이스라인 플랜이 존재할 때 SQL 플랜이 어떻게 선택되는지 보여줍니다.

Oracle SQL 플랜 베이스라인으로 실행 계획 다른 인스턴스로 옮기는 방법

이미지 출처: Metalink Note Automatic SQL Plan Baselines (Doc ID 1930525.1)

마무리

단일 쿼리의 베이스라인을 전송해야 할 때 이 글의 절차를 활용할 수 있습니다. 또한 업그레이드나 마이그레이션 시에는 전체 쿼리에 대한 SQL 베이스라인을 일괄 생성할 수도 있습니다. SQL 플랜 베이스라인을 사용하면 일관된 SQL 실행 계획을 유지하고 성능 저하 문제를 예방할 수 있습니다.

궁금한 점이나 의견이 있다면 피드백 탭을 통해 알려주세요.

Rackspace의 데이터베이스 서비스와 애플리케이션 서비스에 대해 더 자세히 알아보세요.