Axigma

Fechas y Registro

Fecha:

11 de enero del 2010 al 13 de enero del 2010

Horario

de 9 a 17 hs.

Duración del curso

3 días

Lugar

Buenos Aires, Argentina

Para más información:

eva.lupi@axigmatechnologies.com

Database 10g: SQL Tuning

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

Audience

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

Prerequisites

Required Prerequisites

Suggested Prerequisites

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