6-9 Performance Management - Use SQL Access Advisor

/* Via SQL Access Advisor (SAA) you get recommendations to tune the specific SQL statements by creating materialized views, partitions or indexes (b-tree, bitmap and function-based). */

 

난이도: ★★★☆☆

문제 1: DBA_OBJECTS 테이블과 DBA_EXTENTS 테이블을 토대로 두 개의 새 테이블을 생성하고, 오브젝트 별로 extent 수를 count하는 SQL문을 실행하여라. 그리고 SQL Tuning Set을 생성하여, 이를 토대로 SQL Access Advisor을 schedule하여라. SQLACCESS_WAREHOUSE 템플릿을 사용하여, 인덱스 생성이나 Materialized View 및 Partitioning에 관련된 recommendation을 수집하라. Recommendation이 있다면, 추천사항대로 실행한 후 다시 실행계획을 확인해 보아라. (20분)

 

1. 조건에 맞는 테이블을 생성하고, 오브젝트의 extents의 수를 count하는 SQL문을 실행한다. 

conn c##ryu/asdf0930k;

CREATE TABLE tbl_mv_objects
AS
SELECT	*
FROM	dba_objects
WHERE	object_id IS NOT NULL;

INSERT INTO tbl_mv_objects
SELECT * FROM tbl_mv_objects;
/
/

CREATE TABLE tbl_mv_extents
AS
SELECT	*
FROM	dba_extents;

INSERT INTO tbl_mv_extents
SELECT * FROM tbl_mv_extents;
/
/

COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'tbl_mv_objects');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'tbl_mv_extents');


SELECT	/* RYU */ a.object_name, COUNT(extent_id)
FROM	tbl_mv_objects a, tbl_mv_extents b
WHERE	a.object_name = b.segment_name
GROUP BY a.object_name;

 

 

2. 방금 실행한 SQL문을 STS로 만들고, SQL Access Advisor을 실행한다.

 

방금 실행한 SQL문을 토대로 SQL Tuning Set을 생성 완료했다. 이제 SQL Access Advisor을 실행시키러 가보자.

STS까지 지정 완료하였고, SQL Access Advisor도 scheduling이 완료되었다.

Recommendation이 1개 존재한다. 그것은 무려 Cost를 3447에서 21까지 줄여준다고 한다. 약 99.5%이상의 성능 향상을 불러올 것이란 건데, 어떤 항목을 추천하는지 확인해 봐야겠다.

MATERIALIZED VIEW LOG을 2개 생성하고, MATERIALIZED VIEW을 생성할 것을 추천하고 있다. 이 쿼리가 업무 상황에서 많이 사용되는 쿼리라면 MATERIALIZED VIEW을 생성하는 것이 좋지만, 그렇지 않다면 저장 공간을 잡아먹기만 하는 뷰가 될 가능성이 있으므로, 상황에 따라 이 recommendation 사항을 수행하는 것이 낫다. 그건 그렇고, 개인적으로는 index을 생성할 것을 추천할 것을 예상하고 있었다.

 

 

3. recommendation 내용을 수행하고, 실행계획을 확인해 본다. (생략)