Más información

Contacto: Eva Lupi.
Email: elupi@axigmatechnologies.com
Teléfono: +54 11 5355 2900 de 10 a 17 hs.

Fechas y Registro

Fecha:

15 de noviembre del 2010 al 17 de noviembre del 2010

Horario

- 9 a 17 hs-

Duración del curso

3 días

Lugar

Capital Federeal, Buenos Aires, Argentina

Para más información:

Eva Lupi : elupi@axigmatechnologies.com

Database 11g: SQL Tuning

Course Objectives

  • Identify problem SQL statements
  • Modify a SQL statement to perform at its best
  • Trace an application
  • Understand how the Query Optimizer makes decisions about how to access data
  • Interpret execution plans
  • Use optimizer hints effectively
  • Generate a load test

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

  • Oracle Database Server Architecture: Overview
  • Oracle Database Memory Structures: Overview
  • Background Process Roles
  • Automatic Shared Memory Management
  • Automated SQL Execution Memory Management
  • Automatic Memory Management
  • Database Storage Architecture
  • Logical and Physical Database Structures

Introduction to SQL Tuning

  • Reasons for Inefficient SQL Performance
  • Performance Monitoring Solutions
  • Monitoring and Tuning Tools: Overview
  • EM Performance Pages for Reactive Tuning
  • CPU and Wait Time Tuning Dimensions
  • Scalability with Application Design, Implementation, and Configuration
  • Common Mistakes on Customer Systems
  • Proactive Tuning Methodology

Introduction to the Optimizer

  • Structured Query Language
  • SQL Statement Representation, Implementation & Processing: Overview
  • SQL Statement Parsing: Overview
  • Why Do You Need an Optimizer?
  • Optimization During Hard Parse Operation
  • Cost-Based Optimizer
  • Controlling the Behavior of the Optimizer
  • Optimizer Features and Oracle Database Releases

Optimizer Operators

  • Row Source Operations
  • Main Structures and Access Paths
  • Full Table Scan
  • Indexes: Overview
  • Using Indexes: Considering Nullable Columns
  • Bitmap Indexes, Composite Indexes &Invisible Index
  • Guidelines for Managing Indexes
  • Clusters

Interpreting Execution Plans

  • Execution Plan
  • Links Between Important Dynamic Performance Views
  • Automatic Workload Repository (AWR)
  • Generating SQL Reports from AWR Data
  • SQL Monitoring: Overview
  • Reading More Complex Execution Plans
  • Reviewing the Execution Plan
  • Looking Beyond Execution Plans

Case Study: Star Transformation

  • The Star & Snowflake Schema Model
  • Execution Plan Without Star Transformation
  • Retrieving Fact Rows from One Dimension All Dimensions
  • Joining the Intermediate Result Set with Dimensions
  • Using Bitmap Join Indexes
  • Star Transformation
  • Bitmap Join Indexes

Optimizer Statistics

  • Optimizer Statistics & Types of Optimizer Statistics
  • Multicolumn Statistics: Overview
  • Expression Statistics: Overview
  • Gathering System Statistics
  • Statistic Preferences: Overview
  • Optimizer Dynamic Sampling: Overview
  • Locking Statistics

Using Bind Variables

  • Cursor Sharing and Different Literal Values
  • Cursor Sharing and Bind Variables
  • Bind Variables in SQL*Plus & Enterprise Manager
  • Cursor Sharing Enhancements
  • Adaptive Cursor Sharing: Overview
  • Interacting with Adaptive Cursor Sharing

Using Optimizer Hints

  • Optimizer Hints: Overview
  • Types of Hints
  • Specifying Hints
  • Rules for Hints
  • Hint Recommendations
  • Hint Categories
  • Optimization Goals and Approaches
  • Additional Hints

Application Tracing

  • End-to-End Application Tracing Challenge
  • Location for Diagnostic Traces
  • What Is a Service?
  • Use Services with Client Applications
  • Trace Your Own Session
  • SQL Trace File Contents
  • Formatting SQL Trace Files: Overview
  • Invoking the tkprof Utility

Automating SQL Tuning

  • Tuning SQL Statements Automatically
  • Application Tuning Challenges
  • SQL Tuning Advisor: Overview
  • Stale or Missing Object Statistics
  • SQL Statement Profiling
  • Plan Tuning Flow and SQL Profile Creation
  • Database Control and SQL Tuning Advisor
  • Implementing Recommendations