Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

   

Advanced Oracle SQL Tuning Training Course

© 2007-2016 by Burleson Corporation

This course is taught on the BC Oracle cruise.  

January 29 - February 5, 2017

This course is taught as a 3-day class.

The training class cost is $1495.00 per student.

The cruise cabin cost varies depending on the cabin level you choose. Cabin prices start at $820.00 per passenger for an inside cabin.

 

 

 Book your class seat  Buy a seat in the Oracle Training Class at Sea 

 

Key Features

* See how initialization parameters affect SQL optimization.

* Understand how to read an execution plan.

* Learn the importance of schema statistics.

* Learn how to apply "global" changes to tune many SQL statements at once.

* See how to locate missing indexes.

* See how to use the library cache to find SQL tuning opportunities.

Understand how to prioritize your SQL tuning efforts.

* Learn how external system statistics improve SQL execution.

* See how to evaluate and test SQL execution speed.

* Learn the internal table join methods.

* Learn to detect unnecessary large-table full-table scans.

* See how to quickly identify sub-optimal SQL.

* Use hints to change SQL execution plans.

* Tune SQL with stored outlines.

* See how the SQLTuning advisor and SQLAccess advisor recommends SQL tuning opportunities.

* Use the v$ internal views for Oracle SQL tuning.

  

Course Description

Advanced Oracle SQL tuning is an intensive three-day course designed to provide Oracle professionals with an in-depth understanding of Oracle SQL tuning and how to formulate and tune SQL statements for optimal performance. Burleson Corporation instructors offer decades of real world DBA experience in Oracle features, and they will share their secrets in this intense Oracle SQL tuning training.

Book Required

  Advanced Oracle SQL Tuning  The Definitive Reference

Donald K. Burleson,
Rampant Tech Press

ISBN-13 978-0-9823061-5-4

Optional SQL Tuning book:

  Oracle Tuning: The Definitive Reference

Donald K. Burleson,
Rampant Tech Press

ISBN 0-9744486-2-1  

Audience

This course is designed for practicing Oracle professionals, Oracle developers and Systems Administrators professionals who have some basic experience with SQL statements. Prior experience with Oracle is not required, but experience using SQL with a relational database is highly desirable.

The goal of the SQL tuning class is to provide a comprehensive toolkit to allow the DBA to quickly locate and tune a database workload.

Curriculum Design

This course was designed by Donald K. Burleson, an acknowledged leader in Oracle database administration.  Author of more than 20 database books, Burleson was chosen by Oracle Press to write five authorized editions, including Oracle High-Performance SQL tuning.  Burleson Corporation instructors offer decades of real world DBA experience in Oracle features, and they will share their secrets in this intense Oracle SQL tuning training.

Learning Objectives

By the end of this course the student will be able to tune advanced SQL queries including correlated subqueries and outer joins. The student will also become familiar with all of the major SQL tuning techniques for Oracle, including global parameter and statistics changes, adding missing indexes and adjusting optimizer statistics.  The student will also see the internals of the Oracle optimizers, and see proven techniques for tuning Oracle SQL statements for optimal performance.

 

Book your class seat  Buy a seat in the Oracle Training Class at Sea  



Advanced Oracle SQL Tuning
Three Day Syllabus

© 2016 by Donald K. Burleson

Note: You choose the specific SQL tuning topics based on your specific tuning requirements.

Day 1: Introduction to SQL Optimization

1-1 – Introduction to SQL Tuning

Intro to the class
History of SQL
Evolution of SQL
Exercise - declarative SQL

1-2 - Internal processing of SQL statements

Parsing SQL Syntax
SQL Semantic Analysis
Generating the execution plan
Using optimizer plan stability
Using the v$sql view 
Using the v$sql_plan view
Exercise – Query the library cache

1-3 – Optimizer Statistics

Purpose of statistics
Types of statistics (table, column, system)
Histogram statistics
Dynamic sampling
using dbms_stats
Exporting/importing statistics
Statistics management
Exercise – gather system stats

1-4 – Optimizer modes and goals

Management issues with system-wide optimization
Different modes of SQL optimization
Bi-modal databases
Rule-based optimization
Cost-based optimization
All rows optimization
First_rows optimization
Exercise – display and change optimizer_mode
1-5 - Table joining internals
Sort-merge joins
Nested Loop joins
Hash joins
STAR joins
Bitmap joins
Exercise – Change table join techniques & evaluate performance
1-6 – SQL Tuning and full-table scans
Basics of file I/O
Sequential reads vs. scattered reads
When full scans are best
RAM caching in the SGA
Automating table caching
Solid State Disks
Tracking full-scans over time with AWR
Exercise – Query v$sql
 
DAY 2 – Execution plan internals
 
2-1 –Oracle parallel query and parallel DML

Parallel and SMP processing
Parallel query optimal degree
Parallel query management (system, session, statement)
Parallel DML
Parallel parallelism
Exercise: Run a parallel query

2-2 – Exposing execution  plans

Evaluating Large-table, full-table scans
Index Usage Analysis
Reports on system-wide SQL execution
Exercise – run autotrace options
2-3 - Altering SQL execution plans
Using hints
Changing the system-wide optimizer mode
Changing optimizer mode for specific statements
Re-writing SQL queries 
Table join order evaluation
Using the ordered hint
Exercise – Optimizer costing models

2_4 – Tuning SQL with hints

Optimizer directives
Scope of hints (session-level, statement level)
Broad hints (optimizer mode) vs. narrow hints
Good Hints vs. bad hints
Using the ORDERED and LEADING HINTS
Forcing index usage
Using hints in subqueries
Exercise – change an execution  plan with a  hint

2_5 - Oracle Index Optimization

B-Tree indexes 
Bitmap Indexes 
Function-based Indexes
Clustered indexes
Index-only tables
Exercise – Create a function-based index
 
2_6 – Tuning Oracle sorting

When a sort is invoked (order by, group by, etc.)
Sorting with indexes vs internal sorting
Detecting disk sorts
Sorted Hash Clusters
Exercise: Force two sort methods

 

 

 

DAY 3 – Advanced SQL Tuning Topics

3_1 – Monitoring SQL Performance

Measuring end-to-end response time
Measuring SQL throughput
Using v$session_longops
Optional Exercise – Run plan9i.sql

3_2 – Oracle DML Tuning         

DML Tuning is not for neophytes            
Oracle DML tuning          
Optimizing Oracle SQL insert performance          
High Impact insert Tuning Techniques   
Tips for batch inserts     
Low-impact insert techniques (% and % faster) 
Tuning insert speed with the nologging option  
Reverse key indexes and insert performance    
Blocksize and insert performance            
Oracle Delete & Update Tuning     
High impact update techniques (over % faster): 
Low-impact techniques (between % and % faster)          
Using bulk binds for PL/SQL updates      
Oracle subquery factoring (with clause) for DML         
    
3_3 – Tuning with materialized views and
temporary objects


Materialized views
Global temporary tables
Using scalar subqueries (WITH clause)
Simplifying complex SQL with temporary objects
Exercise – Re-write complex query using temporary tables and WITH clause

3_4 – Tuning subqueries

Subquery Tuning and SQL           
Types of SQL Subqueries             
Tuning Guidelines for Subqueries            
Avoiding SQL Subqueries             
Subqueries in the where Clause               
In vs. exists Subqueries
Same Results, Different Syntax and Plans            
Non-correlated subquery:          
Outer Join:         
Correlated Subquery:   
Tuning Scalar Subqueries             
Scalar Subquery Performance   
Removing Subqueries for Fast SQL Performance              
Internals of Temporary Tables  
Correlated vs. Non-correlated Subqueries            
Tuning Correlated Subqueries   
Automatic Rewriting not exists Subqueries         
Automatic Rewriting exists Subqueries 
Rewriting Non-equality Correlated Subqueries 
Rewriting exists Subqueries with the rank Function        
Subquery Hint Tuning   
Subquery Tuning with Index Hints           
Tuning Subqueries With the push_subq Hint      
Table Anti-Join Hints      
The merge_aj Hint         
SQL Tuning With the hash_aj Hint   
Exercise: Tune an anti-join        

3_5 – Troubleshooting bad SQL

Troubleshooting Problem SQL   
The Holistic Approach to SQL Tuning       
Troubleshooting Oracle SQL Bugs             
What is Bad SQL?            
Identifying Problem SQL              
Troubleshooting with v$sql_plan             
SQL Troubleshooting with v$sql_plan_statistics
Finding indexing opportunities 
Exercise: Find top SQL hogs

3_6  – Advanced Optimizer Statistics

Histograms
Exporting/importing statistics
Statistics management
Exercise – Analyze schema and tables

 

 

 

 

 

 

Book your class seat  Buy a seat in the Oracle Training Class at Sea  

 
 

Please note that while the knowledge gained from this Oracle training may be valuable when preparing for OCP exams, the content of this course is not for Oracle Certification, including the Certified Professional (OCP) or Oracle Certified Associate (OCA) programs.

This is a BC Oracle SQL Tuning training course (c) 2016

     
           

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.