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

EXISTS versus DISTINCT
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
ORTUNE	Oracle Database 10g: Performance Tuning	4 Days	II	Details
OROPT	Oracle 10g/11g SQL Statement Tuning	3 Days	II	Details
OWHPSQ	Advanced PL/SQL (and SQL) for Data Warehousing ETL	4 Days	III	Details
OR11DWB	Oracle Database 11g: Data Warehousing and Oracle Warehouse Builder	5 Days	III	Details

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]