Oracle 10g Database SQL Tuning Workshop

Audience:

• Forms Developer
• PL/SQL Developer
• Technical Consultant
• Reports Developer
• Business Intelligence Developer
• Java Developer

Required Prerequisites:

• Oracle Database 10g: SQL Fundamentals I

Suggested Prerequisites:

• Oracle Database 10g: PL/SQL Fundamentals

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

Course Topics:

Database Architecture overview

• Overview of Database architecture
• Listing the SQL Statement Processing Steps
• Identifying Means to Minimize Parsing
• Stating the Use of Bind Variables

Following a Tuning Methodology

• Describing the Causes of Performance Problems
• Identifying Performance Problems
• Using a Tuning Methodology

Designing Applications for performance

• Oracle Methodology
• Understanding Scalability
• System Architecture
• Application Design Principles
• Deploying New Applications

Introducing the optimizer

• 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

Optimizer Operations

• Execution plans
• Types of Joins

Displaying Execution plans

• Using the EXPLAIN PLAN Command
• Interpreting EXPLAIN Output
• Interpreting AUTOTRACE Statistics

Gathering Statistics

• Using the DBMS_STATS Package
• Identifying Table, Column, and Index Statistics
• Building Histograms

Application Tracing

• 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

Identifying High Load SQL

• Use different methods to identify high-load SQL
• ADDM
• Top SQL
• Dynamic Performance views
• Statspack

Automatic SQL Tuning

• Query Optimizer Modes
• Types of Tuning Analysis
• SQL Tuning Advisor
• SQL Tuning Sets
• Top SQL

Introduction to Indexes

• Identifying Row Access Methods
• Creating B-Tree Indexes
• Understanding B-Tree Index Access and Index Merging

Advanced Indexes

• Using Bitmapped Indexes
• Using Function-Based Key Indexes

Optimizer Hints and Plan Stability

• Using Hints
• Purpose and Benefits of Optimizer Plan Stability

Materialized Views and Temporary Tables

• Using the CREATE MATERIALIZED VIEW Syntax
• Utilizing Query Rewrites

<<Back