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. For those wishing to continue their learning experience, or experienced Oracle users wishing to learn more about the CBO, 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
- An introduction to and history of the CBO
- What does cost mean?
- History of the CBO
- Inputs to the CBO
- Optimizer Parameters
- Statistics Collection
- System Statistics
- Workload Statistics
- No-Workload Statistics
- Object Statistics
- System Statistics
- Selectivity and Cardinality Calculations
- What is selectivity & cardinality and how are they calculated?
- Calculating selectivity for single table access
- Equality predicates
- Range predicates
- Multiple predicates
- Extended Statistics
- Expression Statistics
- Multicolumn Statistics
- Virtual Columns
- Published vs. Pending Statistics
- The Cost-Based Optimizer
- The Cost of a Full Table Scan
- Using CPU Costing (Workload Statistics)
- Using CPU Costing (No Workload Statistics)
- The Cost of using Indexes (Table Access by Index ROWID)
- Index Range Scan
- Join Methods
- Join selectivity and cardinality
- The cost of a Nested Loop join
- The cost of a Sort Merge join
- The cost of a Hash join
- When would you need a histogram?
- Frequency Histogram
- Height Balanced Histogram
- New 12c Histograms
- Top-Frequency Histogram
- Hybrid Histogram
- Intelligent Cursor-Sharing
- Bind-sensitive Cursors
- Bind-aware Cursors
- Multiple Child Cursors
- SQL Plan Management
- SQL Plan Baselines
- SQL Profiles
- SQL Patches
- 12c Adaptive Optimization
- What is Adaptive Query Optimization
- Adaptive Plans
- Adaptive Join
- Adaptive Parallel Distribution Methods
- Adaptive Statistics
- Statistics Feedback
- Performance Feedback
- SQL Plan Directives
For questions and discussion about the course, please visit our forum.
- The Cost of a Full Table Scan
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*