특정 SQL 쿼리가 한 데이터베이스(예: 프로덕션)에서 성능이 저조할 수 있지만 다른 데이터베이스(예: 개발)에서는 잘 작동할 수 있습니다. 이 상황은 동일한 쿼리가 각 인스턴스에서 다른 실행 계획을 가질 때 발생할 수 있습니다. 이 블로그는 Oracle® Database®가 버전 11g에서 처음 도입한 SQL 플랜 베이스라인 기능을 사용하여 쿼리가 잘 작동하는 인스턴스에서 실행 계획을 다른 인스턴스로 전송하는 방법을 보여줍니다.
SQL 플랜 관리 소개
Oracle SQL 플랜 관리 (SPM)는 쿼리의 모든 과거 실행 계획을 캡처하는 Oracle 데이터베이스의 기능입니다. 이를 통해 SPM에서 사용 가능한 실행 계획들로부터 좋은 계획에 대한 베이스라인을 생성하고 해당 베이스라인을 활성화하여 시스템이 베이스라인에서만 좋은 계획을 선택하도록 보장할 수 있습니다.
이 기능을 활용하려면, 한 인스턴스에서 잘 작동하고 다른 인스턴스에서 성능이 저조한 쿼리의 sql_id를 식별해야 합니다. 또한 쿼리가 잘 작동하는 인스턴스에서 좋은 실행 계획 ID인 plan_hash_value를 캡처해야 합니다.
한 인스턴스에서 다른 인스턴스로 SQL 베이스 플랜 복사
소스 인스턴스에서 타겟 인스턴스로 SQL 베이스 플랜을 복사하려면 다음 단계를 사용하세요:
- 쿼리가 잘 작동하는 소스 인스턴스에서 쿼리를 실행하여 커서 캐시에 쿼리가 존재하도록 합니다.
- 소스 인스턴스에서 쿼리의 SQL 실행 계획을 커서 캐시에서 SPM으로 베이스라인으로 로드합니다.
- 소스 인스턴스에 스테이징 테이블을 생성합니다. 이 테이블은 소스 인스턴스에서 타겟 인스턴스로 실행 계획을 마이그레이션하는 데 사용됩니다.
- 소스 실행 계획 또는 베이스라인을 소스 인스턴스에서 스테이징 테이블에 팩합니다.
- 내보내기/가져오기 유틸리티를 사용하여 스테이징 테이블을 소스 인스턴스에서 타겟 인스턴스로 전송합니다.
- 타겟 인스턴스에서 스테이징 테이블에서 SPM으로 SQL 플랜을 언팩합니다.
- 타겟 인스턴스에서 생성된 베이스라인이 고정되고 수락되어 다음 실행 시 쿼리에 대해 선택되도록 검증합니다.
- 타겟 인스턴스에서 성능 문제가 있었던 SQL을 테스트하고 전송된 베이스라인을 선택하는지 확인합니다.
실행 예시
앞의 단계를 실행하면 다음과 유사한 출력이 생성됩니다.
1단계: 소스 인스턴스에서 쿼리 실행
소스 인스턴스에서 SQL을 실행하고 sql_id와 plan_hash_value를 식별합니다. 커서 캐시를 조사하여 값을 얻습니다. 이 경우, 이들은 다음 값들입니다:
sql_id: 9xva48wpnsmp6plan_hash_value: 1572948408)
소스 인스턴스에서 다음 쿼리를 실행하세요:
SQL> select distinct plan_hash_value from v$sql where sql_id='9xva48wpnsmp6'; PLAN_HASH_VALUE --------------- 1572948408 2단계: 플랜을 SPM에 로드
이 좋은 쿼리 실행 계획을 커서 캐시에서 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> 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단계: 소스에서 타겟 인스턴스로 스테이징 테이블 전송
소스 인스턴스에서 스테이징 테이블의 내보내기 백업을 수행하려면 다음 명령어를 실행하세요:
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. 이제 타겟 인스턴스에서 다음 명령어를 실행하여 스테이징 테이블의 내보내기 백업을 타겟 인스턴스의 호스트로 전송하고 타겟 인스턴스에서 테이블을 가져옵니다:
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단계: 베이스라인 검증
타겟 인스턴스에서 다음 명령어를 실행하여 베이스라인이 수락되고 고정되었는지 확인하세요.
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> 앞의 출력은 베이스라인이 타겟 인스턴스에 가져와졌지만 고정되지 않았음을 보여줍니다. 옵티마이저가 이 계획만 선택하도록 베이스라인을 고정하고 활성화하려면 다음 쿼리를 실행하세요.
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 플랜이 어떻게 선택되는지 보여줍니다:
이미지 출처: Metalink Note Automatic SQL Plan Baselines (Doc ID 1930525.1)
결론
단일 쿼리에 대한 베이스라인을 전송해야 하는 경우 이 게시물의 단계를 사용하세요. 업그레이드, 마이그레이션 등을 위해 모든 쿼리에 대한 SQL 베이스라인을 생성할 수도 있습니다. 일관된 SQL 실행 계획을 갖고 성능 문제를 방지하려면 SQL 플랜 베이스라인을 사용하세요.
피드백 탭을 사용하여 의견을 남기거나 질문하세요.
데이터베이스 서비스 및 Rackspace 애플리케이션 서비스에 대해 자세히 알아보세요.
