행위

SPM

DB CAFE

1 SPM(SQL Plan Management)

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



1.2 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.3 SPM 절차

1.3.1 SQL_PLAN_BASELINES 관련 파라미터 확인/변경

-- 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')
;

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

1.3.3 DBA_SQL_PLAN_BASELINES 뷰를 통해 확인

-- 등록 확인
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
;

1.3.4 SQL_PLAN_BASELINES 속성 변경을 통한 사용 실행계획 제어

/*--------------------------------------------------------------------
--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.4 SQL 프로파일 등록 프로시져

DECLARE
    l_profile    SYS.sqlprof_attr;
BEGIN
    FOR c1 IN (SELECT   a.sql_id
                      , a.sql_text
                      , b.other_xml
                   FROM dba_hist_sqltext a
                      , dba_hist_sql_plan b
                  WHERE 1 = 1
                    AND a.sql_id = '2z70y3w8w2g9c'
                    AND b.dbid = a.dbid
                    AND b.sql_id = a.sql_id
                    AND b.other_xml IS NOT NULL
               ORDER BY a.sql_id)
    LOOP
        SELECT a.hint
          BULK COLLECT
          INTO l_profile
          FROM XMLTABLE ('other_xml/outline_data/hint' PASSING XMLPARSE (CONTENT c1.other_xml) COLUMNS hint VARCHAR2 (4000) PATH 'text()') a;

        BEGIN
            DBMS_SQLTUNE.IMPORT_SQL_PROFILE (sql_text => c1.sql_text, profile => l_profile, name => 'profile_' || c1.sql_id, replace => TRUE, force_match => TRUE);
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.PUT_LINE (c1.sql_id);
        END;
    END LOOP;
END;
/


SELECT *
  FROM dba_sql_profiles;

SELECT *
  FROM SYS.dbmshsxp_sql_profile_attr;



SELECT /*+ ORDERED USE_NL(B) */        *   FROM scott.dept a, scott.emp b  WHERE b.deptno = a.deptno;
execute dbms_sqltune.drop_sql_profile('profile_9s08dqag5j6bb');

1.5 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
*/
  • (주)씨에스리 서윤식 이사님께서 제공해주신 정보를 기반으로 작성 하였습니다. 감사합니다.