Tuning para Desarrolladores Oracle10g

OBJETIVOS:

Describir los pasos básicos en el proceso de las sentencias SQL Describir las causas de los problemas más comunes Comprender cómo seguir la metodología del tuning Comprender el Automatic SQL Tuning Recopilación de información sobre las sentencias SQL El Optimizador, su influencia en el comportamiento de las sentencias. Cómo afecta el modelo de datos.

CONTENIDOS

Modulo 1. Following a Tuning Methodology

Describing the Causes of Performance Problems

Identifying Performance Problems

Using a Tuning Methodology

Listing the Steps to Tune SQL

Modulo 2. SQL Statement Processing

Describe the basic steps involved in processing a SQL statement

Monitor the use of shared SQL areas

Write SQL statements to take advantage of shared SQL areas

Understand how to use the CURSOR_SHARING parameter

Use automatic PGA memory management

Modulo 3. EXPLAIN and AUTOTRACE

Using the EXPLAIN PLAN Command

Identifying the AUTOTRACE Syntax

Interpreting EXPLAIN Output

Interpreting AUTOTRACE Statistics

Modulo 4. SQL Trace and TKPROF

Invoking the SQL Trace Facility

Setting Up Appropriate Initialization Parameters

Formatting Trace Files with TKPROF

Interpreting the Output of the TKPROF Command

Modulo 5. SQL Analyze

Using SQL Analyze for SQL Tuning

Modulo 6. Introduction to the Optimizer

Cost-Based Optimization

Optimizing for Fast Response

Choosing an Access Paths

Rule-Based Optimization

Modulo 7. Indexes and Basic Access Methods

Identifying Row Access Methods

Creating B*-Tree Indexes

Understanding B*-Tree Index Access and Index Merging

Modulo 8. Gathering Statistics

Using the DBMS_STATS Package

Identifying Table, Column, and Index Statistics

Building Histograms

Modulo 9. Influencing the Optimizer

Setting Up Appropriate Initialization Parameters

Using the ALTER SESSION Command

Using Hints

Modulo 10. Sorting and Joining

Sorting Guidelines

Using Top-N SQL

Nested Loops Joins Sort/Merge Joins

Outer Joins

Star Joins

Hash Joins

Modulo 11. Optimizer Plan Stability

Purpose and Benefits of Optimizer Plan Stability

Creating Stored Outlines

Outline: Administration and Security

Modulo 12. Advanced Indexes

Creating Bitmapped Indexes

Creating Function-Based Key Indexes

Optimizing Star Joins with Star Transformation

Modulo 13. Materialized Views and Temporary Tables

Using the CREATE MATERIALIZED VIEW Syntax

Utilizing Query Rewrites

Creating and Using Temporary Tables

Modulo 14. Alternative Storage Techniques

Creating Index-Organized Tables

Creating External Tables