Oracle Database 12c: SQL Tuning for Developers Ed1 Training

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

2. Introduction to SQL Tuning

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

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

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

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

6. Generating and Displaying Execution Plans

• Execution Plan?
• The EXPLAIN PLAN Command
• Plan Table
• AUTOTRACE
• V$SQL_PLAN View
• Automatic Workload Repository
• SQL Monitoring

7. Interpreting Execution Plans and Enhancements

• Interpreting a Serial Execution Plan
• Adaptive Optimizations

8. Optimizer: Table and Index Access Paths

• Row Source Operations
• Main Structures and Access Paths
• Full Table Scan
• Indexes
• Common Observations

9. Optimizer Join Operations

• Join Methods
• Join Types

10. Other Optimizer Operators

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

11. Introduction to Optimizer Statistics Concepts

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

12. Using Bind Variables

• Cursor Sharing and Different Literal Values
• Cursor Sharing and Bind Variables

13. SQL Plan Management

• Maintaining SQL Performance
• SQL Plan Management

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