Oracle Training Oracle Support Development Oracle Apps

Free Oracle Tips

HTML Text

 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle News

 Oracle Forum
 Class Catalog


 Our Staff
 Our Prices
 Help Wanted!

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


 SQL Tuning
 Security

 UNIX
 Oracle UNIX
 Linux
 Oracle Linux
 Monitoring
 Remote help

 Remote plans
 Remote
services
 Oracle C++
 Oracle Java
 Apache
 JDeveloper
 App Server

 Applications
 Oracle Forms
 Oracle Portal
 11i Upgrades
 SQL Server
 Oracle Concepts
 HTML-DB Tips
 Software Help

 Remote Help  
 Development  

 Implementation


 Financials Training
 Oracle 11i
 Oracle Apps 11i
 Oracle Workflow
 Oracle AR 11i Class
 Oracle AP 11i class
 Oracle GL 11i class
 Oracle HR 11i class
 Oracle FA 11i class
 11i Project Mgt
 11i procurement
 11i collections


 Oracle Posters
 Oracle Books

 Oracle Tuning Book
 Oracle RAC Book
 Oracle Security
 Easy Oracle Books
 Oracle Scripts
 SQL Server DBA
 SQL Design Patterns
 Ion
 Excel-DB   


 BC Oracle News


 Rednecks!
 Dress code
 Arabian Stallion

 Burleson Arabians
 Guide Horses
 Don Burleson Blog
 Golf & Travel


 Privacy Policy
 

 

 

Oracle SQL Tuning Training Course

© 2007-2009 by Burleson Consulting

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

* Understand how to read an execution plan.

* Understand the importance of schema statistics.

* See how to test SQL execution speed.

* Learn the internal table join methods.

* Learn to detect unnecessary full-table scans.

* See how to quickly identify sub-optimal SQL.

* Use hints to change SQL execution.

* Tune SQL with stored outlines.

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

  

Course Description

Advanced Oracle SQL tuning 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 Consulting instructors offer decades of real world DBA experience in Oracle features, and they will share their secrets in this intense hands-on Oracle SQL tuning training.

Book Required

  Oracle SQL Tuning & CBO Internals

Kimberly Floss,
Rampant Tech Press

ISBN 0-9745993-3-6   

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 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 Consulting instructors offer decades of real world DBA experience in Oracle features, and they will share their secrets in this intense hands-on Oracle SQL tuning training.

Learning Objectives

By the end of this course the student will be able to formulate 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 the internals of the Oracle optimizers, materialized views, and techniques for tuning Oracle SQL statements for optimal performance.



Advanced Oracle SQL Tuning
Three Day Syllabus

© 2007-2009 by Donald K. Burleson

Internal processing of SQL statements

  • Parsing SQL Syntax - (page 53-57)
  • SQL Semantic Analysis - (page 56)
  • Generating the execution plan - (page 181-185)
  • Using optimizer plan stability - (page 309-332)
  • Using the v$sql view  - (page 57)
  • Using the v$sql_plan view
  • Exercise – Query the library cache

 Viewing SQL execution Internals

  • Using and interpreting explain plan - (page 310)
  • Using TKPROF -  (page 205)
  • Using SQL*Trace -  (page 200)
  • Using set autotrace in SQL*Plus
  • Exercise – Create a plan table and view SQL plans

Inside the Oracle Optimizers

  • The “best” execution plan (first_rows vs. all_rows) - (page 129-130)
  • Rule-based optimization - (page 129)
  • Cost-based optimization - (page 334)
  • Gathering statistics - (page 340)
  • Using histograms - (page 279, 305)
  • When the optimizers fail - (page 85)
  • Exercise – Change the optimizer mode

Table joining internals

  • Sort-merge joins - (page 341)
  • Nested Loop joins - (page 401)
  • Hash joins - (page 390-392)
  • STAR joins - (page 401)
  • Bitmap joins - (page 291)
  • Exercise – Change table join techniques & evaluate performance

Oracle Index Optimization

  • B-Tree indexes  - (page 233)
  • Bitmap Indexes  - (page 80, 127)
  • Function-based Indexes - (page 36, 61)
  • Clustered indexes - (page 87)
  • Index-only tables - (page 468)
  • Exercise – Create and use bitmap index

Object structures and SQL

  • Effect of PCTFREE & PCTUSED on DML - (page 152-155)
  • Table high-water mark  - (page 130)
  • Table striping - (page 515)
  • Table partitioning - (page 517)
  • Row-resequencing - (page 141-143)
  • Oracle9i Online table redefinition
  • Exercise – resequence table rows

 

 

  Locating poor execution  plans
  • Evaluating Large-table, full-table scans - (page 38, 59)
  • Index Usage Analysis - (page 61)
  • Reports on system-wide SQL execution - (page 220-225)
  • Exercise – run access.sql  

Altering SQL execution plans

  • Using hints - (page 87, 277)
  • Changing the system-wide optimizer mode - (page 92)
  • Changing optimizer mode for specific statements - (page 10)
  • Re-writing SQL queries  -  (page 54, 314)
  • Table join order evaluation - (page 88)
  • Using the ordered hint - (page 249)
  • Exercise – Add hints to queries

DBA tuning for SQL

  • Inside the library cache – (page 222-229)
  • Avoiding SQL re-parsing - (page 223)
  • Cursor Sharing  -  (page 54-55)
  • Optimizer Plan Stability (stored outlines) - (page 310-317)
  • Parallel DML  - (page 441)
  • Parallel SQL and partitions -  (page 517)
  • SQL and the data warehouse
  • Exercise – invoke parallel query

SQL Aggregate Functions

  • Grouping in several levels
  • Grouping and NULLS
  • CUBE and ROLLUP
  •  Performance and grouping
  • Exercise – Using rollup and cube

Tuning SQL sub-queries

  • Correlated subqueries - (page 342)
  • Non-correlated subqueries - (page 342)
  • Replacing subqueries with joins - (page 385)
  • Exercise – Tune a complex query

Expert SQL Tuning Secrets

  • Materialized Views to pre-aggregate and pre-join  - (page 54)
  • Adding indexes  -  (page 62)
  • Exercise – Create a materialized view

 

 


Advanced Oracle SQL Tuning
Five Day Syllabus

© 2007-2009 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-resequencing and Oracle SQL I/O performance     
Oracle9i Online table redefinition
Exercise – resequence 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 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) 2002-2009

   

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


 

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle © is the registered trademark of Oracle Corporation.