Course Overview

Delegates attending the Oracle Database 11g Release 2 DBA Performance & Tuning course will be introduced to the main concepts of Oracle performance tuning and will gain practical experience in analyzing and tuning database performance.  They will learn to identify tuning contentions and be able to take corrective action. 
This Oracle Database 11g Release 2 DBA Performance & Tuning course covers the main topics of the Oracle examination 1Z1-054 Oracle Database 11g Performance Tuning Certified Expert.

Course Objectives

The objective of the Oracle Database 11g Release 2 DBA Performance & Tuning course is to provide the skills needed to monitor and tune an Oracle Database 11g or Oracle Database 10g.

Upon successful completion of this course, delegates will have the necessary skills to:
•    use different tuning methodologies
•    use the database advisors to proactively tune a database
•    use AWR tools to tune a database
•    detect and tune common database performance problems
•    use Enterprise Manager to monitor an Oracle database

Skills Gained

The delegate will practice:
•    Tuning goals and methodology
•    Planning initial database configuration
•    Tuning database applications for optimal performance
•    Identifying and resolving common tuning problems
•    Using diagnostic sources
•    Logging a SR with Oracle support
•    Managing statistics
•    Structuring SQL statements for performance
•    Using the SQL optimizers
•    Using Indexes effectively
•    Examining the execution plan of a SQL statement using Explain Plan
•    Examining the efficiency of SQL statements using SQL trace and autotrace
•    Using table joins
•    Monitoring statistics and wait events
•    Using metrics, alerts and baselines
•    Using statspack
•    Using the Automatic Workload Repository
•    Tuning the SGA and Shared Pool
•    Tuning the data block buffers
•    Using Automatic Memory Management
•    Setting the PGA and temporary space
•    Tuning checkpoint and redo logs
•    Managing disk I/O
•    Managing undo information
•    Tuning block space
•    Improving performance with materialized views
•    Using parallel execution
•    Using Database Replay
•    SQL performance management and analysis

Target Audience

The Oracle Database 11g Release 2 DBA Performance & Tuning course is suitable for database administrators, application developers and technical support staff who are required to monitor and tune an Oracle Database 11g or Oracle Database 10g.

Prerequisites

Delegates wishing to attend the Oracle Database 11g Release 2 DBA Performance & Tuning course should have a working knowledge of SQL*Plus, PL/SQL and Oracle Database Administration.  This can be obtained by attendance on the Oracle SQL and Oracle Database 10g DBA Part I courses.  Some operating system experience is essential.
Knowledge of PL/SQL is desirable.  Some operating system experience is essential.

Pre-Requisite Courses:

•    Oracle SQL
•    Oracle Database 11g Release 2 DBA – Part I
•    Oracle Database 10g DBA – Part I

Follow-On Courses:

•    Oracle Database 11g Release 2 DBA Backup & Recovery with RMAN
•    Oracle Database 11g Release 2 Data Guard

Course Contents

DAY 1

Course Introduction
Administration and Course Materials
Course Structure and Agenda
Delegate and Trainer Introductions

Session 1: INTRODUCTION TO ORACLE PERFORMANCE TUNING
Tuning Overview
Resolving Performance Problems
The Oracle Approach to Tuning
Items to Evaluate
Where to Look
Plan a Routine Monitoring Regime
Build a New Database for Performance
Tune an Existing Database for Performance
Set Suitable Goals

Session 2: DIAGNOSING PROBLEMS
Diagnostic Sources
Log a Performance Service Request (SR)
Tips for Avoiding Problematic Queries

Session 3: TOOLS FOR EVALUATING SQL STATEMENTS
Overview of SQL Statement Tuning
Explain Plan and autotrace
SQL Trace and the tkprof Program
Interpreting SQL Trace

Session 4: THE OPTIMIZER
The SQL Optimizer
The Rule Based Optimizer
The Cost Based Optimizer
The Analyze Command
Manage Statistics with DBMS_STATS

Session 5: INDEXES
Index Overview
B*Tree Indexes
Access Paths with Indexes
Index Scans
Conditions which Stop Indexes Being Used
Parameters Affecting Optimizer Index Choice

DAY 2

Session 6: ADVANCED INDEXES
Bitmap Indexes
Key Compressed Indexes
Index Organized Tables
Function-Based Indexes
Descending Indexes

Session 7: TYPES OF TABLE JOINS
Joining Tables
Nested Loops Join
Cluster Join and Hash Join
Anti Join and Semi Join
Outer Join
Star Join

Session 8: METRICS, ALERTS AND BASELINES
System Generated Alerts
Threshold Based Alerts
Event Based Alerts
Retrieve Threshold Information
View Alert Data
Manage Metrics Using EM
Blackouts
Baselines and Adaptive Thresholds

Session 9: STATSPACK
Overview Of Statspack
Statspack Architecture
Install Statspack
Adjust the Statspack Collection Level
Create, View and Delete Snapshots
Generate a Report
Analyze the Reports to Generate an Execution Plan for a Given SQL Statement
Resolve Wait Events
Remove Statspack from the Database

Session 10: AUTOMATIC WORKLOAD REPOSITORY
Automatic Workload Repository
Create Snapshots and Compare Snapshots
Use Baselines
Generate AWR Reports
Compare AWR Periods Reports
Generate Active Session History (ASH) Report
Automatic Database Diagnostic Monitor (ADDM)
Generate and Analyze ADDM Reports
Session 11: REACTIVE TUNING
Use Enterprise Manager to Identify Common Problems
Eliminate Operating System Contentions

DAY 3

Session 12: TUNING THE SHARED POOL
Overview of the Shared Pool
Alter and Tune the Shared Pool Size
Tune the Data Dictionary Cache and the Library Cache
Shared Pool Advisory Statistics
Pin objects in the Shared Pool
Cursor Sharing
Using the Large Pool
Latches and Mutexes

Session 13: TUNE THE BUFFER CACHE
Memory Structures
Data Block Buffer Pool
Multiple Buffer Caches
Manage Free Buffers
Establish the Buffer Pool Efficiency
Cache Tables in the Buffer Pools
Common Performance Issues and the Buffer Cache

Session 14: AUTOMATIC MEMORY MANAGEMENT
Automatic Memory Management
Automatic Shared Memory Management
Memory Allocation Advisors
The Memory Advisor
The Shared Pool and Buffer Cache Advisors

Session 15: TUNE PGA AND TEMPORARY SPACE
PGA Overview
Automatic PGA Mode
Configure the PGA for a New Instance
The Program Global Area Advisor
PGA Management Data Dictionary Views
Tune the PGA_AGGREGATE_TARGET
SQL Operations that Use Sorts
Sorting to Disk
Multiple Temporary Tablespaces
Allocate Temporary Disk Space for Sorting
Temporary Tablespace Shrink
Data Dictionary Views and Sort Segments

Session 16: CHECKPOINT AND REDO TUNING
Checkpoint and Redo Issues
Instance Recovery Performance Tuning
Redo Log File Size Advisor
Size the Redo Log Buffer
Set Multiple Database Writers

DAY 4

Session 17: TUNE I/O
Database I/O Management
Layout the Files Using OS or Hardware Striping
Manually Distribute Files to Reduce I/O Contention
When to Separate Files
Sample Configurations
Select the Data Block Size
Synchronous I/O Benefits
FILESYSTEMIO_OPTIONS Parameter
Automatic Storage (ASM) Monitoring

Session 18: USE ORACLE BLOCKS EFFICIENTLY
Determine the Database Block Size
Tablespace, Table and Index Storage Parameters
Automatic Segment Space Management
Detect and Resolve Freelist Contention
Rebuild Tables and Indexes
The DBMS_SPACE Package
Row Migration
Chained Rows
Bigfile Versus Smallfile Tablespaces
Extended ROWID Format
Convert from Dictionary Managed Tablespaces
Convert to Automatic Segment Space Management

Session 19: MATERIALIZED VIEWS
Overview of Materialized Views
Materialized View Creation
Materialized View Refresh
Query Rewrite
Materialized View Data Dictionary Views

Session 20: PARALLEL EXECUTION
Overview of Parallel Execution
Enabling and Using Parallel Execution
The Degree of Parallelism
SQL Hints
Restrictions on the Use of Parallel Execution
Dictionary Views on Parallel Execution
Specific Automatic Parallel Query Optimization
Nologging Options

Session 21: TRACING SERVICES
End to End Tracing
Enable and Disable Tracing
Check for Tracing Enabled
The trcess Utility
Session Based Tracing
Enable and View SQL Tracing with Enterprise Manager
Analyze Trace Results

DAY 5

Session 22: NEW 11G SQL AND PL/SQL TUNING FEATURES
SQL Query Result Cache
PL/SQL Function Cache
Adaptive Cursors
Invisible Indexes

Session 23: DATABASE REPLAY
Overview of Database Replay
The Database Replay Process
Capture a Workload
Pre-process the Captured Workload
Replay the Workload
Database Replay Usage
The DBMS_WORKLOAD Packages

Session 24: SQL PERFORMANCE MANAGEMENT AND ANALYSIS
Statistics Gathering
SQL Performance Analyzer
SQL Plan Management
Automatic SQL Tuning
Manual Creation and Use of SQL Tuning Sets

Session 25: ADDM AND AWR NEW FEATURES IN ORACLE 11g
ADDM New Features in Oracle 11g
AWR New Features in Oracle 11g
Adaptive Metric Thresholds
Automatic SQL Tuning

Duration: 5 Days

Cost: £2195 + VAT