How to check the category of a Stored Outline

Stored Outlines is a feature within Oracle which helps preserve Execution Plan Stability.

Unfortunately the parameter use_stored_oulines is not an initialisation parameter and hence cannot be set in the init.ora or spfile.The only way to determine if outlines are being used and their category is by using the oradebug utility.

SQL> oradebug setmypid
Statement processed.

SQL> oradebug dumpvar sga sgauso
qolprm sgauso_ [3C4C708, 3C4C72C) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0

The above output indicates that no outlines are being used

SQL> alter system set use_stored_outlines = true;

System altered.

SQL> oradebug dumpvar sga sgauso
qolprm sgauso_ [3C4C708, 3C4C72C) = 00000001 45440007 4C554146 00000054 00000000 00000000 00000000 0

The above output indicates that the stored outlines are being used and the category
is DEFAULT
(Ascii equivalent of 44 45 46 41 55 4C 54)
The 7 here stands for the length of the category.

If you set a value at the session level you need to check the UGA

SQL> alter session set use_stored_outlines = false;

Session altered.

SQL> oradebug dumpvar uga ugauso
qolprm ugauso_p [8EE7A90, 8EE7AB4) = 00000004 00000000 00000000 00000000 00000000 00000000 00000000

SQL> alter session set use_stored_outlines = true;

Session altered.

SQL> oradebug dumpvar uga ugauso
qolprm ugauso_p [8EE7A90, 8EE7AB4) = 00000004 45440007 4C554146 00000054 00000000 00000000 00000000