Overview
This course is designed to give students a foundation in SQL statement tuning. They are provided the necessary knowledge and skills to effectively tune SQL statements against the Oracle9i server. Students will focus on the Oracle Cost-Based Optimizer (CBO), there is minimal explanatory coverage of the Rule-Based Optimizer. The students learn to use the Oracle diagnostic tools and facilities: EXPLAIN, SQL Trace and TKPROF, SQL*Plus AUTOTRACE, and other tools. In addition, the participants also learn to influence the behavior of the CBO by changing SQL statement syntax, using hinting and modifying physical database objects. The course content is applicable to users of Oracle 8 and 8i as well.
Audience
Experienced Oracle developers and application development support DBAs.
Prerequisites
3 to 6 months of experience writing SQL in Oracle version 9 or 10.
Knowledge of SQL join syntax.
Course Topics
Introduction to tuning
Oracle's SQL tools
SQL statement processing
The Oracle optimizers
Effective indexes
Using the EXPLAIN PLAN
SQL*Plus tuning tools
Collecting statistics
Influencing the optimizer
Using SQL Trace and TKPROF
Optimizing sorts and joins
Creating histograms
Optimizer plan stability
Using private outlines
Optimizing PL/SQL
Currency and consistency
Appendix A - The rule based optimizer
Topic Contents
Oracle’s SQL tools
SQL*Plus
iSQL*Plus
SQL*Worksheet
Introduction to Tuning
Describing Causes of Performance Problems
Identifying Performance Problems
Using a Tuning Methodology
Listing Steps to Tune SQL Statements
Introduction to Indexes
Identifying Row Access Methods
Creating B*-Tree Indexes
Understanding B*-Tree Index Access and Index Merging
RBO Versus CBO
Identifying Rule-Based Optimization
Identifying Cost-Based Optimization
Understanding Execution Plan Cost
SQL Statement Processing
Listing the SQL Statement Processing Steps
Identifying Means to Minimize Parsing
Stating the Use of Bind Variables
Using the EXPLAIN PLAN utility
Creating a PLAN table
Using the EXPLAIN PLAN command
Interpreting EXPLAIN Output
Using SQL*Plus AUTOTRACE and TIMING
Using statement TIMING
Invoking the SQL Trace Facility
Setting Up Appropriate Initialization Parameters
Interpreting AUTOTRACE Statistics
Understanding the Cost Based Optimizer
Optimization mode
Version specific optimization (OPTIMIZER_FEATURES_ENABLE)
Collecting Statistics
Using the ANALYZE Command
Identifying Table, Column, and Index Statistics
Using the DBMS_STATS Package
Building histograms
Collecting index statistics
TKPROF
Formatting Trace Files with TKPROF
Interpreting the Output of the TKPROF Command
Influencing the Optimizer
Using the ALTER SESSION Command
Using Hints/li>
Sorting and Joining
Sorting Guidelines
Nested Loops Joins
Sort/Merge Joins
Outer Joins
Hash Joins
Optimizer Plan Stability
Purpose and Benefits of Optimizer Plan Stability
Creating Stored Outlines
Using system statistics
What are system statistics
How are system statistics used
Gathering system statistics
Advanced Indexes
Creating Bitmap Indexes
Creating Bitmap-Join Indexes
Creating Function-Based Key Indexes
Creating Reverse Key Indexes
Currency and consistency
Statement level read consistence
Transaction level read consistency
Read only transactions
Oracle locking
Working with the Rule Based Optimizer
What are the rules
