Database Reference
In-Depth Information
Two SQL statements with the same text have the same signature. This is also true even if they reference objects in
different schemas. That means that a single SQL plan baseline 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.
SQL plan baselines aren't supported for SQL statements referencing tables stored in remote databases.
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 plan baselines is restricted on Active Data Guard
environments. You can use them on primary instances, but not always on standby instances.
SQL plan baselines are stored in the SQL Management Base in the
sysaux
tablespace. By default,
at most 10 percent of the tablespace can be used for them. The current value can be displayed through the
dba_sql_management_config
view:
SQL> SELECT parameter_value
2 FROM dba_sql_management_config
3 WHERE parameter_name = 'SPACE_BUDGET_PERCENT';
PARAMETER_VALUE
---------------
10
When the threshold is exceeded, a warning message is written in the alert log. To change the default threshold,
the
configure
procedure in the
dbms_spm
package is available. Values between 1 percent and 50 percent are
supported. The following example shows how to change it to 5 percent. If the
parameter_value
parameter is set to
NULL
, the default value is restored:
dbms_spm.configure(parameter_name => 'space_budget_percent',
parameter_value => 5);
Whenever a SQL statement has a SQL plan baseline 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 plan
baseline with a SQL profile is of limited use. In fact, 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.
On to Chapter 12
This chapter describes several SQL optimization techniques. Selecting one of them isn't always easy. Nevertheless,
if you understand not only how they work but also the pros and cons of using them, the choice is much easier. That
said, in practice your choice is limited because you can't apply all techniques in all situations. This may be either
because of technical limits or because there are licensing issues.
Chapter 12 is devoted to parsing, which is surely a central step in the execution of SQL statements. Parsing is so
important because it's when the query optimizer generates the execution plans. To always have an efficient execution
plan, you want to parse every SQL statement executed by the database engine. But conversely, parsing is inherently
a very expensive operation. As a result, it must be minimized, and execution plans should be reused as much as
possible—but not too much, though. This might mean that the execution plan isn't always an efficient one. Once
again, in order to take advantage of the database engine in the best possible way, you have to understand how it works
and what the pros and cons of the different features are.
