ORACLE8i PERFORMANCE and TUNING for DBA’s

Duration: 3 days

Develop the advanced skills of performance and tuning to effectively administer an Oracle Server Database

Print Outline in PDF format

Course Description:

This course provides students with the knowledge and hands-on experience of SQL tuning, database tuning and advanced performance design.

Participants will learn to…

• Understand the role of the DBA in performance tuning

• Design and build appropriate index and other storage structures for optimal performance

• Use a variety of tools to analyze the performance of SQL statements

• Generate statistics for and use the cost-based optimizer

• Use a variety of database analysis and tuning tools

• Configure a database for optimal performance

• Tune database memory

• Tune database sorts

• Tune database file I/O

• Tune contention

Who Needs to Attend:

Application Developers and Designers, Managers, Database Administrators, Systems Administrators, Network Managers and Technical Support Personnel

Prerequisites:

Oracle Foundation Classes, Oracle DBA I or equivalent experience

Course Content:

PERFORMANCE AND TUNING DUTIES OF THE DBA

• DBA’s Performance Tuning Duties

• Steps for Tuning

• A Tuning Methodology

• The Performance Tuning Team

• Tuning Tools

TUNE THE LOGICAL STRUCTURES OF THE DATABASE

• Overview

• Indexes

• Clusters

TUNE DATABASE OPERATIONS

• Objectives

• SQL Parsing and Execution

• Shared SQL Statements

• Contents of the SQL Area

• Understanding SQL Statement Transformation

• Using SQL Syntax

SQL TUNING TOOLS

• Rules-based Optimization

• Rankings of RBS Access Paths

• Cost-based Optimization

• Table and Index Statistics

• Execution Plans

• SQL*Plus and Autotrace

• SQL_TRACE and TKPROF

• Using Hints

DBA TUNING TOOLS

• Initialization Parameter File

• V$ Views

• UTLBSTAT.SQL and UTLESTAT.SQL

• Statspack

TUNING MEMORY

• Memory Tuning Goals

• Tune OS Memory

• Tune the Redo Log Buffer

• Tune Private SQL and PL/SQL Areas

• Tune the Shared Pool

• Tune the Database Buffer Cache

• Segregating Buffer Activity: Multiple Buffer Pools

• Tuning the Java Pool

• Tuning Sorts

TUNE I/O AND PHYSICAL STRUCTURES

• Tune File I/O

• RAID

• Oracle File I/O

TUNE RESOURCE CONTENTION AND UNDERSTANDING THE RESOURCE MANAGER

• Tune Contention

• Understanding the Resource Manager

PL/SQL PACKAGES FOR THE TUNER

• DBMS Packages

PERFORMANCE TUNING CHECKLIST AND WORKSHOP

• Oracle8i Performance Tuning Checklist

PERFORMANCE TUNING WORKSHOP

Course Labs:

1. Indexes and Clusters

2. SQL statement processing

3. Statistics and Execution Plans

4. SQL_Trace and TKPROF, Using Hints

5. Tuning Tools

6. Tuning Memory

7. Tuning I/O

8. Tuning Contention

9. Performance Tuning Workshop