0345 4506120

Oracle 11g SQL Performance tuning

This Oracle 11g Performance Tuning training course is designed to introduce students to the full functional range of features and technologies required to optimise and tune SQL statements within the Oracle 11g RDBMS.

We don't currently have any courses listed for Oracle 11g SQL Performance tuning, would you like to view all courses for Oracle?

Learning Objectives

The Oracle 11g SQL Performance Tuning course will cover using the full functional range of features within the Oracle 11g RDBMS, students will learn to optimise and tune SQL statements. This is done through explanation of the different access methods involved and practical based teaching to control the optimal retrieval paths chosen by Oracle.

Where necessary, this involves explanation of Oracle¿s internal mechanisms, and the supporting hierarchical structures required by a Relational Database.

This Oracle 11g SQL Tuning course will offer students practical hands-on experience.

Oracle 11g SQL Tuning is an essential skill for all Oracle 11g developers and users to ensure that they maximise the performance of the data retrieval components of their business systems.

Pre-Requisites

  • PC Fundamentals
  • Oracle SQL
  • Oracle PL/SQL

Course Content

Introduction

  • Cause of Performance Problems
  • Setting Performance Goals
  • The Tuning Cycle
  • ORACLE Architecture
  • Logical Storage Structures
  • Physical Structures
  • Memory Structures
  • The Shared Pool
  • Processes
  • Some Administration Terminology

Design

  • Data Design Phase
  • Data Model Design
  • Online Transaction Processing
  • Decision Support Systems
  • Multi-purpose Applications

Optimizing SQL

  • Physical Retrieval of Data
  • Full Table Scan versus Index Reads
  • Performance Diagnostic Tools
  • Explain Plan
  • SQL Trace Facility
  • TKPROF
  • Autotrace
  • Join Methods
  • Sort/Merge Joins
  • Nested Loops
  • Hash Joins
  • Hash Join Example Data Access Methods

Indexes

  • Basic Indexes
  • B-Tree Indexes
  • Bitmap Index
  • Comparing B-Tree and Bitmap Indexes
  • Reverse Key Index
  • Index-Organised Tables
  • Invisible index
  • Creating Monitoring and Maintaining Indexes.

Automatic SQL Tuning

  • Query Optimizer Modes Types of Tuning Analysis
  • Automatic workload repository SQL Tuning Advisor SQL Tuning Sets Top SQL
  • Identify high-load SQL Dynamic Performance views

SQL Performance Analyzer

  • Describe SQL Performance Analyzer process and benefits
  • Use SQL Performance Analyzer

The Optimizer

  • The ORACLE Optimizer
  • SQL statement parsing
  • Initialisation parameters
  • Rule Based Optimizer
  • Cost Based Optimizer
  • Rule / Cost Comparisons
  • Choosing an Approach
  • Multiple WHERE Clauses
  • Using Indexes for Sorts
  • Multiple Table Joins
  • Disabling Indexes
  • Hints.
  • Sharing SQL Statements
  • Sharing Cursors
  • Adaptive Cursors Sharing
  • Other SQL Tuning Tips

Gathering Statistics

  • Analyzing Statistics
  • DBMS_STATS
  • Automatic Optimizer Statistics Collection
  • Histograms
  • How to Generate Histograms Statistics

SQL Plan Management

  • SQL outlines
  • SQL profiles
  • SQL Access Advisor
  • Set up and use SQL plan baseline

Advanced Tuning

  • Star Queries
  • Materialized Views
  • Refreshing Views
  • Materialized View Logs
  • SQL Result Cache
  • Temporary Tables

Privacy Notice

In order to provide you with the service requested we will need to retain and use your contact information in accordance with our Privacy Notice. If you choose to provide us with this information you explicitly consent to us using the information as necessary to provide the request service to you. If you do not agree please do not proceed to request the service from us.

Marketing Permissions

Would you like to receive our newsletter and other information on products and services which we think will be of interest to you by email. We will always treat your information with care and in accordance with our Privacy Notice. You are free to withdraw this permission at any time.

 

Our Customers Include