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. 사용하는 파라미터의 의미
| 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 한 상태로 사용하는 시스템에서는 더욱적 구첵적으로 세밀하게 관리할 수 있음을 알 수 있다.
