Detailed Course Outline
Introduction
- Workshops, Demo Scripts, and Code Example Scripts
- SQL Environments Available in the Course
- Course Objectives, Course Agenda and Appendixes Used in this Course
- Sample Schemas Used in the Course
- Class Account Information
- Appendices in the Course
- Audience and Prerequisites
Introduction to SQL Tuning
- SQLTXPLAIN (SQLT) Diagnostic Tool
- Development Environments: Overview
- SQL Tuning Session
- SQL Tuning Strategies
Using Application Tracing Tools
- Using the SQL Trace Facility: Overview
- The trcsess Utility
- Formatting SQL Trace Files: Overview
- Available Tracing Tools: Overview
- Steps Needed Before Tracing
Understanding Basic Tuning Techniques
- Developing Efficient SQL statement
- Index Usage
- Data Type Mismatch
- Table Design
- Scripts Used in This Lesson
- Transformed Index
- Tune the ORDER BY Clause
- NULL usage
Optimizer Fundamentals
- Why Do You Need an Optimizer?
- Query Transformer
- SQL Statement Processing
- Optimizer Features and Oracle Database Releases
- Cost-Based Optimizer
- SQL Statement Representation
- Components of the Optimizer
- Adaptive Query Optimization
Generating and Displaying Execution Plans
- DBML_SQL_MONITOR
- V$SQL_PLAN View
- AUTOTRACE
- The EXPLAIN PLAN Command
- Plan Table
- Execution Plan?
- SQL Monitoring
- Automatic Workload Repository
Interpreting Execution Plans and Enhancements
- Optimizer: Table and Index Access Paths
- Full Table Scan
- Common Observations
- Row Source Operations
- Indexes
- Adaptive Optimizations
- Main Structures and Access Paths
- Interpreting a Serial Execution Plan
Optimizer Join Operations
- Join Methods
- Join Types
Other Optimizer Operators
- SQL operators
- Result Cache operators
- Other N-Array Operations
Introduction to Optimizer Statistics Concepts
- Gather and Manage Optimizer Statistics: Overview
- Optimizer Statistics
- Types of Optimizer Statistics
Using Bind Variables
- Cursor Sharing and Bind Variables
- Cursor Sharing and Different Literal Values
SQL Plan Management
- Maintaining SQL Performance
- SQL Plan Management