To get the outline hints for a specific SQL execution plan in Oracle, use the DBMS_XPLAN.DISPLAY_CURSOR function and pass ‘+OUTLINE’ in the format parameter. This generates the complete set of hints the Cost-Based Optimizer used for that specific execution. You can then apply these hints to modern SQL Plan Baselines or SQL Profiles to permanently stabilize query performance.
why a DBA needs the outline hints today
Outline hints of the sql statement are the hints which are required to reproduce the same plan.
This can be useful in many ways
(a) You can enforce the same plan on the same query with different literal
(b) we could use them between two slightly different queries also
(c) These are the same hints used in the custom sql profile, so we can test it by putting the outline data in the query and if it works well, we can implement the sql profile
How to get the outline hint in Oracle
We can get this using the below query
select * from table( DBMS_XPLAN.display_cursor('&sql_id', NULL,'ADVANCED ROWS ALLSTATS'));
or
select * from table( DBMS_XPLAN.DISPLAY_AWR('&sql_id', NULL,NULL,'ADVANCED ROWS ALLSTATS'));
or
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(sql_id=>'&sql_id',format=>'ADVANCED'));
Example
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(sql_id=>’hjgjhg5878uv1′,format=>’ADVANCED’));
Outline Data
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3’)
DB_VERSION(‘11.2.0.3’)
OPT_PARAM(‘star_transformation_enabled’ ‘true’)
….
END_OUTLINE_DATA*/
How to show the outline data for the last sql statement executed
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'))
How to apply the outline hint for tuning
We just add the same outline data in the sql statement like this
Select /*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('star_transformation_enabled' 'true')
….
END_OUTLINE_DATA
*/
<sql statement>;
I hope this helps in your performance tuning journey
Related Articles
how to create sql baseline in oracle
explain plan in oracle
Sql plan Management in Oracle