Advanced Optimizer Concepts and Performance

Synopsis

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 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 in detail how costs are assigned to different access paths such as full table scans, index access paths, table joins, sorting operations and hashing operations. A strong emphasis will be placed on understanding and working through the costing model. 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.

During the class, as we work through the different access paths, we'll discuss measuring the runtime efficiency, and how to enhance performance.

Syllabus

This course is organized into the following segments:

  • Welcome and Introductions
  • An introduction to and history of the CBO
    • What does cost mean?
    • Why does the CBO make mistake?
    • History of the CBO
  • Inputs to the CBO
    • Optimizer Parameters
    • Statistics Collection
      • Object Statistics
      • System Statistics
        • Workload Statistics
        • No-Workload Statistics
    • Selectivity and Cardinality Calculations
      • What is selectivity & cardinality and how are they calculated?
      • Calculating selectivity for for single table access
        • Equality predicates
        • Range predicates
        • Multiple predictes
        • Histograms
          • When would you need a histogram?
          • Frequency Histogram
          • Height Balanced Histogram
    • Access Paths and Joins methods
      • The Cost of a Full Table Scan
        • Using I/O costing
        • Using CPU Costing (Workload Statistics)
        • Using CPU Costing (No-Workload Statistics)
      • The Cost of using Indexes (Table Access by Index ROWID)
        • Index Unique Scan
        • Index Range Scan
        • Index Skip Scan
        • Index Full Scan
        • Index Fast Full 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
      • Conclusion
        Course wrap up and next steps

      Target Audience

      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.

      Every section and lab exercise in this course will make use of the 10053 CBO trace file to show you how the optimizer works, how it thinks, how it calculates "cost" and how it makes decisions. Other trace files will be used throughout the course. For example, the 10032 and 10033 trace files will be used to determine sort performance. Hash join performance will be analyzed by looking at the 10104 trace file.

      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.

      Prerequisites

      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.

      Instructional Format

      The three-day course is approximately 60% instructor-led lecture and discussion, 40% in-class exercises. The course is conducted in English.

      Materials

      Each student receives the following materials:

      • Course Notes
        A printed copy of all the presentation slides used in the course.
      • Tools
        Access to course software tools and supplemental documentation at hotsos.com.

      Class Forum

      For questions and discussion about the course, please visit our forum.

      Pricing

      • List Price: $1875.00
      • Alumni Price: $500.00*
        *Alumni pricing is available to prior attendees of this course.

      Schedule

      Please visit our Calendar of Events to find when this course will be offered next.