Oracle Database 11g : Performance Tuning DBA


This Oracle Database 11g Performance Tuning training starts with an unknown database that requires tuning. You'll then learn the steps a DBA performs to identify problem areas, diagnose common problems and fix them.


Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling smooth and rapid consolidation within your Datacenter.


Oracle Database 11g Release 2 (


• This lesson introduces the Performance Tuning course objectives and agenda

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

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

• 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 • Performance Tuning Resources & Filing a Performance Service Request • Monitoring and Tuning Tools: Overview

• Metrics, Alerts, and Baselines • Limitation of Base Statistics & Typical Delta Tools • Oracle Database 11g Solution: Metrics • Benefits of Metrics • Viewing Metric History Information & Vsing EM to View Metric Details • Statistic Histograms & Histogram Views • Database Control Usage Model & Setting Thresholds • Server-Generated Alerts, Creating and Testing an Alert & Metric and Alert Views

• 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

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

• 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

• 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 • Generate an Optimizer Trace

• 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

• Real Application Testing: Overview & Use Cases • SQL Performance Analyzer: Process & Capturing the SQL Workload • Creating a SQL Performance Analyzer Task & SPA (NF Lesson 9) DBMS_SQLTUNE.CREATE_TUNING_TASK • Optimizer Upgrade Simulation & SQL Performance Analyzer Task Page • Comparison Report & Comparison Report SQL Detail • Tuning Regressing Statements & Preventing Regressions • Parameter Change Analysis & Guided Workflow Analysis • SQL Performance Analyzer: PL/SQL Example & Data Dictionary Views

• 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

• The Big Picture & System Architecture • Capture & Replay Considerations • Replay Options & Analysis • Database Replay Workflow in Enterprise Manager • Packages and Procedures • Data Dictionary Views: Database Replay • Database Replay: PL/SQL Example • Calibrating Replay Clients

• 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

• 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)

• 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

• 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

• 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

• 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?

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

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


Practice Activities
Case Studies


Pre Test
Post test
Certification Exam


5 Days

Target Audience

Database Administrators , Support Engineer , Technical Consultant


No Schedule