ThatJeffSmith

Force a Different Path with Automatic Plan Hints

Tell Others About This Story:

Are you smarter than the optimizer?

Are you hiding critical information from it? Perhaps your stats are missing, stale, or a bit misleading?

I don’t want to get into whether hints are good or bad – they can only be used to accomplish good or bad things. They are often absused – so please don’t take this post as carte blanche to go crazy with hints.

But.

What if you could easily tell the Optimizer to take a different path with your query. And then test it, to see if you were ‘smarter?’

In v4.2, you can do that.

Let’s look at the explain plan for

SELECT * FROM DBA_DATA_FILES;
I'm right clicking on the line with the red boxed text...the NESTED LOOPS.

I’m right clicking on the line with the red boxed text…the NESTED LOOPS.

Now, there’s more than one way for the optimizer to bring data together via JOINs. I’m not the person to explain the differences, although Tanel has a nice post here.

And I’d be remiss if I didn’t mention our own Oracle Docs – they even have nice pictures 🙂

So, the optimizer has more than one way to get our data together, and it almost always picks the most efficient way. But in this case, I want to ask it to try a different path.

Right click on the join method in the plan, pick your poison by clicking on the hint. We’ll inject the optimizer hint to your plan.

plan_hint2

Pin the plan. Run the query again via an AutoTrace, or ask for a different plan.

Then compare the two.

Is this good, or bad?

Is this good, or bad?

The session stats reflecting the work performed for the 2 queries certainly are different.

The session stats reflecting the work performed for the 2 queries certainly are different.

Maybe it is significantly better. Maybe you need to create some histograms so the optimizer KNOWS it needs to change up its join method. At the end of the day, you now have another tool in your toolbox – just don’t abuse it, or the optimizer.

#TEASE One last tweak – we have also opened up the plan table columns when viewing plans/traces in SQL Developer for our next drop of v4.2. We’ve added Bytes, TEMP_SPACE, TIME, and a few others. Just check your preferences. You’ll see these when 4.2 EA2 is available – so something to look forward to. #TEASE

You have a few more to choose from now in v4.2

You have a few more to choose from now in v4.2

A Nice Video on Comparing Plans using SQL Developer

Chris Saxon has a very nice video on comparing plans. Give it a look-see why don’t you.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Similar Posts by Content Area: , , , ,