Introduction to Oracle SQL Tuning

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

SQL Statement Processing

• Oracle Architecture Overview
• The Oracle 11g SGA
• SQL Statement Processing Overview
• Soft Parse
• Hard Parse: Optimization
• Row Source Generation
• Execute Step
• Fetch Step
• Overview of the 11g Server Result Cache
• 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 Extended 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
• Checking the Optimizer Setting
• Collecting Object Statistics
• 11g Extended Statistics – Expressions and Correlated Columns
• Tweaking CBO
• Collecting System Statistics (CPU Costing)
• 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

Join and Subquery Access Paths

• Join Operations
• Nested Loops Join
• Sort Merge Join
• Full Outer Join (including 11g Enhancement)
• Hash Join
• Join Hints
• Affect of Additional Filters
• Subquery Conversion
• Hash Group By
• 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
• 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 
• Support for LIKE Predicate (Including 11g Enhancement)
• Calculating Selectivity
• Effect of Indexes on ORDER BY Columns
• When to Avoid Adding an Index
• Multi-Column Indexes
• Which Column First?
• Skip Scan Access Path
• 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

• 11g Invisible Indexes
• Bitmap Indexes
• Reverse Key Indexes
• Function-Based Indexes
• Bitmap Join Indexes
• Workshop

Using Oracle 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

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

Tuning Views

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

Optional: PL/SQL Tuning

• Introduction to DBMS_TRACE
• Introduction to DBMS_PROFILER
• Using PL/SQL to Improve Performance
• Bulk Processing
• 11g PL/SQL Result Cache
• 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

Optional: Parallel Query

• What are Parallel Operations
• When to use Parallel Execution (and when not to use)
• How Parallel Query Works
• Enabling Parallel Query (in 10g, in 11g)

Optional: Tuning in Data Warehouse

• Partitioning for Performance
• Bitmap Indexes 
• Bitmap Join Indexes

Optional: Tuning with Materialized Views and Query Rewrite

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 Oracle11g 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.

This class has not been reviewed online yet.

Read reviews of all SkillBuilders classes.

4.8 out of 5

Student Rating