Отримати консультацію
Training Center MUKКурсыOracleDatabaseOracle Database: SQL Tuning for Developers Ed 2

Oracle Database: SQL Tuning for Developers Ed 2

Код курсу
3 Днів, 24 Ак. Годин
Опис курсу
Програма курсу
Опис курсу

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.


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

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


  • Application Developers
  • Developer
  • Support Engineer
  • Data Warehouse Developer
  • PL/SQL Developer
Програма курсу

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

  • V$SQL_PLAN View
  • 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


  • Workshop 9
  • Workshop 2
  • Workshop 4
  • Workshop 8
  • Workshop 5
  • Workshop 6 & 7
  • Workshop 1
  • Workshop 3
Реєстрація на найближчий курс
Oracle Database: SQL Tuning for Developers Ed 2
Код курсу:
3 Днів, 24 Ак. Годин
Отримати консультацію
Свяжитесь со мной
Отримати консультацію
Отправить заявку
Реєстрація на вебінар
Отправить заявку
Ваша заявка отримана!
Ми зв`яжимося з вами найближчим часом.