Oracle 9i: SQL Tuning Workshop

Popis kurzu

Co se naučíte
This course is designed to give the student a firm foundation in the art of SQL tuning. The participant learns the necessary knowledge and skills to effectively tune SQL against the Oracle9i Server. Students gain a thorough conceptual understanding of the Rule-Based and Cost-Based Optimizer. The course uses a series of challenge-level workshops, allowing students to "play, discover, and learn" at their own level and pace. The students learn to use the Oracle diagnostic tools and facilities: EXPLAIN, SQL Trace and TKPROF, SQL*Plus AUTOTRACE. Students also learn to influence the behavior of the Optimizer by changing the physical schema and modifying SQL statement syntax.

Posluchači
Technical Consultant

Cíle kurzu
Learn where SQL tuning fits in an overall tuning methodology
Use the diagnostic tools to gather information about SQL statement processing
Describe alternative methods of accessing data
Influence the physical data model so as to avoid performance problems
Describe the basic steps in processing SQL statements
Learn Rule-Based Optimizer (RBO) and Cost-Based Optimizer (CBO) behavior
Describe the causes of performance problems
Influence the optimizer behavior

Témata kurzu

Following a Tuning Methodology
Describing the Causes of Performance Problems
Identifying Performance Problems
Using a Tuning Methodology
Listing the Steps to Tune SQL

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

EXPLAIN and AUTOTRACE
Using the EXPLAIN PLAN Command
Identifying the AUTOTRACE Syntax
Interpreting EXPLAIN Output
Interpreting AUTOTRACE Statistics

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

Cost-Based Optimization Versus Rule-Based Optimization
Identifying Rule-Based Optimization
Identifying Cost-Based Optimization
Understanding the Cost of an Execution Plan

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

Gathering Statistics
Using the DBMS_STATS Package
Identifying Table, Column, and Index Statistics
Building Histograms
Using the ANALYZE Command

Influencing the Optimizer
Setting Up Appropriate Initialization Parameters
Using the ALTER SESSION Command
Using Hints

Sorting and Joining
Sorting Guidelines
Using Top-N SQL
Nested Loops JoinsSort/Merge Joins
Outer Joins
Star Joins
Hash Joins

Optimizer Plan Stability
Purpose and Benefits of Optimizer Plan Stability
Creating Stored Outlines
Using the OUTLN_PKG Package

Advanced Indexes
Creating Bitmapped Indexes
Creating Function-Based Key Indexes
Optimizing Star Joins with Star Transformation

Materialized Views and Temporary Tables
Using the CREATE MATERIALIZED VIEW Syntax
Utilizing Query Rewrites
Creating and Using Temporary Tables

Alternative Storage Techniques
Creating Index-Organized Tables
Creating Index Clusters
Creating Hash Clusters

SQL Analyze
Using SQL Analyze for SQL Tuning