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.
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;
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.
Pin the plan. Run the query again via an AutoTrace, or ask for a different plan.
Then compare the two.
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
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.