Personal tools
You are here: Home Education Performance Optimization for Developers for SQL - 2 Days
Document Actions

Performance Optimization for Developers for SQL - 2 Days

Hotsos has created this Performance Optimization for Developers course just for you.  If developers don't need it, it's not in this class!  The content is focused just on what's relevant and nothing more.  This course focuses on the following:

  • Write high-performance code that scales as your application grows
  • Learn to gather a complete accounting of your unit test run-time
  • Understand Oracle access paths, execution plans, and how the Cost-Based Optimizer makes these choices
  • Get the Hotsos SQL Test Harness for evaluating competitive execution options

What You Will Learn:

  • What is a Logical IO, and why is it important?
  • SQL statement execution fundamentals
  • Tools for SQL statement problem diagnosis and repair
  • Access methods
  • Coding techniques that may prevent index use
  • Join methods
  • Understanding complex Explain Plans
  • What are things that I can do to write better SQL?

Synopsis:

Upon completion of the course, students will have the knowledge and skills to:

  • Understand the internals of Oracle from a performance perspective,
  • Methodically interpret the execution plan of an SQL statement,
  • Measure the efficiency of their SQL or PL/SQL code,
  • Determine exactly what is contributing to the performance issues with their code,
  • Develop the optimal execution plan for their SQL statement, and
  • Replace "worst" with "best" practices in their code.

This course is designed to teach application developers the essentials of optimizing their SQL code. The students will be taught what the primary and secondary indicators of performance are and how to measure the efficiency of their code. To ensure an understanding of why these indicators are important, students will gain insight on how the Oracle database works internally. Strong emphasis will be placed on understanding the different access paths and join methods used by the Cost-Based Optimizer (CBO) and when it's advantageous to use a particular access path or join method. Throughout the course, students will learn how to interpret complex execution plans rendered by the CBO. Many different scenarios will be discussed and coding techniques for each scenario compared and contrasted.

PL/SQL optimization techniques are an additional optional day. Topics that will be covered include: efficient code logic ordering, implicit vs. explicit cursors, datatype issues, bind variables, cursor sharing, collections, bulk operations, REF CURSOR types and cursor variables, table functions, pipeline functions and more.

Target Audience

This course is designed for the application developers wishing to deepen their knowledge and skill level for optimizing SQL code.

Prerequisites

Students are expected to have a basic working knowledge of Oracle, Oracle SQL, Oracle PL/SQL and some experience as an application developer.

Syllabus:

The course is organized into the following segments:

  1. Introduction
    • Course welcome, instructor and student introductions
    • "False Truths"
  2. Logical LIOs, What are they and why are they important?
    • Understanding and measuring statement resource consumption
    • Fundamentals of Database Architecture
      • Shared Pool
      • Buffer Cache
    • Fundamentals of Statement Execution
      • PARSE, EXECUTE, BIND, FETCH, arraysize issues, etc.
      • Identical Statements
      • Stages of SQL Processing
    • Terms and definitions:
      • LIO, PIO, latching, buffer cache, library cache, etc.
  3. Tools of the Trade
    • Tools for SQL statement problem diagnosis and repair:
      • Statistics, data distribution, and execution plans
      • Extended SQL trace data (10046) and optimizer trace data (10053)
    • The Hotsos SQL Test Harness
      • How to install and use to measure and store test results
  4. DBMS_APPLICATION_INFO
    • Client_Info
    • Client_ID
    • What are modules and actions?
    • Tracing with MODULE and ACTION
    • SET_SESSION_LONGOPS
    • TRACEFILE_IDENTIFIER
  5. Access and Join Methods
    • Compare and contrast access methods:
      • TABLE ACCESS FULL, INDEX ACCESS BY ROWID
    • Index scan type overview:
      • Index unique scan, index skip scan, index fast scan, index full scan, index joins
      • Batched access (12c)
    • Compare and contrast join methods:
      • Nested loop, hash join, sort/merge join, and Cartesian
  6. Understanding Execution Plans
    • Reading Execution Plans
      • Explain plan vs Execution plan
      • Capturing and displaying execution plans
    • Analyzing Execution Plans
      • Plan Table, V$SQL_PLAN, V$SQL_PLAN_STATISTICS, etc.
    • Sub-Plans
  7. Writing SQL "right"
    • Predicate order and filter application
    • INLIST ITERATORs
    • SEMI and ANTI joins
    • Scalar subqueries
    • Analytic functions and SQL modeling

Class Forum

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

Instructional Format

This two-day course is approximately 60% lecture and 40% instructor-led demonstrations to ensure a concrete understanding of the course content.  The course is conducted in English.

Materials

Each student receives the following materials:

  • A PDF copy of the presentation is provided prior to the beginning of training. Please download this prior to start of training.
  • Tools, which allows access to download the Hotsos SQL Test Harness.  This tool is used during the class to measure and compare the performance aspects of SQL code.

Pricing

  • List Price: $1250.00
  • Alumni Price: $400.00*
  • * Alumni pricing is available to prior attendees of the POD course.

Schedule

There are no upcoming schedules for this event

Powered by Plone, the Open Source Content Management System

This site conforms to the following standards: