SPM
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 SPM(SQL Plan Management)[편집]
1.2 SPM 의 3 가지 주요 구성요소[편집]
- SQL PLAN BASELINE CAPTURE
- 현재 SPM에 의해 수락되어 검증된 실행계획에 대한 SQL Baseline을 생성하고,
- PLAN History 정보는 SYSAUX 테이블 스페이스에 저장
- SQL PLAN BASELINE SELECTION
- PLAN History 에는 수락된(accepted)된 실행계획과 수락되기전의(unaccepted) 실행계획을 모두 포함함.
- 수락되기전의(unaccepted) 실행계획은 아직 검증(verified)이 되기 전의 실행계획이며, 거부된(rejected) 실행계획은 검증(verified)을 통해 적용할 수 없는 실행계획을 의미한다.
- 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 SPM 프로파일 등록 프로시져[편집]
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
*/
- 데이터와사람들 서윤식 이사님께서 제공해주신 정보를 기반으로 작성 하였습니다. 감사합니다.