Verhoef Training USA

Training Courses and On-Line Classes Preferred By IT Pros

Visit us at:   http://www.verhoef-training.com

103 Hillside Ave., PO Box 1515
West Caldwell, NJ 07006
Main Office:  800.631.0410
Sales Office:  800.533.3893

Oracle 10g/11g SQL Statement Tuning

Course:   OROPT
Duration:   3 Days
Level:   II
On our website at:   http://www.verhoef-training.com/courses/OROPT.html
 
Course Summary

This course is designed to give students a foundation in SQL statement tuning. They are provided the necessary knowledge and skills to effectively tune SQL statements against the Oracle10g and 11g server. Students will focus on the Oracle Cost-Based Optimizer (CBO). The students learn to use the Oracle diagnostic tools and facilities: EXPLAIN, SQL*Plus AUTOTRACE, and other tools. Gathering and utilizing object and system statistics is covered in detail. In addition, the participants also learn to influence the behavior of the CBO by hinting and modifying physical database objects. The course content is largely applicable to users of Oracle 9i as well.

Topics Covered In This Course

Oracle???s SQL tools

  • SQL*Plus
  • iSQL*Plus

The Petsaver database

  • Understanding the sample database

Introduction to tuning

  • Kinds of performance problems
  • Methods to measure performance
  • Techniques to improve SQL performance

SQL statement processing

  • Understanding SQL statement processing steps

The Oracle optimizers

  • Rule based
  • Cost based
  • Version specific optimization (OPTIMIZER_FEATURES_ENABLE)

Optimizing SHARED_POOL utilization

  • Identifying ways to minimize parsing
  • Using bind variables
  • Using PL/SQL packages

Effective Indexing

  • Creating B*-Tree indexes
  • Utilizing "super" indexes and index merging
  • Indexes in the data dictionary
  • Monitoring index usage

Using the EXPLAIN PLAN utility

  • Creating a PLAN table
  • Using the EXPLAIN PLAN command
  • Interpreting EXPLAIN PLAN output
  • Understanding row access methods

SQL*Plus tuning tools

  • Using statement TIMING
  • Invoking the SQL Autorace Facility
  • Interpreting AUTOTRACE Statistics

Collecting Statistics

  • Using the ANALYZE Command
  • Using the DBMS_STATS Package

Creating histograms

  • Understanding histograms
  • Creating histograms
  • Verifying histogram usage

TKPROF

  • Prerequisites for TKPROF
  • Formatting trace files with TKPROF
  • Interpreting TKPROF output

Choosing the driving table in JOINS

  • Creating the query diagram
  • Rules to choose the driving table

Using Hints to influence the Optimizer

  • Using Hints
  • Optimizer goal hints
  • Access method hints
  • Join hints
  • Other hints

Optimizing sorts

  • When does Oracle sort
  • Sorting efficiently

Advanced Indexes

  • Creating bitmap indexes
  • Creating function-based Indexes
  • Creating reverse key Indexes

Optimizing PL/SQL

  • The RETURN clause
  • Using DBMS_PROFILER to measure PL/SQL execution
  • Using PL/SQL hinting
  • BULK operations
  • Dynamic SQL
  • Native PL/SQL compilation
  • Using DBMS_SHARED_POOL
Who Should Take This Course

Experienced Oracle developers and application development support DBAs.

Recommended Prerequisites

3 to 6 months of experience writing SQL in Oracle version 10 or 11.

Knowledge of SQL join syntax.

Training Style

Lecture with frequent hands on labs.

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.