6-12 Performance Management - Use SQL Plan Management feature

When SQL stataement is complied, the cost-based optimizer determines the execution plan and executes the statement. If SQL Plan Management (SPM is enabled by setting database parameter OPTIMIZER_USE_SQL_PLAN_BASELINES to TRUE (default is FALSE), then the optimizer checks for matching plans. If plans are found, the plan will be used. If not, the execution plan is added to the plan history. Later, you can run the performance comparison for the new plan and accept it if it performs better than selected plan baseline.

 

OPTIMIZER_USE_SQL_PLAN_BASELINES – enables SPM feature and makes optimizer to lock for plans in SQL plan baseline.

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES – enables automatic capture of SQL plans.

 

 

 

난이도: ★★★★☆

문제 1: Automatic Capture of SQL plans를 활성화하고, DBA_OBJECTS 뷰에서 하나의 새로운 테이블을 만들고 "WHERE OBJECT_NAME = 'TABLE1'" 조건을 단 쿼리를 실행해 보아라. 실행 계획을 출력하고, OEM 및 Performance view에서 baseline이 생성되었는 지 확인하여라. 그리고, 새로운 테이블의 object_name 컬럼에 인덱스를 생성하고, 같은 쿼리를 수행하고 실행 계획을 확인하라. 새로운 baseline이 생성되었다면, evolve하고 plan을 accept한 다음 같은 쿼리를 다시 한 번 수행해 보고 실행 계획을 확인해 보아라. 실행 계획이 변한 것을 확인하면 정답. (20분)

 

1. Automatic Capture of SQL plan을 활성화하고, 테이블을 만든 후 쿼리를 실행해본다.

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines=TRUE;

CONN c##ryu/asdf0930k;

DROP TABLE tbl_mv_objects;
CREATE TABLE tbl_mv_objects
AS
SELECT	*
FROM	dba_objects;

SELECT	/* RYU */ *
FROM	tbl_mv_objects
WHERE	object_name = 'TABLE1';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

 

 

실행 결과 Cost는 394.

 

 

2. 인덱스를 생성하고, 실행 계획을 확인해본다.

CREATE INDEX index_01 ON tbl_mv_objects (OBJECT_NAME);

SELECT	/* RYU */ *
FROM	tbl_mv_objects
WHERE	object_name = 'TABLE1';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

쿼리를 다시 수행한 것의 실행 계획. 인덱스를 생성했음에도 불구, 인덱스를 타지 않으며 Cost가 변하지 않았음.

조금 기다리면 자동으로 캡쳐된 SQL Plan이 뜰 것이다. (인덱스를 적용한 쿼리의 예) 그 Plan을  Evlove하면 앞으로 동일 쿼리를 실행할 때 실행 방식을 다르게 적용할 수 있는 것이다.

 

 

 

 

 

 

다시 쿼리 실행 결과, index_01 인덱스를 적용하여 쿼리의 Cost는 4가 된 것을 확인할 수 있다.