* Example of useing stored outline and verify it is being used.
* Different users on the same database. 2 users have their own table on different tablespaces.

--> What is ORACLE doing behind the scene?

SQL> create table tyu as select object_id, object_name, object_type from dba_objects 
  2  where rownum < 100;

Table created.

SQL> alter table tyu add constraint tyu_pk primary key (object_id);

Table altered.

SQL> alter session set create_stored_outlines=upd1;

Session altered.

SQL> update tyu set object_name='For outline' where object_id=9849;

1 row updated.

SQL> alter session set create_stored_outlines=false;

Session altered.

SQL> commit;

Commit complete.

-- Verify outline has been created.
SQL> select sql_text,name,owner, category from dba_outlines where category='UPD1';

SQL_TEXT
--------------------------------------------------------------------------------
NAME                           OWNER
------------------------------ ------------------------------
CATEGORY
------------------------------
update tyu set object_name='For outline' where object_id=9849
SYS_OUTLINE_030613105212120    JYANG
UPD1


SQL> grant select on tyu to jtest;

Grant succeeded.

SQL> alter user jtest default tablespace example;

User altered.

SQL> alter user jtest quota unlimited on example;

User altered.

SQL> conn jtest/jtest@athena
Connected.
SQL> create table tyu as select * from jyang.tyu;

Table created.

SQL> alter table tyu add constraint tyu_pk primary key (object_id);

Table altered.


-- Notice that OUTLINE_CATEGORY is null
SQL> select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%9849%';

SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%9849%'


update tyu set object_name='For outline' where object_id=9849



SQL> alter session set use_stored_outlines=upd1;

Session altered.

SQL> update tyu set object_name='For outline' where object_id=9849;

1 row updated.


-- note that OUTLINE_CATEGORY has been chnaged to UPD1
SQL> select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%9849%';

SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%9849%'


select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%9849%'


update tyu set object_name='For outline' where object_id=9849
UPD1


SQL> commit;

Commit complete.


SQL> alter session set cursor_sharing=FORCE;

Session altered.

SQL> update tyu set object_name='For outline' where object_id=9849;

1 row updated.


-- Note that OUTLINE_CATEGORY still indicates UPD1
SQL> select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%9849%';

SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%9849%'


select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%9849%'


update tyu set object_name='For outline' where object_id=9849
UPD1


SQL> 

From Metalink
Stored Outlines Usage
---------------------

Stored outlines are used when:

o An outline category is enabled

alter session set use_stored_outlines = {category|TRUE}
alter system set use_stored_outlines = {category|TRUE} [NOOVERRIDE]

o The executed statement can be found as a direct match of a statement
in the outline category.

When setting use_stored_outlines to TRUE, the category named DEFAULT will be
used.

For matching statements the same strict rules apply as for statement matching
in the shared pool with the exception of optimizer mode.

Stored outlines are not used when:

o A hint in the stored outline becomes invalid.
o CURSOR_SHARING = FORCE

CURSOR_SHARING = FORCE disables the use of stored outlines.
CURSOR_SHARING was introduced in Oracle8i Release 2. It internally replaces
literals values in queries with bind variables, thus allowing these statements
to be shared.

Stored outlines will not be used if not all hints are valid.
This is in contrast to normal hints where if one hint fails (for
example, an index has been removed) the rest of the hints are still considered.

To test whether a statement did indeed use a stored outline, query the column
OUTLINE_CATEGORY in V$SQL (does not exist in V$SQLAREA); if this column is
NULL the statement did not use a stored outline.

---------------------