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