stored outlines

tru64 5.1
8.1.7.3

Having a problem with stored outlines..

2 databases (A and B)

B is our production database, A is a full copy of its data that we have been using for some performance testing (structure/indexes all the same).

Had a query that has been running very poorly in production. Using DBMS_STATS to change the statistics in database A, and alter system set create_stored_outlines=true we were able to get the application to generate a much better plan for this particular query, not wanting those stats in our production database, stored outlines seemed the way to go.

Reset the stats to normal in database A did alter system set use_stored_outlines=true and the application ran picking up the new plan as expected.

Following the articles on Metalink I have been able to export the outln tables, and import them into our production database.

Had the application try to run that same query against the production database and it is not picking up the outline. Verified that the hash_value from v$session for that instance does match the hash_value in outln.ol$ and that the outline_category on v$sql for the query it is running is null not default like I expected it to be.

According to the doc query_rewrite_enabled, star_transformation_enabled, and optimizer_features_enabled need to all be set the same and they are.

Is there something else I am missing, anyone successfully moved outlines for the same application between databases?

Scott Gamble Received on Thu Oct 24 2002 - 12:19:22 CDT