How to check the category of a Stored Outline

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

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

DUMPVAR
To dump an SGA variable use

ORADEBUG DUMPVAR SGA variable_name

e.g.

ORADEBUG DUMPVAR SGA sgauso

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
 means no stored outlines are using .