SQL Tuning

SQL Tuning
Start Date11th April 2018
Course CodeSS18-35
Full Fee
Duration3 Days
Network Member Subsidised Fee€0.00
  Course expired

Programme Overview

This course aims to cover the following topics:

  • Introduction to SQL Tuning
  • Optimizer Fundamentals
  • Bind Variables
  • Displaying Execution Plans
  • Reading and interpreting Execution Plans
  • Optimizer Access Paths
  • Optimizer Joins
  • Other Optimizer Operators
  • SQL Tuning Techniques
Who should attend
This programme is aimed at those using SQL and requiring advanced knowledge.
Course Content

Introduction to SQL Tuning

  • Brief Introduction to Statement Processing
  • SQL Tuning Session
  • What Is Bad SQL?
  • SQL Tuning Strategies


Optimizer Fundamentals

  • Components of the Optimizer
  • Query Transformer
  • Estimator: Selectivity and Cardinality
  • Estimator: Cost and Cost Components
  • Plan Generator
  • How Statistics affect plans
  • Best practice for gathering statistics
  • Controlling the Behaviour of the Optimizer


Bind Variables

  • Cursor Sharing and Different Literal Values
  • Cursor Sharing and Bind Variables
  • Bind Variable Peeking
  • The CURSOR_SHARING Parameter

  • Forcing Cursor Sharing
  • Adaptive Cursor Sharing (ACS)
  • Interacting with Adaptive Cursor Sharing
  • Adaptive Cursor Sharing: Views

Displaying Execution Plans

  • What Is an Execution Plan?
  • Viewing Execution Plans
  • The EXPLAIN PLAN Command
  • Displaying from PLAN_TABLE
  • Using the V$SQL_PLAN View
  • Links between important Dynamic Performance Views
  • Automatic Workload Repository (AWR)
  • SQL Monitoring
  • DBMS_SQLTUNE output to HTML/Text

Reading and Interpreting Execution Plans

  • Explain the execution plan
  • Interpreting a Serial Execution Plan
  • Interpreting a Parallel Execution Plan
  • Evaluating Estimates and Actuals
  • Adaptive Plans/ Optimizations
  • Reading Adaptive Query plans
  • Understanding the key data in dynamic performance views
  • Understanding Statistics
  • SQL Hints and how hints can affect plans


Optimizer Access Paths

  • Row Source Operations
  • Main Structures and Access Paths
  • Full Table Scan
  • ROWID Scan
  • Sample Table Scans
  • Indexes: Overview
  • Normal B*‐tree Indexes
  • Index Scans
  • Index Unique Scan
  • Index Range Scan
  • Index Range Scan: Descending
  • Index Range Scan: Function-Based Index
  • Full Index Scan
  • Fast Full Index Scan
  • Skip Scan
  • Index Join Scan
  • Composite Indexes 
  • B*-tree Indexes and Nulls
  • Using Indexes: Index-Organized Table Scans
  • Bitmap Indexes
  • Bitmap Operations
  • Bitmap Join Index
  • Invisible Indexes
  • Guidelines for Managing Indexes

Optimizer Joins

  • Join Methods
  • Nested Loops Join
  • Sort-Merge Join
  • Hash Join
  • Cartesian Join
  • Join Methods in Summary 
  • Join Types: Equijoins and Nonequi Joins
  • Join Types: Outer Joins
  • Join Types: Semijoins
  • Join Types: Antijoins


Other Optimizer Operations

  • Sorting Operators
  • Buffer Sort Operator
  • Inlist Iterator
  • View Operator
  • Min/Max and First Row Operators
  • FILTER Operations
  • SET Operations
  • Result Cache


SQL Tuning Techniques

  • Overview of writing efficient SQL
  • Table Design?
  • Index Usage
  • Transformed Index
  • Data Type Mismatch
  • NULL usage
  • Tune the ORDER BY Clause
  • Retrieve a MIN/MAX value
  • Correlated Subquery verses Inline Views
  • Avoid Using HAVING Inappropriately
  • Tune the BETWEEN Operator
  • Tune a Star Query
  • Tune the Join Order
  • Test for Existence of Rows
  • LIKE \'%STRING\'
  • Use Caution When Managing Views
  • Join Column and Index
  • Ordering Keys for Composite Index 
  • Bitmap Join Index
  • Tune Complex Logic
  • Writing Combined SQL Statement 
  • Write a Multitable INSERT Statement 
  • Using Temporary Table 
  • Using the WITH Clause
  • Using Materialized Views
  • Partition Pruning
  • Using a Bind Variable
  • Functions in the WHERE clause
  • Using Scalar Subqueries

About the Trainer
Helen Robertson – Helen is a highly resourceful consultant with valuable and varied experience, gained in the database industry, within a world leading technology corporations. Able to use own initiative and work as part of a team. Excellent technical, communication, analytical and problem solving skills. Helen develops and delivers training courses for courses covering all aspects and versions of SQL (including SQL Tuning), PL/SQL and Application Express, delivers all Oracle certified courses in SQL (including SQL Tuning), PL/SQL, Application Express (all versions), SQL Tuning, New Features for Developers (all database versions).