-- SPM(SQL PLAN MANAGEMENT)
-- SPM은 baseline를 DB내에 여러개의 Plan을 저장 해 놓고 검증된 실행계획만 사용할 수 있도록 하는 기능이다.
-- --------------------------------------------------------------------
-- 기본 설정
-- --------------------------------------------------------------------
set linesize 150
col NAME_COL_PLUS_SHOW_PARAM for a40
col VALUE_COL_PLUS_SHOW_PARAM for a25
show parameter sql_plan
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
---------------------------------------- ----------- -------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
-- OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES => TRUE : 자동수집, FALSE : 수동 수집 , default FALSE
-- OPTIMIZER_USE_SQL_PLAN_BASELINES => BASELINE 등록 이후 SPM PLAN 이용 할지 여부 (TRUE : 사용)
-- --------------------------------------------------------------------
-- test sql
-- --------------------------------------------------------------------
WITH RETRY AS (
SELECT LMR.REQ_URL
FROM LST_MEMSYNC_RETRY LMR
, LST_MEMSYNC_ERR LME
WHERE LMR.TRAN_ID = LME.TRAN_ID
AND PROC_STS_CD = 9500
AND LMR.REQ_URL IN ( '/api/member/ext/join' /* 회원가입 */
, '/api/member/ext/modify' /* 회원수정 */
, '/api/member/ext/secede' /* 회원탈퇴 */
, '/api/member/ext/integrate' /* 회원통합 */
, '/api/member/ext/lastBuyInfo' /* 최종구매일자 조회 */
)
AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24, 'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS')
)
SELECT *
FROM (
SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join' THEN 1 END) AS "1" /* 회원가입 */
, COUNT(CASE WHEN REQ_URL = '/api/member/ext/modify' THEN 1 END) AS "2" /* 회원수정 */
, COUNT(CASE WHEN REQ_URL = '/api/member/ext/secede' THEN 1 END) AS "3" /* 회원탈퇴 */
, COUNT(CASE WHEN REQ_URL = '/api/member/ext/integrate' THEN 1 END) AS "4" /* 회원휴면복구 */
, COUNT(CASE WHEN REQ_URL = '/api/member/ext/lastBuyInfo' THEN 1 END) AS "5" /* 회원휴면처리 */
, COUNT(*) AS TOTAL_CNT
FROM RETRY
) T
WHERE T.TOTAL_CNT > 0;
-- --------------------------------------------------------------------
-- OLD plan
-- --------------------------------------------------------------------
SQL_ID 1s1tfdnkncynj, child number 0
-------------------------------------
WITH RETRY AS ( SELECT LMR.REQ_URL FROM LST_MEMSYNC_RETRY LMR
, LST_MEMSYNC_ERR LME WHERE LMR.TRAN_ID = LME.TRAN_ID
AND PROC_STS_CD = 9500 AND LMR.REQ_URL IN (
'/api/member/ext/join' /* 회원가입 */ ,
'/api/member/ext/modify' /* 회원수정 */ ,
'/api/member/ext/secede' /* 회원탈퇴 */ ,
'/api/member/ext/integrate' /* 회원통합 */ ,
'/api/member/ext/lastBuyInfo' /* 최종구매일자 조회 */
) AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24,
'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') ) SELECT * FROM (
SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join' THEN 1
END) AS "1" /* 회원가입 */ , COUNT(CASE WHEN REQ_URL =
'/api/member/ext/modify' THEN 1 END) AS "2" /* 회원수정 */
, COUNT(CASE WHEN REQ_URL = '/api/member/ext/s '
Plan hash value: 1914046030
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 |00:00:00.01 | 4 | 3 |
| 1 | VIEW | | 1 | 0 |00:00:00.01 | 4 | 3 |
|* 2 | FILTER | | 1 | 0 |00:00:00.01 | 4 | 3 |
| 3 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 4 | 3 |
| 4 | NESTED LOOPS | | 1 | 0 |00:00:00.01 | 4 | 3 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| LST_MEMSYNC_RETRY | 1 | 0 |00:00:00.01 | 4 | 3 |
|* 6 | INDEX RANGE SCAN | IX02_LST_MEMSYNC_RETRY | 1 | 0 |00:00:00.01 | 4 | 3 |
|* 7 | INDEX RANGE SCAN | IDX_LST_MEMSYNC_ERR_01 | 0 | 0 |00:00:00.01 | 0 | 0 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(COUNT(*)>0)
5 - filter((TO_NUMBER("PROC_STS_CD")=9500 AND INTERNAL_FUNCTION("LMR"."REQ_URL")))
6 - access("LMR"."PROC_DT">=TO_DATE(TO_CHAR(SYSDATE@!-.0416666666666666666666666666666666666667,'YYYYMMDDHH24MISS
'),'YYYYMMDDHH24MISS') AND "LMR"."PROC_DT" IS NOT NULL)
7 - access("LMR"."TRAN_ID"="LME"."TRAN_ID")
-- --------------------------------------------------------------------
-- 인덱스 확인
-- --------------------------------------------------------------------
===============================
====== INDEX COLUMN CHECK =====
===============================
OWNER INDEX_NAME COLUMN_NAME PARTITIONED MB TABLESPACE_NAME STATUS UNIQUENES GLO
------------ ---------------------------- ---------------------------- ------------ -------- ------------------------------ -------- --------- ---
LFMMEM IX02_LST_MEMSYNC_RETRY PROC_DT NO 3,122 TS_LFM_IDX VALID NONUNIQUE YES
REQ_FAIL_CNT
IX03_LST_MEMSYNC_RETRY PROC_DT NO 7,085 TS_LFM_IDX VALID NONUNIQUE YES
PROC_STS_CD
REQ_URL
IX_LST_MEMSYNC_RETRY_01 REQ_URL NO 8,330 TS_LFM_IDX VALID NONUNIQUE YES
PROC_STS_CD
REQ_FAIL_CNT
PK_LST_MEMSYNC_RETRY TRAN_ID NO 7,213 TS_LFM_IDX VALID UNIQUE YES
-- --------------------------------------------------------------------
-- NEW plan
-- --------------------------------------------------------------------
SQL_ID 1s1tfdnkncynj, child number 1
-------------------------------------
WITH RETRY AS ( SELECT LMR.REQ_URL FROM LST_MEMSYNC_RETRY LMR
, LST_MEMSYNC_ERR LME WHERE LMR.TRAN_ID = LME.TRAN_ID
AND PROC_STS_CD = 9500 AND LMR.REQ_URL IN (
'/api/member/ext/join' /* 회원가입 */ ,
'/api/member/ext/modify' /* 회원수정 */ ,
'/api/member/ext/secede' /* 회원탈퇴 */ ,
'/api/member/ext/integrate' /* 회원통합 */ ,
'/api/member/ext/lastBuyInfo' /* 최종구매일자 조회 */
) AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24,
'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') ) SELECT * FROM (
SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join' THEN 1
END) AS "1" /* 회원가입 */ , COUNT(CASE WHEN REQ_URL =
'/api/member/ext/modify' THEN 1 END) AS "2" /* 회원수정 */
, COUNT(CASE WHEN REQ_URL = '/api/member/ext/s '
Plan hash value: 1100111422
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 |00:00:00.01 | 4 |
| 1 | VIEW | | 1 | 0 |00:00:00.01 | 4 |
|* 2 | FILTER | | 1 | 0 |00:00:00.01 | 4 |
| 3 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 4 |
| 4 | NESTED LOOPS | | 1 | 0 |00:00:00.01 | 4 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| LST_MEMSYNC_RETRY | 1 | 0 |00:00:00.01 | 4 |
|* 6 | INDEX RANGE SCAN | IX03_LST_MEMSYNC_RETRY | 1 | 0 |00:00:00.01 | 4 |
|* 7 | INDEX RANGE SCAN | IDX_LST_MEMSYNC_ERR_01 | 0 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(COUNT(*)>0)
6 - access("LMR"."PROC_DT">=TO_DATE(TO_CHAR(SYSDATE@!-.0416666666666666666666666666666666666667,'YYYYMMD
DHH24MISS'),'YYYYMMDDHH24MISS') AND "LMR"."PROC_DT" IS NOT NULL)
filter((TO_NUMBER("PROC_STS_CD")=9500 AND INTERNAL_FUNCTION("LMR"."REQ_URL")))
7 - access("LMR"."TRAN_ID"="LME"."TRAN_ID")
-- --------------------------------------------------------------------
-- 이전 플랜으로 SPM 등록
-- --------------------------------------------------------------------
-- sql id와 Plan hash value를 알고 있을때
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '1s1tfdnkncynj',
plan_hash_value => 1914046030);
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
Plans Loaded: 1
PL/SQL procedure successfully completed.
-- sql id만 알고 있을때
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '1s1tfdnkncynj');
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
-- --------------------------------------------------------------------
-- 등록된 SPB 조회
-- --------------------------------------------------------------------
-- SQL_HANDLE : SQL_ID에 대한 SPB 대표이름
-- PLAN_NAME : SQL_HANDLE에 종속된 Plan 이름
-- ENABLED : YES 일때 옵티마이저가 사용 가능
-- ACCEPT : PLAN_NAME 중 ACCEPT가 YES인 경우에만 SPB로 사용됨
-- FIXED : SQL_HANDLE 내에 여러 PLAN_NAME이 있을 경우 FIXED가 YES인 PLAN_NAME만 사용됨
set linesize 150
col plan_name for a40
select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, enabled, accepted, fixed
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME CREATED ENA ACC FIX
------------------------------ ---------------------------------------- ------------------- --- --- ---
SQL_1f1d24da6058d80a SQL_PLAN_1y794v9h5jq0a26ff63a8 2022/06/09 13:46:24 YES NO NO
SQL_1f1d24da6058d80a SQL_PLAN_1y794v9h5jq0aa1311447 2022/06/09 13:45:46 YES YES NO
-- --------------------------------------------------------------------
-- 쿼리 재수행 후 이전 plan 사용 하는지 확인
-- --------------------------------------------------------------------
SQL_ID 1s1tfdnkncynj, child number 2
-------------------------------------
WITH RETRY AS ( SELECT LMR.REQ_URL FROM LST_MEMSYNC_RETRY LMR
, LST_MEMSYNC_ERR LME WHERE LMR.TRAN_ID = LME.TRAN_ID
AND PROC_STS_CD = 9500 AND LMR.REQ_URL IN (
'/api/member/ext/join' /* 회원가입 */ ,
'/api/member/ext/modify' /* 회원수정 */ ,
'/api/member/ext/secede' /* 회원탈퇴 */ ,
'/api/member/ext/integrate' /* 회원통합 */ ,
'/api/member/ext/lastBuyInfo' /* 최종구매일자 조회 */
) AND LMR.PROC_DT >= TO_DATE(TO_CHAR(SYSDATE - 1/24,
'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS') ) SELECT * FROM (
SELECT COUNT(CASE WHEN REQ_URL = '/api/member/ext/join' THEN 1
END) AS "1" /* 회원가입 */ , COUNT(CASE WHEN REQ_URL =
'/api/member/ext/modify' THEN 1 END) AS "2" /* 회원수정 */
, COUNT(CASE WHEN REQ_URL = '/api/member/ext/s '
Plan hash value: 1914046030
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 |00:00:00.01 | 4 |
| 1 | VIEW | | 1 | 0 |00:00:00.01 | 4 |
|* 2 | FILTER | | 1 | 0 |00:00:00.01 | 4 |
| 3 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 4 |
| 4 | NESTED LOOPS | | 1 | 0 |00:00:00.01 | 4 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| LST_MEMSYNC_RETRY | 1 | 0 |00:00:00.01 | 4 |
|* 6 | INDEX RANGE SCAN | IX02_LST_MEMSYNC_RETRY | 1 | 0 |00:00:00.01 | 4 |
|* 7 | INDEX RANGE SCAN | IDX_LST_MEMSYNC_ERR_01 | 0 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(COUNT(*)>0)
5 - filter((TO_NUMBER("PROC_STS_CD")=9500 AND INTERNAL_FUNCTION("LMR"."REQ_URL")))
6 - access("LMR"."PROC_DT">=TO_DATE(TO_CHAR(SYSDATE@!-.0416666666666666666666666666666666666667,'YYYYMMD
DHH24MISS'),'YYYYMMDDHH24MISS') AND "LMR"."PROC_DT" IS NOT NULL)
7 - access("LMR"."TRAN_ID"="LME"."TRAN_ID")
Note
-----
- SQL plan baseline SQL_PLAN_1y794v9h5jq0aa1311447 used for this statement
-- Note 부분에 SQL plan baseline 에서 SQL_PLAN_1y794v9h5jq0aa1311447 사용하고 있다고 표기
-- --------------------------------------------------------------------
-- SPM 삭제
-- --------------------------------------------------------------------
SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_1f1d24da6058d80a');
DBMS_OUTPUT.put_line('Plans Dropped: ' || l_plans_dropped);
END;
/
Plans Dropped: 2
PL/SQL procedure successfully completed.