-- --------------------------------------------------------------------
-- SPM - 튜닝된 SQL의 실행계획을 등록하고 적용 하는 방법
-- --------------------------------------------------------------------
-- --------------------------------------------------------------------
-- 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;
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: 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 등록
-- --------------------------------------------------------------------
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;
/
Plans Loaded: 2
PL/SQL procedure successfully completed.
-- --------------------------------------------------------------------
-- SPM 등록 후 조회
-- --------------------------------------------------------------------
set linesize 150
col plan_name for a40
select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, optimizer_cost, enabled, accepted, fixed
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME CREATED OPTIMIZER_COST ENA ACC FIX
------------------------------ ---------------------------------------- ------------------- -------------- --- --- ---
SQL_1f1d24da6058d80a SQL_PLAN_1y794v9h5jq0a26ff63a8 2022/06/10 10:37:25 6 YES YES NO
SQL_1f1d24da6058d80a SQL_PLAN_1y794v9h5jq0aa1311447 2022/06/10 10:37:25 43 YES YES NO
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: 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")
Note
-----
- SQL plan baseline SQL_PLAN_1y794v9h5jq0a26ff63a8 used for this statement
-- 정상적으로 SQL_PLAN_1y794v9h5jq0a26ff63a8 사용
-- --------------------------------------------------------------------
-- SQL 튜닝
-- --------------------------------------------------------------------
-- IX03_LST_MEMSYNC_RETRY 에서 IX02_LST_MEMSYNC_RETRY 를 사용하도록 변경
WITH RETRY AS (
SELECT /*+ INDEX (LMR IX02_LST_MEMSYNC_RETRY) */
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;
SQL_ID 6h3wa41tqv9sc, child number 0
-------------------------------------
WITH RETRY AS ( SELECT /*+ INDEX (LMR IX02_LST_MEMSYNC_RETRY) */
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" /* 회원수정 */
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")
-- --------------------------------------------------------------------
-- 튜닝 된 sql 을 SPM 등록
-- --------------------------------------------------------------------
-- sql_id 와 plan_hash_value 는 튜닝된 내용으로 기입하고
-- sql_handle 은 원본 쿼리가 등록된 SPM 핸들 값을 넣는다.
-- 튜닝된 plan을 사용하도록 fixed 를 yes 로 고정한다.
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '6h3wa41tqv9sc',
plan_hash_value => 1914046030,
sql_handle => 'SQL_1f1d24da6058d80a',
fixed => 'YES',
enabled => 'YES');
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
Plans Loaded: 1
PL/SQL procedure successfully completed.
set linesize 150
col plan_name for a40
select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, optimizer_cost, enabled, accepted, fixed
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME CREATED OPTIMIZER_COST ENA ACC FIX
------------------------------ ---------------------------------------- ------------------- -------------- --- --- ---
SQL_1f1d24da6058d80a SQL_PLAN_1y794v9h5jq0a26ff63a8 2022/06/10 10:37:25 6 YES YES NO
SQL_1f1d24da6058d80a SQL_PLAN_1y794v9h5jq0aa1311447 2022/06/10 10:41:44 43 YES YES YES
-- --------------------------------------------------------------------
-- 튜닝 전 sql 을 SPM 에서 비활성화
-- --------------------------------------------------------------------
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
SQL_HANDLE => 'SQL_1f1d24da6058d80a',
PLAN_NAME => 'SQL_PLAN_1y794v9h5jq0a26ff63a8',
ATTRIBUTE_NAME => 'ENABLED',
ATTRIBUTE_VALUE => 'NO');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
Plans Altered: 1
PL/SQL procedure successfully completed.
set linesize 150
col plan_name for a40
select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, optimizer_cost, enabled, accepted, fixed
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME CREATED OPTIMIZER_COST ENA ACC FIX
------------------------------ ---------------------------------------- ------------------- -------------- --- --- ---
SQL_1f1d24da6058d80a SQL_PLAN_1y794v9h5jq0a26ff63a8 2022/06/10 10:37:25 6 NO YES NO
SQL_1f1d24da6058d80a SQL_PLAN_1y794v9h5jq0aa1311447 2022/06/10 10:41:44 43 YES YES YES
-- --------------------------------------------------------------------
-- 원본 SQL 수행하여 튜닝된 plan 사용 하는지 확인
-- --------------------------------------------------------------------
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;
SQL_ID 1s1tfdnkncynj, child number 3
-------------------------------------
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
-- 튜닝된 plan 의 인덱스와 SQL_PLAN_1y794v9h5jq0aa1311447 을 사용하는것을 확인