"SPM"의 두 판 사이의 차이

1.1 SQL Plan Management 개요[편집]

11g 부터 9i 때 도입된 바인드 옅보기(Bind Peeking) 기능의 단점을 커버하기 위해 어댑티브 커서(adaptive cursor)와 SQL 의 성능을 유지하기 위해 SPM(SQL PLAN MANAGEMENT) 기능을 제공한다. 특히 SPM 기능으로 인해

1. 옵티마이져는 자동으로 변경되는 SQL 의 실행계획을 관리
2. 새로운 실행계획이 만들어지더라고, 검증(VERIFY)이 끝날 때까지 새로운 실행계획을 사용하지 않으며
3. 현재의 실행계획보다 성능이 비슷하거나 향상될 경우에만 사용할 수 있도록 한다.

SPM 은 SQL 의 성능(Runtime-performance)이 실행계획의 변경으로 인해 저하되는 것을 방지하기 위해 개발된 기능이다.

11G SQL PLAN MANAGEMNET

SPM 은 SQL 의 실행계획이 변경되더라도 바로 적용시키지 않고, 검증을 확인된(VERIFY) 실행계획만 받아들이도록 되어있다.

SPM 의 3 가지 주요 구성요소

1. SQL PLAN BASELINE CAPTURE

   • 현재 SPM에 의해 수락되어 검증된 실행계획에 대한 SQL Baseline을 생성하고,
   • PLAN History 정보는 SYSAUX 테이블 스페이스에 저장

2. SQL PLAN BASELINE SELECTION

   • PLAN History 에는 수락된(accepted)된 실행계획과 수락되기전의(unaccepted) 실행계획을 모두 포함함.
   • 수락되기전의(unaccepted) 실행계획은 아직 검증(verified)이 되기 전의 실행계획이며, 거부된(rejected) 실행계획은 검증(verified)을 통해 적용할 수 없는 실행계획을 의미한다.

3. SQL PLAN BASELINE EVOLUTION

   • PLAN History안의 검증이 되지 않은 (all unverified) 실행계획은 이 단계에서 검증을 통해 수락(accept)되거나 거부(reject)된다.

1.2 SPM 절차[편집]

-- parameter 조회
-- optimizer_capture_sql_plan_baselines : 2회 이상 수행되는 SQL을 SPB에 자동 등록
-- optimizer_use_sql_plan_baselines     : SPB를 활성화
select name, value, display_value, isdefault, issys_modifiable, isses_modifiable
from   v$parameter
where  name in (  'optimizer_capture_sql_plan_baselines'
                , 'optimizer_use_sql_plan_baselines')
;

/*-----------------------------------------------------------------------------------
--Shared Cursor의 SQL을 SPB Baseline에 등록하는 Function
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( 
        sql_id          IN VARCHAR2, 
        plan_hash_value IN NUMBER := NULL, 
        fixed           IN VARCHAR2 := 'NO', 
        enabled         IN VARCHAR2 := 'YES') RETURN PLS_INTEGER; 
-----------------------------------------------------------------------------------*/
declare
  rst  pls_integer;
begin 
  rst := dbms_spm.load_plans_from_cursor_cache(sql_id=>'2m3qs8xmxxkpx', plan_hash_value=>347281913, fixed=>'yes', enabled=>'yes');
  dbms_output.put_line(to_char(rst));
end;
/

-- 등록 확인
SELECT --SQL_TEXT
      signature, sql_handle, plan_name, creator, origin, enabled, accepted, fixed, optimizer_cost, buffer_gets, created, AUTOPURGE
FROM dba_sql_plan_baselines ;

-- sql_id, plan_hash_value 보기
select     sys.dbms_sqltune_util0.sqltext_to_sqlid(sql_text||chr(0)) sql_id,
          ( select      to_number(regexp_replace(plan_table_output,'^[^0-9]*')) 
            from        table(dbms_xplan.display_sql_plan_baseline(sql_handle,plan_name)) 
            where       plan_table_output like 'Plan hash value: %'
          ) plan_hash_value
          ,SQL_HANDLE, plan_name, enabled, accepted, fixed --, reproduced  
          ,dbms_xplan.format_time_s(elapsed_time/1e6) hours,creator,origin,created,last_modified,last_executed
          ,sql_text
from dba_sql_plan_baselines b 
where 1=1
 and  creator = 'SQLTUSER'
order by LAST_MODIFIED desc
;

/*--------------------------------------------------------------------
--SPB Baseline 삭제 패키지 

DBMS_SPM.DROP_SQL_PLAN_BASELINE ( 
  sql_handle IN VARCHAR2 := NULL, 
  plan_name IN VARCHAR2 := NULL) 
RETURN PLS_INTEGER; 
--------------------------------------------------------------------*/
DECLARE 
   pls pls_integer ; 
BEGIN 
   pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle=> 'SYS_SQL_351516f2a705638a') ; 
   dbms_output.put_line( to_char(pls) ) ; 
END ; 
/ 

--- 수정1
declare
  rst  pls_integer;
begin 
  rst := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_752e50964591a909', plan_name=>'SQL_PLAN_7abkhkt2t3a8960610a65', attribute_name=>'enabled', attribute_value=>'yes'); 
  dbms_output.put_line(to_char(rst));
end;
/


-- 수정2
declare
  rst  pls_integer;
begin 
  rst := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_752e50964591a909', plan_name=>'SQL_PLAN_7abkhkt2t3a8960610a65', attribute_name=>'fixed', attribute_value=>'yes'); 
  dbms_output.put_line(to_char(rst));
end;
/

1.3 SPM 실습[편집]

/*-----------------------------------------------------------------------------------
   SPM test
-----------------------------------------------------------------------------------*/
-- 2-1. full scan test
alter session set statistics_level        =all;


alter session set optimizer_index_cost_adj=300;
select -- spm test11111111
       *
from   emp e, dept d 
where  e.sal > 3000
 and  d.deptno = e.deptno;
select * from table(dbms_xplan.display_cursor(null, null, 'iostats last'));

-- 2-2. index scan
alter session set optimizer_index_cost_adj=10;
select -- spm test1
       *
from   emp e, dept d 
where  e.sal > 3000
 and  d.deptno = e.deptno;
select * from table(dbms_xplan.display_cursor(null, null, 'iostats last'));

select sql_id, child_number, PLAN_HASH_VALUE, sql_fulltext
from   v$sql
where  sql_text like 'select -- spm test1%';

-- child cursor 가  1인 즉 index scan을 할 수 있도록 spb 등록
declare
  rst  pls_integer;
begin 
  rst := dbms_spm.load_plans_from_cursor_cache(sql_id=>'2m3qs8xmxxkpx', plan_hash_value=>347281913, fixed=>'yes', enabled=>'yes');
end;
/

SELECT signature, sql_handle, plan_name, creator, origin, enabled, accepted, fixed, optimizer_cost, buffer_gets, created
FROM dba_sql_plan_baselines ;


-- 1-1 full scan을 하도록 sql 수행하고 xplan을 확인
-- => index scan하는 것을 확인 가능
/*
Plan hash value: 347281913

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  NESTED LOOPS                 |            |      1 |        |      1 |00:00:00.01 |       4 |
|   2 |   NESTED LOOPS                |            |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP        |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  4 |     INDEX RANGE SCAN          | IX_EMP_SAL |      1 |      1 |      1 |00:00:00.01 |       1 |
|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT    |      1 |      1 |      1 |00:00:00.01 |       1 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT       |      1 |      1 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."DEPTNO" IS NOT NULL)
   4 - access("E"."SAL">3000)
   5 - access("D"."DEPTNO"="E"."DEPTNO")

Note
-----
   - SQL plan baseline SQL_PLAN_czdty3kjn60zkb1eb199e used for this statement
*/