Oracle SQL Tuning for Developers Workshop

Skills covered:
Displaying courses for Great Britain [Change]

Overview

In this application development-centric course, delegates learn about Oracle SQL statement tuning and how to write well-tuned SQL statements appropriate for the Oracle database. Delegates learn to interpret execution plans and the different ways in which data can be accessed.

Delegates 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 are utilized.

Objectives

  • Choose an appropriate SQL tuning approach
  • Gather and interpret session statistics using the SQL trace facility
  • Identify the SQL statements that perform poorly
  • Use tuning techniques to tune inefficient SQL statements
  • Interpret Execution Plans
  • Describe the Oracle optimizer fundamentals
  • Manage SQL performance through changes

Audience

  • Developer
  • Data Warehouse Developer
  • Database Designers
  • PL/SQL Developer

Syllabus

Introduction to SQL Tuning

  • Find a workaround to enhance performance
  • Analyze a poorly written SQL statement
  • Create a function based index
  • Redesign a simple table
  • Rewrite the SQL statement

Using SQL Trace Facility and TKPROF

  • Explore a trace file to understand the optimizer's decisions

Understand Basic Tuning Techniques

  • Rewrite queries for better performance
  • Rewrite applications for better performance
  • Utilize SQL Tuning Advisor using SQL Developer 3.0

Optomizer Fundamentals

  • Explore a trace file to understand the optimizer's decisions

Understanding Serial Execution Plans

  • Use different techniques to extract execution plans
  • Use SQL monitoring

Optimizer: Table and Index Access Paths

  • Learn about using different access paths for better optimization

Optimizer: Join Operations

  • Use different access paths for better optimization
  • Examine and use the result cache

Other Optimizer Methods

  • Use different access paths for better optimization
  • Examine and use the result cache

Optimizer Statistics

  • Analyze and use system statistics
  • Use automatic statistics gathering

Using Bind Variables

  • Use adaptive cursor sharing and bind peeking
  • Use the CURSOR_SHARING initialization parameter

SQL Plan Management

  • Use SQL Plan Management

Training provider

Teaching mode: Classroom - Instructor Led
Duration: 3 days
Gooroo has partnered with the global leaders in IT training to give you access to quality training, personalised to you, targeted at increasing your job opportunities and salary.

Our pricing

We do not display pricing as Gooroo members qualify for special discounts not available elsewhere. You must enquire through Gooroo to get this benefit.

New courses are happening all the time

Our partner's expert training consultant will provide you with the times and all the details you need. Enquire today.

Top skills covered in this course

SQL
Great Britain
This skill has an average salary of
£50,140
and is mentioned in
4.97%
of job ads in this area.
Oracle Database
Great Britain
This skill has an average salary of
£48,186
and is mentioned in
1.55%
of job ads in this area.
Statistics
Great Britain
This skill has an average salary of
£41,830
and is mentioned in
2.71%
of job ads in this area.