Scribd
Upload
134 views

How To Use SQL Tuning Advisor

The document describes how to use SQL Tuning Advisor to tune a SQL query. It shows the steps to create a tuning task, execute it, and view the results. The tuning task analyzed a 3-table join query and recommended creating an index on the DEPARTMENTS table's LOCATION_ID column to improve the query performance by an estimated 100%. The report section shows the original execution plan and improved plan with the recommended index.

Uploaded by

Pankajkumar Kapase
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
134 views

How To Use SQL Tuning Advisor

The document describes how to use SQL Tuning Advisor to tune a SQL query. It shows the steps to create a tuning task, execute it, and view the results. The tuning task analyzed a 3-table join query and recommended creating an index on the DEPARTMENTS table's LOCATION_ID column to improve the query performance by an estimated 100%. The report section shows the original execution plan and improved plan with the recommended index.

Uploaded by

Pankajkumar Kapase
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 6

How to use SQL Tuning Advisor

GRANT ADVISOR TO HR;


GRANT SELECT_CATALOG_ROLE TO HR;
GRANT EXECUTE ON DBMS_SQLTUNE TO HR;
----------------------------------------------------------- Create a task
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'l.LOCATION_ID < :bnd';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task_6',
description => 'Task to tune a query on a specified employee');
END;
/
---------------------------------------------------------------- Execute
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_6' );
END;
/
--------------------------------------------------------------------------- output
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_6')
FROM DUAL;
-------------------------------------------------------------------------

'SELECT * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'l.LOCATION_ID < :bnd';
-------------------------------------------------------GENERAL INFORMATION SECTION

------------------------------------------------------------------------------Tuning Task Name


: my_sql_tuning_task_6
Tuning Task Owner
: HR
Scope
: COMPREHENSIVE
Time Limit(seconds)
: 60
Completion Status
: COMPLETED
Started at
: 01/08/2010 15:19:08
Completed at
: 01/08/2010 15:19:08
Number of Index Findings
: 1
------------------------------------------------------------------------------Schema Name: HR
SQL ID
: d03mff02dqnkw
SQL Text : SELECT * FROM employees e, locations l, departments d WHERE
e.department_id = d.department_id AND l.location_id =
d.location_id AND l.LOCATION_ID < :bnd
------------------------------------------------------------------------------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 HR.IDX$$_0F7C0001 on HR.DEPARTMENTS('LOCATION_ID');
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: 1937517558
--------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cos
t (%CPU)| Time
|
--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT
|
|
3 | 411 |
5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES
|
10 | 680 |
1 (0)| 00:00:01 |
| 2 | NESTED LOOPS
|
|
3 | 411 |
5 (0)| 00:00:01 |

3 |
NESTED LOOPS
|
|
1 |
69 |
4 (0)| 00:00:01 |
|* 4 |
TABLE ACCESS FULL
| DEPARTMENTS
|
1 |
20 |
3 (0)| 00:00:01 |
| 5 |
TABLE ACCESS BY INDEX ROWID| LOCATIONS
|
1 |
49 |
1 (0)| 00:00:01 |
|* 6 |
INDEX UNIQUE SCAN
| LOC_ID_PK
|
1 |
|
0 (0)| 00:00:01 |
|* 7 |
INDEX RANGE SCAN
| EMP_DEPARTMENT_IX |
10 |
|
0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------4 - filter("D"."LOCATION_ID"<:BND)
6 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
filter("L"."LOCATION_ID"<:BND)
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
2- Using New Indices
-------------------Plan hash value: 3784202861
--------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cos
t (%CPU)| Time
|
--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT
|
|
3 | 411 |
4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES
|
10 | 680 |
1 (0)| 00:00:01 |
| 2 | NESTED LOOPS
|
|
3 | 411 |
4 (0)| 00:00:01 |
| 3 |
NESTED LOOPS
|
|
1 |
69 |
3 (0)| 00:00:01 |
| 4 |
TABLE ACCESS BY INDEX ROWID| DEPARTMENTS
|
1 |
20 |
2 (0)| 00:00:01 |
|* 5 |
INDEX RANGE SCAN
| IDX$$_0F7C0001
|
1 |
|
1 (0)| 00:00:01 |
| 6 |
TABLE ACCESS BY INDEX ROWID| LOCATIONS
|
1 |
49 |
1 (0)| 00:00:01 |
|* 7 |
INDEX UNIQUE SCAN
| LOC_ID_PK
|
1 |
|
0 (0)| 00:00:01 |
|* 8 |
INDEX RANGE SCAN
| EMP_DEPARTMENT_IX |
10 |
|
0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------5 - access("D"."LOCATION_ID"<:BND)
7 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
filter("L"."LOCATION_ID"<:BND)
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

------------------------------------------------------------------------------=====================================
From: Santosh[Cmc] (FIA-MFPD/HP26613758411/121.242.59.45)
at Fri Jan 08 15:22:51 2010 (RSA)(sealed)
(files) Jan052010_MindGame.sql
------------------------------------=====================================
To: Amitava Dasgupta[Madan Mohan] (Fundamentals@Bloomberg/HP25865860921/121.242
.59.52)
at Fri Jan 08 15:24:00 2010 (RSA)
------------------------------------'SELECT * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'l.LOCATION_ID < :bnd';
-------------------------------------------------------GENERAL INFORMATION SECTION
------------------------------------------------------------------------------Tuning Task Name
: my_sql_tuning_task_6
Tuning Task Owner
: HR
Scope
: COMPREHENSIVE
Time Limit(seconds)
: 60
Completion Status
: COMPLETED
Started at
: 01/08/2010 15:19:08
Completed at
: 01/08/2010 15:19:08
Number of Index Findings
: 1
------------------------------------------------------------------------------Schema Name: HR
SQL ID
: d03mff02dqnkw
SQL Text : SELECT * FROM employees e, locations l, departments d WHERE
e.department_id = d.department_id AND l.location_id =
d.location_id AND l.LOCATION_ID < :bnd
------------------------------------------------------------------------------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 HR.IDX$$_0F7C0001 on HR.DEPARTMENTS('LOCATION_ID');
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: 1937517558
--------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cos
t (%CPU)| Time
|
--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT
|
|
3 | 411 |
5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES
|
10 | 680 |
1 (0)| 00:00:01 |
| 2 | NESTED LOOPS
|
|
3 | 411 |
5 (0)| 00:00:01 |
| 3 |
NESTED LOOPS
|
|
1 |
69 |
4 (0)| 00:00:01 |
|* 4 |
TABLE ACCESS FULL
| DEPARTMENTS
|
1 |
20 |
3 (0)| 00:00:01 |
| 5 |
TABLE ACCESS BY INDEX ROWID| LOCATIONS
|
1 |
49 |
1 (0)| 00:00:01 |
|* 6 |
INDEX UNIQUE SCAN
| LOC_ID_PK
|
1 |
|
0 (0)| 00:00:01 |
|* 7 |
INDEX RANGE SCAN
| EMP_DEPARTMENT_IX |
10 |
|
0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------4 - filter("D"."LOCATION_ID"<:BND)
6 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
filter("L"."LOCATION_ID"<:BND)
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
2- Using New Indices
-------------------Plan hash value: 3784202861
--------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cos
t (%CPU)| Time
|
--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT
|
|
3 | 411 |
4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES
|
10 | 680 |
1 (0)| 00:00:01 |
| 2 | NESTED LOOPS
|
|
3 | 411 |

(0)| 00:00:01 |
3 |
NESTED LOOPS
|
|
1 |
69 |
3 (0)| 00:00:01 |
| 4 |
TABLE ACCESS BY INDEX ROWID| DEPARTMENTS
|
1 |
20 |
2 (0)| 00:00:01 |
|* 5 |
INDEX RANGE SCAN
| IDX$$_0F7C0001
|
1 |
|
1 (0)| 00:00:01 |
| 6 |
TABLE ACCESS BY INDEX ROWID| LOCATIONS
|
1 |
49 |
1 (0)| 00:00:01 |
|* 7 |
INDEX UNIQUE SCAN
| LOC_ID_PK
|
1 |
|
0 (0)| 00:00:01 |
|* 8 |
INDEX RANGE SCAN
| EMP_DEPARTMENT_IX |
10 |
|
0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------|

Predicate Information (identified by operation id):


--------------------------------------------------5 - access("D"."LOCATION_ID"<:BND)
7 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
filter("L"."LOCATION_ID"<:BND)
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
------------------------------------------------------------------------------------------------

You might also like