In Oracle 10g, the SQL tuning process has been automated by introducing a new manageability feature call
Automatic SQL Tuning. This feature is designed to work equally well for OLTP and Data Warehouse workloads. Unlike existing tools, automatic SQL Tuning id preformed in the database server by the Oracle query optimizer itself, running in special mode. When running in this mode, the Oracle query optimizer is referred to us the
Automatic Tuning Optimizer.
Create a Table to Test a SQL
Create Table TestEmp
As Select * from Scott.emp;
Write a SQL that you need to tune.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM TESTEMP
ORDER by SAL;
Execute DBMS_STATS Package for Gather Table Satieties
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(
3 ownname => 'TEST',
4 tabname => 'TESTEMP',
5 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
6 method_opt => 'FOR ALL COLUMNS SIZE AUTO'
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
How to use dbms_sqltune Package
CREATE OR REPLACE PROCEDURE P_SQL_TUNING (
p_sql CLOB
)
AS
v_task VARCHAR2(30);
BEGIN
/* Drop the task in case we are re-running... */
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'sql_tuning_task');
EXCEPTION
WHEN OTHERS THEN -- ORA-13605
NULL;
END;
/* Create a SQL Tuning task for our SQL... */
v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => p_sql,
time_limit => 1,
scope => 'COMPREHENSIVE',
task_name => 'sql_tuning_task',
description => 'Demo of DBMS_SQLTUNE'
);
/* Execute the task... */
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'sql_tuning_task');
/* We want to run this again... */
ROLLBACK;
END P_SQL_TUNING;
Execute p_Sql_Tuning Procedure
SQL> exec P_SQL_TUNING('SELECT emp.EMPNO, emp.ENAME, emp.JOB,
emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO
FROM TESTEMP emp,TESTEMP empmgr
Where emp.empno=empmgr.mgr
ORDER by SAL'
);
PL/SQL procedure successfully completed.Tuning Result
SQL> set long 80000
SQL> col recs format a90
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task') AS result
FROM DUAL;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_task
Tuning Task Owner : TEST
Scope : COMPREHENSIVE
Time Limit(seconds) : 1
Completion Status : COMPLETED
Started at : 06/14/2010 12:24:18
Completed at : 06/14/2010 12:24:18
Number of Index Findings : 1
-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID : grfz43h6vdf3d
SQL Text : SELECT emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE,
emp.SAL, emp.COMM, emp.DEPTNO FROM TESTEMP emp,TESTEMP empmgr
Where emp.empno=empmgr.mgr ORDER by SAL
-------------------------------------------------------------------------------
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: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index TEST.IDX$$_041C0001 on TEST.TESTEMP('MGR');
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
-----------
Plan hash value: 719480185
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 533 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 13 | 533 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 13 | 533 | 7 (15)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TESTEMP | 13 | 52 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TESTEMP | 14 | 518 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."EMPNO"="EMPMGR"."MGR")
3 - filter("EMPMGR"."MGR" IS NOT NULL)
2- Using New Indices
--------------------
Plan hash value: 2840960280
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 533 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 13 | 533 | 4 (25)| 00:00:01 |
| 2 | NESTED LOOPS | | 13 | 533 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TESTEMP | 14 | 518 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_041C0001 | 1 | 4 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."EMPNO"="EMPMGR"."MGR")
filter("EMPMGR"."MGR" IS NOT NULL)
Clean up
The DBMS_SQLTUNE package provides APIs to remove the objects we created as follows.
SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tuning_task');
PL/SQL procedure successfully completed. Note: The Following error message means user have no permission to user Advisor privilege ORA-13616: The current user <User Name> has not been granted the ADVISOR privilege. Solutions:Grant ADVISOR to <User Name>;
