Oracle SQL Performance Tuning for Developers

      This hands-on course provides students with the skills necessary to tune SQL statements. Attention is paid to understanding Oracle architecture, rules based and cost based optimization. The following tuning tools are covered in detail: explain plan, TKPROF and autotrace. A detailed discussion of indexes is provided so that the proper index type is chosen for a given scenario.

Topics Covered

  • Introduction to SQL Tuning
  • Oracle Architecture Overview
    • SGA
    • PGA
    • Shared Pool
  • SQL Statement Processing
    • Soft Parse
    • Hard Parse
    • Execute Step
    • Fetch Step
  • Tuning Tools
    • Explain Plan
    • Autotrace
    • TKPROF
  • Bind Variables
  • Cost Based Optimization
    • Table Statistics
    • Column Statistics
    • Index Statistics
    • Histograms
    • Dynamic Sampling
  • Using Hints
  • Indexes
    • B-Tree
    • Bitmap
    • Bitmap Join
    • Function Based
    • Reverse Key
  • Tables
    • Heap Tables
    • Index Organized Tables
    • Materialized Views
    • Global Temporary Tables
  • Join and Subquery Execution Plans
    • Hash Joins
    • Nested Loops Joins
    • Sort Merge Joins
    • Join Order
  • Tuning Views
    • Mergeable Views
    • Non-mergeable Views
    • Pushable Views
    • Inline Views
  • Impact of Reformulating SQL Statements
    • Exists versus Distinct
    • Not In versus Not Exists
    • Union versus Union All
    • Analytic Functions
  • PL/SQL Tuning
    • Bulk Collect
    • Bulk Bind and FORALL
    • Pipelined Table Functions
    • Reference Cursors

Audience:

     Oracle application developers and database administrators.

Prerequisites:

Benefits:

      Students will gain the ability to improve the performance of applications that depend heavily on access to an Oracle database by testing various SQL possibilities and choosing the most efficient option.

Furnished Materials:

  • PC Workstation for use during training
  • Student Manual

Recommended Follow-On Course

Length:

  • 3 Days

Price:

  • $1,485

Training Dates:

Sign Up