Learn To:

• Use Oracle tools to identify inefficient SQL statements.
• Use Automatic SQL Tuning.
• Use Real Time SQL monitoring.
• Write more efficient SQL statements.
• Monitor and trace high load SQL statements.
• Manage optimizer statistics on database objects.
• Understand the optimizer process steps and operators.
• Interpret execution plans.
• Perform application tracing.

Benefit from gaining a deeper understanding of Oracle SQL statement tuning and how write well-tuned SQL statements appropriate for the Oracle database in this application development-centric course. You will learn how to decipher, decide and then apply tuning to your SQL code. Various tuning techniques are demonstrated.

Audience

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

Related Training

Required Prerequisites:

• Oracle Database: PL/SQL Fundamentals
• Knowledge of SQL and PL/SQL
• Oracle Database 12c: Introduction to SQL Ed 1.1 NEW

Suggested Prerequisites:

• Oracle Database: SQL Workshop I NEW

Course Objectives:

• Modify a SQL statement to perform at its best
• Identify poorly performing SQL
• Trace an application through its different levels of the application architecture
• Understand how the Query Optimizer makes decisions about how to access data
• Define how optimizer statistics affect the performance of SQL
• List the possible methods of accessing data, including different join methods

Course Topics:

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

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

Workshops

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