Oracle Database: SQL Tuning for Developers (D79995) – Outline

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