Oracle Database 10g DBA Performance & Tuning

4 days @ £1,995.00 exc. VAT

Course Code: O10GDBAPT

Virtual Events You can attend your training virtually using the London dates shown above. Please contact us for more details on our Virtual training options or to discuss specific Virtual course dates to meet your requirements.

Oracle Database 10g DBA Performance & Tuning Course Overview

Delegates attending the Oracle Database 10g 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. By the end of the course, delegates will be able 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

The delegate will learn and acquire skills as follows:

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

Who will the Course Benefit?

This Oracle Database 10g 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 10g.

Course Objectives

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

Requirements

Delegates who wish to attend the Oracle Database 10g 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

Follow-On Courses

Oracle Database 10g DBA Backup & Recovery with RMAN

Notes:

Course technical content is subject to change without notice.
Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions.

View Course Contents - DAY 1

Oracle Database 10g DBA Performance & Tuning Training Course

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

View Course Contents - DAY 2

Oracle Database 10g DBA Performance & Tuning Training Course

Session 6: ADVANCED INDEXES

Bitmap Indexes
Key Compressed Indexes
Index Organized Tables
Function-Based Indexes
Descending Indexes

Session 7: TYPES OF TABLE JOINS

Session 8: METRICS, ALERTS AND BASELINES

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

View Course Contents - DAY 3

Oracle Database 10g DBA Performance & Tuning Training Course

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

View Course Contents - DAY 4

Oracle Database 10g DBA Performance & Tuning Training Course

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

Home Let Us Call You Enquiry

Place A booking

97.35%
Instructor
Quality
Rating

92.54%
Course
Practical
Rating

94.35%
Overall
Courseware
Rating

96.29%
Overall
Course
Rating

Print Friendly

97.35%
Instructor
Quality
Rating

96.29%
Overall
Course
Rating

Course Reviews

Notably well paced with good content. Well explained.

Jim - Manufacturing

Please call for Dates

Request a Centre .......

If the course you require is not scheduled at your preferred centre, please contact us on 020 7600 6116 or sales@stayahead.com to enquire if we can accommodate your venue requirements.

Enquiry