Oracle 11g Release 2 SQL Performance Tuning

Course Code: O11GSPT

Duration: 2 days

Oracle 11g Release 2 SQL Performance Tuning Course Overview

This Oracle 11g Release 2 SQL Performance Tuning course introduces the delegate to the main concepts of Oracle SQL performance tuning. It is designed to give delegates practical experience in analysing and tuning the performance of SQL. This course is suitable for users of Oracle Database 11g and Oracle Database 12c.

Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.

Course Objectives

To provide the skills needed to monitor and tune an Oracle database application.

Who will the Course Benefit?

The Oracle 11g Release 2 SQL Performance Tuning course is designed for SQL programmers, application developers, designers and technical support professionals who are required to tune the performance of an Oracle application running under Oracle Database 11g or 12c.

Skills Gained

The delegate will learn and acquire skills as follows:

Selecting an Appropriate SQL Tuning Approach
Tuning Database Applications for Optimal Performance
Managing Statistics
Creating and Using Indexes
Structuring SQL Statements for Performance
Examining and Interpreting the Execution Plan of a SQL Statement using EXPLAIN PLAN
Examining the Efficiency of SQL Statements using SQL Trace and Autotrace
Using the SQL Tuning Advisor
Identifying SQL Statements that Perform Badly
Using Hints to Influence Execution Plan
Identifying Unused Indexes

View Course Contents - DAY 1

Oracle 11g Release 2 SQL Performance Tuning Training Course

Course Introduction

Administration and Course Materials
Course Structure and Agenda
Delegate and Trainer Introductions

Session 1: INTRODUCTION TO ORACLE PERFORMANCE TUNING

Overview of Oracle Database Tuning
Application Developer Tuning Responsibilities
Oracle DBA Tuning Responsibilities
Oracle Tuning Process
Plan a Routine Monitoring Regime
Setting Suitable Goals

Session 2: TOOLS FOR EVALUATING SQL STATEMENTS

Overview of SQL Statement Tuning
Tools to Assist in SQL Tuning
Use Explain Plan, Autotrace and SQL Trace to Examine the Execution of a SQL Statement
Interpreting a SQL Trace

Session 3: THE SQL OPTIMIZER

The SQL Optimizer
Statement Transformation
The Optimizer_Mode Initialization Parameter
Cost Based Optimizer
Managing Statistics with DBMS_STATS
Automatic Statistics Gathering
Dynamic Statistics
Online Statistics Gathering for Bulk Loads

Session 4: SORTS

How Oracle Processes Sorts
Temporary Disk Space Assignment
SQL Operations that Use Sorts

Session 5: INDEXES

Index Overview
Selecting Suitable Columns for an Index
B*Tree Indexes
Rebuild an Index
Composite Indexes
Descending Indexes
Access Paths with Indexes
Index Scans
Conditions That Stop Indexes Being Used
Parameters that Affect Optimizer Index Choice

View Course Contents - DAY 2

Oracle 11g Release 2 SQL Performance Tuning Training Course

Session 6: ADVANCED INDEXES

Bitmap Indexes
Key Compressed Indexes
Index Organized Tables
Function Based Indexes
Invisible Indexes

Session 7: JOIN OPERATIONS

Understand Access Paths
Joining Tables
Nested Loops Join
Merge Join
Cluster Join
Hash Join
Anti Join and Semi Join
Outer Joins
Star Join
Improve Optimization with Different Access Paths

Session 8: SQL TUNING ADVISOR USING SQL DEVELOPER

Overview of the DBMS_SQL_TUNE Package
Using the SQL Tuning Advisor with SQL Developer

Session 9: SEQUENCES AND VIEWS

Sequence Caching
Views
View Merging
Inline Views

Session 10: USING HINTS

Session 11: MISCELLANEOUS

Notes:

Course technical content is subject to change without notice.
Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions.

Requirements

A working knowledge of SQL is required. This can be obtained by attendance on the pre-requisite Oracle SQL course.

Pre-Requisite Courses

Oracle SQL

Follow-On Courses

Oracle PL/SQL

Oracle Forms - Part I

Course Reviews

The instructor was a delight from beginning to end, experienced, patient and went out of her way to create a full examples or fully answer a question whenever the need arose, including printing out researched answers. I can't recommend her enough.

Charles - Finance

Instruction and material was good. It is useful to understand what our DBAs can do.

Ben - Analyst Developer - Finance

Very good, would recommend. Trainer was very good!

Gareth - Associate Developer - Finance

Public Scheduled Events

Classroom & Live Virtual Instructor-Led Training

Duration: 2 days

Price: £895.00 exc. VAT

Please contact us to review your requirements and schedule a date.

Print Friendly

Why Choose StayAhead?

Leading indpendent UK training provider, established 1992
Formidable reputation for quality and expertise
Flexibility comes from developing our own independent courses
Course not meeting your needs? Private courses can be tailored specifically for you
Public Schedule & Closed Intructor-Led training - Classroom and Virtual

Live Virtual Classroom

Join live instructor-led classroom training from the comfort of your home or office.
All the convenience and benefits of the classroom experience without the hassle and costs of travel and accommodation.

Live Virtual Classroom

Our Customers Include