Re: Stored Outlines

On May 3, 9:57 am, dombrooks <dombro..._at_hotmail.com> wrote:
> I have some stored outlines that aren't being used and I can't figure
> out why.
> Not sure what the best next step is.
>
> Unfortunately, this is 8.1.7.4 - appreciate it's an old version, it's
> upgrading imminently.
>
> The production database has *some* statistics - a really crap
> situation which again is on the path to being rectified.
>
> Some queries have been developed which work ok against a development
> database where all objects have some representative statistics.
>
> Not surprisingly, these queries do not work at all well against a
> production-like database where only some objects have statistics, and
> some of those are stale (the CBO makes up some defaults and not
> surprisingly gets bad paths).
>
> So, the idea was to:
> - Capture stored outlines for queries against "good" database
> - Export
> - Import to "bad" database
> - Enable stored outline usage at a session level in the "bad" database
> - See the queries run acceptably.
>
> Unfortunately, in the "bad" database, the stored outlines are not
> being used.
> - I have set "alter session set use_stored_outlines=....."
> - I have flushed the shared pool to make sure
> - I can tell from performance that outlines are not being used
> - I can see from a 10046 and 10053 trace that stored outlines are not
> being used
> - I can see from v$sql that the outline_category is null
> - I can see from outln.ol$ that the queries are there and not being
> used
> - I can see in outln.ol$hint that all the hints are there and nothing
> has got corrupted or rejected from imp/exp.
> - I can see from v$sql that the hash_value of the SQL matches the
> hash_value of the SQL in ol$.
>
> What could / should my next steps be to determine what is going on ?

Well, since I don't see it, I have to ask: What is optimizer_mode set to? What did you set use_stored_outlines to? Maybe you need to set it to TRUE and make the category of the outline DEFAULT? (I'm suspecting you have a mismatch there because outline_category is null, so maybe a return to the default might be a good starting point - if that works, you can be more fancy). Does USER_OUTLINES agree with ol $hint? (A reach, I know).

ALTER OUTLINE outline_name CHANGE CATEGORY TO DEFAULT;

jg

--
@home.com is bogus.
http://blog.wired.com/business/2007/05/pc_world_editor.html