JJON's StorY

-- --------------------------------------------------------------------
-- 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 을 사용하는것을 확인

-- 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.