Database Reference
In-Depth Information
When a new execution plan is stored in the SQL plan baseline, it's important to distinguish between two situations:
•
If it's the first execution plan of the SQL plan baseline, the execution plan is stored as
accepted, and consequently, the query optimizer will be able to use it.
•
If it's not the first execution plan of the SQL plan baseline, it's stored as nonaccepted, and as a
result, the query optimizer won't be able to use it. The “Evolving SQL Plan Baselines” section
describes how to validate a SQL plan baseline to make it available to the query optimizer.
Load from Library Cache
To manually load SQL plan baselines into the data dictionary based on cursors stored in the library cache, the
load_plans_from_cursor_cache
function in the
dbms_spm
package is available.
Actually, the function is overloaded several times to support different methods that identify which cursors have to
be processed. There re two main possibilities. First, identify several SQL statements by specifying one of the following
attributes:
•
sql_text
: Text of the SQL statement. Wildcards (for example,
%
) are supported with this
attribute.
•
parsing_schema_name
: Schema name that was used to parse the cursor.
•
module
: Name of the module that executed the SQL statement.
action
: Name of the action that executed the SQL statement.
To illustrate, the following call, an excerpt of the
baseline_from_sqlarea1.sql
script, creates a SQL plan
baseline for each SQL statement stored in the library cache that contains the
MySqlStm
string as a comment in its text:
•
ret := dbms_spm.load_plans_from_cursor_cache(attribute_name => 'sql_text',
attribute_value => '%/* MySqlStm */%');
Second, identify a single SQL statement by its SQL ID and, optionally, the hash value of the execution plan. If the
hash value isn't specified or set to
NULL
, all execution plans available for the specified SQL statement are loaded. The
following call, an excerpt of the
baseline_from_sqlarea2.sql
script, illustrates this:
ret := dbms_spm.load_plans_from_cursor_cache(sql_id => '2y5r75r8y3sj0',
plan_hash_value => NULL);
Execution plans loaded with these functions are stored as accepted, and so the query optimizer might
immediately take advantage of them.
In the previous examples, the SQL plan baselines are based on the text of the SQL statement found in
the library cache. This is relevant only if you want to ensure that the current execution plan will also be used in
the future. Sometimes, the purpose of using a SQL plan baseline is to optimize a SQL statement without modifying the
application. Let's look at an example of such a situation, based on the
baseline_from_sqlarea3.sql
script.
Let's say one of your applications executes the following SQL statement. The execution plan generated by the
query optimizer is based on a full table scan. This is because the SQL statement contains a hint forcing the query
optimizer toward this operation:
SQL> SELECT /*+
full(t)
*/ count(pad) FROM t WHERE n = 42;
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
