PDF Print E-mail
Oracle 10g SQL Tuning Training

Oracle10g SQL Tuning for Developers and DBAs


Learn to tune SQL statements with EXPLAIN PLAN, SQL Trace and TKPROF (and AUTOTRACE and CBO 10053 trace and Much More).

Your Oracle database has so much more to offer.

Have you learned about optimizer (CBO) features like bind variable peeking, histograms, dynamic sampling?

Do you know when hints should be used - and when they should not?

No? Then why expect your SQL statements to perform (and scale) well?

This hands-on course will provide you with the skills necessary to create scalable, high performance SQL-based Oracle applications (proactive SQL tuning). You will also learn how to identify existing high-load, poor performing SQL statements and tune them (reactive SQL tuning). Particular attention is paid to making the best use of Oracle architecture, bench-marking various SQL formulations and identifying the best schema object (e.g. heap table, IOT, etc.) for a given situation. Oracle10g introduces "automatic tuning" features such as the Automatic Workload Repository, Automatic Database Diagnostic Monitor and SQL Tuning Advisor. These features are covered in this class.

The course also includes a lesson on tuning PL/SQL programs. This includes using bulk processing and profiling to identify high-load statements.

Working examples are included throughout the course book. Students have access to electronic versions of all sample scripts and lab solutions. Approximately 50% of class time is spent on hands-on exercises.

As with all of our courses, this class is highly customizable to your specific training requirements. Hands-on workshops constitute approximately 50% of the class.

Audience: Application developers and database administrators. Analysts with strong Oracle SQL skills will also benefit from this course. /This course is n ot intended for end-users/.

Prerequisites: Solid experience with Oracle SQL. PL/SQL programming is required to understand the lesson on PL/SQL tuning. This knowledge can be obtained via the following SkillBuilders' courses:

  • Introduction to Oracle for Developers - The skills taught in the introductory class are required for successful completion of this Tuning course.
  • Advanced SQL Queries for Oracle10g databases - The skills taught in this Advanced course are not required, but helpful. For example, prior knowledge of the Oracle Analytic functions will help you when you learn to improve the performance of a complex join by converting it into an analytic function call.
  • PL/SQL Programming Workshop - Required to successfully complete the PL/SQL Tuning lesson

Next Courses: Effective Data Warehousing in Oracle, Advanced SQL Queries for Oracle10g Databases, Oracle10g Database Administration.


Objectives: After successfully completing this course, you will be able to:

Successfully improve the performance of SQL-based applications

Have a solid understanding of Oracle architecture. Specifically, you will:

Understand the purpose of the Oracle primary memory structures (e.g. SGA, PGA)

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 your application. Specifically, you will:

Understand the various data storage options (e.g. heap, index organized, cluster, sorted hash cluster, etc) and benchmark which provides the best performance for your situation

Choose which index type best fits for a given scenario

Use the latest tuning techniques including:

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 your understanding of Oracle and a particular situation

Describe the various access paths used by Oracle, and usually understand why the optimizer chose one access path over another

Rewrite queries with the goal of finding a less expensive query (note that to some degree this objective relies on your preexisting knowledge of Oracle SQL)

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

Know how to use the 10g-specific automatic SQL tuning features

Know when to implement parallel query and parallel DML operations

Implement the Oracle plan stability feature

Tune PL/SQL programs including (but not limited to):

Using DBMS_PROFILER and DBMS_TRACE to identify areas within the code that deserve your attention

Use bulk processing to improve performance

Avoiding inefficient lookups

Coding pipelined table functions

Topic Summary

Introduction to Oracle SQL Tuning

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

SQL Statement Processing

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

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
  • Workshop

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
  • Workshop

The Importance of Bind Variables

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

Getting to Know Cost Based Optimization

  • Optimization Concepts
  • Rule-Based Optimization (Deprecated in 10g)
  • 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
  • Workshop

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
  • Workshop

Using Hints

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

Schema (Data Storage) Options

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

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?
  • Oracle9i Skip Scan
  • 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
  • Workshop

Indexes, Part II - Introduction to Other Index Types

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

Using Oracle10g 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
  • Workshop

Impact of Reformulating SQL Statements

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

Join and Subquery Access Paths

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

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
  • Workshop

Parallel Query

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

Optimizer Plan Stability

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

Tuning Views

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

PL/SQL Tuning

  • Introduction to DBMS_TRACE
  • Introduction to DBMS_PROFILER
  • Using PL/SQL to Improve Performance
  • 10g 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
  • Workshop
 

Your are currently browsing this site with Internet Explorer 6 (IE6).

Your current web browser must be updated to version 7 of Internet Explorer (IE7) to take advantage of all of template's capabilities.

Why should I upgrade to Internet Explorer 7? Microsoft has redesigned Internet Explorer from the ground up, with better security, new capabilities, and a whole new interface. Many changes resulted from the feedback of millions of users who tested prerelease versions of the new browser. The most compelling reason to upgrade is the improved security. The Internet of today is not the Internet of five years ago. There are dangers that simply didn't exist back in 2001, when Internet Explorer 6 was released to the world. Internet Explorer 7 makes surfing the web fundamentally safer by offering greater protection against viruses, spyware, and other online risks.

Get free downloads for Internet Explorer 7, including recommended updates as they become available. To download Internet Explorer 7 in the language of your choice, please visit the Internet Explorer 7 worldwide page.