Cost-Based Oracle Fundamentals (Expert's Voice in Oracle)

Category: Programming
Author: Jonathan Lewis
All Stack Overflow 7
This Month Stack Overflow 1


by anonymous   2019-01-13

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.

by anonymous   2017-08-20

For Oracle, your best resource would be Cost Based oracle Fundamentals. It's about 500 pages (and billed as Volume 1 but there haven't been any followups yet).

For a (very) simple full-table scan, progress can sometimes be monitored through v$session_longops. Oracle knows how many blocks it has to scan, how many blocks it has scanned, how many it has to go, and reports on progress.

Indexes are a different matter. If I search for records for a client 'Frank', and use the index, the database will make a guess at how many 'Frank' entries are in the table, but that guess can be massively off. It may be that you have 1000 'Frankenstein' and just 1 'Frank' or vice versa.

It gets even more complicated as you add in other filter and access predicates (eg where multiple indexes can be chosen), and makes another leap as you include table joins. And thats without getting into the complex stuff about remote databases, domain indexes like Oracle Text and Locator.

In short, it is very complicated. It is stuff that can be useful to know if you are responsible for tuning a large application. Even for basic development you need to have some grounding in how the database can physically retrieve that data you are interested.

But I'd say you are going the wrong way here. The point of an RDBMS is to abstract the details so that, for the most part, they just happen. Oracle employs smart people to write query transformation stuff into the optimizer so us developers can move away from 'syntax fiddling' to get the best plans (not totally, but it is getting better).

by anonymous   2017-08-20

Jonathan Lewis Cost-Based Oracle Fundamentals is the best book on the market if you want to learn how Oracle "thinks" when selecting access paths, join mechanism, join order etcetera.

It takes a few readings to get the hang of it, but once it "clicks" you have a ridiculusly powerful tool in your box, because not only can you troubleshoot much better, but you start to design tables and indexes in a way that play to the strengths of the database.

by anonymous   2017-08-20

The Performance Tuning Guide is a great place to start but Jonathan Lewis's Cost Based Oracle Fundamentals is the canonical reference on what the optimizer is doing and why. Depending on the complexity of the problem, CBO Fundamentals may be radical overkill, though.

As a first pass, if you look at the estimated cardinality of each step in the plan, it's useful to ask whether that cardinality is reasonable. If the cardinalities are reasonable, it's likely that Oracle is picking the most efficient plan and you need to look for other approaches to tuning the query. If the cardinalities are wildly incorrect, on the other hand, it's likely that Oracle has chosen a poorly performing query plan and that something about the statistics needs to be adjusted.