Question on stored outlines

From: <dbaplusplus_at_hotmail.com>
Date: 6 Nov 2005 09:20:24 -0800
Message-ID: <1131297624.609276.25360@f14g2000cwb.googlegroups.com>

I am using Oracle 9.2.0.5 on HP UNIX 11i. I am supporting EMC Documentum's
canned application. I am using cost based optimizer. There is a query which is taking 7 hours to run (yes I have updated statistics on all table/indexes - used COMPUTE STATISTICS). I have isolated this query and when I run in sqlplus by adding a hint: /*+ RULE */, query takes less than a minute.

Since I do not have access to Documentum's code, STORED OUTLINES seem like good candidate to add this hint.

Based on my reading, to use ORACLE's stored outlines, one has to set a parameter in the session, for example ... ALTER Session SET USE_STORED_OUTLINES=true I do not see USE_STORED_OUTLINES as an init.ora parameter

Since Documentum's code cannot be modified how STORED OUTLINES can really be used? Am I missing something? Why all the articles on Stored Outlines claim no code changes are required.

Only option seems to me is to use a logon trigger and set this parameter.
Are there any other options, will like to know from the experts.

Thanks a lot. Received on Sun Nov 06 2005 - 11:20:24 CST