How to get the outline hint in oracle and use for tuning

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

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