Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Advanced Oracle SQL Tuning Training Course for the Developer

This course is taught at your Company site
with up to 20 students.

It can be taught as a 3-day, 4-day, or 5-day class.

Click here for on-site course prices

Optional supplemental mentoring

* See how the optimizer is affected by external factors.

* Understand how to read an execution plan.

* Understand the importance of schema and system statistics.

* See how to test SQL execution speed.

* Learn how to optimizer the internal table join methods.

* See how to enforce the most efficient table join order.

* Learn how to detect unnecessary large-table full-table scans.

* See how to quickly identify sub-optimal SQL.

* Understand how to use hints to change SQL execution.

* See how to re-write SQL for faster performance.

* See the v$ internal views for Oracle SQL tuning.

This advanced Oracle SQL tuning is an intensive three-day, four-day, or five-day customized course designed to provide Oracle developers, programmers or analysts with an in-depth understanding of Oracle SQL tuning. Students will learn proven methods for optimizing their SQL and understand how to interpret execution plans for optimal performance. Burleson Corporation instructors offer decades of real world DBA experience in Oracle features, and they will share their secrets in this intense Oracle SQL tuning training. For SQL tuning training for the DBA also see our class on Oracle Performance Tuning Training and Oracle SQL Tuning for the DBA.

Optional SQL Tuning book:

Advanced Oracle SQL Tuning The Definitive Reference

Donald K. Burleson, Rampant Tech Press

ISBN-13 978-0-9823061-5-4

This course is designed for practicing Oracle developer, analysts and programmers who have basic experience with SQL and the use of a relational database. Prior experience with Oracle is not required, but experience using SQL with a relational database is highly desirable.

This course was designed by Donald K. Burleson, an acknowledged leader in Oracle database administration. Author of more than 20 database books, Burleson was chosen by Oracle Press to write five authorized editions, including Oracle High-Performance SQL tuning. Burleson Corporation instructors offer decades of real world DBA experience in Oracle features, and they will share their secrets in this intense Oracle SQL tuning training.

Learning Objectives

By the end of this course the student will be able to use advanced SQL techniques including query-requiting, using temporary tables, using hints and changing optimizer setting to achieve faster and more efficient SQL performance. correlated subqueries and outer joins. The student will also become familiar with all of the major SQL tuning techniques for Oracle, including the internals of the Oracle optimizers, materialized views, and techniques for tuning Oracle SQL statements for optimal performance.

The overall goal for the class will be to leave the Oracle developer, programmer or analyst with the skills to expose a SQL execution plan, evaluate a plan for optimal execution and how to modify an execution plan for faster performance and throughput.

Advanced Oracle SQL Tuning for Developers
Three Day Syllabus

Day 1: Introduction to SQL Optimization

1 – 1: Inside the Oracle Optimizer

Job of the SQL optimizer

Parsing, semantic check, decision tree, generating plan

How the Oracle optimizer chooses the “best:” execution plan

The Oracle Optimizer SQL cache (library cache)

1 – 2: Oracle optimization goals

The Oracle Optimizer: Rule Based Optimization

The Oracle Optimizer: Cost Based Optimization

The all_rows vs first_rows_n optimizer modes

Generating optimizer Statistics for tables and system

1 – 3: Table Join internals

Oracle Joins, Join Methods and Access Paths

Nested loops, hash joins, merge joins

The star transformation

1-4: Evaluating full-table scans

Understanding multi-block reads (db_file_scattered_reads)

Threshold between full table scan and index range scan

Parallelizing full-table scans

When full table scans are evil

Using plan91.sql and plan10g.sql

1 – 5: Oracle Index Optimization

Oracle Index types

Index cardinality

SQL with multi-column indexes

SQL with bitmap indexes

Index unique scans vs. index range scans

Finding missing indexes

1 – 6: Oracle rowset sorting

When a sort is invoked (order by, group by, etc.)

Sorting with indexes vs internal sorting

Detecting disk sorts

Day 2: SQL Execution plan evaluation

2-1: The Goals of SQL tuning

Evaluate large-table full-table scans

Evaluate table join order

Evaluate index access

2 - 2: Exposing the SQL execution plans

The autotrace command

Using the explain plan facility

2 - 3: Reading an execution plan

Evaluating the steps of an execution plan

The cost and cardinality columns of an execution plan

Adding RAM usage details to an execution plan

2 – 4: Tracing SQL execution plans:

Using TKPROF

Using the 10046 trace event

2 – 5: Changing SQL: execution plans

Using optimizer directives (hints)

Changing plans with histograms

Changing plans with dynamic sampling

2 - 6: Freezing execution plans:

Freezing plans with multiple hints

Using optimizer plan stability

Using SQL profiles

Day 3: Advanced SQL Tuning Topics

3 – 1: Using hints effectively in SQL

Good Hints vs. Bad Hints

Global hints (Optimizer mode hints)

Index hints

Parameter change hints

3 – 2: Measuring SQL performance

Measuring end-to-end response time

Measuring SQL throughput

Using v$session_longops

3 – 3: SQL tuning with temporary objects

Using the WITH clause

Using global temporary tables

Re-writing SQL in PL/SQL

3 – 4: Tuning subqueries

Tuning IN and NOT IN subqueries

Tuning EXISTS subqueries

Tuning correlated subqueries

Using subquery hints (push_subq)

3 – 5: Tuning SQL with cardinality estimates and histograms

Height vs weight balanced histograms

Skew and histograms

Overhead of histograms

Histograms for foreign & primary keys

Histograms for FBI’s

Tuning with cardinality estimation

3 – 6: Oracle SQL tuning secrets

Disk I/O and SQL optimization

Network and SQL optimization (SQL*Net roundtrips)

Diagnosing network issues

CPU vs I/O optimizer coasting

Using gather_system_stats

Advanced Oracle SQL Tuning for Developers
Five Day Syllabus

DAY 1 – Introduction to SQL tuning concepts

1 – Introduction to the course

Review of course topics

Goals of SQL Tuning

Impact of SQL tuning on overall performance

Exercise - Finding SQL tuning tips on the Web

2 – The evolution of Oracle SQL

Navigational vs. declarative database access

Rule-based optimization

Cost-based optimizer

Optimizer plan stability

Cursor Sharing and SQL

Oracle9i ISO 99 Table syntax

Oracle9i New internals

External tables and SQL

10g new SQL tuning features (SQLAccess Advisor, SQLTuning advisor)

Quiz:

3 – Understanding SQL as a database access method

Declarative Syntax

Select, project & join

Distributed SQL

Subqueries

Views and SQL

Exercise – See permutations of SQL for an identical query

4 – Internal processing of SQL statements

Parsing SQL Syntax

SQL Semantic Analysis

Generating the execution plan

Using optimizer plan stability

Using the v$sql view

Using the v$sql_plan view

Exercise – Query the library cache

5 - Oracle SQL optimizer statistics

Using dbms_stats

All about histograms

Dynamic sampling

6 – Exposing SQL data access paths

Using and interpreting explain plan

Using TKPROF

Using SQL*Trace

Using set autotrace in SQL*Plus

Exercise – Create a plan table and view SQL plans

DAY 2 – Introduction to SQL tuning techniques

1 – Changing SQL Execution Plans

Using hints

Changing the system-wide optimizer mode

Changing optimizer mode for specific statements

Re-writing SQL queries

Table join order evaluation

Using the ordered hint

Exercise – Add hints to queries

2 – Database-level SQL Tuning

Inside the library cache – SQL reusability

Avoiding SQL re-parsing - using host variables

Cursor Sharing

Optimizer Plan Stability a.k.a. stored outlines

10g SQL Profiles

Parallel DML

Parallel SQL and partitions

SQL and the data warehouse

Exercise – invoke parallel query

3a – SQL and Boolean expressions

Equality conditions

Using the LIKE, BETWEEN, EXISTS and NOT

Using the decode and CASE operators

Exercise – Using Decode and CASE

3b – Using Built-in Functions (BIF’s)

Basics of BIFs

Function-based indexing

Creating a custom BIF using PL/SQL

The effect of BIFs on SQL execution

Exercise – Create a custom BIF and use it in SQL

4a – Aggregate Functions

Grouping in several levels

Grouping and NULLS

CUBE and ROLLUP

Performance and grouping

Exercise – Using rollup and cube

4b – Tuning SQL subqueries

Correlated subqueries

Non-correlated subqueries

Replacing subqueries with joins

Exercise – Tune a complex query

2 - Optimizing SQL RAM Resources

PGA Overview

SQL Sorting

SQL hash joins

Super-sizing PGA regions

5 – Oracle execution plan costing

The “best” execution plan (first_rows vs. all_rows)

Rule-based optimization

Cost-based optimization

Gathering statistics

Using histograms

When the optimizers fail

Exercise – Change the optimizer mode

6 – Table joining techniques

Sort-merge joins

Nested Loop joins

Hash joins

STAR joins

Bitmap joins

Exercise – Change table join techniques & evaluate performance

DAY 3 – Advanced Oracle SQL tuning

1 – Indexing techniques

B-Tree indexes

Bitmap Indexes

Function-based Indexes

Clustered indexes

Index-only tables

Exercise – Create and use bitmap index

2 – Table structures and SQL Performance

Effect of PCTFREE & PCTUSED on DML

Table high-water mark

Table striping

Table partitioning

Row-re-sequencing and Oracle SQL I/O performance

Oracle9i Online table redefinition

Exercise – res-sequence table rows

3 – Oracle SQL Tuning Techniques- Book Chapter 15, page 551-559

Overview of time-series SQL tuning

SQL execution metrics

Re-writing SQL syntax

Replace SQL with PL/SQL

Adding hints

10g SQLAccess advisor - SQL profiles

11g SQL Performance analyzer (SPA)

4a - Time-series SQL Tuning

Predictive modeling for SQL performance

BSTAT-ESTAT, STATSPACK and AWR

Trending SQL operations (full scans hash joins, &c)

Holistic workload SQL tuning (11g SPA, Quest benchmark factory)

5 – Finding Suboptimal SQL

Evaluating Large-table, full-table scans

Index Usage Analysis

Displaying reports on system-wide SQL execution

Exercise – run access.sql

6 – Oracle SQL Tuning Silver Bullets

Materialized Views to pre-aggregate and pre-join

Adding indexes

Exercise – Create a materialized view

Course wrap-up

Optional SQL tuning topics:

1 – Using object-oriented SQL

Nested Tables

VARRAYS within tables

Using object ID’s in SQL

Using abstract data types (ADTs)

Exercise – Create and use an ADT

Please note that while the knowledge gained from this Oracle training may be valuable when preparing for Oracle certification OCP exam, the content of this course is not for Oracle Certification, including the Certified Professional (OCP) or Oracle Certified Associate (OCA) programs.