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 for the Developer

© 2016 by Burleson Corporation

This course is taught at your Company site
with up to 20 students.   

It 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 the optimizer is affected by external factors.

* Understand how to read an execution plan.

* Understand the importance of schema and system statistics.

* See how to test SQL execution speed.

* Learn how to optimizer the internal table join methods.

* See how to enforce the most efficient table join order.

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

* See how to quickly identify sub-optimal SQL.

* Understand how to use hints to change SQL execution.

* See how to re-write SQL for faster performance.

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

  

Course Description

This advanced Oracle SQL tuning is an intensive three-day, four-day, or five-day customized course designed to provide Oracle developers, programmers or analysts with an in-depth understanding of Oracle SQL tuning.  Students will learn proven methods for optimizing their SQL and understand how to interpret execution plans 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.  For SQL tuning training for the DBA also see our class on Oracle Performance Tuning Training and Oracle SQL Tuning for the DBA.

Book Required

Optional SQL Tuning book:

  Advanced Oracle SQL Tuning  The Definitive Reference

Donald K. Burleson,
Rampant Tech Press

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

Audience

This course is designed for practicing Oracle developer, analysts and programmers who have basic experience with SQL and the use of a relational database. Prior experience with Oracle is not required, but experience using SQL with a relational database is highly desirable.

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 use advanced SQL techniques including query-requiting, using temporary tables, using hints and changing optimizer setting to achieve faster and more efficient SQL performance.  correlated subqueries and outer joins. The student will also become familiar with all of the major SQL tuning techniques for Oracle, including the internals of the Oracle optimizers, materialized views, and techniques for tuning Oracle SQL statements for optimal performance. 

The overall goal for the class will be to leave the Oracle developer, programmer or analyst with the skills to expose a SQL execution plan, evaluate a plan for optimal execution and how to modify an execution plan for faster performance and throughput.



Advanced Oracle SQL Tuning for Developers
Three Day Syllabus

© 2016 by Donald K. Burleson

Day 1: Introduction to SQL Optimization

 

1 – 1:  Inside the Oracle Optimizer

Job of the SQL optimizer

Parsing, semantic check, decision tree, generating plan

How the Oracle optimizer chooses the “best:” execution plan

The Oracle Optimizer SQL cache (library cache)

 

1 – 2:  Oracle optimization goals

The Oracle Optimizer: Rule Based Optimization

The Oracle Optimizer: Cost Based Optimization

The all_rows vs first_rows_n optimizer modes

Generating optimizer Statistics for tables and system

 

1 – 3: Table Join internals

Oracle Joins, Join Methods and Access Paths

Nested loops, hash joins, merge joins

The star transformation

 

 

 1-4:  Evaluating full-table scans

Understanding multi-block reads (db_file_scattered_reads)

Threshold between full table scan and index range scan

Parallelizing full-table scans

When full table scans are evil

Using plan91.sql and plan10g.sql

 

1 – 5: Oracle Index Optimization

Oracle Index types

Index cardinality

SQL with multi-column indexes

SQL with bitmap indexes

Index unique scans vs. index range scans

Finding missing indexes

1 – 6: Oracle rowset sorting

When a sort is invoked (order by, group by, etc.)

Sorting with indexes vs internal sorting

Detecting disk sorts

 

Day 2: SQL Execution plan evaluation

 

2-1:  The Goals of SQL tuning

Evaluate large-table full-table scans

Evaluate table join order

Evaluate index access

 

2 - 2: Exposing the SQL execution plans

The autotrace command

Using the explain plan facility

 

2 - 3:  Reading an execution plan

Evaluating the steps of an execution plan

The cost and cardinality columns of an execution plan

Adding RAM usage details to an execution plan

 

2 – 4: Tracing SQL execution plans:

Using TKPROF

Using the 10046 trace event

 

2 – 5:  Changing SQL: execution plans

Using optimizer directives (hints)

Changing plans with histograms

Changing plans with dynamic sampling

 

 

2 - 6: Freezing execution plans:

Freezing plans with multiple hints

Using optimizer plan stability

Using SQL profiles

 

 Day 3: Advanced SQL Tuning Topics

 

3 – 1: Using hints effectively in SQL

Good Hints vs. Bad Hints

Global hints (Optimizer mode hints)

Index hints

Parameter change hints

 

3 – 2: Measuring SQL performance

Measuring end-to-end response time

Measuring SQL throughput

Using v$session_longops

 

3 – 3: SQL tuning with temporary objects

 

Using the WITH clause

Using global temporary tables

Re-writing SQL in PL/SQL

 

3 – 4: Tuning subqueries

Tuning IN and NOT IN subqueries

Tuning EXISTS subqueries

Tuning correlated subqueries

Using subquery hints (push_subq)

 

3 – 5: Tuning SQL with cardinality estimates and histograms

Height vs weight balanced histograms

Skew and histograms

Overhead of histograms

Histograms for foreign & primary keys

Histograms for FBI’s

Tuning with cardinality estimation

 

3 – 6:  Oracle SQL tuning secrets

Disk I/O and SQL optimization

Network and SQL optimization (SQL*Net roundtrips)

Diagnosing network issues

CPU vs I/O optimizer coasting

Using gather_system_stats

     
 


Advanced Oracle SQL Tuning for Developers
Five Day Syllabus

© 2016 by Donald K. Burleson

DAY 1 – Introduction to SQL tuning concepts
 
 
1 – Introduction to the course
 
Review of course topics
Goals of SQL Tuning                                                       
Impact of SQL tuning on overall performance                 
            Exercise - Finding SQL tuning tips on the Web
 
 
2 – The evolution of Oracle SQL
 
Navigational vs. declarative database access
Rule-based optimization                                                     
Cost-based optimizer                                                         
Optimizer plan stability                                                       
Cursor Sharing and SQL                                                    
Oracle9i ISO 99 Table syntax
Oracle9i New internals
External tables and SQL
10g new SQL tuning features (SQLAccess Advisor, SQLTuning advisor)
Quiz:   
 
3 – Understanding SQL as a database access method
 
Declarative Syntax                                                              
Select, project & join                                                           
Distributed SQL                                                                   
Subqueries                                                                           
Views and SQL                                                                    
Exercise – See permutations of SQL for an identical query 
 
4 – 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

5 - Oracle SQL optimizer statistics

Using dbms_stats
All about histograms
Dynamic sampling
 
 
6 – Exposing SQL data access paths
 
            Using and interpreting explain plan                                       
Using TKPROF                                                                
Using SQL*Trace                                                             
Using set autotrace in SQL*Plus                                      

Exercise – Create a plan table and view SQL plans

 
DAY 2 – Introduction to SQL tuning techniques
 
 
1 – Changing 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 – Add hints to queries
 
2 – Database-level SQL Tuning
 
Inside the library cache – SQL reusability                        
Avoiding SQL re-parsing - using host variables               
Cursor Sharing                                                                 
Optimizer Plan Stability a.k.a. stored outlines
10g SQL Profiles
Parallel DML                                                                     
Parallel SQL and partitions                                               
SQL and the data warehouse                                             
Exercise – invoke parallel query
 
3a – SQL and Boolean expressions
 
Equality conditions                                                        
Using the LIKE, BETWEEN, EXISTS and NOT            
Using the decode and CASE operators                        
Exercise – Using Decode and CASE
 
 
3b – Using Built-in Functions (BIF’s)
 
Basics of BIFs                                                                  
Function-based indexing                                                  
Creating a custom BIF using PL/SQL
The effect of BIFs on SQL execution
Exercise – Create a custom BIF and use it in SQL
 
 4a – Aggregate Functions
 
            Grouping in several levels                                                   
            Grouping and NULLS
            CUBE and ROLLUP
            Performance and grouping
            Exercise – Using rollup and cube
 
4b – Tuning SQL subqueries
 
Correlated subqueries                                                        
Non-correlated subqueries                                                 
Replacing subqueries with joins                                                         
            Exercise – Tune a complex query
 
2 - Optimizing SQL RAM Resources
 
PGA Overview
SQL Sorting
SQL hash joins
Super-sizing PGA regions                                                                      
 
5 – Oracle execution plan costing
 
The “best” execution plan (first_rows vs. all_rows)          
Rule-based optimization                                                  
Cost-based optimization                                                  
Gathering statistics                                                          
Using histograms                                                             
When the optimizers fail                                                   
Exercise – Change the optimizer mode
 
6 – Table joining techniques
 
Sort-merge joins                                                                  
Nested Loop joins                                                                
Hash joins                                                                             
STAR joins                                                                            
Bitmap joins                                                                          
Exercise – Change table join techniques & evaluate performance
 
DAY 3 – Advanced Oracle SQL tuning
 
1 – Indexing techniques
 
B-Tree indexes                                                                     
Bitmap Indexes                                                                    
Function-based Indexes                                                      
Clustered indexes                                                                
Index-only tables                                                                   
Exercise – Create and use bitmap index
 
2 – Table structures and SQL Performance
 
Effect of PCTFREE & PCTUSED on DML                      
Table high-water mark                                                     
Table striping                                                                   
Table partitioning                                                             
Row-re-sequencing and Oracle SQL I/O performance     
Oracle9i Online table redefinition
Exercise – res-sequence table rows
 
 
3 – Oracle SQL Tuning Techniques- Book Chapter 15, page 551-559
 
Overview of time-series SQL tuning
SQL execution metrics
Re-writing SQL syntax
Replace SQL with PL/SQL
Adding hints
10g SQLAccess advisor - SQL profiles
11g SQL Performance analyzer (SPA)
 
 

4a - Time-series SQL Tuning

Predictive modeling for SQL performance
BSTAT-ESTAT, STATSPACK and AWR
Trending SQL operations (full scans hash joins, &c)
Holistic workload SQL tuning (11g SPA, Quest benchmark factory)
 
5 – Finding Suboptimal SQL
 
Evaluating Large-table, full-table scans                          
Index Usage Analysis                                                      
Displaying reports on system-wide SQL execution         
Exercise – run access.sql  
 
 
6 – Oracle SQL Tuning Silver Bullets
 
Materialized Views to pre-aggregate and pre-join          
Adding indexes                                                               
Exercise – Create a materialized view
Course wrap-up

 

Optional SQL tuning topics:

1 – Using object-oriented SQL
 
Nested Tables                                                                      
VARRAYS within tables                                                      
Using object ID’s in SQL                                                     
Using abstract data types (ADTs)                                      
            Exercise – Create and use an ADT

Please note that while the knowledge gained from this Oracle training may be valuable when preparing for Oracle certification OCP exam, 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.