I have had to use far fewer hints starting with Oracle10. This optimizer takes a much closer look at index processing than all prior Oracle databases. I have found I simply don’t need index hints of any kind anymore.
Try this during your testing. This setting will cause the CBO to ignore any hint present. I learned this trick from an internal person at Oracle as they feel the optimizer in Oracle 10 is quite a bit better as well.
alter session set “_optimizer_ignore_hints” = true;
About the only time I see I need a hint anymore is for a SQL statement like this.
These are clips out of my JS Tuner explain plan tool using the DBMS_XPLAN to show this the estimated and actual rows returned.
Hash joins are to drive from a smaller table to a larger table. If you notice the explain plan estimated rows vs actual rows, line 3 is the inner hash join with the TEAMS table as the driving table for this query (ie: the first table being accessed). Both the estimated and actual row counts show smaller to larger accesses. Good job so far.
The output of this join is shown on line 3 where the CBO things (estimated rows) there will be 6688 rows when in actuality, there were over 94,000 rows! Quite the poor guess. The math really goes south joining up to the MASTER table. The output of the inner join (in this case, the join on line 3) feeds into the hash join on line 2 as the outer table, or first table accessed. Line 2 then works with the MASTER table on line 6…this is where the performance issue is with this query. The estimated row counts shows smaller to larger on the hash join on line 2 when in actuality (actual rows) shows this hash join is going much larger to smaller (94,000 rows and 61000 rows).
Oracle 11 fixes this with a little known feature called cardinality feedback. If you were to run an explain plan on the next execution of this SQL in Oracle 11, you would see a note saying cardinality feedback being used and the estimated rows and actual rows would match up and would have produced a better and higher performance explain plan. If you wish to run this query and have it perform well initially, I’d probably put a dynamic sample hint in to give this query the correct row counts. You could also pass in row counts using the cardinality hint. I would probably try a nested loop first although, nested loops work far better when the second table being joined is very small (selection from the table is just a few rows). Nested loops work well larger to smaller.
Cardinality feedback is valid until stats are run again or the database is recycled.
Oracle 12 resolves this by making a SQL directive of this cardinality feedback. Now, these actual values will be reused for up to 6 months.
Again, the CBO is getting better as the databases progress. It is this kind of technology that causes me to dislike seeing DB_VERSION or OPTIMIZER_FEATURE_ENABLE init.ora settings in use.
We’ll conclude this series in the next article with an exploration of hints I find quite useful.
This part does not seem correct.
” I would probably try a nested loop first although, nested loops work far better when the second table being joined is very small (selection from the table is just a few rows). Nested loops work well larger to smaller.”
A NL join would be better with a small result set as the outer loop. That determines the number of times the inner loop is executed. Can you explain why you think the opposite is true?