Oracle 11g - Application Tuning - Workshop Training Course

Primary tabs

Course Code


Duration Duration

28 hours (usually 4 days including breaks)

Requirements Requirements

  • The free use of the basics of SQL and knowledge of Oracle database environment (preferably Oracle 11g completion of training - Native SQL for Programmers - Workshops)
  • Practical experience in working with Oracle

Overview Overview

For who

The workshop is intended for advanced programmers and Oracle users who seek knowledge and information on the efficient development of information systems in an Oracle database, and the tuning and testing of performance issues in existing applications. This course builds on knowledge often unavailable or incorrectly presented in the technical documentation, and collected during many years of practice leading them instructors. These workshops may be the end of the training path for developers, or a single step for people with extensive experience designing and programming in Oracle

Purpose of training

The workshop aims to provide mechanisms that occur in an Oracle database when performing SQL statements. Allows participants to avoid errors during software development, and explore, diagnose, and resolve performance problems in existing applications.

Particular emphasis is placed on the workshops, where we show the methodology and the practical aspects of the application and tuning SQL statements.

The content of the training

  • Mechanics perform SQL commands
  • Managing the process cost optimization
  • Methods of data storage and indexing
  • Monitoring database performance and processes based on dictionaries and track system applications
  • Analysis of cases of the most common problems that cause performance


The workshops are based on the software version 11g XE

Course Outline Course Outline

Application Tuning Methodology

Architecture database and instance

  • Server processes
  • Memory structure (SGA, PGA)
  • Parsing and share cursors
  • The data files, log files, parameter files

Analysis of the command execution plan

  • Hypothetical plan (EXPLAIN PLAN, SQLPlus AutoTrac XPlane)
  • The actual execution plan (V $ SQL_PLAN, XPlane, AWR)

Monitoring the performance and find bottlenecks in the process

  • Monitoring the current status of the instance by system dictionary views
  • The monitoring of historical dictionaries
  • Tracking application (SQLTrace, TkProf, TreSess

The optimization process

  • Properties cost optimization and regulated
  • Determination to optimize

Control work cost-based optimizer by:

  • Session parameters and instance
  • Tips (hints)
  • Patterns of query plans

Statistics and Histograms

  • Impact statistics and histograms for performance
  • The methods of collecting statistics and histograms
  • Strategy of counting and estimating statistics
  • Management statistics: blocking, copying, editing, automation of collection, monitoring changes
  • Dynamic data sampling (temporary plates, complex predicates)
  • Multi-column statistics, based on expressions
  • Statistics System

The logical and physical structure of the database

  • Spaces tables.
  • segments
  • Extensions (EXTENTS)
  • Blocks

Data storage methods

  • The physical aspects of the table
  • temporary Tables
  • Tables index
  • external Tables
  • Partition Table (span, letter, hash, mixed)
  • Physical reorganization of tables

Materialized views and mechanism QUERY REWRITE

Methods of data indexing

  • Building B-TREE indexes
  • Properties index
  • Indexes: a unique, multi-column, function, inverse
  • Compression indices
  • Reconstruction and merging indexes
  • Virtual indexes
  • Indexes private and public
  • Bitmap Indexes and junction

Case study - full-scan data

  • The impact of a place at the table level and block performance readings
  • Loading Data conventional and direct path
  • The order of predicates

Case Study - access to data via the index

  • Using functional indices
  • The selectivity index (Clustering Factor)
  • Multi-column indexes and SKIP SCAN
  • NULL and indexes
  • Index tables (IOT)
  • Impact indices DML operations

Case Study - sorting

  • Sorting memory
  • Sort index
  • Sort linguistic
  • The effect of entropy to sort (Clustering Factor)

Case Study - joins and subqueries

  • The merger: MERGE, HASH, NESTED LOOP
  • Joins in OLTP and OLAP systems
  • The order of switching
  • Outer Joins
  • AntI-join
  • Joins incomplete (SEMI)
  • Subqueries simple
  • Correlated subqueries
  • The views, the WITH clause

Other operations cost-based optimizer

  • Buffer Sort
  • VIEW
  • Count Stop Key
  • Result Cache

Inquiries dispersed

  • Read query plans for use dblinks
  • Choosing the leading mark

Parallel processing

Public Classroom Public Classroom
Participants from multiple organisations. Topics usually cannot be customised
From 6510EUR
Private Classroom Private Classroom
Participants are from one organisation only. No external participants are allowed. Usually customised to a specific group, course topics are agreed between the client and the trainer.
Private Remote Private Remote
The instructor and the participants are in two different physical locations and communicate via the Internet
From 6310EUR
Request quote

The more delegates, the greater the savings per delegate. Table reflects price per delegate and is used for illustration purposes only, actual prices may differ.

Number of Delegates Public Classroom Private Remote
1 6510EUR 6310EUR
2 3985EUR 3860EUR
3 3143EUR 3043EUR
4 2723EUR 2635EUR
Cannot find a suitable date? Choose Your Course Date >>
Too expensive? Suggest your price

Related Categories

Upcoming Courses

VenueCourse DateCourse Price [Remote / Classroom]
Frankfurt am MainTue, 2017-03-14 09:306310EUR / 7110EUR
LeipzigMon, 2017-03-20 09:306310EUR / 7110EUR
MünchenTue, 2017-03-21 09:306310EUR / 7110EUR
NürnbergTue, 2017-03-21 09:306310EUR / 7110EUR
HannoverMon, 2017-03-27 09:306310EUR / 7110EUR

Course Discounts

Course Venue Course Date Course Price [Remote / Classroom]
Docker and Kubernetes Stuttgart Wed, 2017-02-22 09:30 3653EUR / 4303EUR
Git für Benutzer Köln Thu, 2017-03-02 09:30 891EUR / 1241EUR
Excel Data Analysis München Tue, 2017-03-21 09:30 1416EUR / 1916EUR
Python Programming Köln Tue, 2017-07-18 09:30 3285EUR / 4085EUR
Marketing Analytics using R Hannover Mon, 2017-07-31 09:30 2475EUR / 3125EUR
Forecasting with R Berlin Tue, 2017-08-08 09:30 1836EUR / 2436EUR

Course Discounts Newsletter

We respect the privacy of your email address. We will not pass on or sell your address to others.
You can always change your preferences or unsubscribe completely.

Some of our clients