SkillBuilders Welcome  |  Log in
All Oracle Classes

Oracle SQL Tuning for Developers and DBA's
3 Day Onsite or 21 Hour Online Hands-On Training

*** ALL NEW FOR 12c ***

Tuning SQL is a science, not an art. In this class you will learn how to discover what the Oracle Cost Based Optimizer is doing with your SQL, and why it is doing it that way. Then learn how to make the CBO do it better. The techniques presented let you reverse engineer the CBO's decisions, experiment with alternatives, and then push the CBO towards better decisions.

This is an intense course;  students should expect to be thoroughly engaged during class.  Students attending the online course should expect some homework.  Students who are fairly new to Oracle Database and SQL should consider our 1-Day SQL Tuning: Core Skills for Beginners class. 

"John is super knowledgable about the subject, and is able to explain the topics very clearly. Great instructor as always."

"John was an excellent teacher and I thought the content of the course was very good, but John's examples and what he showed at the command line was what made it really sink in."


John Watson
Average Student Rating:

Study the SQL execution cycle

The parse-bind-execute-fetch steps

Identify statements that need tuning

The concepts behind cost based optimization

Consider the critical decisions that must be made

Join order

Join method

Access method

Generate and understand query execution plans

EXPLAIN PLAN and SQL*Plus Autotrace

SQL Trace

The DBMS_XPLAN package

Reading query execution plans

Gather statistics

Object, system, and extended statistics

Dynamic sampling and SQL directives

Histograms

Column group statistics

Learn how and when to make use of various index types

B-tree indexes in all their forms

Bitmap indexes

Index use suppression - deliberate or otherwise

Determine when different join methods and access methods are appropriate

Nested loop and hash joins, and other methods

Scan access as opposed to indexed asccess paths

Use hints

Hints as an investigative tool

Using hints in production

Appreciate the possibilities for query re-writes and CBO transformations

Equivalent SQLs

View merging

Star transformations

Understand the possibilities for reoptimization and various adaptive capabilities

Execution plan stability

Adaptive reoptimization and adaptive execution plans

Adaptive cursor sharing and bind variables

The methodology

Calculate optimal join orders and methods

Recognize common programmer errors

Validate statistics

 
SkillBuilders Sample Video