Database Reference
In-Depth Information
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9f5khq8zrk3pr3fdbb376 Plan id: 1071362934
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 505 | 20 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 505 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 505 | 20 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N"=28)
■
to correctly display information about a SQL plan baseline in versions 11.1 and 11.2, the
display_sql_plan_baseline
function has to be able to reproduce the execution plans associated to it. if the function
doesn't succeed, it might return wrong results or even an error message. to avoid such problems, from version 12.1
onward, the execution plan is stored for reporting purposes, and only for reporting purposes, in the SQL management
Base. You can execute the
baseline_unreproducible.sql
script to observe what the output looks like in the case of an
unreproducible execution plan.
Caution
Unfortunately, data dictionary tables must be queried in version 11.1 to display the list of hints associated with a
SQL plan baseline. The following SQL statement shows an example. Note that because hints are stored in XML format,
a conversion is necessary to have a readable output:
SQL> SELECT extractValue(value(h),'.') AS hint
2 FROM sys.sqlobj$data od, sys.sqlobj$ so,
3 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
4 WHERE so.name = 'SQL_PLAN_9f5khq8zrk3pr3fdbb376'
5 AND so.signature = od.signature
6 AND so.category = od.category
7 AND so.obj_type = od.obj_type
8 AND so.plan_id = od.plan_id;
HINT
-------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
