Database Reference
In-Depth Information
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
|* 3 | INDEX RANGE SCAN | I |
---------------------------------------------
3 - access("N"=42)
Note
-----
- SQL plan baseline SQL_PLAN_3u6sbgq7v4u8z59340d78 used for this statement
To know whether a SQL plan baseline was used for a specific SQL statement, it's also possible to check the sql_
plan_baseline column in the v$sql view. Note that the column shows the SQL plan name, not the SQL handle, as the
dbms_xplan package does.
Load from SQL Tuning Set
To load SQL plan baselines from SQL tuning sets, the load_plans_from_sqlset function in the dbms_spm package is
available. Loading is simply a matter of specifying the owner and the name of the SQL tuning set. The following call,
an excerpt of the baseline_from_sqlset.sql script, illustrates this:
ret := dbms_spm.load_plans_from_sqlset(sqlset_name => 'test_sqlset',
sqlset_owner => user);
Execution plans loaded with this function are stored as accepted. Therefore, the query optimizer is immediately
able to take advantage of them.
A possible use of this function is the upgrade to a newer release. In fact, it's also possible, for example, to load in
a version 11.2 database a SQL tuning set created with a version 10.2 database. The baseline_upgrade_10g.sql and
baseline_upgrade_11g.sql scripts illustrate this utilization.
Displaying SQL Plan Baselines
General information about the available SQL plan baselines can be displayed through the dba_sql_plan_baselines
view (from version 12.1 onward, the cdb_sql_plan_baselines view is also available). To display detailed information
about them, the display_sql_plan_baseline function in the dbms_xplan package is available. Note that it works
similarly to the other functions in the dbms_xplan package discussed in Chapter 10. The following example shows the
kind of information that can be displayed with it:
SQL> SELECT *
2 FROM table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_971650b23f790eb7'));
--------------------------------------------------------------------------------
SQL handle: SQL_971650b23f790eb7
SQL text: SELECT /* MySqlStm */ count(pad) FROM t WHERE n = 28
--------------------------------------------------------------------------------
 
Search WWH ::




Custom Search