오라클 19c AWR 스냅샷의 플랜을 SPM(SQL Plan Management)으로 고정

OS 환경 : Oracle Linux 8.7 (64bit)

 

DB 환경 : Oracle Database 19.27.0.0

 

방법 : 오라클 19c AWR에만 존재하는 좋은 플랜을 SPM(SQL Plan Management)으로 고정

본문에서는 샘플 쿼리를 수행하여 AWR에는 좋은 플랜이 존재하지만
Cursor(라이브러리 캐시)에는 안 좋은 플랜만 남아 쿼리가 느려진 상황을 가정함
이 상태에서 SPM을 이용해 AWR에만 존재하는 정상 플랜을 찾아 Baseline으로 고정하는 방법을 설명함

 

 

SPM(SQL Plan Management)이란?
SPM(SQL Plan Management)은 SQL 실행 계획이 예기치 않게 바뀌어 성능이 떨어지는 것을 방지하기 위한 옵티마이저의 계획 관리 프레임워크임
SPM은 SQL 실행 계획의 안정성을 보장하기 위해 승인된 실행 계획만 사용하도록 SQL Plan Baseline을 생성하고 유지함
옵티마이저가 새로운 플랜을 찾더라도 성능이 더 좋거나 동등하게 검증된 경우에만 Baseline에 추가해 사용을 허용함
DB 통계 변화, 옵티마이저 버전 업그레이드, 패치 등으로 플랜이 바뀌어도 성능 회귀가 발생하지 않도록 보호함
SPM은 플랜 캡처, 플랜 저장, 플랜 진화(evolution)를 포함한 전체 관리 기능을 제공함
참고로 DBMS_SPM 패키지를 통해 Baseline 생성, 조회, 수정, 삭제 등 모든 관리 작업을 수행할 수 있음

 

 

테스트
샘플 테이블 및 인덱스 생성

1 2 3 4 5 6

SQL>  create table t_temp as  select level as id, rpad('a', 100, 'a') as val  from dual connect by level <= 10000;   create index ix_t_temp_id on t_temp(id);

 

 

기존 SQL 수행(awr에 샘플링되기 위해 여러번 수행)
(인덱스를 잘 타는 좋은 쿼리로 가정함)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> SELECT * FROM t_temp WHERE id <= 10;           ID VAL ---------- ----------------------------------------------------------------------------------------------------          1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          2 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          3 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          4 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          5 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          6 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          7 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          8 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          9 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         10 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   10 rows selected.

 

 

직전 sql의 sql_id 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL>  SELECT     s.sql_id,     SUBSTR(q1.sql_text, 1, 20) AS sql_text_20,     s.prev_sql_id,     SUBSTR(q2.sql_text, 1, 20) AS prev_sql_text_20 FROM v$session s,      v$sql q1,      v$sql q2 WHERE s.sid = (SELECT sid FROM v$mystat WHERE rownum = 1)   AND s.sql_id = q1.sql_id(+)   AND s.sql_child_number = q1.child_number(+)   AND s.prev_sql_id = q2.sql_id(+)   AND 0 = q2.child_number(+);   SQL_ID        SQL_TEXT_20                              PREV_SQL_ID   PREV_SQL_TEXT_20 ------------- ---------------------------------------- ------------- ---------------------------------------- agaf9ncx78yuu SELECT     s.sql_id,                     a58w5hkvg4gfs SELECT * FROM t_temp

a58w5hkvg4gfs이 직전 sql의 sql_id임

 

 

a58w5hkvg4gfs 쿼리의 플랜 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

SQL> select * from dbms_xplan.display_cursor('a58w5hkvg4gfs', NULL, 'TYPICAL');   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID  a58w5hkvg4gfs, child number 0 ------------------------------------- SELECT * FROM t_temp WHERE id <= 10   Plan hash value: 362386456   ---------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |              |       |       |     3 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_TEMP       |    10 |   650 |     3   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN                  | IX_T_TEMP_ID |    10 |       |     2   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("ID"<=10)   Note -----    - dynamic statistics used: dynamic sampling (level=2)     23 rows selected.

index scan을 하고 있음

 

 

awr 스냅샷 수동 생성

1 2 3

SQL> exec dbms_workload_repository.create_snapshot;   PL/SQL procedure successfully completed.

 

 

인덱스 비용을 증가시켜서 Full Scan 유도

1 2 3

SQL> alter session set optimizer_index_cost_adj = 3000;   Session altered.

 

 

동일 SQL 재수행

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> SELECT * FROM t_temp WHERE id <= 10;           ID VAL ---------- ----------------------------------------------------------------------------------------------------          1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          2 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          3 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          4 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          5 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          6 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          7 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          8 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          9 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         10 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   10 rows selected.

 

 

a58w5hkvg4gfs 쿼리 플랜 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

SQL> select * from dbms_xplan.display_cursor('a58w5hkvg4gfs', NULL, 'TYPICAL');   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID  a58w5hkvg4gfs, child number 0 ------------------------------------- SELECT * FROM t_temp WHERE id <= 10   Plan hash value: 362386456   ---------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |              |       |       |     3 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_TEMP       |    10 |   650 |     3   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN                  | IX_T_TEMP_ID |    10 |       |     2   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("ID"<=10)   Note -----    - dynamic statistics used: dynamic sampling (level=2)   SQL_ID  a58w5hkvg4gfs, child number 1  <<-- !! ------------------------------------- SELECT * FROM t_temp WHERE id <= 10   Plan hash value: 2604469312   ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |    47 (100)|          | |*  1 |  TABLE ACCESS FULL| T_TEMP |    10 |   650 |    47   (0)| 00:00:01 | ----------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("ID"<=10)   Note -----    - dynamic statistics used: dynamic sampling (level=2)     45 rows selected.

플랜이 2개 표시되는데 child_number 1이 나중에 수행된 쿼리 플랜임
현재 full scan을 하고 있음

 

 

xplan에서도 확인 가능하지만 v$sql에서 해당 쿼리들의 sql_id와 plan_hash_value 확인

1 2 3 4 5 6 7 8 9 10

SQL>  col sql_text_10 for a20 select sql_id, plan_hash_value, child_number, buffer_gets, substr(sql_text, 1, 20) sql_text_20 from v$sql where sql_id = 'a58w5hkvg4gfs';   SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER BUFFER_GETS SQL_TEXT_20 ------------- --------------- ------------ ----------- ---------------------------------------- a58w5hkvg4gfs       362386456            0         302 SELECT * FROM t_temp a58w5hkvg4gfs      2604469312            1        3023 SELECT * FROM t_temp

plan_hash_value 2604469312이 full scan 플랜이고, 362386456이 index scan 플랜임

 

 

awr 스냅샷 확인

1 2 3 4 5 6 7 8 9 10 11 12

SQL>  select a.instance_number inst_id, a.snap_id, a.sql_id, a.plan_hash_value, to_char(begin_interval_time,'yyyy/mm/dd hh24:mi:ss') btime,  abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes, executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)", buffer_gets_delta from dba_hist_sqlstat a, dba_hist_snapshot b where sql_id in ('a58w5hkvg4gfs') and a.snap_id=b.snap_id and a.instance_number=b.instance_number order by snap_id desc, a.instance_number;      INST_ID    SNAP_ID SQL_ID        PLAN_HASH_VALUE BTIME                  MINUTES EXECUTIONS avg duration (sec) BUFFER_GETS_DELTA ---------- ---------- ------------- --------------- ------------------- ---------- ---------- ------------------ -----------------          1       3112 a58w5hkvg4gfs       362386456 2026/05/24 19:54:26          0         19              .0005               302

awr에 인덱스를 사용한 플랜(plan_hash_value 362386456)이 존재함

 

 

awr에 존재하는 플랜으로 spm에 baseline 등록(좋은 플랜)
(index scan하는 플랜(좋은 쿼리 plan_hash_value 362386456이)는 awr에만 존재한다고 가정하고 이 플랜을 베이스라인에 등록함)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL>  set serveroutput on declare  ret number;  begin   ret := dbms_spm.load_plans_from_awr(  begin_snap => 3111,   -- 시작 스냅샷 ID  end_snap => 3112,   -- 종료 스냅샷 ID  basic_filter => q'[ sql_id = 'a58w5hkvg4gfs' and plan_hash_value = 362386456 ]',          enabled => 'YES',          fixed => 'YES');   dbms_output.put_line('LOAD PLANS : '|| ret); end; /   LOAD PLANS : 1   PL/SQL procedure successfully completed.

 

 

등록한 sql plan baseline 확인

1 2 3 4 5 6 7 8 9 10

SQL> set lines 200 pages 1000 col PLAN_NAME for a30 select sql_handle, plan_name, executions, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, accepted, fixed from dba_sql_plan_baselines where signature in ( select exact_matching_signature FROM v$sql WHERE sql_id='a58w5hkvg4gfs');   SQL_HANDLE                     PLAN_NAME                      EXECUTIONS CREATED             ACC FIX ------------------------------ ------------------------------ ---------- ------------------- --- --- SQL_2853025b1b55c455           SQL_PLAN_2hns2bcdpbj2paf835ec9         11 2026/05/24 19:55:32 YES YES

좋은 플랜이 spm에 등록됨

 

 

실제 플랜 확인을 위해 세션 레벨로 statistics_level 변경

1 2 3

SQL> alter session set statistics_level = all;   Session altered.

 

 

a58w5hkvg4gfs 쿼리 재수행

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> SELECT * FROM t_temp WHERE id <= 10;           ID VAL ---------- ----------------------------------------------------------------------------------------------------          1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          2 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          3 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          4 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          5 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          6 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          7 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          8 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          9 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         10 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   10 rows selected.

 

 

a58w5hkvg4gfs 쿼리 플랜 재확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197

SQL> select * from dbms_xplan.display_cursor('a58w5hkvg4gfs', NULL, 'ADVANCED LAST'); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID  a58w5hkvg4gfs, child number 0 ------------------------------------- SELECT * FROM t_temp WHERE id <= 10   Plan hash value: 362386456   ---------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |              |       |       |     3 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_TEMP       |    10 |   650 |     3   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN                  | IX_T_TEMP_ID |    10 |       |     2   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------   Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------      1 - SEL$1 / T_TEMP@SEL$1    2 - SEL$1 / T_TEMP@SEL$1   Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('19.1.0')       DB_VERSION('19.1.0')       OPT_PARAM('_optimizer_gather_stats_on_load' 'false')       OPT_PARAM('approx_for_count_distinct' 'true')       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       INDEX_RS_ASC(@"SEL$1" "T_TEMP"@"SEL$1" ("T_TEMP"."ID"))       BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T_TEMP"@"SEL$1")       END_OUTLINE_DATA   */   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("ID"<=10)   Column Projection Information (identified by operation id): -----------------------------------------------------------      1 - "ID"[NUMBER,22], "T_TEMP"."VAL"[VARCHAR2,100]    2 - "T_TEMP".ROWID[ROWID,10], "ID"[NUMBER,22]   Note -----    - dynamic statistics used: dynamic sampling (level=2)   Query Block Registry: ---------------------     <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T_TEMP]]></t><s><![CDATA[SEL$1]]></         s></h></f></q>     SQL_ID  a58w5hkvg4gfs, child number 1 ------------------------------------- SELECT * FROM t_temp WHERE id <= 10   Plan hash value: 2604469312   ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |    47 (100)|          | |*  1 |  TABLE ACCESS FULL| T_TEMP |    10 |   650 |    47   (0)| 00:00:01 | ----------------------------------------------------------------------------   Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------      1 - SEL$1 / T_TEMP@SEL$1   Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('19.1.0')       DB_VERSION('19.1.0')       OPT_PARAM('_optimizer_gather_stats_on_load' 'false')       OPT_PARAM('approx_for_count_distinct' 'true')       OPT_PARAM('optimizer_index_cost_adj' 3000)       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       FULL(@"SEL$1" "T_TEMP"@"SEL$1")       END_OUTLINE_DATA   */   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("ID"<=10)   Column Projection Information (identified by operation id): -----------------------------------------------------------      1 - "ID"[NUMBER,22], "T_TEMP"."VAL"[VARCHAR2,100]   Note -----    - dynamic statistics used: dynamic sampling (level=2)   Query Block Registry: ---------------------     <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T_TEMP]]></t><s         ><![CDATA[SEL$1]]></s></h></f></q>     SQL_ID  a58w5hkvg4gfs, child number 3  <<-- !! ------------------------------------- SELECT * FROM t_temp WHERE id <= 10   Plan hash value: 362386456   ---------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |              |       |       |    90 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_TEMP       |    10 |   650 |    90   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN                  | IX_T_TEMP_ID |    10 |       |    60   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------   Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------      1 - SEL$1 / T_TEMP@SEL$1    2 - SEL$1 / T_TEMP@SEL$1   Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('19.1.0')       DB_VERSION('19.1.0')       OPT_PARAM('_optimizer_gather_stats_on_load' 'false')       OPT_PARAM('approx_for_count_distinct' 'true')       OPT_PARAM('optimizer_index_cost_adj' 3000)       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       INDEX_RS_ASC(@"SEL$1" "T_TEMP"@"SEL$1" ("T_TEMP"."ID"))       BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T_TEMP"@"SEL$1")       END_OUTLINE_DATA   */   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("ID"<=10)   Column Projection Information (identified by operation id): -----------------------------------------------------------      1 - "ID"[NUMBER,22], "T_TEMP"."VAL"[VARCHAR2,100]    2 - "T_TEMP".ROWID[ROWID,10], "ID"[NUMBER,22]   Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 8 ---------------------------------------------------------------------------      0 -  STATEMENT            -  ALL_ROWS            -  DB_VERSION('19.1.0')            -  IGNORE_OPTIM_EMBEDDED_HINTS            -  OPTIMIZER_FEATURES_ENABLE('19.1.0')            -  OPT_PARAM('_optimizer_gather_stats_on_load' 'false')            -  OPT_PARAM('approx_for_count_distinct' 'true')      1 -  SEL$1 / T_TEMP@SEL$1            -  BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T_TEMP"@"SEL$1")            -  INDEX_RS_ASC(@"SEL$1" "T_TEMP"@"SEL$1" ("T_TEMP"."ID"))   Note -----    - dynamic statistics used: dynamic sampling (level=2)    - SQL plan baseline SQL_PLAN_2hns2bcdpbj2paf835ec9 used for this statement   Query Block Registry: ---------------------     <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T_TEMP]]></t><s><![CDATA[SEL$1]]></         s></h></f></q>       192 rows selected.

child_number 0, 1 은 이전플랜이라 무시하면 되고,
child_number 3를 보면 Note 부분에 SQL plan baseline을 이용하였다고 표시됨
optimizer_index_cost_adj = 3000로 설정했음에도 index를 잘 타는것을 확인할 수 있음

 

 

참고용1. cursor에 있는 정보로 baseline 등록

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL>  set serveroutput on declare  ret number;  begin   ret := dbms_spm.load_plans_from_cursor_cache(          sql_id => '4kdvdryf0774f',          plan_hash_value => 3177216401,          enabled => 'YES',          fixed => 'YES');   dbms_output.put_line('LOAD PLANS : '|| ret); end; /

 

 

참고용2. 베이스라인 제거

1 2 3 4 5 6 7 8 9 10 11

SQL> set serveroutput on declare  ret number;  begin   ret := dbms_spm.drop_sql_plan_baseline(          sql_handle => 'SQL_2853025b1b55c455',          plan_name => 'SQL_PLAN_2hns2bcdpbj2paf835ec9');   dbms_output.put_line('DROP PLANS : '|| ret); end; /

 

 

참고용3. baseline에 등록된 쿼리 및 플랜을 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

SQL> select * from dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_2853025b1b55c455',plan_name => 'SQL_PLAN_2hns2bcdpbj2paf835ec9');   PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -------------------------------------------------------------------------------- SQL handle: SQL_2853025b1b55c455 SQL text: SELECT * FROM t_temp WHERE id <= 10 --------------------------------------------------------------------------------   -------------------------------------------------------------------------------- Plan name: SQL_PLAN_2hns2bcdpbj2paf835ec9         Plan id: 2944622281 Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD-FROM-AWR Plan rows: From dictionary --------------------------------------------------------------------------------   Plan hash value: 362386456   ---------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |              |       |       |     3 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_TEMP       |    10 |   650 |     3   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN                  | IX_T_TEMP_ID |    10 |       |     2   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("ID"<=10)   Note -----    - dynamic statistics used: dynamic sampling (level=2)   30 rows selected.

 

 

결론 :
본문 방식을 이용해 플랜이 변경되었고 정상 플랜이 cursor에 남아있지 않눈 문제가 발생했을때
spm으로 awr에 존재하는 좋은 플랜으로 고정 하여 효과적으로 성능 저하 상황에 대비할 수 있음

 

 

참조 : 

SPM(SQL Plan Management) SQL PLAN BASELINE 이용한 Export / Import 테스트 (실행계획 변경) ( https://positivemh.tistory.com/447 )
SQL을 변경하지 않고 실행계획 변경 테스트(SQL Plan Management) ( https://positivemh.tistory.com/217 )
오라클 19c SQL Profile 플랜 고정 방법 ( https://positivemh.tistory.com/1377 )
오라클 19c SQL Profile 등록된 플랜 SPM(SQL Plan Management)으로 변경 ( https://positivemh.tistory.com/1378 )
오라클 19c SQL Profile 등록된 플랜 SPM으로 변경 자동화 스크립트 ( https://positivemh.tistory.com/1379 )
오라클 19c 실행계획 변경 시 spm 적용 방법 ( https://positivemh.tistory.com/844 )
오라클 19c 하드파싱 쿼리 플랜 변경 의심시 조치방법 ( https://positivemh.tistory.com/849 )