Re: Stored Outlines

On 3 May 2007 09:57:27 -0700, dombrooks <dombrooks_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 ?

I would consider dumping the 'good' statistics into a stattab in the 'good' database (using export_schema_stats) exporting the stattab using exp
importing the stattab using imp in the 'bad' database running import_schema_stats.

Sybrand Bakker
Senior Oracle DBA Received on Thu May 03 2007 - 13:50:17 CDT