Advanced Optimizer Concepts and Performance
During this class we’ll exam in detail how costs are assigned to different access paths such as full table scans and index access paths. A strong emphasis will be placed on understanding and working through the costing model. Additionally, the 10053 optimizer trace file will be used extensively so students can see how and why the Oracle cost-based optimizer assigned cost to the access paths it considered.
This course is designed for application developers and database administrators who want to expand their knowledge of the Cost Based Optimizer (CBO) and improve their optimization skills. Optimal execution plans are required for the fast performance you desire. Understanding how the CBO calculates "cost" is paramount in troubleshooting performance problems. Additionally, knowing how the CBO works, how it thinks, and how it arrives at the decisions it makes, will make your task of writing great performing SQL easy.
The course is most effective for students who are strongly motivated to increase their knowledge of the CBO and learn how to apply this knowledge to performance optimization.
Students are expected to have a basic working knowledge of Oracle, Oracle SQL, Oracle PL/SQL and the CBO. Attendance of Optimizing Oracle SQL, Intensive is desired.
A thorough understanding of the Oracle Cost-Based Optimizer (CBO) is essential to consistently optimize SQL in a timely manner. Our Optimizing Oracle SQL, Intensive class teaches students a repeatable and reliable systematic method for optimizing SQL, and the underlying knowledge of that methodology is an understanding of the CBO. However, it's impossible to pack a lifetime of information and learning into 5 days. Therefore we've developed Advanced Optimizer Concepts and Performance as the next step in learning more about the CBO.
The costing model of the Cost Based Optimizer (CBO) has changed over the years, and there's no doubt that it will continue to grow with future releases of Oracle.
Early releases of the CBO assigned costs to steps execution plans based on the number of blocks to be read. Additionally, those early releases of the CBO assumed that all block reads would result in physical I/O requests, but didn't account for size or duration of those requests, or the CPU required to service those requests.
In more recent releases of the CBO, the costing model has changed. As in the past, it still considers the number of blocks to be read, but it also factors into the cost equation, the size and duration of I/O requests, as well as the CPU required to perform those operations.
Regardless of the release of the CBO you use, you've probably asked one or more of these questions or some variation of them:
- "What does that cost number mean?"
- "How did the CBO calculate that cost?"
- "Does a lower cost plan ensure better run time performance?"
- "I see a great access path. Why doesn't the CBO use it?"
During this class, we'll examine how costs are assigned to full table scans and index access paths. Additionally, the 10053 optimizer trace file and other trace events will be used extensively so students can see how and why the optimizer assigned cost to the access paths it considered.
When someone says, "That SQL statement costs a lot." Are they talking about the cost of the execution plan generated by the optimizer or the cost of the actual resources consumed during run time? It would be excellent if there was a simple, consistent and easy to understand relationship between the two. Unfortunately, there isn't always.
This course is organized into the following segments:
Welcome and Introductions
Review of Harness Tools
An introduction to and history of the CBO
What does cost mean?
Why does the CBO make mistake?
History of the CBO
The 10053 Trace
Inputs to the CBO
Published vs. Pending Statistics
Access Path Calculations
Table Access Full
CPU Costing Model
Table Access by Index Rowid
Intelligent Cursor Sharing
Binds and Parsing
How does Intelligent Cursor Sharing Work
The CBO's Look at Partitions
What are Partitions
Why use Partitions
The CBO's Look at Clusters
What are Clusters
Why use Clusters
Table (Index) Clusters
The CBO's Look at Index-Organized Tables (IOTs)
For questions and discussion about the course, please visit our forum.
The three-day course is approximately 60% instructor-led lecture and discussion, 40% in-class exercises. The course is conducted in English.
Each student receives the following materials:
- Course Notes
- A PDF copy of the presentation is provided prior to the beginning of training. Please download this prior to start of training.
Access to course software tools and supplemental documentation at hotsos.com.
- List Price: $1875.00
- Alumni Price: $500.00*