[19c] Outline 기능 이용하여 Hint 없이 실행계획 변경

해당 테스트는 출처의 테스트를 19c에서 수행해본 결과 정리입니다.

 

Outline을 사용하는 방법은 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

Changes for Earlier Releases of Oracle Database

테스트 결과를 요약하면, 실행계획을 Hint 없이 변경할 수 있습니다. 하지만 파라미터 변경이 필요하고

파라미터 변경이 되었을때에만 실행계획이 변경되는 것을 확인하였습니다.

단독으로는 사용하기에는 어려움이 있을 것 같습니다.(주관적인 저의 생각)

 

테스트시나리오는 아래와 같습니다.

1.테이블을 생성 후 데이터를 입력하고 통계정보를 수집하여 실행계획을 확인합니다.(Index를 이용한 Range Scan) (Slow 주석)

2.Hint의 사용없이 Full Table Scan으로 변경. (Fast 주석)

 

1.테이블 생성

테이블 및 인덱스 생성
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);

 

2. 쿼리 수행

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 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 실행계획 관리