특정 SQL 쿼리는 한 데이터베이스(예:프로덕션)에서는 제대로 수행되지 않을 수 있지만 다른 데이터베이스(예:개발)에서는 제대로 작동합니다. 이 상황은 동일한 쿼리가 각 인스턴스에 대해 다른 실행 계획을 가지고 있는 경우 발생할 수 있습니다. 이 블로그는 Oracle® Database®가 처음 inversion 11g를 도입한 SQLplan 기준 기능을 사용하여 쿼리가 제대로 작동하는 인스턴스에서 쿼리가 제대로 작동하지 않는 다른 인스턴스로 실행 계획을 전송하는 방법을 보여줍니다.
SQL 계획 관리 소개
Oracle SPM(SQL 계획 관리)은 쿼리에 대한 모든 기록 실행 계획을 캡처하는 Oracle Database의 기능입니다. 이를 통해 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.
이제 대상 인스턴스에서 다음 명령을 실행하여 스테이징 테이블의 exportbackup을 대상 인스턴스의 호스트로 전송하고 대상 인스턴스의 테이블을 가져옵니다.
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 자동 SQL 계획 기준(Doc ID 1930525.1)
결론
단일 쿼리에 대한 기준선을 전송해야 하는 경우 이 게시물의 단계를 사용하세요. 업그레이드, 마이그레이션 등에 대한 모든 쿼리에 대한 SQL 기준선을 생성할 수도 있습니다. SQL 계획 기준선을 사용하여 일관된 SQL 실행 계획을 갖고 성능 문제를 방지하십시오.
피드백 탭을 사용하여 의견을 남기거나 질문하십시오.
데이터베이스 서비스 및 Rackspace 애플리케이션 서비스에 대해 자세히 알아보십시오.