6-8 Performance Management - Use SQL Tuning Advisor

/* Oracle automatically runs the SQL Tuning Advisor (STA) on SQL statements with high load from AWR on every night during a default maintenance window and provides performance tuning recommendations. It checks every query for missing or stale statistics and recommends gathering statistics for specific objects. */

 

/* query DBA_AUTOTASK_CLIENT view from the database to check the status of the STA. */

SELECT       client_name, status

FROM         dba_autotask_client;

 

/* query DBA_AUTOTASK_JOB_HISTORY view to get the history of STA jobs. */

SELECT       job_start_time, job_status, job_duration

FROM         dba_autotask_job_history

WHERE      client_name = 'sql tuning advisor'

AND            job_start_time >= SYSDATE - 7

ORDER BY  1 desc;

 

 

 

 

난이도: ★★☆☆☆

문제 1: STS(SQL Tuning Set)를 생성하고, 커서에서 세 개의 다른 SQL을 실행하여라. 그리고, STS의 쿼리들을 SQL Tuning Advisor를 사용하여 결과를 확인해 보아라. (10분)

 

 

1. 커서에서 다음과 같은 SQL을 실행한다.

CREATE USER c##ryu IDENTIFIED BY asdf0930k;
GRANT dba TO c##ryu;
sqlplus c##ryu/asdf0930k;
CREATE TABLE table1 
RESULT_CACHE (mode force)
AS
SELECT * FROM dba_objects;
CREATE INDEX index1 ON table1 (object_id);

SELECT /* RYU */ count(1) FROM table1 WHERE object_id >= 10000;
SELECT /* RYU */ count(1) FROM table1 ORDER BY object_id DESC;
SELECT /* RYU */ count(1) FROM table1 WHERE object_id < 1000 AND ROWNUM <= 50;

 

2. STS를 만들고, SQL Tuning Advisor를 실행해본다.

방금 실행했던 SQL 3개만 STS에 남겨둔다.

이제 STS를 만들었으니 SQL Tuning Advisor를 실행해보자.

실행 완료 결과이다. 방금 생성했던 쿼리는 인덱스를 생성했고, 실행 계획 확인 결과 (따로 캡쳐본을 첨부하지 않음) 인덱스를 이미 효율적으로 잘 타고 있었으므로 Recommendation 정보가 딱히 나타나지 않않다.

 

 

 

 

난이도: ★★☆☆☆

문제 2: AWR snapshot에서 SQL statement를 load하고, SQL Tuning Advisor을 사용하여 recommendation 정보를 확인하여라. (8분)

 

1번 문제에서 STS의 "Load Methods"부분을 "AWR Snapshot"으로 바꾸기만 하면 된다.

 

 

 

 

난이도: ★★☆☆☆

문제 3: OEM에서 top active sessions에 대한 리스트를 얻고, SQL Tuning Advisor Job을 Schedule하여라. (8분)

 

/* Active Session History Analytics 하단의 Active session 시간이 긴 순서대로 SQL 목록이 표시된다. SQL들을 클릭하여 Create SQL Tuning Set을 클릭한 후, SQL Tuning Advisor을 실행하면 된다. */