"SPM"의 두 판 사이의 차이
DB CAFE
(새 문서: == SPM(SQL Plan Management) === SQL Plan Management 개요 === 11g 부터 9i 때 도입된 바인드 옅보기(Bind Peeking) 기능의 단점을 커버하기 위해 어댑티브...) |
|||
(같은 사용자의 중간 판 18개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
− | == SPM(SQL Plan Management) | + | == SPM(SQL Plan Management) == |
=== SQL Plan Management 개요 === | === SQL Plan Management 개요 === | ||
+ | {{틀:고지상자 | ||
+ | |제목 = SQL Plan Management | ||
+ | |내용 = 11g 부터 9i 때 도입된 바인드 옅보기(Bind Peeking) 기능의 단점을 커버하기 위해 | ||
+ | :어댑티브 커서(adaptive cursor)와 SQL 의 성능을 유지하기 위해 SPM(SQL PLAN MANAGEMENT) 기능을 제공한다. | ||
+ | * SPM 기능으로 | ||
+ | # 옵티마이져는 자동으로 변경되는 SQL 실행계획 관리 | ||
+ | # 새로운 실행계획이 만들어지더라도, 검증(VERIFY)이 끝날 때까지 새로운 실행계획을 사용하지 않음 | ||
+ | # 현재의 실행계획보다 성능이 비슷하거나 향상될 경우에만 사용할 수 있도록 한다. | ||
+ | |||
+ | * SPM 은 SQL의 성능(Runtime-performance)이 실행계획의 변경으로 인해 저하되는 것을 방지하기 위해 개발된 기능. | ||
+ | * 11G SPM 은 SQL의 실행계획이 변경되더라도 바로 적용 시키지 않고, 검증을 확인된(VERIFY) 실행계획만 받아들이도록 되어있다. | ||
+ | }} | ||
+ | |||
+ | === 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)된다. | ||
+ | |||
+ | === SPM 절차 === | ||
+ | ==== SQL_PLAN_BASELINES 관련 파라미터 확인/변경 ==== | ||
+ | * parameter 조회 | ||
+ | ** optimizer_capture_sql_plan_baselines : 2회 이상 수행되는 SQL을 SPB에 자동 등록 | ||
+ | ** optimizer_use_sql_plan_baselines : SPB를 활성화 | ||
+ | |||
+ | <source lang=sql> | ||
+ | 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') | ||
+ | ; | ||
+ | </source> | ||
+ | |||
+ | ==== SQL_PLAN_BASELINES 에 실행계획 등록 ==== | ||
+ | {{틀:설명상자 | ||
+ | |내용=--Shared Cursor의 SQL을 SPB Baseline에 등록하는 Function | ||
+ | <source lang=sql> | ||
+ | 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; | ||
+ | </source> | ||
+ | }} | ||
+ | |||
+ | <source lang=sql> | ||
+ | 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; | ||
+ | / | ||
+ | </source> | ||
+ | |||
+ | ==== DBA_SQL_PLAN_BASELINES 뷰를 통해 확인 ==== | ||
+ | <source lang=sql> | ||
+ | -- 등록 확인 | ||
+ | SELECT --SQL_TEXT | ||
+ | signature, sql_handle, plan_name, creator, origin, enabled, accepted, fixed, optimizer_cost, buffer_gets, created, AUTOPURGE | ||
+ | FROM dba_sql_plan_baselines ; | ||
+ | </source> | ||
+ | |||
+ | <source lang=sql> | ||
+ | -- 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 | ||
+ | ; | ||
+ | </source> | ||
+ | |||
+ | ==== SQL_PLAN_BASELINES 속성 변경을 통한 사용 실행계획 제어 ==== | ||
+ | <source lang=sql> | ||
+ | /*-------------------------------------------------------------------- | ||
+ | --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; | ||
+ | / | ||
+ | </source> | ||
+ | |||
+ | === SQL 프로파일 등록 프로시져 === | ||
+ | <source lang=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'); | ||
− | + | </source> | |
− | + | ||
− | + | === SPM 실습 === | |
− | + | <source lang=sql> | |
− | + | /*----------------------------------------------------------------------------------- | |
+ | 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 | ||
+ | */ | ||
+ | </source> | ||
− | |||
− | + | [[category:oracle]] | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− |
2023년 10월 7일 (토) 00:50 기준 최신판
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 에 실행계획 등록[편집]
record_voice_over --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
*/