힌트없이 실행계획 변경 (outline 기능 , 19c 신규) 문서 원본 보기

== Outline , Hint 없이 실행계획 변경 ==
* 19c new feature
# 8i부터 추가된 기능으로 실행계획의 안정화하는 방법 중 하나
# 11g R1부터 deprecated(권장하지 않음)된 기능, 대신 SPM(SQL Pan Management) 기능을 사용하도록 권장
## SPM은 유료기능이므로 확인 후 사용
 
* 원문 글
The use of stored outlines is deprecated in Oracle Database 11g Release 1 (11.1). Instead, you should use the SQL plan management feature that enables the optimizer to maintain a history of execution plans for a SQL statement. Using the execution plan history, the optimizer can detect a new plan representing a plan change for a SQL statement. When the optimizer detects a new plan, it stores the new plan and marks it for performance evaluation and uses the old (currently known good) plan. The optimizer uses the new plan only after its performance is verified to be better than that of the old plan. A SQL plan baseline consists of a set of known good plans for a SQL statement.
 
출처 : https://docs.oracle.com/database/121/UPGRD/changes.htm#UPGRD12487

=== 결론 ===
# 실행계획을 Hint 없이 변경, 하지만 파라미터 변경 필수

=== 테스트 시나리오 ===
# 테이블을 생성 후 데이터를 입력하고 통계정보를 수집하여 실행계획 확인.(Index를 이용한 Range Scan) (Slow 주석)
# Hint의 사용없이 Full Table Scan으로 변경. (Fast 주석)

# 테이블 생성 / 데이터 입력 
## 테이블 및 인덱스 생성
CREATE TABLE T_OUTLN(C1 INT, C2 INT);
CREATE INDEX T_OUTLN_I2 ON T_OUTLN(C2);
## 데이터 입력
INSERT INTO T_OUTLN
SELECT LEVEL, MOD(LEVEL, 10) 
  FROM DUAL 
CONNECT BY LEVEL <= 100000;
COMMIT;
## 통계정보생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_OUTLN', CASCADE=>TRUE, NO_INVALIDATE=>FALSE);
 
# 쿼리 수행
#:<source lang=sql>
SELECT /* Slow */
* FROM T_OUTLN
WHERE C1=1 AND C2=1;
</source>
## 실행계획 확인
<source lang=sql>
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
</source>
-------------------------------------------------------------------
| Id  | Operation                           | Name       | E-Rows |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |        |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_OUTLN    |      1 |
|*  2 |   INDEX RANGE SCAN                  | T_OUTLN_I2 |      1 |
-------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C1"=1)
   2 - access("C2"=1)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
 
3. 쿼리에 대한 OutLine과 Category 생성
ALTER SESSION SET CREATE_STORED_OUTLINES = TEST_OUTLN;

SQL 실행
SELECT /* Slow */ *
FROM T_OUTLN
WHERE C1 = 1
 AND C2 = 1;
ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;


생성된 OUTLINE 확인
SELECT NAME, CATEGORY, USED, ENABLED, SQL_TEXT
FROM USER_OUTLINES
WHERE CATEGORY='TEST_OUTLN'
;
 
4.변경할 쿼리 수행(FULL)
SELECT /* Fast */ /*+ FULL(T_OUTLN)*/*
FROM T_OUTLN
WHERE C1 = 1
 AND C2 = 1;
 

실행계획 확인
select *
  from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
  

  
----------------------------------------------
| Id  | Operation         | Name    | E-Rows |
----------------------------------------------
|   0 | SELECT STATEMENT  |         |        |
|*  1 |  TABLE ACCESS FULL| T_OUTLN |      8 |
----------------------------------------------
 
5.변경할 쿼리 OUTLINE 생성
CREATE OUTLINE TMP_OUTLN FOR CATEGORY TEST_OUTLN ON
SELECT /* Fast */ /*+ FULL(T_OUTLN)*/*
FROM T_OUTLN
WHERE C1 = 1
 AND C2 = 1;


outline의 이름 확인
select * from user_outlines;


AS-IS의 Outline 이름
SYS_OUTLINE_22060318083861522

To-BE의 Outline 이름
TMP_OUTLN
 
 
*********핵심***********
7. AS-IS OUTLINE과 TO-BE OUTLINE overwrite.


UPDATE OUTLN.OL$
 SET HINTCOUNT = (SELECT HINTCOUNT
 FROM OUTLN.OL$
 WHERE OL_NAME = 'SYS_OUTLINE_22060318083861522') --기존의 OL_NAME
WHERE OL_NAME = 'TMP_OUTLN';



--AS-IS 의 힌트정보를 삭제
DELETE
 FROM OUTLN.OL$HINTS
WHERE OL_NAME = 'SYS_OUTLINE_22060318083861522';



--To-BE 의 OL_NAME 을 AS-IS 의 이름으로 변경
UPDATE OUTLN.OL$HINTS
 SET OL_NAME = 'SYS_OUTLINE_22060318083861522'
WHERE OL_NAME = 'TMP_OUTLN';


--AS-IS 의 노드정보 삭제
DELETE
 FROM OUTLN.OL$NODES
WHERE OL_NAME = 'SYS_OUTLINE_22060318083861522';


--TO-BE 의 OL_NAME 을 AS-IS 의 이름으로 변경
UPDATE OUTLN.OL$NODES
 SET OL_NAME = 'SYS_OUTLINE_22060318083861522'
WHERE OL_NAME = 'TMP_OUTLN';
COMMIT;
 
해당 부분에서 use_stored_outlines 파라미터를 변경하지 않고 사용하면  AS-IS의 실행계획으로 수행됩니다.
 
8. 변경되었는지 확인
ALTER SESSION SET use_stored_outlines=TEST_OUTLN;  -----중요!! 해당 category를 사용하겠다라고 선언해야 실행계획이 바뀝니다.

SELECT /* Slow */ 
* FROM T_OUTLN
WHERE C1=1 AND C2=1;


실행계획
select *
  from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
  

----------------------------------------------
| Id  | Operation         | Name    | E-Rows |
----------------------------------------------
|   0 | SELECT STATEMENT  |         |        |
|*  1 |  TABLE ACCESS FULL| T_OUTLN |      1 |
----------------------------------------------
 
아래는 수행시 꼬였을 때 Outline 삭제 및 Share Pool의 쿼리 flush하는 방법입니다.
--만약 변경이 안되었다면, Shared Pool 상에 올라온 SQL을 flush 시키고 다시 수행
select address,hash_value from v$sqlarea
where sql_id='fr2udpf90afvu'; ---기존이 이미 memory상에 올라와 있는 Sql의 id

--exec sys.dbms_shared_pool.purge('07000200998C4AF0,3472762523','C'); --address와 hash_value값 입력
exec sys.dbms_shared_pool.purge('000000012E0A4630,2449816442','C');

--OUTLINE 삭제하는 쿼리
BEGIN
DBMS_OUTLN.drop_by_cat (cat => 'DEFAULT'); --CATEGORY 이름 입력
END;
/
 
출처 : [기술노트 76회] SQL 실행계획 관리
[[category:oracle]]