Course 262:
Oracle SQL Tuning: Hands-On

(4 days)

 

Course Description

Delivering data is one thing, but delivering accurate, reliable information in a timely manner is paramount to effective business operations. In this hands-on course, you will gain experience with the manual and automated tools and techniques used to write well-tuned, high performing SQL queries. You will learn investigative methods and the tools to use to reveal varying levels of detail about how the Oracle database executes a SQL statement. You will learn the different ways in which data can be accessed, which ones are most efficient under specific circumstances, and how to ensure that the best method is used. Partitioning topics are covered including the new Oracle partitioning methods. Learn to take advantage of hints, bind variables, adaptive cursor sharing, different types of indexes, and optimal storage structures. You will gain an in-depth understanding of the cost-based optimizer and the methods for controlling it.

Learning Objectives

  • Understand tuning concepts
  • Learn tools for identifying and diagnosing performance problems
  • Discuss various access paths and join methods
  • Learn how to generate and maintain statistics
  • Use indexes, various table storage options, and partitioning to improve performance of data retrieval
  • Control the optimizer with hints and plan stability
  • Discover different ways to improve performance by restructuring and rewriting SQL statements

Who Should Attend

Anyone involved in complex data retrieval and analysis who wants to understand how to diagnose and tune applications. Typical job functions include application developers, DBAs, report writers, and technical consultants.

Prerequisites

Course 267: Oracle Database: A Comprehensive Hands-On Introduction, or equivalent SQL knowledge is assumed.

Course Outline

Unit 1: Tuning Concepts

  • Oracle’s Tuning Methodology
  • Basic Tuning Concepts
  • Plan and Manage the Tuning Process
  • Design Issues Related to Performance

Unit 2: Diagnostic Tools

  • How SQL Statements Are Processed
  • The Role of the Optimizer
  • Optimizer Approaches and Goals
  • Utilize Explain Plan for Diagnostics
  • Work with DBMS_XPLAN and Autotrace
  • Generate and Analyze Statistics Using SQL Trace and TKPROF

Unit 3: Oracle Automated Tuning Tools

  • SQL Tuning Advisor
  • SQL Access Advisor
  • Automatic SQL Tuning
  • Real-Time SQL Monitoring

Unit 4: Access Paths and Join Methods

  • Access Paths
    • Table Access Paths
    • Index Access Paths
    • Other Storage Structures Access Paths
  • Join Methods
    • Sort-Merge Join
    • Nested-Loops Join
    • Hash Join
    • Cartesian Join
    • Star Join

Unit 5: Generating and Maintaining Statistics

  • The Optimizer and Its Use of Statistics
  • Gather Statistics
  • Statistics Management
  • Column Statistics and Histograms
  • Deferred Statistics Management

Unit 6: Providing the Optimizer with a Better Way: Indexes

  • Indexing Options
    • B-Tree Indexes
    • Variations of B-Tree Indexes
      • Reverse Key Indexes
      • Function-Based Indexes
    • Bitmap Indexes
      • Star Join Transformation
      • Bitmap Join Indexes
    • Monitoring Index Usage
    • Index Key Compression

Unit 7: Providing the Optimizer with a Better Way: Table Storage

  • Index Organized Tables
  • Clusters
  • Hash Clusters
  • Sorted Hash Clusters
  • External Tables
  • Materialized Views

Unit 8: Providing the Optimizer with a Better Way: Partitioning

  • Partitions and Their Uses
  • Table Partitioning Options
    • Range Partitioning
    • List Partitioning
    • Hash Partitioning
    • Interval Partitioning
    • Reference Partitioning
    • Virtual Column Partitioning
    • System Partitioning
    • Composite Partitioning
  • Examine Index Partitioning Options
    • Local Prefixed
    • Local Non-Prefixed
    • Global Prefixed

Unit 9: Controlling the Optimizer: Hints and SQL Plan Management

  • Optimizer Hints
  • SQL Plan Management Architecture
  • SPA Process and Use

Unit 10: Tuning SQL Statements

  • Using the Result Cache
  • Restructure SQL Statements for Optimal Performance
  • Bind Variables and Adaptive Cursor Sharing
  • Store Intermediate Result Sets
  • Other Tuning Considerations
    • The Cache Option
    • Visiting Data as Few Times as Possible
    • Introduction to the Analytic Functions

Unit 11: Course Summary

  • Summary

Please Contact Your ROI Representative to Discuss Course Tailoring!