Optimizing Oracle SQL, Intensive

Oregon Health & Science University
Beaverton, Oregon, United States

Course Format: Classroom | E-learning | Virtual Class | Online | On-site | Blended | Self-paced

Course Description:

'' This class is a 5-day intensive combination of both the Optimizing Oracle SQL, Part I and Part II courses. Focus is directed towards the optimizer and understanding how it behaves when developing query execution plans. The importance of optimizing SQL at the resource usage level is learned and the Hotsos SQL Test Harness and Tools Pack scripts are used to facilitate the comparison of multiple approaches to writing SQL statements to discover the "performance optimal" choice among several approaches. Different approaches to writing high performance SQL are demonstrated as well as review of database design elements that can affect performance.

Target Audience
The course is designed for application developers and database administrators who want to respond faster and more permanently to Oracle system performance problems that are known to be caused by inefficient application SQL code.

The course is most effective for students who will be motivated to solve real Oracle system performance problems immediately upon returning to work after the course.

Synopsis This course is intended to teach students a repeatable and reliable method for optimizing SQL and to assimilate the new habit of testing optimization hypotheses rapidly during the performance improvement process. Instead of relying on "rules of thumb" and hit-and-miss guessing, this course helps the student understand the inner workings of the Oracle optimizer and how SQL execution paths are determined. Particular emphasis is placed on discovering how to capture, read, and understand execution plans and how to use 10053 trace data to follow the optimizer’s plan selection. The student’s attention is focused not upon rote learning and superficial understanding of Oracle rules of thumb, but upon a thorough understanding how the Oracle database works.

A provided set of scripts (called the Hotsos SQL Test Harness) is used to teach the student a method of determining the actual resource impact of a SQL statement. Using the methods taught in class, students leave with the tools and information needed to make informed and more accurate decisions when attempting to optimize SQL.

Lab exercises allow the student to test and verify each concept and to prove for themselves how and why statements perform as they do. The course provides an overview of using 10053 trace data to follow the steps considered by the optimizer to determine the execution plan. Students find that understanding the optimizer?s decisions is often the most important step in learning how to write efficient SQL.

The course is not just about learning how to write SQL. The course is about arming the student with the skills to be able to scientifically determine the efficiency of any SQL statement. While the course does offer alternative ways to write SQL in many cases, the primary objective is to equip the student to determine whether a statement is optimal and to achieve a deeper understanding of the cost-based optimizer. This course presents problem-solving strategies that can be carried forward directly to common problems and customized for solving less common ones. The result is a fortified foundation for optimizing SQL in ever more challenging situations.

Instructional Format
The five-day course is approximately 60% instructor-led lecture and discussion, 40% in-class exercises on provided computers. Each computer is configured to provide a stand-alone Oracle unit testing environment in which to complete lab exercises and conduct experiments.

The instructors place a special emphasis on teaching students to think and test for themselves instead of relying on widely believed but unfortunately unreliable rules of thumb.

The course is conducted in English.
Course Outline The course is organized into the following segments:

Introduction

Course welcome, instructor and student introductions.

Fundamentals

Course unit testing environment

Terms and definitions: LIO, PIO, latching, buffer cache, library cache, etc.

Fundamentals of testing: Understanding and measuring statement resource consumption

The Hotsos SQL Test Harness: How to install and use to measure and store test results

SQL statement execution fundamentals: PARSE, EXECUTE, BIND, FETCH, arraysize issues, etc.

Tools for SQL Statement Problem Diagnosis and Repair: statistics report, block and row selectivity report, execution plans

Oracle 10046 and 10053 trace data collection and analysis

The Optimizer

Oracle query optimizer technologies (RBO, CBO)

Optimizer query transformation behaviors

view merging

subquery unnesting

predicate pushing

Optimizer parameters

Optimizer hints

Stored outlines

Statistics collection

Managing and manipulating statistics

Selectivity

Histograms

Join mapping

Access methods

Compare and contrast access methods: table access full, access by rowid

Index scan type overview: index unique scan, index range scan, index skip scan, index fast full scan, index full scan, index joins

Join methods

Compare and contrast join methods: nested loops, hash join, sort/merge join, and cartesian

Review conditions under which each method offers best and worst performance

Execution plans

Capturing and displaying execution plans

Review dynamic views associated with execution plans: V$SQL_PLAN,
V$SQL_PLAN_STATISITICS, etc.

How to read and analyze execution plans

Creating and using plan tree diagrams

Using Indexes Effectively

Coding techniques that may prevent index use:

column expressions

implicit datatype conversion

indexing null values

impact of composite key order

Indexes and Performance

Estimating the "cost" of indexes (what are the performance effects of adding indexes?)

Function-based indexes

Index-organized tables

Bitmap indexes

Writing SQL "Right"

IN-Lists: how they work and alternatives

Expressions using CASE, COALESCE and NULLIF

ANTI and SEMI joins

Scalar subqueries

WITH clause subquery factoring

Using ROWNUM

Conclusion

Case study review, perspective, course wrap-up, graduation.

Prerequisites

Students are expected to have a working knowledge of Oracle SQL through experience either as a database administrator or application developer.

...''

Phone:(503) 748-1121

School Address:

Center for Professional Development
OGI School of Science & Engineering
20000 NW Walker Road
Beaverton, OR 97006 USA

Jobs & Resumes: Beaverton
Houses & Roommates: Beaverton

UML
Programming with Microsoft .NET Framework (C# .NET)
Agile Project Planning with Scrum
AJAX Development with .NET
.NET Debugging
Developing Web Applications with ASP.NET 2.0
Perl
Engineering Software Quality
Developing Web Service Clients with Java
Writing Use Cases
Windows Presentation Foundation (WPF)

Notice: The course description on this page was captured from the Internet as historical reference or submitted by visitors. It was archived statically and not updated from day to day.
 

Tips: Our combined search function does not only provide you with the training courses and students, but also talent resumes and jobs, or shared apartments nearby for rent.