Database Reference
In-Depth Information
the only usage for SQL plan management mentioned in Oracle documentation is the stabilization of execution
plans. For some reasons i ignore, the possibility of using SQL plan management to change the current execution plan
(related to a given SQL statement) without modifying the application submitting that SQL statement, isn't mentioned.
Note
Here are the key elements SQL Plan Management consists of:
SQL plan baselines: The actual objects that are used to make execution plans stable.
Statement log: A list of SQL statements that were executed in the past.
SQL Management Base (SMB): Where the SQL plan baselines and the statement log are
stored. The required space is allocated in the sysaux tablespace.
How It Works
The following sections describe how SQL plan management works. Specifically, they cover what SQL plan baselines
are and how to manage them. To manage them, a graphical interface is integrated into Enterprise Manager. I don't
spend time here looking at it, because in my opinion, if you understand what is going on in the background, you will
have no problem at all in using the graphical interface.
What Are SQL Plan Baselines?
A SQL plan baseline is an object associated with a SQL statement that's designed to influence the query optimizer
while it generates execution plans. More concretely, a SQL baseline contains, among other things, one or more
execution plans which in turn contain a set of hints. Basically a SQL plan baseline is used to force the query optimizer
to consistently generate specific execution plans for a given SQL statement.
not all hints can be stored in SQL plan baselines. to know which hint can't be stored, you can run the
following query:
Caution
SELECT name FROM v$sql_hint WHERE version_outline IS NULL
even though most of the hints that can't be stored in SQL plan baselines don't impact execution plans (for example,
gather_plan_statistics ), some of them do (for example, materialize and inline ). as a result, there are some
execution plans that can't be forced through a SQL plan baseline without specifying a hint in the SQL statement itself.
One of the advantages of a SQL plan baseline is that it applies to a specific SQL statement, and yet no modification
of the SQL statement itself is needed. In fact, the SQL plan baselines are stored in the SQL Management Base, and the
query optimizer selects them automatically. Figure 11-8 shows the basic steps carried out during this selection. These are:
1.
First, the SQL statement is parsed in the conventional way. In other words, the query
optimizer generates an execution plan without the support of a SQL plan baseline.
2.
Then, the query optimizer normalizes the SQL statement to make it both case-insensitive
and independent of the blank spaces present in the text. The signature of the resulting SQL
statement is computed, and a lookup into the SQL Management Base is performed.
If a SQL plan baseline with the same signature is found, a check is performed to make sure
that the SQL statement to be optimized and the SQL statement associated with the SQL
 
 
Search WWH ::




Custom Search