Oracle Database: SQL Tuning for Developers

Course code

Pr_SQL_PT

Duration

3 Days, 24 Acad. Hours

Download pdf

Course Overview

Objectives

Prerequisites

Course Outline

Course Overview

Course overview

In the Oracle Database: SQL Tuning for Developers course, you learn about Oracle SQL tuning and how to apply tuning techniques to your SQL code. Learn the different ways in which data can be accessed efficiently. In this course, you will be introduced to Oracle Database Exadata Express Cloud Service.

Objectives

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
Gain an understanding of the Oracle Database Exadata Express Cloud Service

Prerequisites

Suggested Prerequisite

Familiarity with database architecture
Knowledge of SQL and PL/SQL

Required Prerequisite

Oracle Database 12c: Introduction to SQL Ed 1.1
Oracle Database: SQL Workshop I

Audience

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

Course Outline

Course Topics

1. Introduction

Class Account Information
Sample Schemas Used in the Course
Appendices in the Course
Course Objectives, Course Agenda and Appendixes Used in this Course
Audience and Prerequisites
SQL Environments Available in the Course
Workshops, Demo Scripts, and Code Example Scripts

2. Introduction to SQL Tuning

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

3. Using Application Tracing Tools

Available Tracing Tools: Overview
Formatting SQL Trace Files: Overview
Using the SQL Trace Facility: Overview
Steps Needed Before Tracing
The trcsess Utility

4. Understanding Basic Tuning Techniques

NULL usage
Developing Efficient SQL statement
Data Type Mismatch
Table Design
Scripts Used in This Lesson
Tune the ORDER BY Clause
Index Usage
Transformed Index

5. Optimizer Fundamentals

SQL Statement Processing
SQL Statement Representation
Cost-Based Optimizer
Optimizer Features and Oracle Database Releases
Why Do You Need an Optimizer?
Query Transformer
Components of the Optimizer
Adaptive Query Optimization

6. Generating and Displaying Execution Plans

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

7. Interpreting Execution Plans and Enhancements

Adaptive Optimizations
Interpreting a Serial Execution Plan

8. Optimizer: Table and Index Access Paths

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

9. Optimizer Join Operations

Join Types
Join Methods

10. Other Optimizer Operators

Result Cache operators
Other N-Array Operations
SQL operators

11. Introduction to Optimizer Statistics Concepts

Gather and Manage Optimizer Statistics: Overview
Types of Optimizer Statistics

Optimizer Statistics

12. Using Bind Variables

Cursor Sharing and Bind Variables
Cursor Sharing and Different Literal Values

13. SQL Plan Management

Maintaining SQL Performance
SQL Plan Management

Workshops

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

Oracle Cloud Overview

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

Back to trainings (Oracle)

Request the training

Oracle Database: SQL Tuning for Developers

Course code:

Pr_SQL_PT

Duration:

3 Days, 24 Acad. Hours

Apply

Download pdf