Oracle 11g SQL Performance Tuning for Data Warehouses

Course:  ORPTDW
Duration:  4 Days
Level:  II
Course Summary

This course will provide the student with the skills necessary to create scalable, high performance SQL-based Oracle applications. They will also learn how to identify existing high-load, poor performing SQL statements and tune them. The focus is on making the best use of Oracle architecture, bench-marking various SQL formulations and identifying the best schema object for a given situation.

Oracle11g introduces "automatic tuning" features such as the Automatic Workload Repository, Automatic Database Diagnostic Monitor and SQL Tuning Advisor. These features will be included in the course. It will also include tuning PL/SQL programs using bulk processing and profiling to identify high-load statements. ETL consideration and physical modeling considerations are included.

« Hide The Details
Topics Covered In This Course

Introduction to Oracle SQL Tuning

  • What Can Be Tuned?
  • What You Need to Know to Tune Oracle SQL

SQL Statement Processing

  • Oracle11g Architecture
  • The SGA
  • SQL Statement Processing Overview
  • Soft Parse
  • Hard Parse: Optimization
  • Row Source Generation
  • Execute Step
  • Fetch Step

Viewing The Execution Plan

  • The EXPLAIN PLAN Command
  • PLAN_TABLE Columns
  • Reading Execution Plans
  • Retrieving the Historical Plan: V$SQL_AREA
  • Using SQL*Plus Autotrace

Using SQL Trace and TKPROF

  • Trace Concepts
  • Trace Steps
  • Trace-Related Initialization Parameters
  • Enable Trace
  • Find the Trace File
  • Using the TKPROF Utility
  • Trace Analysis Tips

The Importance of Bind Variables

  • Shared Pool Reuse
  • Bind Variables
  • Querying V$SQL
  • PL/SQL Bind Variables
  • Cursor Sharing

Getting to Know Cost Based Optimization

  • Optimization Concepts
  • Rule-Based Optimization (Deprecated in 11g)
  • Enabling CBO
  • Checking the Optimizer Setting
  • Collecting Object Statistics
  • Tweaking CBO
  • Collecting System Statistics
  • Creating Histograms
  • Dynamic Sampling
  • Optimizer Calculations for Equal Comparisons
  • Optimizer Calculations for Range Comparisons
  • Bind Variable Peeking and Range Optimization Involving Bind Variables

Learning the Access Paths

  • Full Scans
  • Multi-Block Reads
  • Forcing Full Table Scans
  • High-Water Mark
  • Large Deletes and the HWM
  • When is a Full Scan Bad?
  • Index-Based Scans
  • Unique, Range, Full and Fast-Full Index Scans
  • Index Skip Scan

Using Hints

  • What are Hints?
  • When to Use Hints
  • Hint Examples
  • Some Syntax Notes

Schema (Data Storage) Options

  • Heap Tables
  • Index Organized Tables
  • Index Clustered Tables
  • Hash Clustered Tables
  • Sorted Hash Clusters (11g)
  • Materialized Views
  • Temporary Tables
  • Nested Tables
  • Object Tables

Indexes, Part I - All About B-Tree Indexes

  • Index Concepts
  • B-Tree Index Concepts
  • B-Tree Effect on Query Performance
  • Creating B-Tree Indexes
  • Update Statistics with DBMS_STATS
  • Impact of Clustering Factor
  • Choosing Columns to Index
  • Calculating Selectivity
  • Effect of Indexes on ORDER BY Columns
  • When to Avoid Adding an Index
  • Multi-Column Indexes
  • Which Column First?
  • Avoiding Table Access with Multi-Column Indexes
  • Multiple Single Column Indexes
  • When and Where to Create Indexes
  • What to Avoid when using an Index
  • Finding Unused Indexes with MONITORING
  • Additional Indexing Guidelines

Indexes, Part II - Introduction to Other Index Types

  • Bitmap Indexes
  • Reverse Key Indexes
  • Function-Based Indexes
  • Bitmap Join Indexes

Using Oracle11g Advisors to Tune SQL

  • Introduction to Automatic Workload Repository Concepts
  • Using ADDM to find Problems
  • Using SQL Tuning Advisor to Get Recommendations
  • Exploring STA Recommendations
  • Using SQL Profiles

Impact of Reformulating SQL Statements

  • NOT IN versus NOT EXISTS
  • IN versus EXISTS
  • UNION ALL versus UNION
  • Oracle Analytic Functions

Join and Subquery Access Paths

  • Join Operations
  • Nested Loops Join
  • Sort Merge Join
  • Hash Join
  • Join Hints
  • Additional Filters
  • Subquery Conversion

Concurrency Control

  • Introduction to Locks
  • Row Lock Architecture
  • Basic Locking Rules
  • DDL and DML locks
  • Locking Issues: Blocking
  • Locking Issues: Deadlocks
  • Detecting Waits Due to Blocking

Parallel Query

  • When Parallel Query Helps
  • Parallel Query Examples
  • Parallel DML
  • Conditions To Avoid

Optimizer Plan Stability

  • Plan Stability Concepts
  • Implementing Plan Stability
  • Exporting/Importing Outlines
  • Managing Outlines

Tuning Views

  • Mergeable Views
  • Hints & Mergeable Views
  • Non-Mergeable Views
  • Hints and Non-Mergeable Views

PL/SQL Tuning

  • Introduction to DBMS_TRACE
  • Introduction to DBMS_PROFILER
  • Using PL/SQL to Improve Performance
  • 11g PL/SQL Performance Improvements
  • Bulk Processing
  • Efficient Lookups
  • Coding Pipelined Table Functions
  • Static SQL versus Dynamic SQL
  • Using RefCursors versus Arrays to Return Data
  • Explicit versus Implicit Cursors
  • Pipelined Functions

Data Warehouse Considerations

  • Making Hardware and I/O Perform in a Data Warehouse
  • Data Warehouse Physical Architecture
  • Effective Data Warehouse Indexing
  • Constraints and Integrity in Data Warehouses
  • Using Partitioning and Tuning Parallel Processing
  • Materialized Views and Query Rewrites
  • Oracle Dimension Objects
  • Data Loading: Extraction and Transportation
  • Data Loading: Loading and Transformation
  • SQL Aggregation Using GROUP BY Clause Extensions
What You Can Expect

At the end of this course, delegates will be able to:

  • Successfully improve the performance of SQL-based applications and Data Warehouses
  • Understand, in-depth, the Oracle architecture.
  • Understand Oracle query processing technology including parsing (soft parse, hard parse), cursors, the importance of bind variables, optimizer statistics and more
  • Understand Oracle lock architecture, and code applications that do not impede scalability
  • Determine what type of data storage object is best for their application.
  • Choose which index type best fits for a given scenario
  • Use the EXPLAIN PLAN and V$SQL_PLAN to externalize access path choices made by the optimizer
  • Use SQL Trace and V$SQL_PLAN_STATISTICS to find the least expensive and best performing solution
  • Query many "V$" dynamic performance views to aid their understanding of Oracle and a particular situation
  • Describe the various access paths used by Oracle, and understand why the optimizer chose one access path over another
  • Rewrite queries with the goal of finding a less expensive query
  • Identify queries that are blocked and find the source of the blocking lock
  • Use HINTs to influence optimizer choices
  • Create histograms to educate the optimizer about skewed data
  • Take advantage of parallel processing features
  • Use the 11g-specific automatic SQL tuning features
  • Know when to implement parallel query and parallel DML operations
  • Implement the Oracle plan stability feature
  • Use DBMS_PROFILER and DBMS_TRACE to identify areas within the code that deserve their attention
  • Use bulk processing to improve performance
  • Avoid inefficient lookups
  • Code pipelined table functions
Who Should Take This Course

This course is designed for Application developers and database administrators. Analysts with strong Oracle SQL skills will also benefit from this course.

Recommended Prerequisites

A good understanding of Oracle SQL, Data Warehouse Overview, and Dimensional modeling will be needed.

Training Style

Instructor led with 50% lecture and 50% lab.

« Hide The Details
Related Courses
Code Course Title Duration Level
Oracle Database 10g: Performance Tuning
4 Days
Oracle 10g/11g SQL Statement Tuning
3 Days
Advanced PL/SQL (and SQL) for Data Warehousing ETL
4 Days
Oracle Database 11g: Data Warehousing and Oracle Warehouse Builder
5 Days

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]