SQL PLAN MANAGEMENT 관련 파라메터
SQL>SELECT name , value , DESCRIPTION FROM v$parameter WHERE name like '%sql_plan%' ;
NAME VALUE DESCRIPTION
------------------------------------ ----------------- ----------------------------------------
optimizer_capture_sql_plan_baselines FALSE automatic capture of SQL plan baselines
optimizer_use_sql_plan_baselines TRUE use of SQL plan baselines for captured s
2 rows
optimizer_capture_sql_plan_baseline : true일 경우 2회 이상 수행된 SQL에 대하여 자동으로 SQL baseline을 수집 함.
optimizer_use_sql_plan_baseline : SQL baseline을 사용할지 여부를 결정
SYSAUX 테이블 스페이스
Plan baseline , SQL statement log , Plan History , SQL Profile 등의 정보들이 기본적으로 SYSAUX 테이블에 저장이 된다.
해당 정보들은 DBMS_SPM 패키지를 이용하여 저장주기와 저장공간을 제한 할 수 있다.
- 현재의 설정 값 조회
SQL>SELECT * FROM DBA_SQL_MANAGEMENT_CONFIG ;
PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY
------------------------------ --------------- ------------- ------------------------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
Name Possible Values Default Value Description
--------------------- ---------------- ------------- ------------------------------------------------------
space_budget_percent 1,2, …, 50 10 Maximum percent of SYSAUX space that can be used for
SQL management base
plan_retention_weeks 5,6, …, 523 53 Number of weeks to retain unused plans before they are
purged
- 설정 파라미터 변경
BEGIN
DBMS_SPM.CONFIGURE( 'SPACE_BUDGET_PERCENT' , 20 ) ;
DBMS_SPM.CONFIGURE( 'PLAN_RETENTION_WEEKS' , 60 ) ;
END;
/
SQL>SELECT * FROM DBA_SQL_MANAGEMENT_CONFIG
PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY
------------------------------ --------------- ------------- ------------------------------
SPACE_BUDGET_PERCENT 20 4.2E4 SCOTT
PLAN_RETENTION_WEEKS 60 4.2E4 SCOTT
수동으로 SQL baseline을 저장하고 하고 사용하기
1. 테스트 Table 생성 및 테스트 SQL 수행
- 테이블 생성 및 통계정보 수집
DROP TABLE TSPM PURGE ;
CREATE TABLE TSPM
AS
SELECT level c1 , mod(level ,5) +1 c2 , 'SQL PLAN MANAGEMENT' c3
FROM dual
CONNECT BY level <= 1000000 ;
COMMIT ;
exec DBMS_STATS.gather_table_stats(user,'TSPM', method_opt => 'for all columns size skewonly') ;
- 테스트 SQL 수행
SELECT /*+ SPM_TEST */
*
FROM TSPM
WHERE c1 = 5000 ;
- Plan 확인
SELECT *
FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last rows cost bytes +alias +outline +predicate'))
PLAN_TABLE_OUTPUT
SQL_ID 2ujrgd2vxq042, child number 0
-------------------------------------
SELECT /*+ SPM_TEST */ * FROM TSPM WHERE c1 = 5000
Plan hash value: 2642347283
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1272 (100)| 1 |00:00:00.02 | 4573 |
|* 1 | TABLE ACCESS FULL | TSPM | 1 | 1 | 28 | 1272 (1)| 1 |00:00:00.02 | 4573 |
---------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TSPM@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('_optimizer_cost_model' 'cpu')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TSPM"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=5000)
2. SQL Baseline 생성
- 수행한 SQL의 SQL_ID 확인
SELECT sql_id , hash_value , sql_text FROM v$sql WHERE sql_text like '%SPM_TEST%' ;
SQL_ID HASH_VALUE SQL_TEXT
--------------- -------------- -----------------------------------------------------------------
9wdxjytgm0zyh 1597013968 SELECT sql_id , hash_value , sql_text FROM v$sql WHERE sql_text like '%SPM_TEST%'
2ujrgd2vxq042 3084583042 SELECT /*+ SPM_TEST */ * FROM TSPM WHERE c1 = 5000
amy82kpgk3jgg 1596048879 SELECT * FROM v$sql WHERE sql_text like '%SPM_TEST%'
- Baseline 생성
DECLARE
A_RETURN PLS_INTEGER ;
BEGIN
A_RETURN := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=> '2ujrgd2vxq042') ;
END ;
/
- 생성한 Baseline 확인
SELECT SQL_HANDLE , SQL_TEXT ,PLAN_NAME FROM dba_sql_plan_baselines WHERE sql_text like '%SPM_TEST%';
SQL_HANDLE SQL_TEXT PLAN_NAME
--------------------------- -------------------------------------- ------------------------------------
SYS_SQL_a6cec4d7410a9e92 SELECT /*+ SPM_TEST */ SQL_PLAN_admq4ux0hp7nk1e0ba6ab
*
FROM TSPM
WHERE c1 = 5000
등록된 baseline 중에서 Enable과 Accepted가 YES인 베이스 라인만 사용이 된다.
플랜이 변경되어서 새로 등록이 되면, Accepted는 NO인 상태로 등록이 되고, 추후 검증 작업을 거쳐서 사용가능하다.
Fixed 값은 Enable과 Accepted가 YES인 baseline 중에서 우선 선택이 된다.
3. 베이스 라인이 사용되는지 확인
- 하드파싱을 수행하게 Shared Pool을 비움
ALTER SYSTEM FLUSH SHARED_POOL ;
- 하드 파싱이 일어나게 테스트 SQL 재 수행
SELECT /*+ SPM_TEST */
*
FROM TSPM
WHERE c1 = 5000 ;
- Plan 확인
SELECT *
FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last rows cost bytes +alias +outline +predicate'))
PLAN_TABLE_OUTPUT
SQL_ID 2ujrgd2vxq042, child number 0
-------------------------------------
SELECT /*+ SPM_TEST */ * FROM TSPM WHERE c1 = 5000
Plan hash value: 2642347283
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1272 (100)| 1 |00:00:00.02 | 4573 |
|* 1 | TABLE ACCESS FULL| TSPM | 1 | 1 | 28 | 1272 (1)| 1 |00:00:00.02 | 4573 |
---------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TSPM@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('_optimizer_cost_model' 'cpu')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TSPM"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=5000)
Note
-----
- SQL plan baseline SQL_PLAN_admq4ux0hp7nk1e0ba6ab used for this statement
위에서 생성한 PLAN이 사용되고 있다.
4. 테이블이 변경 됨
- 테이블에 인덱스가 추가 됨
CREATE INDEX TSPM_N1 ON TSPM(c1) ;
exec DBMS_STATS.gather_table_stats(user,'TSPM', method_opt => 'for all columns size skewonly') ;
- 테스트 SQL 재 수행
SELECT /*+ SPM_TEST */
*
FROM TSPM
WHERE c1 = 5000 ;
- Plan 확인
SELECT *
FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last rows cost bytes +alias +outline +predicate'))
PLAN_TABLE_OUTPUT
SQL_ID 2ujrgd2vxq042, child number 1
-------------------------------------
SELECT /*+ SPM_TEST */ * FROM TSPM WHERE c1 = 5000
Plan hash value: 2642347283
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1272 (100)| 1 |00:00:00.02 | 4573 |
|* 1 | TABLE ACCESS FULL| TSPM | 1 | 1 | 28 | 1272 (1)| 1 |00:00:00.02 | 4573 |
---------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TSPM@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('_optimizer_cost_model' 'cpu')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TSPM"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=5000)
Note
-----
- SQL plan baseline SQL_PLAN_admq4ux0hp7nk1e0ba6ab used for this statement
- baseline 조회
SELECT SQL_HANDLE , SQL_TEXT ,PLAN_NAME ,ENABLED , ACCEPTED , LAST_VERIFIED FROM dba_sql_plan_baselines WHERE sql_text like '%SPM_TEST%';
SQL_HANDLE SQL_TEXT PLAN_NAME ENABLED ACCEPTED LAST_VERIFIED
---------------------------- ----------------------------- ----------------------------------- ------ ------
SYS_SQL_a6cec4d7410a9e92 SELECT /*+ SPM_TEST */ SQL_PLAN_admq4ux0hp7nk1e0ba6ab YES YES
*
FROM TSPM
WHERE c1 = 5000
SYS_SQL_a6cec4d7410a9e92 SELECT /*+ SPM_TEST */ SQL_PLAN_admq4ux0hp7nk85ed95f3 YES NO
*
FROM TSPM
WHERE c1 = 5000
위와 같이 하나의 Baseline이 더 추가 된 것을 확인 할 수 있다.
인덱스를 생성함으로 인해서 옵티마이저가 Plan을 하나 더 생성 한 것이다.
하지만 아직 해당 Plan을 사용하고 있지는 않는다.
- 새로이 생성된 플랜 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(plan_name=>'SQL_PLAN_admq4ux0hp7nk85ed95f3')) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_a6cec4d7410a9e92
SQL text: SELECT /*+ SPM_TEST */ * FROM TSPM WHERE c1 = 5000
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_admq4ux0hp7nk85ed95f3 Plan id: 2246940147
Enabled: YES Fixed: YES Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 3789710727
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TSPM | 1 | 28 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | TSPM_N1 | 1 | | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=5000)
5. 새로운 Plan 검증
- 두 개의 Plan 비교
SELECT DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_a6cec4d7410a9e92') FROM dual ;
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_a6cec4d7410a9e92
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_admq4ux0hp7nk85ed95f3
------------------------------------
Plan was verified: Time used .187 seconds.
Plan passed performance criterion: 1143.68 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- ----------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): 17.883 .025 715.32
CPU Time(ms): 17.361 0
Buffer Gets: 4573 4 1143.25
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
6. Plan 고정
- Plan을 명시적으로 고정
DECLARE
A_RETURN PLS_INTEGER ;
BEGIN
A_RETURN := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle=> 'SYS_SQL_a6cec4d7410a9e92'
, plan_name=>'SQL_PLAN_admq4ux0hp7nk1e0ba6ab'
, attribute_name=>'FIXED'
, attribute_value=>'YES') ;
END ;
/
attribute_name attribute_value Description
-------------- ---------------------------- ------------------------------------------
enabled 'YES' or 'NO' 'YES' means the plan is available for use by the optimizer. It may o
r may not be used depending on accepted status.
fixed 'YES' or 'NO' YES' means the SQL plan baseline is not evolved over time. A fixed
plan takes precedence over a non-fixed plan.
autopurge 'YES' or 'NO' 'YES' means the plan is purged if it is not used for a time period. 'NO'
means it is never purged.
plan_name String of up to 30-characters Name of the plan
description String of up to 500-characters Plan description.
- 적용된 것 확인
SELECT SQL_HANDLE , PLAN_NAME ,ENABLED , ACCEPTED , LAST_VERIFIED ,FIXED FROM dba_sql_plan_baselines WHERE sql_text like '%SPM_TEST%' ;
SQL_HANDLE PLAN_NAME ENABLED ACCEPTED LAST_VERIFIED FIXED
---------------------------- ---------------------------------- ----- ----- ---------------------- -----
SYS_SQL_a6cec4d7410a9e92 SQL_PLAN_admq4ux0hp7nk1e0ba6ab YES YES YES
SYS_SQL_a6cec4d7410a9e92 SQL_PLAN_admq4ux0hp7nk85ed95f3 YES YES 2014-02-10 오후 3:03:51 NO
- 테스트 SQL 재 수행
SELECT /*+ SPM_TEST */
*
FROM TSPM
WHERE c1 = 5000 ;
SELECT *
FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last rows cost bytes +alias +outline +predicate'))
PLAN_TABLE_OUTPUT
SQL_ID 2ujrgd2vxq042, child number 1
-------------------------------------
SELECT /*+ SPM_TEST */ * FROM TSPM WHERE c1 = 5000
Plan hash value: 2642347283
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1272 (100)| 1 |00:00:00.02 | 4573 |
|* 1 | TABLE ACCESS FULL| TSPM | 1 | 1 | 28 | 1272 (1)| 1 |00:00:00.02 | 4573 |
---------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TSPM@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('_optimizer_cost_model' 'cpu')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TSPM"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=5000)
Note
-----
- SQL plan baseline SQL_PLAN_admq4ux0hp7nk1e0ba6ab used for this statement
- 다시 변경하기
DECLARE
A_RETURN PLS_INTEGER ;
BEGIN
A_RETURN := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle=> 'SYS_SQL_a6cec4d7410a9e92'
, plan_name=>'SQL_PLAN_admq4ux0hp7nk1e0ba6ab'
, attribute_name=>'FIXED'
, attribute_value=>'NO') ;
A_RETURN := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle=> 'SYS_SQL_a6cec4d7410a9e92'
, plan_name=>'SQL_PLAN_admq4ux0hp7nk85ed95f3'
, attribute_name=>'FIXED'
, attribute_value=>'YES') ;
END ;
/
7. Baseline 삭제
- 특정 Plan만 삭제
DECLARE
A_RETURN PLS_INTEGER ;
BEGIN
A_RETURN := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle=> 'SYS_SQL_a6cec4d7410a9e92'
, plan_name=>'SQL_PLAN_admq4ux0hp7nk1e0ba6ab'
) ;
END ;
/
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_spm.htm#CACHAGGA