웹 개발과 관련된 모든 것들

Automatic SQL Tuning in Oracle Database 10g(SQL Tuning Advisor) 

이번 강좌에서는 오라클 10g의 새 특징인 자동 SQL 튜닝기능에 대해 알아 보도록 하겠습니다. 

Normal Mode에서 오라클 옵티마이저는 아주 짧은 시간에 최적의 실행 계획을 계산해 내야 합니다. 그러므로 항상 최선의 실행 계획을 만들어 낼 수는 없다 이겁니다~^^ 

Oracle 10g는 옵티마이저가 튜닝 모드에서 실행될 수 있도록 하여 추가적인 통계 정보를 모아 추후에는 튜닝된 최적의 실행 계획을 만들어 낼 수 있도록 지원 합니다. 물론 이러한 프로세스는 하나의 SQL문장에 대해 몇 분이 걸릴지도 모르므로 리소스를 많이 잡아 먹는 경향이 있습니다. 

튜닝모드에서 옵티마이저가 하는 일에 대해 정리해 볼까요? 

- 통계 분석(statistics Analysis) : 옵티마이저는 오래전에 만들어진 통계 정보나 또는 통계 정보가 없는 부분에 대해 통계 정보를 생산 하는 것을 권고 하며 SQL Profile안에 부가적인 객체에 대한 통계 정보를 저장 합니다. 

- SQL Profileing : CBO(Cost Base Optimizer)로 수행될 때 SQL문을 위해서는 부가 정보들이 필요 합니다. 이러한 SQL문의 정보들을 SQL 프로파일이라는 형태로 수집해 놓습니다. 이러한 SQL 프로파일이 필요할 때마다 SQL Tuning Advisor 에 의해 업데이트됩니다. 

- 실행 경로 분석(Access Path Analysis) : 어떤 인덱스를 통해 데이터를 접근하여 추출할지를 결정 합니다. 필요하다면 SQL Access Advisor를 호출해 인덱스에 대한 권고를 요구하기도 합니다. 

- SQL 구조 분석(SQL Structure Analysis) : SQL문이 비효율적인 실행 계획을 생성할 경우 같은 결과를 보여줄 수 있는 비슷한 SQL문을 생성해 권고하는 역할을 합니다. 

이러한 Automatic SQL Tuning 특징은 EM(Enterprise Manager)의 “Advisor Central” 을 이용해서도 사용 할 수 있으며 또한 PL/SQL의 DBMS_SQLTUNE 패키지를 이용해서도 사용 가능 합니다. 본 강좌에서는 PL/SQL에 초점을 맞추어서 진행토록 하겠습니다. 

SQL Tuning Advisor 

SQL Tuning API에 접근 하기 위해서는 ADVISOR라는 권한이 있어야 합니다. 

아래처럼 하면 됩니다. 

SQL>CONN sys/password AS SYSDBA 
SQL>GRANT ADVISOR TO scott; 
SQL>CONN scott/tiger 

SQL Tuning Advisor를 사용하기 위한 첫 번째 단계는 CREATE_TUNING_TASK를 이용하여 새로운 tuning task를 만드는 일입니다. 분석되고자 하는 SQL 문장은 AWR이나 CURSOR CACHE, SQL Tuning set 또는 매뉴얼하게 만들어져 검색 될 수 있습니다. 

SET SERVEROUTPUT ON 

-- Tuning task created for specific a statement from the AWR. 
DECLARE 
l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
begin_snap => 764, 
end_snap => 938, 
sql_id => '19v5guvsgcd1v', 
scope => DBMS_SQLTUNE.scope_comprehensive, 
time_limit => 60, 
task_name => '19v5guvsgcd1v_AWR_tuning_task', 
description => 'Tuning task for statement 19v5guvsgcd1v in AWR.'); 
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/ 

-- Tuning task created for specific a statement from the cursor cache. 
DECLARE 
l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
sql_id => '19v5guvsgcd1v', 
scope => DBMS_SQLTUNE.scope_comprehensive, 
time_limit => 60, 
task_name => '19v5guvsgcd1v_tuning_task', 
description => 'Tuning task for statement 19v5guvsgcd1v.'); 
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/ 

-- Tuning task created from an SQL tuning set. 
DECLARE 
l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
sqlset_name => 'test_sql_tuning_set', 
scope => DBMS_SQLTUNE.scope_comprehensive, 
time_limit => 60, 
task_name => 'sqlset_tuning_task', 
description => 'Tuning task for an SQL tuning set.'); 
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/ 

-- Tuning task created for a manually specified statement. 
DECLARE 
l_sql VARCHAR2(500); 
l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
l_sql := 'SELECT e.*, d.* ' || 
'FROM emp e JOIN dept d ON e.deptno = d.deptno ' || 
'WHERE NVL(empno, ''0'') = :empno'; 

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
sql_text => l_sql, 
bind_list => sql_binds(anydata.ConvertNumber(100)), 
user_name => 'scott', 
scope => DBMS_SQLTUNE.scope_comprehensive, 
time_limit => 60, 
task_name => 'emp_dept_tuning_task', 
description => 'Tuning task for an EMP to DEPT join query.'); 
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/ 


만약 TASK_NAME 파라미터에 값이 있다면 SQL tune task의 식별자로서 사용 되구요, 생략된다면 시스템에서 “TASK_1478” 등과 같이 만들어서 리턴 합니다. 

NVL이 SQL 문장에 사용되다면 옵티마이저로부터 반작용을 유발 하며 부가적으로 그러한 테이블에 관한 통계정보를 지우는 것도 가능 합니다. 

EXEC DBMS_STATS.delete_table_stats('SCOTT','EMP'); 

위에서 tuning task에 대해 정의를 했는데 그 다음으로 할 일은 EXECUTE_TUNING_TASK procedure를 이용하는 일 입니다. 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task'); 

물론 아래처럼 task를 중단하거나 재시작 하거나 취소하거나 하는 것들이 가능 합니다. 

-- Interrupt and resume a tuning task. 
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task'); 
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task'); 

-- Cancel a tuning task. 
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task'); 

-- Reset a tuning task allowing it to be re-executed. 
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task'); 
다음과 같이 tuning task는 DBA_ADVISOER_LOG라는 뷰를 통해서 확인 가능 합니다. 

SQL>SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT'; 

TASK_NAME STATUS 
------------------------------ ----------- 
emp_dept_tuning_task COMPLETED 

tuning task가 성공적으로 수행 되면 REPORT_TUNING_TASK라는 함수를 통해 권고를 확인 할 수 있습니다. 

SET LONG 10000; 
SET PAGESIZE 1000 
SET LINESIZE 200 
SELECT DBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_task') AS recommendations FROM dual; 
SET PAGESIZE 24 

결과는 아래와 같습니다. 

RECOMMENDATIONS 
-------------------------------------------------------------------------------- 
GENERAL INFORMATION SECTION 
------------------------------------------------------------------------------- 
Tuning Task Name : emp_dept_tuning_task 
Scope : COMPREHENSIVE 
Time Limit(seconds): 60 
Completion Status : COMPLETED 
Started at : 05/06/2004 09:29:13 
Completed at : 05/06/2004 09:29:15 

------------------------------------------------------------------------------- 
SQL ID : 0wrmfv2yvswx1 
SQL Text: SELECT e.*, d.* FROM emp e JOIN dept d ON e.deptno = d.deptno 
WHERE NVL(empno, '0') = :empno 

------------------------------------------------------------------------------- 
FINDINGS SECTION (2 findings) 
------------------------------------------------------------------------------- 

1- Statistics Finding 
--------------------- 
Table "SCOTT"."EMP" and its indices were not analyzed. 

Recommendation 
-------------- 
Consider collecting optimizer statistics for this table and its indices. 
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE) 

Rationale 
--------- 
The optimizer requires up-to-date statistics for the table and its indices 
in order to select a good execution plan. 

2- Restructure SQL finding (see plan 1 in explain plans section) 
---------------------------------------------------------------- 
The predicate NVL("E"."EMPNO",0)=:B1 used at line ID 2 of the execution plan 
contains an __EXPRESSION!__ on indexed column "EMPNO". This __EXPRESSION!__ prevents 
the optimizer from selecting indices on table "SCOTT"."EMP". 

Recommendation 
-------------- 
Rewrite the predicate into an equivalent form to take advantage of 
indices. Alternatively, create a function-based index on the __EXPRESSION!__. 

Rationale 
--------- 
The optimizer is unable to use an index if the predicate is an inequality 
condition or if there is an __EXPRESSION!__ or an implicit data type conversion 
on the indexed column. 

------------------------------------------------------------------------------- 
EXPLAIN PLANS SECTION 
------------------------------------------------------------------------------- 

1- Original 
----------- 
Plan hash value: 1863486531 

---------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
---------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 107 | 4 (0)| 00:00:01 | 
| 1 | NESTED LOOPS | | 1 | 107 | 4 (0)| 00:00:01 | 
| 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 3 (0)| 00:00:01 | 
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | 
| 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | 
---------------------------------------------------------------------------------------- 

Note 
----- 
- dynamic sampling used for this statement 

------------------------------------------------------------------------------- 


1 row selected. 

Tuning task는 다음처럼 DROP 합니다. 

BEGIN 
DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task'); 
DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_tuning_task'); 
DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqlset_tuning_task'); 
DBMS_SQLTUNE.drop_tuning_task (task_name => 'emp_dept_tuning_task'); 
END; 
/