오라클 19c 실행계획 변경 시 spm 적용 방법

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 19c 실행계획 변경 시 spm 적용 방법

테이블 ddl이나 특정 상황으로 인해 sql의 실행계획이 변경 될 수 있음

이 때 문제되는 sql id를 찾을 경우 spm을 이용해서 신속하게 기존 실행계획으로 변경시킬 수 있음

spm을 이용해 일단 빠르게 이전 실행계획으로 고정 시킨 다음

추후 쿼리에 원하는 방식으로 풀리게끔 힌트를 추가할 수 있음

 

 

0. 문제되는 쿼리 sql_id 확인

운영팀을 통해 sql_id를 받거나, ash 모니터링을 통해 문제 sql 식별

SQL> 
select * from (
select 
user_id,
sql_id,
event,
count(*) "cnt",
sum(wait_time) "wait_time",
sum(time_waited) "time_wait"
from v$active_session_history
where 1=1
and sample_time between sysdate - interval '1' minute and sysdate
--and sample_time between to_date('2022/11/28 09:00:00', 'yyyy/mm/dd hh24:mi:ss')
--and to_date('2022/11/28 09:05:00', 'yyyy/mm/dd hh24:mi:ss')
and session_type != 'background'
group by user_id, sql_id, event
order by count (*) desc 
) a
where rownum <6;

 

 

1. 차일드 커서 확인 (실행계획 변경여부 확인 child_number 0이상이면 실행계획이 변경되었을 수 있음)

sql_id가 '4kdvdryf0774f'라고 가정함

(https://positivemh.tistory.com/372)

--https://positivemh.tistory.com/372
SQL>
select sql_id,
  PARSING_SCHEMA_NAME "USER",
  PLAN_HASH_VALUE,
  OPTIMIZER_MODE,
  child_number,
  parse_calls,
  USERS_OPENING,
  USERS_EXECUTING,
  loads,
  executions,
  invalidations,
  decode(sign(invalidations), 1, (loads-invalidations), 0) reloads,
  FIRST_LOAD_TIME,
  LAST_LOAD_TIME
from v$sql
where sql_id='4kdvdryf0774f'
and  sql_text not like '%v$sql%';

 

 

2_1. 동일 sql_id의 다른 plan_hash_value 확인

SQL>
select distinct sql_id, sql_plan_hash_value from dba_hist_active_sess_history
where sql_id = '4kdvdryf0774f';

 

 

------------------하드파싱 쿼리일 경우 확인 사항---------------------------------

2_2. 2_1결과가 다른 plan_hash_value가 나오지 않는경우 하드파싱 쿼리일 가능성이 있음, 
 v$sql이나 dba_hist_active_sess_history에서 sql_id를 이용해 sql_text 확인 후 like 절로 비슷한 쿼리가 있는지 검색

SQL>
select sql_id, sql_fulltext, plan_hash_value from v$sql 
where sql_id = '4kdvdryf0774f';

 

 

2_3. sql text로 비슷한 쿼리 검색

SQL>
select sql_id, sql_text, plan_hash_value from v$sql 
where sql_text like '%selct * from%';

 

 

2_4. 비슷한 쿼리의 sql_id를 찾았다면 해당쿼리 플랜 확인

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('05qj3sk9fssgw', null, 'ADVANCED ALLSTATS LAST'));

 

 

2_5. 이전 플랜으로 실행될수 있게끔 힌트 작성 후 AP팀에 전달

 

------------------하드파싱 쿼리일 경우 확인 사항---------------------------------

 

 

3. sql_id 실행계획 plan_hash_value 별 성능 확인

SQL> 
select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') 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)" 
from dba_hist_sqlstat a, dba_hist_snapshot b
where sql_id='4kdvdryf0774f' and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by snap_id desc, a.instance_number;

 

 

4_1. spm에 sql_id 와 plan_hash_value로 baseline 등록

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;
/

 

 

4_2. spm 안되면 sqlset 이용(cursor에 없을경우 4_1 실행결과가 Load : 0으로 나옴)

(3에서 확인한 좋은성능일때의 snap_id를 dbms_sqltune.select_workload_repository에 넣어줘야함)

SQL> 
exec dbms_sqltune.create_sqlset('sts_4kdvdryf0774f_3177216401');
set serveroutput on
declare
 baseline_cursor dbms_sqltune.sqlset_cursor;
 x pls_integer;
 begin
  open baseline_cursor for
   select value(p) from table (dbms_sqltune.select_workload_repository(300120,300125,'sql_id='||CHR(39)||'4kdvdryf0774f'||CHR(39)||' and plan_hash_value=3177216401',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
   dbms_sqltune.load_sqlset('sts_4kdvdryf0774f_3177216401', baseline_cursor);
  x := dbms_spm.load_plans_from_sqlset(sqlset_name => 'sts_4kdvdryf0774f_3177216401', fixed => 'YES', enabled => 'YES');
  dbms_output.put_line(to_char(x) || ' plan baselines loaded');
 end;
/

 

 

5. 등록한 sql plan baseline 확인

SQL> 
select sql_handle, plan_name, executions, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, accepted, fixed
from dba_sql_plan_baselines
--where sql_text like 'with /* Trend%';
where signature in ( select exact_matching_signature FROM v$sql WHERE sql_id='4kdvdryf0774f');

 

 

참고1. baseline 변경

SQL> 
declare
 ret number;
 begin
  ret := dbms_spm.alter_sql_plan_baseline(
         sql_handle => 'SQL_e18f9b7e02f01359',
         plan_name => 'SQL_PLAN_f33wvgs1g04ut5ac47e2d',
         attribute_name => 'fixed',
         attribute_value => 'NO');
  dbms_output.put_line('PLANS ALTERED: ' || ret);
END;
/

 

 

참고2. baseline 제거

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

 

 

참고3. 커서 플랜 확인

SQL> select * from table(dbms_xplan.display_cursor('4kdvdryf0774f', null, 'ADVANCED ALLSTATS LAST'));

 

 

참고4. awr 플랜 확인

SQL> select * from table(dbms_xplan.display_awr('4kdvdryf0774f', 3177216401, format=>'ADVANCED ALLSTATS LAST'));

 

 

참조 : https://blogs.oracle.com/optimizer/post/does-sql-plan-management-require-pack-license

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/managing-sql-plan-baselines.html

https://positivemh.tistory.com/217

https://www.funoracleapps.com/2022/01/how-can-we-run-sql-tuning-advisor-for.html