Free Oracle Tips

HTML Text

Home
E-mail Us
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

Privacy Policy

Blog

Golf Travel

Oracle SQL Tuning Training Course

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

* Understand how to read an execution plan.

* Understand the importance of schema statistics.

* See how to test SQL execution speed.

* Learn the internal table join methods.

* Learn to detect unnecessary full-table scans.

* See how to quickly identify sub-optimal SQL.

* Use hints to change SQL execution.

* Tune SQL with stored outlines.

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

Advanced Oracle SQL tuning is an intensive three-day, four-day, or five-day course designed to provide Oracle professionals with an in-depth understanding of Oracle SQL tuning and how to formulate and tune SQL statements for optimal performance. Burleson Consulting instructors offer decades of real world DBA experience in Oracle features, and they will share their secrets in this intense Oracle SQL tuning training.

Oracle SQL Tuning & CBO Internals

Kimberly Floss, Rampant Tech Press

ISBN 0-9745993-3-6

Optional SQL Tuning book:

Oracle Tuning: The Definitive Reference

Donald K. Burleson, Rampant Tech Press

ISBN 0-9744486-2-1

This course is designed for practicing Oracle professionals 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 Consulting 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 formulate advanced SQL queries including 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.

Advanced Oracle SQL Tuning
Three Day Syllabus

Internal processing of SQL statements

Parsing SQL Syntax - (page 53-57)
SQL Semantic Analysis - (page 56)
Generating the execution plan - (page 181-185)
Using optimizer plan stability - (page 309-332)
Using the v$sql view - (page 57)
Using the v$sql_plan view
Exercise – Query the library cache

Viewing SQL execution Internals

Using and interpreting explain plan - (page 310)
Using TKPROF - (page 205)
Using SQL*Trace - (page 200)
Using set autotrace in SQL*Plus
Exercise – Create a plan table and view SQL plans

Inside the Oracle Optimizers

The “best” execution plan (first_rows vs. all_rows) - (page 129-130)
Rule-based optimization - (page 129)
Cost-based optimization - (page 334)
Gathering statistics - (page 340)
Using histograms - (page 279, 305)
When the optimizers fail - (page 85)
Exercise – Change the optimizer mode

Table joining internals

Sort-merge joins - (page 341)
Nested Loop joins - (page 401)
Hash joins - (page 390-392)
STAR joins - (page 401)
Bitmap joins - (page 291)
Exercise – Change table join techniques & evaluate performance

Oracle Index Optimization

B-Tree indexes - (page 233)
Bitmap Indexes - (page 80, 127)
Function-based Indexes - (page 36, 61)
Clustered indexes - (page 87)
Index-only tables - (page 468)
Exercise – Create and use bitmap index

Object structures and SQL

Effect of PCTFREE & PCTUSED on DML - (page 152-155)
Table high-water mark - (page 130)
Table striping - (page 515)
Table partitioning - (page 517)
Row-resequencing - (page 141-143)
Oracle9i Online table redefinition
Exercise – resequence table rows

Locating poor execution plans

Evaluating Large-table, full-table scans - (page 38, 59)
Index Usage Analysis - (page 61)
Reports on system-wide SQL execution - (page 220-225)
Exercise – run access.sql

Altering SQL execution plans

Using hints - (page 87, 277)
Changing the system-wide optimizer mode - (page 92)
Changing optimizer mode for specific statements - (page 10)
Re-writing SQL queries - (page 54, 314)
Table join order evaluation - (page 88)
Using the ordered hint - (page 249)
Exercise – Add hints to queries

DBA tuning for SQL

Inside the library cache – (page 222-229)
Avoiding SQL re-parsing - (page 223)
Cursor Sharing - (page 54-55)
Optimizer Plan Stability (stored outlines) - (page 310-317)
Parallel DML - (page 441)
Parallel SQL and partitions - (page 517)
SQL and the data warehouse
Exercise – invoke parallel query

SQL Aggregate Functions

Grouping in several levels
Grouping and NULLS
CUBE and ROLLUP
Performance and grouping
Exercise – Using rollup and cube

Tuning SQL sub-queries

Correlated subqueries - (page 342)
Non-correlated subqueries - (page 342)
Replacing subqueries with joins - (page 385)
Exercise – Tune a complex query

Expert SQL Tuning Secrets

Materialized Views to pre-aggregate and pre-join - (page 54)
Adding indexes - (page 62)
Exercise – Create a materialized view

Advanced Oracle SQL Tuning
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-resequencing and Oracle SQL I/O performance

Oracle9i Online table redefinition

Exercise – resequence 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 exams, the content of this course is not for Oracle Certification, including the Certified Professional (OCP) or Oracle Certified Associate (OCA) programs.