Stored Outlines

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 ? Received on Thu May 03 2007 - 11:57:27 CDT