728x90
# TUNING ADVISOR
l SQL tuning advisor 란
- 악성 SQL을 오라클이 알아서 튜닝하라고 맡기는 기능
l 악성 SQL
SQL> drop index emp_sal;
SQL> create index emp_sal on emp(sal);
1) 실행계획 비교
SQL> select /*+ index(emp emp_sal) */ ename, sal
from emp
where sal = 3000;
SQL> select /*+ no_index(emp emp_sal) */ ename, sal
from emp
where sal=3000;
l index를 이용할 때와 이용하지 않을 떄 index scan과 full scan 을 하는 것을 확인할 수 있다
n sa.txt
accept p_task_name prompt 'Enter the task name ? ' declare my_task_name VARCHAR2(30); my_sqltext CLOB; - CLOB : 큰 텍스트를 생성할 때 쓰임 v_task_name varchar2(20); begin my_sqltext := ' select /*+ no_index(emp emp_sal) */ ename, sal from emp where sal=3000'; - 수행할 악성 쿼리를 따옴표 사이에 집어넣고 수행한다 v_task_name := '&p_task_name'; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_text => my_sqltext, user_name => 'SCOTT', scope => 'COMPREHENSIVE', time_limit => 600, task_name => v_task_name, description => 'Task to tune a query on a specified table' ); DBMS_SQLTUNE.EXECUTE_TUNING_TASK ( task_name => v_task_name ); - 튜닝 테스크 실행 end; / |
n SQL 튜닝 어드바이저 확인(sr.txt)
SET LONG 7000000 SET LONGCHUNKSIZE 1000 set lines 32000 select DBMS_SQLTUNE.REPORT_TUNING_TASK( '&task') from DUAL; |
GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : ts02 Tuning Task Owner : SYS Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 600 Completion Status : COMPLETED Started at : 07/24/2014 17:17:58 Completed at : 07/24/2014 17:17:58 ------------------------------------------------------------------------------- Schema Name: SCOTT SQL ID : 76qv7f6p2dvg2 SQL Text : select /*+ no_index(emp emp_sal) */ ename, sal from emp where sal=3000 ------------------------------------------------------------------------------- FINDINGS SECTION (3 findings) ------------------------------------------------------------------------------- 1- Statistics Finding --------------------- Table "SCOTT"."EMP" was not analyzed. Recommendation -------------- - Consider collecting optimizer statistics for this table. execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO'); Rationale --------- The optimizer requires up-to-date statistics for the table in order to select a good execution plan. 2- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 83.19%) ------------------------------------------ - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'ts02', task_owner => 'SYS', replace => TRUE); Validation results ------------------ The SQL profile was tested by executing both its plan and the original plan and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time. Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time(us): 47 76 -61.7 % CPU Time(us): 100 100 0 % User I/O Time(us): 0 0 Buffer Gets: 6 1 83.33 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 2 2 Fetches: 2 2 Executions: 1 1 Notes ----- 1. The original plan was first executed to warm the buffer cache. 2. Statistics for original plan were averaged over next 9 executions. 3. The SQL profile plan was first executed to warm the buffer cache. 4. Statistics for the SQL profile plan were averaged over next 9 executions. 3- Index Finding (see explain plans section below) -------------------------------------------------- The execution plan of this statement can be improved by creating one or more indices. Recommendation (estimated benefit: 50%) --------------------------------------- - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. If you choose to create the recommended index, consider dropping the index "SCOTT"."EMP_SAL" because it is a prefix of the recommended index. create index SCOTT.IDX$$_00E50001 on SCOTT.EMP("SAL","ENAME"); Rationale --------- Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 8 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("SAL"=3000) 2- Using SQL Profile -------------------- Plan hash value: 414773821 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 8 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | EMP_SAL | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SAL"=3000) 3- Using New Indices -------------------- Plan hash value: 2728366778 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IDX$$_00E50001 | 1 | 8 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("SAL"=3000) ------------------------------------------------------------------------------- |
SQL> ed sa.sql SQL> ed sr.sql SQL> @sa Enter the task name ? ts01 SQL> @sr Enter the task name ? ts01 - 이름을 물어보는데 2개다 같은 이름으로 입력해주면 advisor가 개선할 점을 알려준다 |
l SQL tuning advisor 역할
- SQL 계획 튜닝 : 추천해준 SQL 프로파일을 적용하면 SQL 을 변경안해도 튜닝 후 실행계획으로 자동으로 변경된다.
l SQL access advisor 와 SQL tuning advisor 와의 차이
- SQL tuning advisor는 하나의 SQL만 튜닝한다. 그런데 전체 SQL을 다 보면서 해당 SQL에 대한 튜닝 레포트를 제시하지는 못한다
- SQL access advisor는 전체 SQL을 다 살펴보면서 성능을 좋게하는 방법을 제시한다
- 예 :
SQL> select ename, sal
from emp
where ename='SCOTT';
문제1. 아래의 SQL을 SQL tuning advisor으로 튜닝을 수행하시오
SQL> select *
from emp100
where ename='scott1';
SQL> exec dbms_stats.gather_table_stats('SCOTT','SALES200'); SQL> @sa SQL> @sr FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- Index Finding (see explain plans section below) -------------------------------------------------- The execution plan of this statement can be improved by creating one or more indices. Recommendation (estimated benefit: 77.95%) ------------------------------------------ - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index SCOTT.IDX$$_00EF0001 on SCOTT.EMP100("ENAME"); - index를 생성하라고 조언하는 것을 확인할 수 있다 Rationale --------- Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption. |