Database Reference
In-Depth Information
Capturing SQL Plan Baselines
You can capture new SQL plan baselines in several ways. Basically, they're created automatically by the database
engine or manually by database administrators or developers. The next three sections describe three of these methods.
Automatic Capture
When the
optimizer_capture_sql_plan_baselines
initialization parameter is set to
TRUE
, the query optimizer
automatically stores new SQL plan baselines. By default, the initialization parameter is set to
FALSE
. You can change it
at the session and system levels.
When the automatic capture is enabled, the query optimizer stores a new SQL plan baseline for each SQL
statement that is executed repeatedly (that is, executed at least twice). To that end, it manages a log in the SQL
Management Base where it inserts the signature of each SQL statement it works on. This means that the first time a
specific SQL statement is executed, its signature is inserted only into the log. Then, when the same SQL statement is
executed for the second time, a SQL plan baseline containing only the current execution plan is created and marked
as accepted. From the third execution on, because a SQL plan baseline is already associated with the SQL statement,
the query optimizer also compares the current execution plan with the execution plan generated with the help of
the SQL plan baseline. If they don't match, it means that according to the current query optimizer estimations, the
optimal execution plan isn't the one stored in the SQL plan baseline. To save that information, the current execution
plan is added to the SQL plan baseline and marked as nonaccepted. As you've seen before, however, the current
execution plan can't be used. The query optimizer is forced to use the execution plan generated with the help of the
SQL plan baseline. Figure
11-9
summarizes the whole process.
