Oracle Database: SQL Tuning for Developers Ed 2

Suggested Prerequisite

• Knowledge of SQL and PL/SQL
• Familiarity with database architecture

Required Prerequisite

• Oracle Database: Introduction to SQL Ed 2
• Oracle Database: SQL Workshop I Ed 2

Audience

• Application Developers
• Developer
• Support Engineer
• Data Warehouse Developer
• PL/SQL Developer

Course Topics

1. 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

2. Introduction to SQL Tuning

• SQLTXPLAIN (SQLT) Diagnostic Tool
• Development Environments: Overview
• SQL Tuning Session
• SQL Tuning Strategies

3. 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

4. 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

5. 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

6. 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

7. 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

8. Optimizer Join Operations

• Join Methods
• Join Types

9. Other Optimizer Operators

• SQL operators
• Result Cache operators
• Other N-Array Operations

10. Introduction to Optimizer Statistics Concepts

• Gather and Manage Optimizer Statistics: Overview
• Optimizer Statistics
• Types of Optimizer Statistics

11. Using Bind Variables

• Cursor Sharing and Bind Variables
• Cursor Sharing and Different Literal Values
• 12. SQL Plan Management
• Maintaining SQL Performance
• SQL Plan Management

Workshops

• Workshop 9
• Workshop 2
• Workshop 4
• Workshop 8
• Workshop 5
• Workshop 6 & 7
• Workshop 1
• Workshop 3