Oracle Database: SQL Tuning for Developers Ed 2

Course code

ODSQLTD

Duration

3 Days, 24 Acad. Hours

Download pdf

Course Overview

Objectives

Prerequisites

Course Outline

Course Overview

Course overview

In this application development-centric course, students learn about Oracle SQL statement tuning and how to write well-tuned SQL statements appropriate for the Oracle database. Students learn to interpret execution plans and the different ways in which data can be accessed. Students are shown how to decipher, decide and then apply tuning to their SQL code. Various tuning techniques are demonstrated. For example, taking advantage of bind variables, trace files, and using the different types of indexes.

Objectives

Course Objectives

Modifying a SQL statement to perform at its best
Identifying poorly performing SQL
Tracing an application through its different levels of the application architecture
Understanding how the Query Optimizer makes decisions about how to access data
Defining how optimizer statistics affect the performance of SQL
Listing the possible methods of accessing data, including different join methods

Prerequisites

Suggested Prerequisite

Knowledge of SQL and PL/SQL
Familiarity with database architecture

Required Prerequisite

Oracle Database: Introduction to SQL Ed 2
Oracle Database: SQL Workshop I Ed 2

Audience

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

Course Outline

Course Topics

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

2. Introduction to SQL Tuning

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

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

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

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

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

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

8. Optimizer Join Operations

Join Methods
Join Types

9. Other Optimizer Operators

SQL operators
Result Cache operators
Other N-Array Operations

10. Introduction to Optimizer Statistics Concepts

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

11. Using Bind Variables

Cursor Sharing and Bind Variables
Cursor Sharing and Different Literal Values
12. SQL Plan Management
Maintaining SQL Performance
SQL Plan Management

Workshops

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

Back to trainings (Oracle)

Request the training

Oracle Database: SQL Tuning for Developers Ed 2

Course code:

ODSQLTD

Duration:

3 Days, 24 Acad. Hours

Apply

Download pdf