Oracle Database 10g: SQL Tuning (Self-Study Course)
Who should attend
- Java developers
- Forms developers
- PL/SQL developers
- Technical consultants
Course Objectives
- Describe Automatic SQL Tuning
- Describe the basic steps in processing SQL statements
- Describe the causes of performance problems
- Influence the optimizer behavior
- Influence the physical data model so as to avoid performance problems
- Understand Optimizer behavior
- Understand where SQL tuning fits in an overall tuning methodology
- Use the diagnostic tools to gather information about SQL statement processing
Product Description
- 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
- Using 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
