Advanced Optimizer Concepts and Performance

Register Now!Click REGISTER NOW to see upcoming Advanced Optimizer Concepts and Performance classes in our calendar of events.

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 in 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.

Objective

During this class we'll examine 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.

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.

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.

Syllabus

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 mistakes?
    • History of the CBO
    • The 10053 Trace
  • Inputs to the CBO
    • Optimizer Parameters
      • Obsolete Parameters
    • Memory Management
      • ASMM
      • PGA
      • AMM
    • Virtual Columns
    • Statistics Collection
      • System Statistics
        • Workload Statistics
        • No-Workload Statistics
      • Object Statistics
      • Global Prefs
      • Extended Statistics
      • Expression Statistics
      • Published vs. Pending Statistics
    • Histograms
      • Access Path Calculations
      • Table Access Full
        • CPU Costing Model
      • Table Access by Index Rowid
        • Index Costing
      • Intelligent Cursor Sharing
      • Binds and Parsing
      • Cursor Sharing
      • How does Intelligent Cursor Sharing Work
      • V$ Views
    • The CBO's Look at Partitions
      • What are partitions
      • Why use Partitions
      • Table Partitions
      • Index Partitions
      • Composite Partitioning
      • Partitioning Extensions
      • Single-Level Partitioning
        • Range Partitioning
        • Hash Partitioning
        • List Partitioning
    • The CBO's Look at Clusters
      • What are Clusters
      • Why use Clusters
      • Table (Index) Clusters
      • Hash Clusters
    • The CBO's Look at Index-Organized Tables (IOTs)
      • Syntax
      • Secondary Indexes
        • The "Guess"
        • Stale Indexes
      • Partitioning IOTs

      Class Forum

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

      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.

      Pricing

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

      Schedule

      Register Now!Click REGISTER NOW to see upcoming Advanced Optimizer Concepts and Performance classes in our calendar of events.