Re: Stored Outlines

On 3 May 2007 09:57:27 -0700, dombrooks <dombrooks@xxxxxxxxxxx> 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
.

Relevant Pages

  • Re: access 2003
    ... I would focus on the queries behind the combo boxes. ... the Access 97 database, I wouldn't have thought any expressions would be ... When you select a customer and a job in the two combo boxes, ...
    (microsoft.public.access.conversion)
  • Re: access 2003
    ... I would focus on the queries behind the combo boxes. ... the Access 97 database, I wouldn't have thought any expressions would be ... When you select a customer and a job in the two combo boxes, ...
    (microsoft.public.access.conversion)
  • Re: Question on conversion to ADP
    ... the money toward developping .NET technologies instead of Access. ... result of passthrough queries read/write instead of read only and also make ... database become corrupted when the users will start making their very own ... of ADP but what they are pushed toward is - how could I say that politely - ...
    (microsoft.public.access.adp.sqlserver)
  • Re: User has group permissions to object, but still denied access.
    ... permissions to those query objects. ... The owners for the other 68 queries can be changed. ... may have been some of the original queries created in the database. ... Regarding the security, the database was converted from an Access 97 ...
    (microsoft.public.access.security)
  • Re: Access/Word 2007 Mail Merge
    ... have thought that they would have occured earlier in the connection process. ... specify DDE, I always get the DDE dialog. ... might be "unusual" about your database, its location, etc. ... This isn't quite what I normally see either: Queries with joins are ...
    (microsoft.public.word.mailmerge.fields)
Loading