[Oracle] SPM (SQL Plan Management)

• SPM 이란 무엇일까?

SQL Plan Management 으로 최고 실행 계획을 찾는 다기 보다는 최악의 실행 계획을 피하는 것을 목적으로 한다고 생각하면 된다.

 

• SPB  (SQL Plan Baseline) 

SQL Plan 변화를 제어하는 기능을 제공, 새로 생성 된 실행 계획이 원래 라이브러리 캐시에 있던 것보다 효과적이지  않을 경우 반복적으로 사용되는 SQL 문이 회귀하는 것을 방지한다.

 

https://support.oracle.com/knowledge/Oracle%20Database%20Products/456518_1.html

How to Use SQL Plan Management (SPM) - Plan Stability Worked Example

• 관련 파라미터

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = NO[Default] 
--SQL Plan Baseline을 캡쳐하지 않음, Oracle 이 자동으로 SPB에 자동 등록 여부 결정 

OPTIMIZER_USE_SQL_PLAN_BASELINES = YES[Default]
--SQL Plan Baseline을 사용할 지의 여부를 지정, Oracle Optimizer 가 등록된 SPB 를 사용 여부를 결정

 

• SPM 등록 매커니즘

1. 자동

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = YES 환경에서,  

2회 이상 반복 수행 되는 SQL(Plan이 같은)은  자동 등록

최초 PLAN: DBA_SQL_PLAN_BASELINES의 등록 값은

ENABLED = YES, ACCPTED = YES, FIXED = NO 로 등록됨 

2TH PLAN : DBA_SQL_PLAN_BASELINES의 등록 값은

ENABLED = YES, ACCPTED = NO, FIXED = NO 로 등록됨 

: DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 을 통해 Verify 후, Cost 가 적을 경우 Accepted = YES로 변경

          

2. 수동

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = NO 상태에서, 

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 등 DBMS_SPM 패키지 수동 등록 할 수 있다.

등록 시,  DBA_SQL_PLAN_BASELINES의 등록 값은 ENABLED = YES, ACCPTED =YES, FIXED = NO 로 등록됨 

                

• SPM 사용 매커니즘

기본적으로 등록된 SPB 는 ENABLED = YES, ACCPETED= YES 면, OPTIMIZER 가 등록된 SPB 를 사용 한다.

ENABLED = YES, ACCPETED= YES 인 Plan 이, 둘 이상 일 경우, Optimizer 가 Choice  

추가로 FIXED=YES 로 변경 하면 FIXED=YES 인 SPB 만 사용 한다. 

마찬가지로 FIXED= YES 이, 둘 이상일 경우, Optimizer 가 Choice

ENABLED < ACCPTED < FIXED  [ 모두 YES 일 경우, Optimier 영향도  ] 

 

• 실제 업무에서 사용한 Query
  
  SPM이 이미 적용된 서버에서 런칭을 한 서비스에서 데이터의 양에 따라 실행계획이 달라진 경우 더 좋은 실행계획을 타지 못한 채 SPM으로 고정된 Query를 찾는 쿼리이다. 
  
  

select *
  from (
    select sql_handle, 
           min(optimizer_cost) min_cost, 
           min(case when accepted='YES' then optimizer_cost end) accept_cost, 
           max(optimizer_cost) max_cost,
           max(last_executed) last_executed
    from dba_sql_plan_baselines
    group by sql_handle
    having (max(optimizer_cost) - min(optimizer_cost) >10000) 
    order by min(optimizer_cost)
)
where min_cost <> accept_cost
and last_executed > sysdate -10;

 

나의 개인적인 생각인데 데이터 모델을 설계했을때와 실제로 그 모델이 개발되고 서비스가 오픈되었을떼 데이터가 유입되는 양이나 조회되는 패턴이 예상과 다른 경우가 많아서 SPM을 사용하는건 예상 가능하고 심플한 비즈니스에 적당한것 같다.