Database Reference
In-Depth Information
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 9892K| 6210 (1)| 00:01:40 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 5000 | 9892K| 6210 (1)| 00:01:40 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 9646 | 9542K| 1385 (0)| 00:00:23 |
|* 4 | INDEX RANGE SCAN | T1_COL1_COL2_I | 9500 | | 27 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | T2_PK | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1013 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
4 - access("T1"."COL1"=666 AND "T1"."COL2">42 AND "T1"."COL2" IS NOT NULL)
5 - access("T1"."ID"="T2"."ID")
2- Using SQL Profile
--------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 9892K| | 1081 (1)| 00:00:18 |
|* 1 | HASH JOIN | | 5000 | 9892K| 5008K| 1081 (1)| 00:00:18 |
| 2 | TABLE ACCESS FULL| T2 | 5000 | 4946K| | 174 (0)| 00:00:03 |
|* 3 | TABLE ACCESS FULL| T1 | 9646 | 9542K| | 344 (0)| 00:00:06 |
---------------------------------------------------------------------------------
1 - access("T1"."ID"="T2"."ID")
3 - filter("T1"."COL1"=666 AND "T1"."COL2">42)
To use the SQL profile recommended by the SQL Tuning Advisor, you have to accept it. The next section
describes how you do it. Independently of whether the SQL profile is accepted, once you no longer need the tuning
task, you can drop it by calling the
drop_tuning_task
procedure in the
dbms_sqltune
package:
dbms_sqltune.drop_tuning_task('TASK_3401');
Accepting SQL Profiles
The
accept_sql_profile
procedure in the
dbms_sqltune
package is used to accept a SQL profile recommended by
the SQL Tuning Advisor. It accepts the following parameters:
task_name
and
task_owner
parameters reference the tuning task that advises the SQL
profile.
•
The
name
and
description
parameters specify a name and a description for the SQL profile
itself. In the example, I use the name of the script generating it as the name.
•
The
category
parameter is used to group together several SQL profiles for management
purposes. It defaults to the value
DEFAULT
.
•
The
