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
Technical Requirements
Nothing is required for labs (workshops). SkillBuilders will provide everything you need for all hands-on labs. Modest PC or Mac specs are required to connect to the online class. Detailed specs can be found here.
Also, we strongly recommend using two monitors for this class.
“So far, it’s the best formal training I have had as a DBA. Thank you, Rachel F. OCP, OCE DBA”
“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.”
Course Features
- Lecture 0
- Quiz 0
- Duration 5 Half Days (~20 hours)
- Skill level All levels
- Language English
- Students 70
- Assessments Yes
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
