Database Reference
In-Depth Information
However, from version 11.2 onward, it's also possible to display the list of hints with the
display_sql_plan_
baseline
function. In fact, as for the other functions in the
dbms_xplan
package, the
format
parameter can be used to
influence the output. The following is an excerpt of the output produced when the
format
parameter is set to
outline
:
SQL> SELECT *
2 FROM table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_971650b23f790eb7',
3
format => 'outline'
));
Outline Data from SMB:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Evolving SQL Plan Baselines
When the query optimizer generates an execution plan different from one present in the SQL plan baseline associated
to the SQL statement it's optimizing, a new nonaccepted execution plan is automatically added to the SQL plan
baseline. This happens even if the query optimizer can't immediately use the nonaccepted execution plan. The
idea is to keep the information that another and possibly better execution plan exists. To verify whether one of the
nonaccepted execution plans will in fact perform better than the ones generated with the help of accepted SQL
plan baselines, an
evolution
must be attempted. This is nothing other than asking the SQL engine to run the SQL
statement with different execution plans and finding out whether a nonaccepted SQL plan baseline will lead to better
performance than an accepted one. If this is in fact the case, the nonaccepted SQL plan baseline is set to accepted.
■
the SQL engine processes SQL statements in a special way during an evolution. in fact, for
INSERT
/
UPDATE
/
MERGE
/
DELETE
statements, the data is accessed but not modified. hence, SQL statements are only
partially executed. however, i don't regard this fact as a problem. in fact, the operations that modify the data should
always perform the same work independently of how the data to be modified is accessed.
Caution
To execute an evolution, the
evolve_sql_plan_baseline
function in the
dbms_spm
package is available. To call
this function, in addition to identifying the SQL plan baseline with the
sql_handl
e and/or
plan_name
parameters, the
following parameters can be specified:
time_limit
: How long, in minutes, the evolution can last. This parameter accepts either a
natural number or the
dbms_spm.auto_limit
and
dbms_spm.no_limit
constants.
•
verify
: If set to
yes
(default), the SQL statement is executed to verify the performance.
If set to
no
, no verification is performed, and the SQL plan baselines are simply accepted.
•
