Oracle Database 11g: SQL Tuning Workshop

3 Day Course
Hands On
Code OC11GSQLTUN

This course has been retired. Please view currently available Oracle 11G Training Courses.

Modules

Hide all

Exploring the Oracle Database Architecture (8 topics)

  • Oracle Database Server Architecture: Overview
  • Oracle Database Memory Structures: Overview
  • Background Process Roles
  • Automatic Shared Memory Management
  • Automated SQL Execution Memory Management
  • Automatic Memory Management
  • Database Storage Architecture
  • Logical and Physical Database Structures

Introduction to SQL Tuning (8 topics)

  • Reasons for Inefficient SQL Performance
  • Performance Monitoring Solutions
  • Monitoring and Tuning Tools: Overview
  • EM Performance Pages for Reactive Tuning
  • CPU and Wait Time Tuning Dimensions
  • Scalability with Application Design, Implementation, and Configuration
  • Common Mistakes on Customer Systems
  • Proactive Tuning Methodology

Introduction to the Optimizer (8 topics)

  • Structured Query Language
  • SQL Statement Representation, Implementation & Processing: Overview
  • SQL Statement Parsing: Overview
  • Why Do You Need an Optimizer?
  • Optimization During Hard Parse Operation
  • Cost-Based Optimizer
  • Controlling the Behavior of the Optimizer
  • Optimizer Features and Oracle Database Releases

Optimizer Operators (8 topics)

  • Row Source Operations
  • Main Structures and Access Paths
  • Full Table Scan
  • Indexes: Overview
  • Using Indexes: Considering Nullable Columns
  • Bitmap Indexes, Composite Indexes &Invisible Index
  • Guidelines for Managing Indexes
  • Clusters

Interpreting Execution Plans (8 topics)

  • Execution Plan
  • Links Between Important Dynamic Performance Views
  • Automatic Workload Repository (AWR)
  • Generating SQL Reports from AWR Data
  • SQL Monitoring: Overview
  • Reading More Complex Execution Plans
  • Reviewing the Execution Plan
  • Looking Beyond Execution Plans

Case Study: Star Transformation (7 topics)

  • The Star & Snowflake Schema Model
  • Execution Plan Without Star Transformation
  • Retrieving Fact Rows from One Dimension All Dimensions
  • Joining the Intermediate Result Set with Dimensions
  • Using Bitmap Join Indexes
  • Star Transformation
  • Bitmap Join Indexes

Optimizer Statistics (7 topics)

  • Optimizer Statistics & Types of Optimizer Statistics
  • Multicolumn Statistics: Overview
  • Expression Statistics: Overview
  • Gathering System Statistics
  • Statistic Preferences: Overview
  • Optimizer Dynamic Sampling: Overview
  • Locking Statistics

Using Bind Variables (6 topics)

  • Cursor Sharing and Different Literal Values
  • Cursor Sharing and Bind Variables
  • Bind Variables in SQL*Plus & Enterprise Manager
  • Cursor Sharing Enhancements
  • Adaptive Cursor Sharing: Overview
  • Interacting with Adaptive Cursor Sharing

Using Optimizer Hints (8 topics)

  • Optimizer Hints: Overview
  • Types of Hints
  • Specifying Hints
  • Rules for Hints
  • Hint Recommendations
  • Hint Categories
  • Optimization Goals and Approaches
  • Additional Hints

Application Tracing (8 topics)

  • End-to-End Application Tracing Challenge
  • Location for Diagnostic Traces
  • What Is a Service?
  • Use Services with Client Applications
  • Trace Your Own Session
  • SQL Trace File Contents
  • Formatting SQL Trace Files: Overview
  • Invoking the tkprof Utility

Automating SQL Tuning (8 topics)

  • Tuning SQL Statements Automatically
  • Application Tuning Challenges
  • SQL Tuning Advisor: Overview
  • Stale or Missing Object Statistics
  • SQL Statement Profiling
  • Plan Tuning Flow and SQL Profile Creation
  • Database Control and SQL Tuning Advisor
  • Implementing Recommendations

Prerequisites

Required Prerequisites:

  • Oracle Database: Introduction to SQL
  • Suggested Prerequisites:
  • Oracle Database: SQL Fundamentals I

Additional Learning

The courses below may help you meet the knowledge level required to take this course.

Course PDF

Print

Sections