Personal tools
You are here: Home Education Advanced Optimizer Concepts and Performance
Document Actions

Advanced Optimizer Concepts and Performance

Objective

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.

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.

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

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 mistake?

        History of the CBO

        The 10053 Trace       

    Inputs to the CBO

        Optimizer Parameters

                Obsolete Paramters

        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 Partioning

                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 PDF copy of the presentation is provided prior to the beginning of training. Please download this prior to start of training.
  • 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 the OACP course.

Schedule

Location Starts Ends Instructors Register
Online [Map] 2014-06-10 2014-06-12

Powered by Plone, the Open Source Content Management System

This site conforms to the following standards: