Stored Outlines (실행계획 고정)

특정 SQL의 실행계획 번번히 바뀌는 경우가 발생하였다.

바뀔때 plan이 이상하게 풀릴경우 응답시간이 10분을 초과하는 일이 발생하여.

특정 SQL에서 plan을 고정시킬때 오라클에서는 stored outlines 를 제공한다.

 

-- outline 생성  (동적 SQL에 대한 plan 고정)

create or replace outline mlb_bp_contract_1  for category mlb
on
SELECT /*+ NO_MERGE(A) NO_PUSH_PRED(A) */ A.SERVICE_CODE, A.SERVICE_NAME,
    DECODE(C.POTTERY, 'O', DECODE(C.PC_CODE, :1, DECODE(NVL(B.SERVICE_CODE, 'NULL'), 'NULL', 'X', DECODE(A.MUSIC_CODE, 'NULL', 'X', 'O')), 'X'),
                        DECODE(NVL(B.SERVICE_CODE, 'NULL'), 'NULL', 'X', DECODE(A.MUSIC_CODE,'NULL', 'X', 'O')))  SERVICE_STATUS,
    (SELECT DECODE(COUNT(1), 0, 'X', 'O') FROM TMLBG010 WHERE MUSIC_CODE = :2 AND SERVICE_CODE = A.SERVICE_CODE AND PC_CODE = :3) CONTENTS_STATUS
FROM (
   SELECT NVL(C.MUSIC_CODE, 'NULL') MUSIC_CODE, S.SERVICE_CODE, S.SERVICE_NAME
   FROM TMLBC010 C, TMLBS010 S
   WHERE C.MUSIC_CODE(+) = :4
     AND C.SERVICE_CODE(+) = S.SERVICE_CODE
     AND C.PC_CODE1(+) IS NOT NULL
     AND C.PC_CODE2(+) IS NOT NULL
     AND C.PC_CODE3(+) IS NOT NULL
) A, (
   SELECT SERVICE_CODE
   FROM TMLBS050
   WHERE USER_ID=:5
     AND SERVICE_YN = 'Y'
) B, (
   SELECT MUSIC_CODE, SERVICE_CODE, PC_CODE, 'O' POTTERY
   FROM TMLBC050
   WHERE MUSIC_CODE = :6
     AND TO_CHAR(SYSDATE, 'YYYYMMDD') BETWEEN START_DATE AND END_DATE
     AND USE_YN = 'Y'
) C
WHERE A.SERVICE_CODE = B.SERVICE_CODE(+)
  AND A.SERVICE_CODE = C.SERVICE_CODE(+);
==> Outline created.   
       

-- 생성한 outline 조회         
select * from user_outlines;
-- 처음에는 USED 값이 UNUSED 로 지정되어있다.

-- outline에 저장된 SQL의 plan확인.
select * from user_outline_hints;

-- 생성한 outline이 여러개 일때 그중 특정 category의 outline을 사용                  
alter system set use_stored_outlines = mlb;

 

--  특별한 session에 outline을 적용하고자 한다면 아래를..

alter session set use_stored_outlines = mlb;

-- use_stored_outlines 를 한후에 저장된 SQL을 실행하고 나서 user_outlines 테이블의 USED 컬럼 값이 USED로 바뀌면 outline이 제대로 사용됨을 확인 할 수 있다.
 

참고로 outline을 생성하고자 할때는 생성할 수 있는 권한을 가지고 있어야 한다.