Oracle Performance Tuning

Course Overview:

Oracle Performance Tuning training will develop the skills and knowledge required to improve the performance analysis of the Oracle database. You will learn how to design and develop SQL tuning tasks, instance tuning, performance tools, etc., through implementing industry-based real-time projects in our training, and this will enable you to become a Certified Oracle Performance Tuning expert. 

Course Content:


♦ 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


♦ 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


♦ Autotrace

♦ Join Methods

♦ Sort/Merge Joins

♦ Nested Loops

♦ Hash Joins

♦ Hash Join Example Data Access Methods


♦ 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

♦ Sharing SQL Statements

♦ Sharing Cursors

♦ Sharing SQL Statements

♦ Sharing Cursors

♦ Adaptive Cursors Sharing

♦ Other SQL Tuning Tips

Gathering Statistics

♦ Analyzing Statistics


♦ 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

Statistics and Wait Events

♦ Identify dynamic performance views useful in tuning

♦ Identify key tuning components of the alert log file

♦ Identify key tuning components of user trace files

♦ Use dynamic performance views to view statistics and wait events

Metrics, Alerts, and Baselines

♦ View metrics using the metrics history views

♦ Create metric thresholds

♦ View alerts

♦ Create metric baselines

♦ Enable adaptive thresholds

Using Statspack

♦ Install Statspack

♦ Create Statspack snapshots

♦ Generate Statspack reports

♦ Identify the major sections of the Statspack report

Using Statspack

♦ Create and manage AWR snapshots

♦ Generate AWR reports

♦ Create snapshot sets and compare periods

♦ Generate ADDM reports

♦ Generate ASH reports

We can assure a 100% job guarantee and Placement. Contact us for Free - Demo.

Quick Enroll