메뉴 건너뛰기

검색창 닫기

Korea Oracle User Group

Admin

SQL Plan Management(SPM) - 1 파라미터의 기능 확인

명품관 2020.03.19 11:22 조회 수 : 1516

SQL Plan Management(SPM) - 1 파라미터의 기능 확인

 

1. SPM의 목적 

실행계획 변경으로 SQL의 성능이 저하되는 것을 방지하고 SQL의 실행계획을 관리하기 위함

 

2. SPM의 기능

SQL의 실행계획을 관리하고, 새로운 실행 계획이 만들어지더라도 검증이 끝날 때까지 새로운 실행계획을 사용하지 않으며, 현재의 실행계획보다 성능이 비슷하거나 향상될 경우에만 사용할 수 있도록 함

 

3. SPM 활용도

  1. 1. Oracle Upgrade
  2. 2. 신규 Application 이나 Module Deploy
  3. 3. SQL 성능 튜닝 및 관리
  4. 4. 통계 수집에 따른 역효과 방지
  5. 5. 그밖의 어떤 변경으로 SQL 성능이 저하되는 것을 방지

 

4. SPM사용을 위한 설정 방법

1) 초기 파라미터 설정

1. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

SQL 문장을 캡쳐한 후 Baseline에 자동으로 저장하는 기능을 enable 시킨다. 디폴트 값은 false 이다.

DBMS_SPM.CONFIGURE 프로시저를 통해 반복 수행되는 SQL 중 관리 대상으로 선별할 SQL문을 여러 조건으로 선별할 수 있다.

 

2. OPTIMIZER_USE_SQL_PLAN_BASELINE

옵티마이저가 SQL Plan Baseline게 캡처된 SQL 실혱계획 정보를 사용하여 SQL을 실행할 수 있도록 할 수 있다. 디폴트 값은 true 이다.

 

 

2) OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

캡쳐 기능에 대한 파라미터 사용 내용을 살펴보자

 

db_mon@testdb> show parameter sql_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

db_mon@testdb> select sql_handle
  2       ,plan_name
  3       ,creator
  4       ,origin
  5       ,enabled
  6       ,accepted
  7       ,fixed     
  8       ,created
  9  from dba_sql_plan_baselines
 10  where creator = 'HR';

no rows selected

db_mon@testdb> alter system set optimizer_capture_sql_plan_baselines=true;

System altered.

db_mon@testdb>

 

위와 같이 파라미터 정보를 확인해 보고 HR 기준으로 캡쳐가 된 SQL 문장이 있는지 확인해 봤다.

파라미터의 경우 캡쳐 기능이 설정되어 있지 않아 캡처 기능을 true로 변경해 준다.

HR 기준으로 Baseline에 캡쳐된 SQL 문장은 현재 없다.

 

HR 계정으로 아래 3가지 SQL을 여러번 실행해 준다.

 

SQL1)
select * from employees
where employee_id > 113;

SQL2)
select * from employees
where employee_id < 113;

SQL3)
select * from employees
where employee_id < 103;

 

HR 계정으로 위의 SQL 3개를 수차례 실행해 본다. 

실행 후 캡쳐된 정보가 있는지 확인해 본다. 

 

db_mon@testdb> select sql_handle
  2       ,plan_name
  3       ,creator
  4       ,origin
  5       ,enabled
  6       ,accepted
  7       ,fixed     
  8       ,created
  9  from dba_sql_plan_baselines
 10  where creator = 'HR';

SQL_HANDLE                     PLAN_NAME                      CREATOR         ORIGIN         ENA ACC FIX CREATED
------------------------------ ------------------------------ --------------- -------------- --- --- --- --------------------
SQL_35b49a6a18ed94b7           SQL_PLAN_3bd4ud8cfv55re994a4cc HR              AUTO-CAPTURE   YES YES NO  20200319 16:14:58.000000
SQL_7553934070149a29           SQL_PLAN_7anwm81s196j9e994a4cc HR              AUTO-CAPTURE   YES YES NO  20200319 16:15:11.000000
SQL_a97c8c83e10b6a14           SQL_PLAN_akz4chghhquhne994a4cc HR              AUTO-CAPTURE   YES YES NO  20200319 16:15:06.000000

3 rows selected.

 

위의 내용과 같이 자동 캡쳐 기능을 활성화하는 파라미터를 true로 변경하니 반복 수행되는 SQL에 대해 자동으로 캡쳐된 것을 확인할 수 있다. 

 

이제 자동 캡처 기능에 대해 OPTIMIZER_USE_SQL_PLAN_BASELINES 파라미터의 값은 영향을 미치지 않는 것에 대해 살펴보자

 

먼저 Baseline에 캡쳐된 정보를 삭제하자

 

db_mon@testdb> DECLARE
  2     L_PLANS_DROPPED   PLS_INTEGER;
  3  BEGIN
  4     FOR C1_REC IN (SELECT SQL_HANDLE, PLAN_NAME
  5                    FROM   DBA_SQL_PLAN_BASELINES
  6                    WHERE  CREATOR = 'HR')
  7     LOOP
  8        L_PLANS_DROPPED := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
  9                               SQL_HANDLE => C1_REC.SQL_HANDLE
 10                             , PLAN_NAME => C1_REC.PLAN_NAME);
 11        DBMS_OUTPUT.PUT_LINE (L_PLANS_DROPPED);
 12     END LOOP;
 13  END;
 14  /
1
1
1

PL/SQL procedure successfully completed.

db_mon@testdb> select sql_handle
  2       ,plan_name
  3       ,creator
  4       ,origin
  5       ,enabled
  6       ,accepted
  7       ,fixed     
  8       ,created
  9  from dba_sql_plan_baselines
 10  where creator = 'HR';

no rows selected

db_mon@testdb>

 

이제 파라미터 값을 변경하고 확인해 보자

 

db_mon@testdb> show parameter sql_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE
db_mon@testdb> alter system set optimizer_use_sql_plan_baselines=false;

System altered.

db_mon@testdb> show parameter sql_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     FALSE
db_mon@testdb>

 

위와 같이 마라미터를 바꾸고 위 테스트에서 수행했던 SQL 3가지를 수행해 보자

HR 계정에서 수행하자

 

SQL1)
select * from employees
where employee_id > 113;

SQL2)
select * from employees
where employee_id < 113;

SQL3)
select * from employees
where employee_id < 103;

db_mon@testdb> select sql_handle
     ,plan_name
  2    3       ,creator
  4       ,origin
  5       ,enabled
  6       ,accepted
  7       ,fixed     
  8       ,created
  9  from dba_sql_plan_baselines
 10  where creator = 'HR';

SQL_HANDLE                     PLAN_NAME                      CREATOR         ORIGIN         ENA ACC FIX CREATED
------------------------------ ------------------------------ --------------- -------------- --- --- --- --------------------
SQL_35b49a6a18ed94b7           SQL_PLAN_3bd4ud8cfv55re994a4cc HR              AUTO-CAPTURE   YES YES NO  20200319 16:20:18.000000
SQL_7553934070149a29           SQL_PLAN_7anwm81s196j9e994a4cc HR              AUTO-CAPTURE   YES YES NO  20200319 16:20:25.000000
SQL_a97c8c83e10b6a14           SQL_PLAN_akz4chghhquhne994a4cc HR              AUTO-CAPTURE   YES YES NO  20200319 16:20:22.000000

3 rows selected.

db_mon@testdb>

 

위의 내용과 같이 optimizer_use_sql_plan_baselines 파라미터는 캡쳐 액션과는 상관 없으며

optimizer_capture_sql_plan_baselines 파라미터가 실행계획 캡쳐에 대한 기능을 작동하게 만든다.

 

3) OPTIMIZER_USE_SQL_PLAN_BASELINE

옵티마이저가 SQL Plan Baseline을 사용하도록 하는 파라미터에 대해 살펴보자

먼저 테스트 케이스를 확인해 보자

테스트를 위해 오라클 샘플 스키마의 SH 계정의 CUSTOMERS 테이블을 복제해서 사용하도록 하겠다.

 

SQL> DROP TABLE TEST_SPM PURGE;

Table TEST_SPM이(가) 삭제되었습니다.

SQL> CREATE TABLE TEST_SPM
  2  AS
  3  SELECT *
  4  FROM CUSTOMERS
  5  WHERE 1=0;

Table TEST_SPM이(가) 생성되었습니다.

SQL> CREATE INDEX TEST_SPM_LAST_NAME_IDX ON TEST_SPM(CUST_LAST_NAME);

Index TEST_SPM_LAST_NAME_IDX이(가) 생성되었습니다.

 

먼저 Full Table Sacn으로 유도되는 SQL 케이스를 만들도록 하겠다.

 

SQL> INSERT INTO TEST_SPM
  2  SELECT * 
  3  FROM   CUSTOMERS;

55,500개 행 이(가) 삽입되었습니다.

SQL> COMMIT;
커밋 완료.
SQL> BEGIN
  2      DBMS_STATS.GATHER_TABLE_STATS(
  3           OWNNAME => 'HR'
  4          ,TABNAME => 'TEST_SPM'
  5          ,CASCADE => TRUE);
  6  END;
  7  /

PL/SQL 프로시저가 성공적으로 완료되었습니다.

SQL> SELECT /* SPM TEST */
  2        CUST_FIRST_NAME, CUST_LAST_NAME
  3 FROM   TEST_SPM
  4 WHERE  CUST_LAST_NAME BETWEEN 'Puleo' AND 'Zzz';
.
.
.
SQL_ID  5st43muqadyw2, child number 0
-------------------------------------
SELECT /* SPM TEST */        CUST_FIRST_NAME, CUST_LAST_NAME FROM   
TEST_SPM WHERE  CUST_LAST_NAME BETWEEN 'Puleo' AND 'Zzz'
 
Plan hash value: 1145642998
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |   685 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST_SPM | 22202 |   325K|   685   (1)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("CUST_LAST_NAME">='Puleo' AND "CUST_LAST_NAME"<='Zzz'))

 

이제 같은 SQL 문장으로 Index Range Scan 되도록 만들어 보자

 

SQL> TRUNCATE TABLE TEST_SPM;

Table TEST_SPM이(가) 잘렸습니다.

SQL> INSERT INTO TEST_SPM
  2  SELECT * 
  3  FROM   CUSTOMERS 
  4  ORDER BY CUST_LAST_NAME;

55,500개 행 이(가) 삽입되었습니다.

SQL> COMMIT;
커밋 완료.
SQL> BEGIN
  2      DBMS_STATS.GATHER_TABLE_STATS(
  3           OWNNAME => 'HR'
  4          ,TABNAME => 'TEST_SPM'
  5          ,CASCADE => TRUE);
  6  END;
  7  /

PL/SQL 프로시저가 성공적으로 완료되었습니다.

SQL> SELECT /* SPM TEST */
  2        CUST_FIRST_NAME, CUST_LAST_NAME
  3 FROM   TEST_SPM
  4 WHERE  CUST_LAST_NAME BETWEEN 'Puleo' AND 'Zzz';
.
.
.
SQL_ID  5st43muqadyw2, child number 0
-------------------------------------
SELECT /* SPM TEST */        CUST_FIRST_NAME, CUST_LAST_NAME FROM   
TEST_SPM WHERE  CUST_LAST_NAME BETWEEN 'Puleo' AND 'Zzz'
 
Plan hash value: 1874805010
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |       |       |   465 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_SPM               | 13886 |   203K|   465   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TEST_SPM_LAST_NAME_IDX | 13886 |       |    38   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CUST_LAST_NAME">='Puleo' AND "CUST_LAST_NAME"<='Zzz')

 

먼저 위의 테스트 케이스의 SQL 문장과 두개의 실행계획이 캡쳐가 되도록 해 보자

 

SQL> alter system set optimizer_capture_sql_plan_baselines=true;

System SET이(가) 변경되었습니다.

SQL> show parameter sql_plan
NAME                                 TYPE    VALUE 
------------------------------------ ------- ----- 
optimizer_capture_sql_plan_baselines boolean TRUE  
optimizer_use_sql_plan_baselines     boolean FALSE

 

이제 위 테스트 경우의 문장을 수행해 자동 캡쳐가 되도록 하자

 

db_mon@testdb> select sql_handle
  2       ,plan_name
  3       ,substr(sql_text, 1, 20)
  4       ,creator
  5       ,origin
  6       ,enabled
  7       ,accepted
  8       ,fixed     
  9       ,created
 10       ,last_modified
 11       ,last_executed
 12  from dba_sql_plan_baselines
 13  where creator = 'HR'
 14  and sql_text like '%SPM TEST%';

SQL_HANDLE                     PLAN_NAME                      SUBSTR(SQL_TEXT,1,20)                       CREATOR          ORIGIN         ENA ACC FIX
------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- -------------- --- --- ---
CREATED              LAST_MODIFIED                                                               LAST_EXECUTED
-------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
SQL_462ec18b331ff374           SQL_PLAN_4cbq1jctjzwvn8b323ba7 SELECT /* SPM TEST *                        HR               AUTO-CAPTURE   YES NO  NO
20200325 19:43:46.00 20200325 19:43:46.000000
0000

SQL_462ec18b331ff374           SQL_PLAN_4cbq1jctjzwvneb1890ae SELECT /* SPM TEST *                        HR               AUTO-CAPTURE   YES YES NO
20200325 17:27:05.00 20200325 17:27:05.000000                                                    20200325 17:27:05.000000
0000


2 rows selected.

 

위와 같이 두개의 SQL 문장와 해당하는 플랜이 Baseline에 등록이 되었다.

현재 쿼리를 수행할 경우 Index Range Scan을 하는 플랜으로 동작하게 테이블이 구성되어 있다.

즉, 아래와 같다.

 

SQL> SELECT /* SPM TEST */
  2        CUST_FIRST_NAME, CUST_LAST_NAME
  3 FROM   TEST_SPM
  4 WHERE  CUST_LAST_NAME BETWEEN 'Puleo' AND 'Zzz';
.
.
.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

SQL_ID  5st43muqadyw2, child number 1
-------------------------------------
SELECT /* SPM TEST */        CUST_FIRST_NAME, CUST_LAST_NAME FROM   
TEST_SPM WHERE  CUST_LAST_NAME BETWEEN 'Puleo' AND 'Zzz'
 
Plan hash value: 1874805010
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |       |       |   461 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_SPM               | 13791 |   202K|   461   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TEST_SPM_LAST_NAME_IDX | 13791 |       |    37   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CUST_LAST_NAME">='Puleo' AND "CUST_LAST_NAME"<='Zzz')

 

여기에서 OPTIMIZER_USE_SQL_PLAN_BASELINES 파라미터의 값을 바꿀 경우

DBA_SQL_PLAN_BASELINES 뷰에서 조회한대로 ACCEPTED 컬럼의 값이 YES인 SQL_HANDLE 이 SQL_462ec18b331ff374 이며

PLAN_NAME이 SQL_PLAN_4cbq1jctjzwvneb1890ae 인 실행계획을 사용하게 될 것이다.

내용을 확인해 보자

 

SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES=true;

System SET이(가) 변경되었습니다.

SQL> 
SQL> show parameter sql_plan
NAME                                 TYPE    VALUE 
------------------------------------ ------- ----- 
optimizer_capture_sql_plan_baselines boolean TRUE  
optimizer_use_sql_plan_baselines     boolean TRUE

 

위와 같이 옵티마이저가 SQL Plan Baseline을 사용할 수 있게 파라미터 값을 변경했다.

이제 같은 쿼리가 Index Range Scan을 사용하는 플랜을 쓰지 않고 Baseline의 정보를 참조해서 Full Table Scan 방식의 플랜을 선택하게 될 것이다.

 

SQL> SELECT /* SPM TEST */
  2        CUST_FIRST_NAME, CUST_LAST_NAME
  3 FROM   TEST_SPM
  4 WHERE  CUST_LAST_NAME BETWEEN 'Puleo' AND 'Zzz';
.
.
.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

SQL_ID  5st43muqadyw2, child number 2
-------------------------------------
SELECT /* SPM TEST */        CUST_FIRST_NAME, CUST_LAST_NAME FROM   
TEST_SPM WHERE  CUST_LAST_NAME BETWEEN 'Puleo' AND 'Zzz'
 
Plan hash value: 1145642998
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |   685 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST_SPM | 13791 |   202K|   685   (1)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("CUST_LAST_NAME">='Puleo' AND "CUST_LAST_NAME"<='Zzz'))
 
Note
-----
   - SQL plan baseline SQL_PLAN_4cbq1jctjzwvneb1890ae used for this statement

 

마지막 Note를 보면 SQL plan baseline의 SQL_PLAN_4cbq1jctjzwvneb1890ae 플랜 정보를 사용한 것을 알 수 있다.

 

즉, 마지막으로 테스트한 optimizer_use_sql_plan_baselines 파라미터를 변경해야

옵티마이저가 플랜을 생성할 때 SQL Plan Baseline을 확인한다는 사실을 확인할 수 있다.

 

5. 정리

 

SPM 기능을 사용하는데 필요한 파라미터는 아래 두가지 이다.

 

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
OPTIMIZER_USE_SQL_PLAN_BASELINES

 

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES의 기능은 SQL Plan을 Baseline에 자동 캡쳐 하는 기능을 enable/disable 하는 것이다.

 

OPTIMIZER_USE_SQL_PLAN_BASELINES의 기능은 Baseline에 저장된 정보를 옵티마이저가 사용할지 말지를 결정하는 파라미터이다. 

 

, , , , , , , , , 이 게시물을
목록
번호 제목 글쓴이 날짜 조회 수
34 Windows 접속 에러 ORA-12638 [1] file Talros 2024.02.14 485
33 Python 을 이용해 파일 업로드 해 보기 [1] file Talros 2023.04.19 1064
32 수집된 통계가 바로 반영되지 않고 원할 때 반영하도록 하는 방식 명품관 2023.04.04 763
31 ORA-28014: Cannot Drop Administrative Users 에러 현상 [1] 명품관 2023.03.27 2963
30 ORA-3136 inbound connection time out & JDBC Io exception: Connection reset [1] Talros 2023.02.24 1446
29 default 값을 포함한 컬럼 추가시 오라클 버전별 개선 사항 [1] 명품관 2023.01.17 1279
28 mbr(multi block read) 과 sbr(single block read) 사이에 과연 어떤 방식을 선택해야 할까요? [1] 명품관 2022.09.16 789
27 Schema password 재 사용 불가능 하게 하기 [1] Talros 2022.01.26 3886
26 Pga 메모리 설정관련 내용 [1] 우뽕 2021.03.05 2404
25 DB option Enable / Disable 정리 방법 - Mos 참고 file 우뽕 2021.01.31 1427
24 RECO 프로세스 트레이스 발생 하면서 ORA-02019 에러 발생시 명품관 2021.01.12 1510
23 OS 터미널 접속 없이 오라클 접속을 통해 OS CPU 사용율 모니터링 하기 [3] file 명품관 2020.12.22 1196
22 SQL Plan Management(SPM) - 3 Manual Plan Capture 명품관 2020.06.01 2065
21 SQL Plan Management(SPM) - 2 DBMS_SPM.CONFIG로 필터링 file 명품관 2020.03.26 1004
» SQL Plan Management(SPM) - 1 파라미터의 기능 확인 명품관 2020.03.19 1516
19 [12cR2 이상] 오브젝트 이름 30자이상 사용 가능 우뽕 2020.01.22 8128
18 Alert log 에서 갑자기 패치 정보가 나타나는 현상 Talros 2019.09.23 2049
17 External Table 기능을 사용해 파일 읽기 명품관 2017.04.05 1107
16 DBMS_SCHEDULER을 통해 OS 레벨(EXTERNAL)의 shell 수행하기 명품관 2017.04.05 5951
15 오라클 패치 정보를 조회할 수 있는 뷰 DBA_REGISTRY_SQLPATCH 명품관 2017.03.02 5898
쓰기 태그
위로