Training

How We're Different

Course Summaries

Course Logistics

Client-site Classes

Virtual Classes

FAQ

Cancellation Policy

Request Information

Course Surveys

Oracle SQL Statement Tuning Workshop

Overview

Give your technical staff that competitive edge with this Oracle Workshop. The focus of this tuning course is to illustrate coding techniques that insure a consistent response time between instances and releases of the Oracle database. This course is perfect for developers who need to know more on how Oracle works and how to get their SQL to perform better.  This course is also good for those who are light on SQL tuning or perhaps are new to the Oracle RDBMS.
The course utilizes a variety of current tools.  Students will have the opportunity to learn more about Oracle’s free SQL Developer, TOAD, Eagle Eye, TextPAD, as well as the Oracle tools like SQL*Plus, TKProf, and Stats Pack.

Course Objectives

The course starts out with a complete overview of the Oracle architecture so students can get an understanding how their SQL and applications can take advantage of the computing environment.  This course includes a discussion on the differences of the various Explain Plan steps such as Merge-Join and Nested-Loop, and when is it best to use each.  The instructor goes into considerable detail, with SQL examples, on how the optimizers (both rule and cost) make their decisions.  Students will work with a variety of SQL statements, reviewing explain plans and making changes to make these SQL statements perform better.  Students will also experience how to monitor their application environment, their indexes, and see how to find problem SQL in their applications.
This course includes advanced topics which focus on a variety of parameter settings that make a big difference to Oracle-based applications (such as OPTIMIZER_INDEX_CACHING & INDEX_COST_ADJUSTMENT).  This workshop includes a section on monitoring Oracle RDBMS and SQL via the v$ tables and TOAD.  This course is packed full of scripts to discover a variety of issues.

Topics Covered in the Course Include:

• How Oracle arrives at an Execution Plan
• Index Review/Index Statistics/How Oracle Selects Indexes
• Advanced problem-solving topics using V$ Table information
• In-depth look at Explain Plans
• SQL Trace/Stats Pack
• PL/SQL Tuning/Profiling
• SQL Troubleshooting/Problem solving

Lectures and topics are enhanced with live illustrations and hands-on exercises.

Attendees Receive the Following:

• Study guide with presentations and relevant white papers
• Book: Oracle SQL Tuning Pocket Reference by Mark Gurry
• Diskette full of tuning and problem discovery scripts
• Opportunity to ask the tough Oracle questions
• A hands-on opportunity to learn more about Oracle, SQL Developer, Eagle Eye, and TOAD

Course Agenda:

Day 1: SQL Statement Tuning

• Oracle RDBMS Architecture overview
• Understanding/Reading/Interpreting Explain Plans
• Understanding the Rule-based Optimizer
• Understanding the Cost-based Optimizer
• Working with Hints

Day 2: SQL Troubleshooting Tips and Techniques

• Index Review/Tips & Techniques
• A close look at sub-query techniques
• Tuning Tool Review: Statspack, Events, Tracing/Tkprof
• Troubleshooting SQL
• Profiling and tuning PL/SQL

Day 3: SQL Troubleshooting Tips and Techniques (Advanced Topics)

• Oracle Internals: How Oracle writes
• Various Space Management Issues discussed
• Finding Problem SQL using v$ information
• Tuning the network/Tuning the client
• Troubleshooting SQL
• Tuning by Object Placement

Prerequisites

Delivery