Clarification/Explanation
==========================
The query optimizer can sometimes produce inaccurate estimates about
an attribute of a statement due to lack of information,leading to poor
execution plans. Traditionally, users have corrected this problem by
manually adding hints to the application code to guide the optimizer
into making correct decisions. For packaged applications, changing
application code is not an option and the only alternative available
is to log a bug with the application vendor and wait for a fix.
Automatic SQL Tuning deals with this problem with its SQL Profiling
capability. The Automatic Tuning Optimizer creates a profile of the
SQL statement called a SQL Profile, consisting of auxiliary statistics
specific to that statement. The query optimizer under normal mode makes
estimates about cardinality, selectivity, and cost that can sometimes be
off by a significant amount resulting in poor execution plans. SQL Profile
addresses this problem by collecting additional information using
sampling and partial execution techniques to verify and, if necessary,
adjust these estimates.
During SQL Profiling, the Automatic Tuning Optimizer also uses execution
history information of the SQL statement to appropriately set optimizer
parameter settings, such as changing the OPTIMIZER_MODE initialization
parameter setting from ALL_ROWS to FIRST_ROWS for that SQL statement.
The output of this type of analysis is a recommendation to accept the
SQL Profile. A SQL Profile, once accepted, is stored persistently in
the data dictionary. Note that the SQL Profile is specific to a
particular query. If accepted, the optimizer under normal mode uses the
information in the SQL Profile in conjunction with regular
database statistics when generating an execution plan.
The availability of the additional information makes it possible
to produce well-tuned plans for corresponding SQL statement without
requiring any change to the application code.
The scope of a SQL Profile can be controlled by the CATEGORY profile attribute.
This attribute determines which user sessions can apply the profile.
You can view the CATEGORY attribute for a SQL Profile in CATEGORY column
of the DBA_SQL_PROFILES view. By default, all profiles are created in the
DEFAULT category. This means that all user sessions where the SQLTUNE_CATEGORY
initialization parameter is set to DEFAULT can use the profile.
By altering the category of a SQL profile, you can determine which sessions are
affected by the creation of a profile. For example, by setting the category
of a SQL Profile to DEV, only those users sessions where the SQLTUNE_CATEGORY
initialization parameter is set to DEV can use the profile. All other sessions
do not have access to the SQL Profile and execution plans for SQL statements
are not impacted by the SQL profile. This technique enables you to test
a SQL Profile in a restricted environment before making it available to other
user sessions.
It is important to note that the SQL Profile does not freeze the execution plan
of a SQL statement, as done by stored outlines. As tables grow or indexes
are created or dropped, the execution plan can change with the same SQL Profile.
The information stored in it continues to be relevant even as the data
distribution or access path of the corresponding statement change.
However, over a long period of time, its content can become outdated and
would have to be regenerated. This can be done by running Automatic
SQL Tuning again on the same statement to regenerate the SQL Profile.
SQL Profiles apply to the following statement types:
SELECT statements
UPDATE statements
INSERT statements (only with a SELECT clause)
DELETE statements
CREATE TABLE statements (only with the AS SELECT clause)
MERGE statements (the update or insert operations)
Managing SQL Profiles
=====================
While SQL Profiles are usually handled by Oracle Enterprise Manager as part
of the Automatic SQL Tuning process, SQL Profiles can be managed through the
DBMS_SQLTUNE package. To use the SQL Profiles APIs, you need the
CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, and ALTER ANY SQL_PROFILE
system privileges.
Accepting a SQL Profile
=======================
You can use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a
SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a
SQL Profile in the database. For example:
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task',
name => 'my_sql_profile');
END;
Where my_sql_tuning_task is the name of the SQL tuning task.
You can view information about a SQL Profile in the DBA_SQL_PROFILES view.
Altering a SQL Profile
======================
You can alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of
an existing SQL Profile with the ALTER_SQL_PROFILE procedure. For example:
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'my_sql_profile',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
In this example, my_sql_profile is the name of the SQL Profile that you want to alter.
The status attribute is changed to disabled which means the SQL Profile is
not used during SQL compilation.
Dropping a SQL Profile
======================
You can drop a SQL Profile with the DROP_SQL_PROFILE procedure. For example:
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
END;
/
In this example, my_sql_profile is the name of the SQL Profile you want to drop.
You can also specify whether to ignore errors raised if the name does not exist.
For this example, the default value of FALSE is accepted.
Example
=======
SESSION 1 -- SCOTT
=========
SQL> create table test (n number );
Table created.
declare
begin
for i in 1 .. 10000
loop
insert into test values(i);
commit;
end loop;
end;
PL/SQL procedure successfully completed.
create index test_idx on test(n);
Index created.
analyze table test estimate statistics (OR use dbms_stats)
Table analyzed.
select /*+ no_index(test test_idx) */ * from test where n=1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=13)
1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=6 Card=1 Bytes
=13)
SESSION 2 -- SYS
=========
1 DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=
1';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text => my_sqltext,
8 user_name => 'SCOTT',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'my_sql_tuning_task_2',
12 description => 'Task to tune a query on a specified table');
13* END;
14 /
PL/SQL procedure successfully completed.
1 BEGIN
2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');
3* end;
SQL> /
PL/SQL procedure successfully completed.
1 SET LONG 1000
2 SET LONGCHUNKSIZE 1000
3 SET LINESIZE 100
4* SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
--------------------------------------------------------------------------------
--------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task_2
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/04/2004 17:36:05
Completed at : 05/04/2004 17:36:05
-------------------------------------------------------------------------------
SQL ID : d4wgpc5g0s0vu
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
--------------------------------------------------------------------------------
--------------------
SQL Text: select /*+ no_index(test test_idx) */ * from test where n=1
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 83.84%)
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
--------------------------------------------------------------------------------
--------------------
------------------------------------------
Consider accepting the recommended
1 DECLARE
2 my_sqlprofile_name VARCHAR2(30);
3 BEGIN
4 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
5 task_name => 'my_sql_tuning_task_2',
6 name => 'my_sql_profile');
7* END;
8 /
PL/SQL procedure successfully completed.
SQL> select to_char(sql_text) from dba_sql_profiles;
TO_CHAR(SQL_TEXT)
------------------------------------------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=1
SESSION 1 --- SCOTT
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)
1 0 INDEX (RANGE SCAN) OF 'TEST_IDX' (INDEX) (Cost=1 Card=1 By
tes=13
