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;
/
