Database Reference
In-Depth Information
For that purpose, you should use either stored outlines or SQL plan management. The only exception is when you
want to take advantage of the text normalization feature related to the
force_match
parameter. In fact, neither stored
outlines nor SQL plan management offer a similar feature.
When the
control_management_pack_access
initialization parameter is set to
none
or
diagnostic
, it's not possible
to use the SQL Tuning Advisor. If you try to do so, the database engine raises an
ORA-13717: Tuning Package License
is needed for using this feature
error. In addition, existing SQL profiles are ignored by the query optimizer.
Pitfalls and Fallacies
One of the most important properties of SQL profiles is that they're detached from the code. Nevertheless, that could
lead to problems. In fact, because there's no direct reference between the SQL profile and the SQL statement, it's
possible that a developer will completely ignore the existence of the SQL profile. As a result, if the developer modifies
the SQL statement in a way that leads to a modification of its signature, the SQL profile will no longer be used.
Similarly, when you deploy an application that needs some SQL profiles to perform correctly, you must not forget to
install them during the database setup.
If you have to generate a SQL profile, it's good practice to do it in the production environment (if available) and
then to move it to another environment for the necessary tests. The problem is that before moving a SQL profile, you
have to accept it. Because you don't want to enable it in production without having tested it, you should make sure
to accept it by using a category that's different from the one activated through the
sqltune_category
initialization
parameter. In that way, the SQL profile won't be used in the production database. In any case, it's always possible to
change the category of a SQL profile later.
You must be aware that SQL profiles aren't dropped when the objects they depend on are dropped. This
isn't necessarily a problem, though. For example, if a table or an index needs to be re-created because it must be
reorganized or moved, it's a good thing that the SQL profiles aren't dropped; otherwise, it would be necessary to
re-create them.
Two SQL statements with the same text have the same signature. This is also true even if they reference objects in
different schemas. This means that a single SQL profile could be used for two tables that have the same name but are
located in different schemas! You should be very careful, especially if you have a database with multiple copies of the
same objects.
Because of the bug described in the Oracle Support note
SQL profile not used in the Active Physical Standby
(10050057.8), up to an including 11.2.0.2 the use of SQL profiles is restricted on Active Data Guard environments.
You can use them on primary instances, but not always on standby instances.
Whenever a SQL statement has a SQL profile and a stored outline, the query optimizer uses only the stored
outline. Of course, this is the case only when the usage of stored outlines is active.
Whenever a SQL statement has a SQL profile and a SQL plan baseline, the query optimizer tries to merge the
hints associated to the SQL profile with those associated to the SQL plan baseline. However, merging a SQL profile
with a SQL plan baseline is of limited use. In fact, as described in the next section, the aim of a SQL plan baseline
is to force a specific execution plan to be used. As a result, the SQL profile might only be useful to generate a new
nonaccepted execution plan before taking the SQL plan baseline into consideration.
SQL Plan Management
From version 11.1 onward, SQL Plan Management (SPM) replaces stored outlines. Actually, it can be considered an
enhanced version of stored outlines. In fact, not only does it share several characteristics with them, but SQL Plan
Management shares the same design goal of providing stable execution plans in case of changes in the execution
environment or object statistics. In addition, as with stored outlines, SQL Plan Management can be used to optimize
an application without modifying it.
