Oracle Database 12c - SQL Tuning for Developers

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

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

Oracle Cloud Overview

• Introduction to Oracle Cloud & Oracle Cloud Services
• Cloud Deployment Models
• Evolving from On-premises to Exadata Express
• What is in Exadata Express?
• Exadata Express for Users & Developers
• Oracle Exadata Express Cloud Service
• Getting Started with Exadata Express
• Service Console & Web Access through Service Console

Workshops

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