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.

Nice.

About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 70 other followers

%d bloggers like this: