Oracle Database 11g: SQL Tuning Workshop

3 Day Course
Hands On

Book Now - 1 Delivery Method Available:

Classroom Virtual Classroom Private Group - Virtual Self-Paced Online


The SQL Tuning Workshop class covers investigative methods that reveal varying levels of detail about how the Oracle database executes a SQL statement. Students 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. Topics on taking advantage of hints, bind variables, and different types of indexes are also covered.

This course counts towards the Hands-on course requirement for the Oracle Database 11g Administrator Certification. Only instructor-led inclass or instructor-led online formats of this course will meet the Certification Hands-on Requirement. Self Study CD-Rom and Knowledge Center courses DO NOT meet the Hands-on Requirement.

Learn to:

  • Identify Poorly Performing SQL
  • Understand the Query Optimizer
  • Trace applications
  • Monitor SQL execution in real time
  • Interpret execution plans


  • Support Engineer
  • Developer
  • Technical Consultant
  • Data Warehouse Developer
  • Application Developers

Course Objectives:

  • Identify problem SQL statements
  • Modify a SQL statement to perform at its best
  • Trace an application
  • Understand how the Query Optimizer makes decisions about how to access data
  • Interpret execution plans
  • Use optimizer hints effectively
  • Generate a load test

Training Partners

We work with the following best of breed training partners using our bulk buying power to bring you a wider range of dates, locations and prices.


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


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. If you are unsure please ask a training advisor .

  • Oracle Database: Introduction to SQL

    This 5 day course helps the participants to write subqueries, combine multiple queries into a single query using SET operators and report aggregated data using group functions.

    5 Day Course Hands On Training Course Code OCDGISQL
    Classroom Virtual Classroom Private Group - Virtual Self-Paced Online
  • Oracle Database: SQL Fundamentals I

    This 3 day course introduces participants to the fundamentals of SQL using Oracle Database technology. Participants learn the concepts of relational databases and the powerful SQL programming langu...

    3 Day Course Hands On Training Course Code OCDSQLFUND
    Classroom Virtual Classroom Private Group - Virtual Self-Paced Online

Course PDF


Share this Course


Recommend this Course