Synopsis

This course is intended to teach students a repeatable and reliable method for optimizing SQL and to assimilate the new habit of testing optimization hypotheses rapidly during the performance improvement process. Instead of relying on “rules of thumb” and hit-and-miss guessing, this course helps the student understand the inner workings of the Oracle optimizer and how SQL execution paths are determined. Particular emphasis is placed on how to create and read the 10046 and 10053 trace files. This allows the students to know exactly what happened when the SQL ran (10046) and how the optimizer developed the plan it used (10053). The student will also learn how to capture and read the execution plan. The student's attention is focused on a thorough understanding of exactly how the Oracle Database works.

The course is not just about learning how to write SQL. The course is about arming the student with the skills to be able to scientifically determine the efficiency of any SQL statement. While the course does offer alternative ways to write SQL in many cases, the primary objective is to equip the student to determine whether a statement is optimal and to achieve a deeper understanding of the cost-based optimizer. This course presents problem-solving strategies that can be carried forward directly to common problems and customized for solving less common ones. The result is a fortified foundation for optimizing SQL in ever more challenging situations.

Objective

This class is a 5-day intensive combination of material that would typically be covered in 7 days. Focus is directed towards the optimizer and understanding how it behaves when developing query execution plans. The importance of optimizing SQL at the resource usage level is learned and the Hotsos SQL Test Harness and Tools Pack scripts are used to facilitate the comparison of multiple approaches to writing SQL statements to discover the “performance optimal” choice among several approaches. Different approaches to writing high performance SQL are demonstrated as well as review of database design elements that can affect performance.

Target Audience

The course is designed for application developers and database administrators who want to respond faster and more permanently to Oracle system performance problems that are known to be caused by inefficient application SQL code. Whether you are wanting to learn how to write new code that performs optimally from the start or are looking to update and maintain previously written code, this course teaches you the skills you need. We take you 'under the covers' to see how and why your SQL performs as it does and lead you to understand how to most effectively provide the Oracle optimizer with the information it needs to produce optimal execution plans.

Simply put, if you write SQL, this class is for you!

The course is most effective for students who will be motivated to solve real Oracle system performance problems immediately upon returning to work after the course.

Prerequisites

Students are expected to have a working knowledge of Oracle SQL through experience either as a database administrator or application developer.

Syllabus

This course is organized into the following segments:

1. Introduction
   
   • Course welcome, instructor and student introductions
   • "False Truths"
   • Classroom Environment
2. Fundamentals
   
   • Fundamentals of Testing
     
     • Understanding and measuring statement resource consumption
     • Performance Is The Study Of Time
     • Scalability Threats
   • Fundamentals of Database Architecture
     
     • Shared Pool
     • Buffer Cache
     • Latching
   • Fundamentals of Statement Execution
     
     • PARSE, EXECUTE, BIND, FETCH, arraysize issues, etc.
     • Identical Statements
     • Stages of SQL Processing
     • Response Time and Resource Consumption
   • 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 report (including table and index descriptions)
     • Block- and row-selectivity report
     • Extended SQL trace (Event 10046) data
     • A good profiler (TKPROF)
   • The Hotsos SQL Test Harness
     
     • How to install and use to measure and store test results
4. The Optimizer
   
   • Oracle query optimizer technologies
     
     • Rule Based (RBO)
     • Cost Based (CBO)
   • Adaptive Query Optimization (12c)
     
     • Adaptive Plans
     • Adaptive Statistics
5. Hints and Selectivity
   
   • Optimizer hints
   • Statistics collection
     
     • DBMS_STATS
     • CLUSTERING_FACTOR
     • System Statistics
     • Selectivity Estimates
     • Extended Stats
     • Block And Row Selectivity
     • Data Skew
   • Histograms
     
     • Frequency, Height Based, Top Frequency and Hybrid
     • Histogram and bind variables
     • Adaptive Cursor Sharing
6. 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
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.
   • Query Transformation
     
     • View merging
     • Subquery unnesting
     • Predicate pushing
8. Indexes and Performance
   
   • Coding techniques that may prevent index use:
     
     • Column expressions
     • Implicit datatype conversion
   • Indexing null values
   • Impact of composite key order
   • Invisible indexes
   • Function-based indexes
   • Bitmap indexes
9. Getting with it
   
   • Defining Subquery Factoring
   • The MATERIALIZE hint
   • Recursive Subquery Factoring
   • WITH functions
10. Writing SQL "right"
    
    • Analytic functions and SQL modeling
    • Predicate order and filter application
    • SEMI and ANTI joins
    • Scalar subqueries
    • INLIST ITERATORs

Class Forum

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

Instructional Format

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

The instructors place a special emphasis on teaching students to think and test for themselves instead of relying on widely believed but unfortunately unreliable rules of thumb.

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 down load this prior to start of training.
• Tools
  Access to course software tools and supplemental documentation at hotsos.com.