Oracle Performance Optimization for Developers

Register here for the three-day course or register here for the three-day course and Indexing in Oracle bundle here (same price)

Synopsis

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

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 (and, optionally, 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 three-day SQL and PL/SQL-focused 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.

Target Audience

This course is designed for application developers wishing to deepen their knowledge and skill level for optimizing SQL and/or PL/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. Reading Plans
    • Simple plans
    • Parent Child relationship
    • Which step is first?
    • Full table scans
    • Index scans
  4. Tools of the Trade
    • Tools for SQL statement problem diagnosis and repair:
      • Statistics and data distribution
      • Explain plan vs Execution plan
      • Extended SQL trace data (10046)
    • The Hotsos SQL Test Harness
      • How to install and use to measure and store test results
  5. DBMS_APPLICATION_INFO
    • Client_Info
    • Client_ID
    • What are modules and actions?
    • Tracing with MODULE and ACTION
    • SET_SESSION_LONGOPS
    • TRACEFILE_IDENTIFIER
  6. Access and Join Methods
    • Compare and contrast access methods:
      • TABLE ACCESS FULL, INDEX ACCESS BY ROWID
    • Index scan type overview:
      • What is a ROWID?
      • Batched access (12c)
      • Index unique scan, index skip scan, index fast scan, index full scan, index joins
    • Compare and contrast join methods:
      • Nested loop, hash join, sort/merge join, and Cartesian
  7. 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
  8. Writing SQL „right“
    • Analytic functions and SQL modeling
    • Predicate order and filter application
    • SEMI and ANTI joins
    • Scalar subqueries
  9. Optimizing PL/SQL
    • DBMS_PROFILER
    • DBMS_HPROF
    • Function Result Cache
    • Code Considerations
      • Code logic order
      • Datatype conversions
      • Bind variables
    • Cursors
      • Implicit
      • Explicit
      • Ref
    • Collections
    • Bulk operations
    • Table functions
    • Subprogram inlinging
    • PLSQL_OPTIMIZE_LEVEL
    • Native Execution

Class Forum

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

Instructional Format

This 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:

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

About the speaker

Ric Van Dyke is an Oracle Ace with over 30 years of experience in the IT industry. Coming from a 3GL programing back ground he started working with Oracle Version 6 as a developer with Forms 2.3 and became a DBA at Ford Motor Credit. He then worked at a variety of companies as an independent consultant. Ric worked for Oracle Corporation for 10 years, starting as a core database instructor where he taught and developed several courses. He became the Education Manager of the North Central Region. Ric then served in the Advanced Technologies Services consulting group as a Technical Manager where he worked with several clients doing RAC installs and performance engagements. Ric is currently the Education Director at Hotsos where he coordinates training activities. He also teaches both in-person and on-line training. Ric frequently speaks at user group meetings and gives on-line webinars.

When is it planned

October 23rd – 25th 2017.
Time 12.30 – 20.00

Venue

The exact location will be announced later but we can tell you that the event will take place in Reykjavík, Iceland.

Price

The price for the three-day course is ISK 269.500

This price includes the fee for the course, refreshments during breaks as well as the course material.

Email us at info@miracle.is for more information.
Register here for the three-day course or register here for the three-day course and Indexing in Oracle bundle here (same price)