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 SQL tuning course is taught at your Company site with up to 20 students.   

This SQL course can be taught as a 3-day, 4-day or 5-day class.

Click here for on-site course prices

Optional supplemental mentoring

 

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

Our advanced Oracle SQL tuning training is an intensive three-day, four-day, or five-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 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 SQL tuning course is designed for practicing Oracle DBA's and Systems Administrators professionals who have 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 SQL tuning 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 SQL tuning 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.



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

 


Advanced Oracle SQL Tuning Class

Five Day Syllabus

© 2016 by Burleson Corporation

 

Note: This is the continuation of the three day class with an emphasis on expert SQL Tuning Training for experienced developers and DBA's.

 

Day 4 – Advanced Topics

4_1 - Tuning Distributed SQL 

Distributed Database Technology            
Coordinating Distributed Databases       
Distributed SQL Table Joins         
The remote-to-remote Distributed Join               
The local-to-remote distributed join      
Troubleshooting Distributed Oracle SQL               
Performance Issues with Distributed Queries    
Creating Cross-database Execution Plans             
Determining the Driving Site and Driving Table for Cross-database Queries          
The Problem of Remote Joins   
Sorting and Distributed SQL        
Parallelism and Distributed Queries        
Using Views for Distributed SQL               
Tuning with the driving_site Hint             
Forcing Partition Pruning on Distributed SQL       
Tuning distributed DDL 
 
4_2 – Tuning with partitioning

Types of partitioning

Sub-partitioning
Partition-aware SQL performance

4_3 - Time-series SQL Tuning

estat-bstat reports

Statspack
ASH
AWR Reports
Using linear regression
Identifying signatures
Exercise: Analyze a AWR report
 4_4 - Advanced WHERE clause tuning
Sequence of SQL Predicates (ordered_predicates)
Ordering clauses in WHERE statements
CASE statement
Using the ordered_predicates hint
Optional exercise: Change predicate order
4_5 – Optimizer Statistics
Purpose of statistics
Types of statistics (table, column, system)
Histogram statistics
Dynamic sampling
using dbms_stats
Exporting/importing statistics
Exercise:  Use 12c extended statistics
 
4_6 – Row re-sequencing for SQL performance
 

Using clustering factor
Using CTAS with ORDER BY
Using sorted hash clusters
Using IOT’s
Reorganizing Tables for High Performance          
Faster SQL with Database Reorganizations          
Tuning SQL Access with clustering_factor             
Tuning SQL with Cluster Tables 
Managing Row Chaining in Oracle            
A Summary of Object Tuning Rules         

 


DAY 5 – Advanced SQL Tuning Topics
 
5_1 – Tuning Object-oriented SQL
       

Object Oriented Oracle SQL       
The SQL Impedance Mismatch  
SQL Object Extension Performance        
Performance of Oracle Object-oriented SQL      
Repeating Data Items in Relational Tables            
Inside Varray Tables      
Execution Plans for varray Tables             
Oracle Nested tables and SQL Performance     
ADTs and Oracle SQL     
Abstract Datatypes and SQL Performance   
Exercise: Test performance of an abstract datatype
 
5_2 – Tuning with the SPA

The history of automated advisors
The SQLAccess and SQLTuning advisors
Recommending “missing” indexes
Recommending materialized views
The future for automatic SQL Tuning
 
5_3 – Data compression and SQL Performance

Types of Oracle compression
Compression and disk space
Effect of compression on full-scan behavior
 
 5_4 - SQL Tuning with Views  

Tuning SQL with Views 
Benefits of Oracle Views             
Drawbacks in Tuning SQL that Contains Views    
Abusing Views in Oracle SQL      
Merging Views and SQL Tuning 
Predicate Pushing with Views   
Combining Hints and Views        
Oracle In-line Views       
Tips for Tuning SQL with Views 
SQL Tuning with Materialized Views       
Materialized Views and Automatic SQL Query Rewrite  
A Case Study in Materialized Views        
Exercise: Tuning an in-line view
 
5_5 Tracing execution Plans

Using TKPROF
Using the 10046 trace event
Exercise – run TKPROF
5_6 - Expert SQL Tuning Secrets


 


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.