Oracle Database SQL Tuning

This Oracle Database: SQL Tuning training is designed to give the experienced SQL Developer or DBA a firm foundation in SQL tuning techniques. You’ll develop the necessary knowledge and skills to effectively tune SQL in the Oracle Database 10g Release 2.

Learn To:

  • Understand the benefits of the new automatic tuning mechanisms available in Oracle Database 10g.
  • Compare and contrast the steps involved to tune manually as in prior releases.
  • Use the automatic SQL tuning features provided in the current release.
  • Use the Oracle diagnostic tools and facilities: Automatic SQL Tuning components, EXPLAIN, SQL Trace and TKPROF, SQL*Plus AUTOTRACE.
  • Influence the behavior of the Optimizer by changing the physical schema and modifying SQL statement syntax.
  • Understand tuning methodology, as well as proactive tuning and reactive tuning methods.

Benefits to You

Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling smooth and rapid consolidation within your Datacenter.

Explore the Oracle Optimizer

You’ll also gain a thorough conceptual understanding of the Oracle Optimizer. This new knowledge will be reinforced as you participate in structured hands-on practices. This uses a series of challenge-level workshops, allowing you to “play, discover and learn” at your own level and pace.

Course Topics

  • Overview of Database architecture
  • Listing the SQL Statement Processing Steps
  • Identifying Means to Minimize Parsing
  • Stating the Use of Bind Variables
  • Describing the Causes of Performance Problems
  • Identifying Performance Problems
  • Using a Tuning Methodology
  • Oracle Methodology
  • Understanding Scalability
  • System Architecture
  • Application Design Principles
  • Deploying New Applications
  • Describe the functions of the Oracle optimizer
  • Identify the factors that the optimizer considers when it selects an execution plan
  • Set the optimizer approach at the instance and session level
  • Use dynamic sampling
  • Execution plans
  • Types of Joins
  • Using the EXPLAIN PLAN Command
  • Interpreting EXPLAIN Output
  • Interpreting AUTOTRACE Statistics
  • Using the DBMS_STATS Package
  • Identifying Table, Column, and Index Statistics
  • Building Histograms
  • Statspack
  • End to End tracing
  • Invoking the SQL Trace Facility
  • Setting Up Appropriate Initialization Parameters
  • Formatting Trace Files with TKPROF
  • Interpreting the Output of the TKPROF Command
  • Use different methods to identify high-load SQL
  • ADDM
  • Top SQL
  • Dynamic Performance views
  • Statspack
  • Query Optimizer Modes
  • Types of Tuning Analysis
  • SQL Tuning Advisor
  • SQL Tuning Sets
  • Top SQL
  • Identifying Row Access Methods
  • Creating B-Tree Indexes
  • Understanding B-Tree Index Access and Index Merging
  • Using Bitmapped Indexes
  • Using Function-Based Key Indexes
  • Using Hints
  • Purpose and Benefits of Optimizer Plan Stability
  • Using the CREATE MATERIALIZED VIEW Syntax
  • Utilizing Query Rewrites

Course Objectives

  • Describe the basic steps in processing SQL statements
  • Describe the causes of performance problems
  • Understand where SQL tuning fits in an overall tuning methodology
  • Describe Automatic SQL Tuning
  • Use the diagnostic tools to gather information about SQL statement processing
  • Understand Optimizer behavior
  • Influence the optimizer behavior
  • Influence the physical data model so as to avoid performance problems