Oracle SQL Tuning for Developers and DBAs
Duration: 3 Days

John Watson
,

 4.8/5 (18)
$2,700.00

Online or on-premise hands-on Oracle SQL Tuning classes with expert John Watson, one of the world’s most experienced Oracle Database “tuners”!

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.

The current release of the databases is used for exercises and demonstrations, and the content back-ported as necessary for previous releases. 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.

You can start today with John’s free tutorial How to Read an Execution Plan

Objectives

  • Understand the concepts behind cost based optimization
  • Capture and read execution plans
  • Gather various types of statistics
  • Tune the basic operations: join order, join method, access method
  • Use various index types
  • Exploit the possibilities for SQL transformations and rewrites
  • Apply a SQL tuning method

 

Pre-requisites

  • Understanding of Oracle database and instance architecture
  • Familiarity with SQL and basic PL/SQL
  • Able to work with Linux command line tools

 

“So far, it’s the best formal training I have had as a DBA. Thank you, Rachel F. OCP, OCE DBA”

Thank you,
Rachel Fritts OCP, OCE

“John [Watson] 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.”

  • Call 1-401-783-6172 for Group or Alumni Discounts.
    If you are a student of an accredited academic institution call us for a 25% discount.

    • Price Qty
    02/22 - 02/26show details + $2,700.00 (USD)  

    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
    • The Filtered Rows Percentage tuning methodology
    Capture and understand query execution plans
    • EXPLAIN PLAN and SQL*Plus Autotrace
    • SQL Trace
    • Te DBMS_MONITOR package
    • 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 and bitmap join 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 paths as opposed to indexed access paths
    • Direct and indirect read
    Use hints
    • Hints as an investigative tool
    • Using hints in production
    • Commonly used (and abused) hints
    Appreciate the possibilities for query re-writes and CBO transformations
    • Equivalent SQLs
    • View merging
    • Star transformations
    • Correlated sub-queries
    • Set operators
    • Implications of null values
    The methodology
    • Calculate optimal join orders and methods
    • Look for common programmer errors
    • Recognize problematic SQL structures and operations
    •  Validate statistics
    John Watson
    https://skillbuilders.com/our_team/john-watson/
     5/5

    great materials and better understanding of SQL tuning

    need to increase the hours or days for the course

    No the instructor did a great job
    David Esuka
    September 25, 2020
     5/5

    Lots of detailed discussions. John took the time to really explain all questions that came up. Very detailed and always willing to go way beyond the scope of the course. Always a treat to have John as an instructor.

    Can\'t think of anything to do differently

    Online format worked very well. Especially since it was a small class (4 folks).
    Chris Ermlich
    September 25, 2020
     4.4/5

    John is always a pro and we look forward to hearing from him.

    Everything was fine in this regard.
    Jeremy Schleicher
    September 25, 2020
     4.8/5

    As a \'core\' DBA (with a non-development background), this was undoubtedly the most challenging Skillbuilders\' course I\'ve attended. A slightly slower pace may have suited me better but, as usual, John was able to answer all my questions.

    John\'s practical exercises are an excellent way to consolidate the knowledge gained on the course. Perhaps it would be even more helpful to have an explanation / a reminder of why a particular solution was appropriate/necessary.

    No. It\'s a tried and tested method which works very well for me. Thanks again for an excellent course!
    Jes Challand
    September 25, 2020
     5/5

    The course was very practical and I\'ve already used what I learned in my job. John is very knowledgeable and presented the complex material well. The labs reinforced the material presented.

    The information to set up access to the Linux server for the labs should have been sent in advance so as not to waste time in class.

    No, the materials were delivered fine.
    Dennis Malkowski
    October 21, 2019
     5/5

    John was very knowledgable and was able to easily communicate complex topics.


    Lisa Plausse
    October 21, 2019
     5/5

    John\'s ability to take complex topics and make them easy to understand is fantastic.


    Ross Evans
    October 18, 2019
     4.7/5



    Maria Finch
    June 8, 2018
     4.7/5

    Hands on labs. Helped me learned the subject.

    Could have used another day or at last half day.

    Kelly Cousins
    June 8, 2018
     4.7/5

    John's knowledge on the topics was amazing.

    None

    Could have been a little more interactive.
    Vijay Patel
    June 8, 2018
     4.6/5



    Kevin Sneel
    June 8, 2018
     4.9/5

    Lab work re-enforced concepts taught.

    Course notes could have had more details.

    Joe Saunders
    June 8, 2018
     4.6/5

    Really like the real world examples that John referenced during the class.

    Lot of material to cover in two and a half days.

    Gene Castro
    February 9, 2018
     4.5/5



    JP Peterson
    February 9, 2018
     4.8/5

    Half day sessions. Would have been a lot of information for a full day class.


    Vipul Nair
    February 9, 2018
     4.7/5

    John's knowledge of Tuning.


    Jim Stegar
    February 9, 2018
     5/5



    Peter Jones
    February 9, 2018
     5/5
    Arien Lee
    February 29, 2016
    $2,700.00

    You May Like

    Copyright © Skillbuilders.com