CBO Archives - SolarWinds

Using Oracle Cost-based Optimizer Hints: Part 4

This series of articles has focused on Cost-Based Optimizer (CBO) hints or directives to the cost-based optimizer (CBO) on manual directions for the explain plan (be sure to read part 1, part 2 and part 3). In this final article, let’s take a look at the hints that I do use. QB_NAME Hint I use the QB_NAME hint quite […]

Using Oracle Cost-based Optimizer Hints: Part 3

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 […]

Using Oracle Cost-based Optimizer Hints: Part 2

This article series will focus using Oracle cost-based optimizer (CBO) hints, and this second article on producing a series of hints that will replicate an exact explain plan. In Part 1, we discussed the OUTLINE produced by DBMS_XPLAN.Display(FORMAT=>’OUTLINE’), I find the IGNORE_OPTIM_EMBEDDED_HINTS to be an interesting hint to appear here. This hint says to ignore all hints. […]

Using Oracle Cost-based Optimizer Hints: Part 1

This article series will focus using Oracle cost-based optimizer (CBO) hints, and this first article on producing a series of hints that will replicate an exact explain plan. This technique might be useful for some problem SQL but this technique is also a useful learning tool for hints. Remember, the DBMS_XPAN.Display procedure can be run […]

Oracle Hard Parsing: A Closer Look

The prior article discussed the Oracle library cache, the difference between a hard parse and a soft parse, and a bit on bind variable processing as well. To review, Oracle hard parsing reads in statistics, utilizes index information and creates an execution plan. Soft parsing already has the execution plan and doesn’t need to revisit the statisticsand […]

Oracle Hard Parsing: A Closer Look at Permutations

The prior article started a discussion on the hard parsing process. It left off with the query transformation step. Please read these articles in the proper order as they each build on the prior article. The next step in the hard parse process is the permutations, as shown in the Figure below, taken from the […]