Oracle SQL Tuning for Developers and DBAs
Duration: 3 Days
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”
“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.”
- The parse-bind-execute-fetch steps
- Identify statements that need tuning
- The concepts behind cost based optimization
- Join order
- Join method
- Access method
- The Filtered Rows Percentage tuning methodology
- EXPLAIN PLAN and SQL*Plus Autotrace
- SQL Trace
- Te DBMS_MONITOR package
- The DBMS_XPLAN package
- Reading query execution plans
- Object, system, and extended statistics
- Dynamic sampling and SQL directives
- Histograms
- Column group statistics
- B-tree indexes in all their forms
- Bitmap and bitmap join indexes
- Index use suppression - deliberate or otherwise
- Nested loop and hash joins, and other methods
- Scan access paths as opposed to indexed access paths
- Direct and indirect read
- Hints as an investigative tool
- Using hints in production
- Commonly used (and abused) hints
- Equivalent SQLs
- View merging
- Star transformations
- Correlated sub-queries
- Set operators
- Implications of null values
- Execution plan stability
- Adaptive reoptimization and adaptive execution plans
- Adaptive cursor sharing and bind variables
- Calculate optimal join orders and methods
- Look for common programmer errors
- Recognize problematic SQL structures and operations
- Validate statistics
