Performance Monitoring Experts

All training is provided by LightSpeed PM

a Certified Quest Partner

This is a 3-day class (6-hours per day) that includes lecture, lab and testing for up to 12 students.

Attendees will learn coding techniques that insure a consistent response time between instances and releases of the Oracle database.  This course focuses on performance tuning of actual SQL statements via coding style.  The instructor finds that tuning in this fashion maintains the performance of the SQL when upgrading Oracle.

The attendees will work with a variety of SQL statements and tune them by making coding changes based on their review of the explain plans, modifying indexes and using hints and coding style to control the explain plans.  They will also learn how to use tools such as index monitoring, SQL Trace and the PL/SQL profiler.  They will be instructed on how Oracle selects indexes, why they are sometimes not used and how to tell which indexes are being used or not.

Lectures and topics are enhanced with live illustrations and hands-on exercises.

 Topics Covered:

  1. Oracle Architecture from a SQL Performance point-of-view

  2. Understanding SQL Tuning Statement Topics

  3. Reading Explain Plans/Understanding Explain Plans

  4. Controlling both the Cost-based and Rule-based Optimizers

  5. A close look at Indexes – how they work and how they are selected

  6. SQL Tuning via coding style

  7. A review of how Oracle computes row cardinality

  8. Important CBO Statistics review

  9. Oracle Trace Facility – collecting SQL and interpreting using TKProf and TOAD’s Trace Analyzer

  10. Quest SQL Optimizer

  11. Profiling PL/SQL, PL/SQL Coding Tips


  1. Introduction to Explain Plans and Hints.

  2. Introduction to Cost-Based Optimizer.

  3. Understanding Indexes.

  4. Introduction to Tuning Tools:  SQL Tracing & TKProf

  5. Learning SQL Coding Tips.

  6. Learning PL/SQL Coding Tips.

The focus of this course is to give SQL and PL/SQL developers the knowledge and experience so that they can tune their statements and scripts for optimal performance.

Working knowledge of SQL. 

      o   Study Guide with presentations and relevant white papers
      o   Example files of tuning and problem discovery scripts   

Day 1:  SQL Statement Tuning
      o   Oracle RDBMS Architecture overview
      o   Explain Plans explained
            - Understanding
            - Reading
            - Interpreting
      o   Understanding the Cost-Based Optimizer (CBO)
      o   Understanding Parallel Explain Plans            
​      o   Working with Hints
      o   Indexes
            - Review
            - Tips and Techniques

Day 2:  Sub-Queries, Coding Tips, Profiling, Tuning     
      o   Sub-Query Coding Techniques
      o   SQL Coding Tips
      o   WHERE Clause and controlling Cardinality
      o   CBO Statistics

Day 3:  Tuning
      o   Hints Tips and Techniques

      o   Finding Problem SQL
      o   Tuning Tools
            - SQL Tracing
            - TKProf
       o   Profiling and tuning PL/SQL
       o   PL/SQL Coding Tips
            - Tips and Techniques

Oracle SQL PERFORMANCE TUNING Tips and Techniques

Tips and Techniques