Autotrace – no cost, part II

I wrote just a bit earlier about how a lack of statistics on some objects in one system was causing the CBO is use some defaults and generate a sub-optimal plan, especially compared to a similar system which does have accurate statistics for all objects.

I tried adding various hints to the SQL in the “bad” system to get to the same plan as the “good” system but, whilst nearly there, I wasn’t quite nailing it.

As I’ve touched on in previous plans, I’ve been looking at stored outlines as part of the strategy to upgrade to 10g. So, I captured the stored outline for the statement in the “good” system using:

create or replace outline myoutline
for category mycategory
on select.....

I was not interested in exporting and enabling this in the “bad” system. But by looking at the outline views in the data dictionary, I was able to more easily figure out which hints to put where in what order.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: