All plan generator plans in Oracle

user_6215774 0
Viewed 123

While reading Oracle SQL tuning guide I came across such a quote (you can find it in this chapter):

The plan generator explores various plans for a query block by trying out different access paths, join methods, and join orders.

Many plans are possible because of the various combinations that the database can use to produce the same result. The optimizer picks the plan with the lowest cost.

Figure 4-2 Optimizer Components

Figure 4-2 Optimizer Components

Is it possible somehow to get all these plans that were generated by plan generator?

Maybe there are standard PL/SQL packages for this or system views?

optimization oracle sql
1 Answers

Typically you do not need to see all possible plans, but in some cases there is an interesting question, why does Oracle discard some plan and use an alternative one instead.

To answer this the ideal entry is the 10053 trace produced by Oracle.

There is a classical paper on this thema from Wolfgang Breitling which can be found here.

Also Jonathan Lewis in his Cost-Base Oracle Fundamentals dedicated a chapter 14 The 10053 Trace to this thema.

Google will for sure provide other references.

A word of warning. The 10053 is not the simplest stuff in Oracle context. Also in each release you may expect changes in the output. A good starting point to learn is to investigate a query that produces a different execution plan in two different environments. Simple take the 10053 trace in both databases and make a file diff and observe the difference. You will see, if there is a difference in the optimizer parameters, object or system statistics and how those changes have influence on the selection of the final execution plan.

user_48081220
Related