메뉴 건너뛰기

Korea Oracle User Group

Admin

SQL Plan Management(SPM) - 2 DBMS_SPM.CONFIG로 필터링

 

1. DBMS_SPM.CONFIG 사용 목적

 

SMB(SQL Management Base)와 SQL Plan Baseline에 대한 여러 옵션을 설정하기 위해 사용한다.

 

2. Syntax

 

DBMS_SPM.CONFIGURE(
 parameter_name   IN VARCHAR2,
 parameter_value  IN VARCHAR2 := null,
 allow    IN BOOLEAN := TRUE);

 

3. 사용하는 파라미터의 의미

 

DBMS_SPM.CONFIGURE 프로시저 사용 파라미터

Parameter Description
parameter_name 세팅하려는 설정의 명칭
parameter_value 세팅 값
allow

true 인 경우 파라미터에 설정한 부분을 포함, false 인 경우 제외한 나머지를 설정함

만약 null 값일 경우 설정 사항을 무시

 

4. parameter name과 value의 의미

 

auto_capture_action
: 자동 수집시 세션 레벨의 Action 명을 기준으로 필터한다.

 

 

auto_capture_module
: 자동 수집시 세션 레벨의 Module 명을 기준으로 필터한다.

 

 

auto_capture_parsing_schema_name
: 자동 수집시 SQL 문장의 Parsing schema 명을 기준으로 필터한다.

 

 

auto_capture_sql_text
: 자동 수집시 SQL문의 내용을 기준으로 LIKE 또는 NOT LIKE 형식으로 필터한다.

 

 

auto_spm_evolve_task
: 자동 수집시 SPM Evolve Task가 자주 돌도록 설정할 수 있다. 잦은 수행은 매시간마다 30분 이내의 작업 시간으로 수행하는 것을 말한다.

  이 설정에 대해서는 ON/OFF/AUTO 로만 설정이 가능하며 기능 활성화/비활성화에 대한 스위치 역할을 한다. 기본값을 OFF 이며 

  AUTO는 OFF와 동일하다 allow 설정 값에 영향을 받지 않는다.

 

 

plan_retention_weeks

: 사용되지 않는 Plan 정보를 삭제하기 전에 유지하는 주(week)를 설정한다.

  allow 설정 값에 영향을 받지 않으며 값의 범위는 5~523 주 사이의 값을 설정할 수 있다. 기본값은 53 주이다.

 

 

 

space_budget_percent
: SYSAUX 테이블스페이스에 SMB(SQL Management Base)를 저장할 때 최대 사용할 수 있는 % 를 설정한다.

  1~50 % 까지 설정 가능하며 기본값은 10% 이다. 

 

 

※ 위 파라미터 내용은 20c 기준이며 버전마다 지원하는 파라미터가 다르다.

※ 위 설정 사항은 DBA_SQL_MANAGEMENT_CONFIG 뷰를 통해 확인할 수 있다.

 

5. 사용예

 

1) 설정 확인

 

파라미터 확인

 

DB_MON@pdb1> show parameter sql_plan
NAME                                 TYPE    VALUE
------------------------------------ ------- -----
optimizer_capture_sql_plan_baselines boolean TRUE 
optimizer_use_sql_plan_baselines     boolean TRUE 

 

 

SPM Configure 정보 확인

 

DB_MON@pdb1> select *
  2  from dba_sql_management_config;
                     PARAMETER_NAME    PARAMETER_VALUE    LAST_MODIFIED    MODIFIED_BY
___________________________________ __________________ ________________ ______________
AUTO_CAPTURE_ACTION                                                                   
AUTO_CAPTURE_MODULE                                                                   
AUTO_CAPTURE_PARSING_SCHEMA_NAME                                                      
AUTO_CAPTURE_SQL_TEXT                                                                 
AUTO_SPM_EVOLVE_TASK                OFF                                               
AUTO_SPM_EVOLVE_TASK_INTERVAL       3600                                              
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME    1800                                              
PLAN_RETENTION_WEEKS                53                                                
SPACE_BUDGET_PERCENT                10

9개 행이 선택되었습니다.

 

2) auto_capture_action 설정 테스트

 

HR 계정으로 이미 수집되어 있는 Plan 정보를 먼저 삭제한다.

 

DB_MON@pdb1> 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  /

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

DB_MON@pdb1>

 

SPM_TEST 란 action 정보를 가진 세션에 대해서 수행된 SQL을 Baseline에 저장하게 Configure 정보 추가

 

DB_MON@pdb1> EXEC DBMS_SPM.CONFIGURE(parameter_name => 'auto_capture_action',parameter_value => 'SPM_TEST',allow => TRUE)

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

DB_MON@pdb1>

 

위 설정 후 설정이 제대로 되었는지 정보 확인

 

DB_MON@pdb1> SELECT *
  2  FROM DBA_SQL_MANAGEMENT_CONFIG;
                     PARAMETER_NAME         PARAMETER_VALUE                  LAST_MODIFIED    MODIFIED_BY 
___________________________________ _______________________ ______________________________ ______________ 
AUTO_CAPTURE_ACTION                 action IN (SPM_TEST)    20200518 17:30:48.000000000    DB_MON         
AUTO_CAPTURE_MODULE                                                                                       
AUTO_CAPTURE_PARSING_SCHEMA_NAME                                                                          
AUTO_CAPTURE_SQL_TEXT                                                                                     
AUTO_SPM_EVOLVE_TASK                OFF                                                                   
AUTO_SPM_EVOLVE_TASK_INTERVAL       3600                                                                  
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME    1800                                                                  
PLAN_RETENTION_WEEKS                53                                                                    
SPACE_BUDGET_PERCENT                10                                                                    


9개 행이 선택되었습니다. 

DB_MON@pdb1> 

 

이제 세션 1 에서는 DBMS_APPLICATION_INFO 패키지를 이용해 세션 정보 중 action에 SPM_TEST를 설정해 주고 세션 2에서는 설정 없이 SQL을 수행한다.

 

app_info_action.sql

BEGIN
    DBMS_APPLICATION_INFO.SET_ACTION('SPM_TEST');
END;
/

 

세션1)

HR@pdb1> @app_info_action

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

HR@pdb1> 

 

모니터링 세션)

DB_MON@pdb1> SELECT SID, SERIAL#,USERNAME, TERMINAL, ACTION 
  2  FROM V$SESSION
  3  where username = 'HR';
   SID    SERIAL#    USERNAME    TERMINAL      ACTION 
______ __________ ___________ ___________ ___________ 
   397       8112 HR          unknown     SPM_TEST    
   780       2898 HR          unknown                 

 

세션1과 2가 같이 보인다.

 

세션1)

HR@pdb1> @spm_captured
   EMPLOYEE_ID    FIRST_NAME    LAST_NAME       EMAIL    PHONE_NUMBER            HIRE_DATE        JOB_ID    SALARY    COMMISSION_PCT    MANAGER_ID    DEPARTMENT_ID 
______________ _____________ ____________ ___________ _______________ ____________________ _____________ _________ _________________ _____________ ________________ 
           111 Ismael        Sciarra      ISCIARRA    515.124.4369    20050930 00:00:00    FI_ACCOUNT         7700                             108              100 


1개 행이 선택되었습니다. 

   EMPLOYEE_ID     FIRST_NAME    LAST_NAME      EMAIL    PHONE_NUMBER            HIRE_DATE        JOB_ID    SALARY    COMMISSION_PCT    MANAGER_ID    DEPARTMENT_ID 
______________ ______________ ____________ __________ _______________ ____________________ _____________ _________ _________________ _____________ ________________ 
           112 Jose Manuel    Urman        JMURMAN    515.124.4469    20060307 00:00:00    FI_ACCOUNT         7800                             108              100 


1개 행이 선택되었습니다. 

   EMPLOYEE_ID    FIRST_NAME    LAST_NAME    EMAIL    PHONE_NUMBER            HIRE_DATE        JOB_ID    SALARY    COMMISSION_PCT    MANAGER_ID    DEPARTMENT_ID 
______________ _____________ ____________ ________ _______________ ____________________ _____________ _________ _________________ _____________ ________________ 
           113 Luis          Popp         LPOPP    515.124.4567    20071207 00:00:00    FI_ACCOUNT         6900                             108              100 


1개 행이 선택되었습니다. 

   EMPLOYEE_ID    FIRST_NAME    LAST_NAME       EMAIL    PHONE_NUMBER            HIRE_DATE    JOB_ID    SALARY    COMMISSION_PCT    MANAGER_ID    DEPARTMENT_ID 
______________ _____________ ____________ ___________ _______________ ____________________ _________ _________ _________________ _____________ ________________ 
           114 Den           Raphaely     DRAPHEAL    515.127.4561    20021207 00:00:00    PU_MAN        11000                             100               30 


1개 행이 선택되었습니다. 

   EMPLOYEE_ID    FIRST_NAME    LAST_NAME    EMAIL    PHONE_NUMBER            HIRE_DATE      JOB_ID    SALARY    COMMISSION_PCT    MANAGER_ID    DEPARTMENT_ID 
______________ _____________ ____________ ________ _______________ ____________________ ___________ _________ _________________ _____________ ________________ 
           115 Alexander     Khoo         AKHOO    515.127.4562    20030518 00:00:00    PU_CLERK         3100                             114               30 


1개 행이 선택되었습니다. 

   EMPLOYEE_ID    FIRST_NAME    LAST_NAME     EMAIL    PHONE_NUMBER            HIRE_DATE      JOB_ID    SALARY    COMMISSION_PCT    MANAGER_ID    DEPARTMENT_ID 
______________ _____________ ____________ _________ _______________ ____________________ ___________ _________ _________________ _____________ ________________ 
           116 Shelli        Baida        SBAIDA    515.127.4563    20051224 00:00:00    PU_CLERK         2900                             114               30 


1개 행이 선택되었습니다. 

   EMPLOYEE_ID    FIRST_NAME    LAST_NAME      EMAIL    PHONE_NUMBER            HIRE_DATE      JOB_ID    SALARY    COMMISSION_PCT    MANAGER_ID    DEPARTMENT_ID 
______________ _____________ ____________ __________ _______________ ____________________ ___________ _________ _________________ _____________ ________________ 
           117 Sigal         Tobias       STOBIAS    515.127.4564    20050724 00:00:00    PU_CLERK         2800                             114               30 


1개 행이 선택되었습니다. 

   EMPLOYEE_ID    FIRST_NAME    LAST_NAME      EMAIL    PHONE_NUMBER            HIRE_DATE      JOB_ID    SALARY    COMMISSION_PCT    MANAGER_ID    DEPARTMENT_ID 
______________ _____________ ____________ __________ _______________ ____________________ ___________ _________ _________________ _____________ ________________ 
           118 Guy           Himuro       GHIMURO    515.127.4565    20061115 00:00:00    PU_CLERK         2600                             114               30 


1개 행이 선택되었습니다. 

   EMPLOYEE_ID    FIRST_NAME     LAST_NAME       EMAIL    PHONE_NUMBER            HIRE_DATE      JOB_ID    SALARY    COMMISSION_PCT    MANAGER_ID    DEPARTMENT_ID 
______________ _____________ _____________ ___________ _______________ ____________________ ___________ _________ _________________ _____________ ________________ 
           119 Karen         Colmenares    KCOLMENA    515.127.4566    20070810 00:00:00    PU_CLERK         2500                             114               30 


1개 행이 선택되었습니다. 

   EMPLOYEE_ID    FIRST_NAME    LAST_NAME     EMAIL    PHONE_NUMBER            HIRE_DATE    JOB_ID    SALARY    COMMISSION_PCT    MANAGER_ID    DEPARTMENT_ID 
______________ _____________ ____________ _________ _______________ ____________________ _________ _________ _________________ _____________ ________________ 
           120 Matthew       Weiss        MWEISS    650.123.1234    20040718 00:00:00    ST_MAN         8000                             100               50 


1개 행이 선택되었습니다. 

HR@pdb1> @spm_captured
.
.
.

위와 같이 2번 수행

 

모니터링 세션)

DB_MON@pdb1> 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    ENABLED    ACCEPTED    FIXED                        CREATED 
_______________________ _________________________________ __________ _______________ __________ ___________ ________ ______________________________ 
SQL_0a9f3f1a800d695b    SQL_PLAN_0p7tz3a00uuavc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.977146000    
SQL_1f3a04238e3f4483    SQL_PLAN_1yfh44f73yj43c0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.891514000    
SQL_333ae7d982ca0391    SQL_PLAN_36fr7v61cn0wjc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.941039000    
SQL_6af9e236f64d1438    SQL_PLAN_6pyg26vv4u51sc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.872906000    
SQL_778f6e93b4c313b3    SQL_PLAN_7g3vfkfuc64xmc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.958549000    
SQL_7c4e3713c38db368    SQL_PLAN_7smjr2g1svcv8c0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.910255000    
SQL_9963bb506486c197    SQL_PLAN_9ksxva1k8dhcrc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.901003000    
SQL_c63e1f575d5fa5f0    SQL_PLAN_ccghzaxfpz9ghc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.949430000    
SQL_dad5b94c78ac742e    SQL_PLAN_dppdt9jwasx1fc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.932069000    
SQL_e5053b65b42c9e78    SQL_PLAN_fa19vcqu2t7msc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.968050000    


10개 행이 선택되었습니다. 

DB_MON@pdb1> 

 

위와 같이 세션1에서 수행한 SQL 10개가 등록된 것을 확인할 수 있다.

 

이제 action 설정이 되지 않은 세션에서 다른 SQL을 수행해 보자

 

세션2)

HR@pdb1> @spm_non_captured
HR@pdb1> @spm_non_captured
.
.
.

위와 같이 2번 수행 후 baseline 조회

 

모니터링 세션)

DB_MON@pdb1> 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    ENABLED    ACCEPTED    FIXED                        CREATED 
_______________________ _________________________________ __________ _______________ __________ ___________ ________ ______________________________ 
SQL_0a9f3f1a800d695b    SQL_PLAN_0p7tz3a00uuavc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.977146000    
SQL_1f3a04238e3f4483    SQL_PLAN_1yfh44f73yj43c0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.891514000    
SQL_333ae7d982ca0391    SQL_PLAN_36fr7v61cn0wjc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.941039000    
SQL_6af9e236f64d1438    SQL_PLAN_6pyg26vv4u51sc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.872906000    
SQL_778f6e93b4c313b3    SQL_PLAN_7g3vfkfuc64xmc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.958549000    
SQL_7c4e3713c38db368    SQL_PLAN_7smjr2g1svcv8c0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.910255000    
SQL_9963bb506486c197    SQL_PLAN_9ksxva1k8dhcrc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.901003000    
SQL_c63e1f575d5fa5f0    SQL_PLAN_ccghzaxfpz9ghc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.949430000    
SQL_dad5b94c78ac742e    SQL_PLAN_dppdt9jwasx1fc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.932069000    
SQL_e5053b65b42c9e78    SQL_PLAN_fa19vcqu2t7msc0e983c6    HR         AUTO-CAPTURE    YES        YES         NO       20200518 17:36:45.968050000    


10개 행이 선택되었습니다. 

DB_MON@pdb1>

 

위와 같이 세션2에서 수행한 SQL은 등록되지 않을 것을 확인할 수 있다.

 

이제 세션1의 action 정보를 제거하고 다시 SQL을 수행해 보고 Plan이 등록되는지 확인해 보자

 

모니터링 세션)

DB_MON@pdb1> 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  /

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

DB_MON@pdb1> 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';


0개 행이 선택되었습니다. 

DB_MON@pdb1> 

 

위와 같이 Baseline에 등록된 HR 계정의 Plan 정보를 삭제하였다.

 

이제 세션1 의 action 정보를 제거후 위 테스트에서 사용한 SQL 10개를 수행해 보자

 

HR@pdb1> BEGIN
2     DBMS_APPLICATION_INFO.SET_ACTION('');
3 END;
4 /

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

HR@pdb1> @spm_captured
HR@pdb1> @spm_captured
.
.
.

 

모니터링 세션)

DB_MON@pdb1> 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';


0개 행이 선택되었습니다. 

DB_MON@pdb1> 

 

위와 같이 수집되지 않은 것을 확인할 수 있다.

 

즉 auto_capture_action 설정이 잘 동작하는 것을 확인할 수 있다.

 

결론

위의 테스트에서 볼 수 있듯이 여러 Filter 를 통해 SQL Plan 정보를 수집해 Baseline에 선별적으로 저장할 수 있다.

앞 선 글에서 Plan이 자동으로 수집되게 하되 Configure 정보를 적절히 정리하면 관리자에게 필요한 SQL에 대해서만 선별적으로 

Baseline에 저장할 수 있으며 Plan 정보를 관리할 수 있다.

 

이러한 부분은 SQL의 성능 관리에 있어 매우 유용할 것으로 생각이 들고 DBMS_APPLICATION_INFO를 통해 세션의 정보를 효율적으로 

identified 한 상태로 사용하는 시스템에서는 더욱적 구첵적으로 세밀하게 관리할 수 있음을 알 수 있다.

 

 

 

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