Introduction

·       Course Objectives, Course Agenda and Appendixes Used in this Course

·       Audience and Prerequisites

·       Sample Schemas Used in the Course

·       Class Account Information

·       SQL Environments Available in the Course

·       Workshops, Demo Scripts, and Code Example Scripts

·       Appendices in the Course

 

Introduction to SQL Tuning

·       SQL Tuning Session

·       SQL Tuning Strategies

·       Development Environments: Overview

·       SQLTXPLAIN (SQLT) Diagnostic Tool

 

Using Application Tracing Tools

·       Using the SQL Trace Facility: Overview

·       Steps Needed Before Tracing

·       Available Tracing Tools: Overview

·       The trcsess Utility

·       Formatting SQL Trace Files: Overview

 

Understanding Basic Tuning Techniques

·       Developing Efficient SQL statement

·       Scripts Used in This Lesson

·       Table Design

·       Index Usage

·       Transformed Index

·       Data Type Mismatch

·       NULL usage

·       Tune the ORDER BY Clause

Optimizer Fundamentals

·       SQL Statement Representation

·       SQL Statement Processing

·       Why Do You Need an Optimizer?

·       Components of the Optimizer

·       Query Transformer

·       Cost-Based Optimizer

·       Adaptive Query Optimization

·       Optimizer Features and Oracle Database Releases

 

Generating and Displaying Execution Plans

·       Execution Plan?

·       The EXPLAIN PLAN Command

·       Plan Table

·       AUTOTRACE

·       V$SQL_PLAN View

·       Automatic Workload Repository

·       SQL Monitoring

·       DBML_SQL_MONITOR

 

Interpreting Execution Plans and Enhancements

·       Interpreting a Serial Execution Plan

·       Adaptive Optimizations

·       Optimizer: Table and Index Access Paths

·       Row Source Operations

·       Main Structures and Access Paths

·       Full Table Scan

·       Indexes

·       Common Observations

 

Optimizer Join Operations

·       Join Methods

·       Join Types

 

Other Optimizer Operators

·       SQL operators

·       Other N-Array Operations

·       Result Cache operators

 

Introduction to Optimizer Statistics Concepts

·       Optimizer Statistics

·       Types of Optimizer Statistics

·       Gather and Manage Optimizer Statistics: Overview

 

Using Bind Variables

·       Cursor Sharing and Different Literal Values

·       Cursor Sharing and Bind Variables

 

SQL Plan Management

·       Maintaining SQL Performance

·       SQL Plan Management