Oracle Advanced Performance Tuning

Faculty profile:

Real time professionals with 15+ years of experience.


25 Hours


Database Administrators, Support Engineers

Software needed on student PC:

Linux OS on laptop or desktop,  Oracle Database 11g

Course Objectives:

•    Use the Oracle Database tuning methodology appropriate to the available tools
•    Utilize database advisors to proactively tune an Oracle Database Instance
•    Use the tools based on the Automatic Workload Repository to tune the database
•    Diagnose and tune common SQL related performance problems
•    Diagnose and tune common Instance related performance problems

Course Topics


•    This lesson introduces the Performance Tuning course objectives and agenda

Basic Tuning Tools

•    Monitoring tools overview
•    Enterprise Manager
•    V$ Views, Statistics
•    Wait Events

Using Automatic Workload Repository

•    Managing the Automatic Workload RepositoryCreate AWR Snapshots
•    Real Time SQL Monitoring (a 11.1 feature new lesson in NF L-15)

Defining Problems

•    Defining the Problem
•    Limit the Scope & Setting the Priority
•    Top SQL Reports
•    Common Tuning Problems & Tuning During the Life Cycle
•    ADDM Tuning Session
•    Performance Versus Business Requirements

Using Baselines

•    Comparative Performance Analysis with AWR Baselines
•    Automatic Workload Repository Baselines
•    Moving Window Baseline
•    Baselines in Performance Page Settings & Baseline Templates
•    AWR Baselines & Creating AWR Baselines
•    Managing Baselines with PL/SQL & Baseline Views
•    Performance Monitoring and Baselines & Defining Alert

  Thresholds Using a Static Baseline

•    Using EM to Quickly Configure & Changing Adaptive Threshold Settings

Using AWR Based Tools

•    Automatic Maintenance Tasks
•    ADDM Performance Monitoring
•    Active Session History: Overview

Monitoring an Application

•    What Is a Service? Service Attributes & Service Types
•    Creating Services & Managing Services in a Single-Instance Environment
•    Everything Switches to Services.
•    Using Services with Client Applications & Using Services with the Resource Manager
•    Services and Resource Manager with EM & Using Services with the Scheduler
•    Using Services with Parallel Operations & Metric Thresholds
•    Service Aggregation and Tracing & Service Aggregation Configuration.
•    Client Identifier Aggregation and Tracing & Service Performance Views

Identifying Problem SQL Statements

•    SQL Statement Processing Phases & Role of the Oracle Optimizer
•    Identifying Bad SQL, Real Time SQL Monitoring (a 11.1 feature new lesson in NF L-15) & TOP SQL Reports
•    What Is an Execution Plan? Methods for Viewing Execution Plans & Uses of Execution Plans
•    DBMS_XPLAN Package: Overview & EXPLAIN PLAN Command
•    Reading an Execution Plan, Using the V$SQL_PLAN View & Querying the AWR
•    SQL*Plus AUTOTRACE & SQL Trace Facility
•    How to Use the SQL Trace Facility

Influencing the Optimizer

•    Functions of the Query Optimizer, Selectivity, Cardinality and Cost & Changing Optimizer Behavior
•    Using Hints, Optimizer Statistics & Extended Statistics
•    Controlling the Behavior of the Optimizer with Parameters
•    Enabling Query Optimizer Features & Influencing the Optimizer Approach
•    Optimizing SQL Statements, Access Paths & Choosing an Access Path
•    Join & Sort Operations
•    How the Query Optimizer Chooses Execution Plans for Joins
•    Reducing the Cost

SQL Performance Management

•    Maintaining SQL Performance and Optimizer Statistics & Automated Maintenance Tasks
•    Statistic Gathering Options & Setting Statistic Preferences
•    Restore Statistics
•    Deferred Statistics Publishing: Overview & Example
•    Automatic SQL Tuning: Overview
•    SQL Tuning Advisor: Overview
•    Using the SQL Access Advisor
•    SQL Plan Management: Overview

Tuning the Shared Pool

•    Shared Pool Architecture & Operation
•    The Library Cache & Latch and Mutex
•    Diagnostic Tools for Tuning the Shared Pool
•    Avoiding Hard & Soft Parses
•    Sizing the Shared Pool & Avoiding Fragmentation
•    Data Dictionary Cache & SQL Query Result Cache
•    UGA and Oracle Shared Server
•    Large Pool & Tuning the Large Pool

Tuning the Buffer Cache

•    Oracle Database Architecture: Buffer Cache
•    Database Buffers
•    Buffer Hash Table for Lookups
•    Working Sets
•    Buffer Cache Tuning Goals and Techniques
•    Buffer Cache Performance Symptoms & Solutions
•    Automatically Tuned Multiblock Reads
•    Flushing the Buffer Cache (for Testing Only)

Tuning PGA and Temporary Space

•    SQL Memory Usage & Performance Impact
•    SQL Memory Manager
•    Configuring Automatic PGA Memory & Setting PGA_AGGREGATE_TARGET Initially
•    Monitoring & Tuning SQL Memory Usage
•    PGA Target Advice Statistics & Histograms
•    Automatic PGA and Enterprise Manager & Automatic PGA and AWR Reports
•    Temporary Tablespace Management: Overview & Monitoring Temporary Tablespace
•    Temporary Tablespace Shrink & Tablespace Option for Creating Temporary Table

Automatic Memory Management

•    Oracle Database Architecture, Dynamic SGA & Memory Advisories
•    Granule & Manually Adding Granules to Components
•    Increasing the Size of an SGA Component, SGA Sizing Parameters & Manually Resizing Dynamic SGA Parameters
•    Automatic Shared Memory Management & Memory Broker Architecture
•    Behavior of Auto-Tuned & Manually TunedSGA Parameters
•    Using the V$PARAMETER View & Resizing SGA_TARGET
•    Disabling, Configuring & Monitoring Automatic Shared Memory Management (ASMM)
•    Automatic Memory Management

Tuning Segment Space Usage

•    Space and Extent Management & Locally Managed Extents
•    How Table Data Is Stored & Anatomy of a Database Block
•    Minimize Block Visits
•    The DB_BLOCK_SIZE Parameter
•    Small & Large Block Size: Considerations
•    Block Allocation, Free Lists & Block Space Management with Free Lists
•    Automatic Segment Space Management
•    Migration and Chaining, Shrinking Segments & Table Compression: Overview

Tuning I/O

•    I/O Architecture, File System Characteristics, I/O Modes & Direct I/O
•    Bandwidth Versus Size & Important I/O Metrics for Oracle Databases
•    I/O Calibration and Enterprise Manager, I/O Calibration and the PL/SQL Interface & I/O Statistics and Enterprise Manager
•    Stripe and Mirror Everything
•    Using RAID
•    I/O Diagnostics
•    Database I/O Tuning
•    What Is Automatic Storage Management?

Performance Tuning Summary

•    Best practices identified throughout the course
•    Summarize the performance tuning methodology

Appendix B: Using Statspack

•    Installing Statspack
•    Capturing Statspack Snapshots
•    Reporting with Statspack
•    Statspack Considerations
•    Statspack and AWR Reports
•    Reading a Statspack Report
•    Statspack and AWR