Explain Plans Archives

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

Advanced Oracle Explain Plan Techniques: Part 4

This is the fourth of an article series on advanced Oracle explain plan techniques (read Part 3 here). In this article, we will finish the exploration of DBMS_XPLAN format options with a focus on the ‘ALLSTATS LAST’ formatting option. In the example above, notice that the SQL text has a hint: /+* GATHER_PLAN_STATISTICS */ . I have shown this in […]

Advanced Oracle Explain Plan Techniques: Part 3

This is the third of an article series on advanced Oracle explain plan techniques (read Part 2 here), continuing where we left off: a discussion of the Oracle DBMS_XPLAN format options. This article will illustrate the ‘ALL STATS LAST’ as well as the ‘ADVANCED’ formatting options. Notice above that the SQL text has a hint: /+* GATHER_PLAN_STATISTICS */ . I […]

Advanced Oracle Explain Plan Techniques: Part 2

This is the second of an article series on advanced Oracle explain plan techniques (read Part 1 here). In this article, we continue down the DBMS_XPLAN path and dig a little deeper into DBMS_XPLAN to show useful SQL tuning information. The above illustration shows the DBMS_XPLAN package and the three main functions I use to display useful […]

Advanced Oracle Explain Plan Techniques: Part 1

This is the first in an article series that will illustrate some advanced SQL explain plan techniques that were introduced with Oracle 10g. Oracle 10g was a big change for the Oracle optimizer, the code that tries to determine the best path to retrieve the requested data. Changes include how Oracle uses indexes and just a […]

How the Oracle Explain Plan Can Uncover Slow SQL Queries

The prior article finished the discussion on hard parsing. This article will take a look at some of the explain plan content then wrap up with what to look for when you have poorly performing SQL. Up to now, we have been discussing how Oracle processes SQL, the hard and soft parse processes, and how […]

How to Make Sure You’re Using the Correct Oracle PLAN_TABLE

This article will discuss the importance of the PLAN_TABLE and how to make sure you are using the correct one. The PLAN_TABLE, shown below, is used to produce SQL Explain Plans. When SQL is submitted to Oracle and processed (details to follow in other articles), an execution plan is created and used to process the […]