By equipping delegates with a thorough understanding of Oracles' internal architecture, by highlighting the performance consequences of poor design and build decisions and by giving them the diagnostics knowledge and tools to locate problems, we believe that delegates leaving our course will be equipped to work independently on and locate the majority of performance issues they are likely to encounter. 

• To gain an greater understanding of performance tuning
• Gather statistical information
• Diagnose and resolve issues
• Optimize key areas of the system
• Tune SQL work area and Operating system

Overview of Database Performance Tuning

The Oracle RDBMS as a data repository

The Oracle RDBMS as an SQL engine

How Oracle determines an execution plan

The work involved in determining an execution plan

The work involved in executing an execution plan

Trade-offs in relation design

Tuning During Design

Tuning During Development

Tuning a Production Database

Tuning Methodology

Performance versus Safety Trade-Offs

Gathering Performance Information

Background Processes and Trace Files

User Trace Files

Statistics and Wait Events

Viewing statistics and wait events

Dictionary and Special Views

Displaying System wide Statistics

Displaying Session-Related Statistics

Oracle Wait Events

Displaying System wide Wait Events

Displaying Session Wait Events

Reporting on statistics and wait events

Statspack reports

Installing Statspack

Creating Statspack snapshots

Generating Statspack reports

AWR Reports

Generating AWR reports

Generating AWR SQL reports

Generating AWR comparative reports

ASH Reports

Generating ASH reports

Level of Statistics Collection

Monitoring Full Table Scan Operations

Table Scan Statistics

The cost of Full Table Scans

B-Tree Indexes

Reverse Key Index

Creating Reverse Key Indexes

Rebuilding Indexes

Compressed Indexes

Bitmap Indexes

Creating and Maintaining Bitmap Indexes

B-Tree Indexes and Bitmap Indexes

Index Reorganization

Monitoring Index Space

The ANALYZE Statement

Deciding Whether to Rebuild or Coalesce an Index

When to use an Index

Which columns to index

Monitoring Index Usage

Identifying Unused Indexes

Choosing an optimal Table Structure

Data Storage Structures

Selecting the Physical Structure

Data Access Methods

The Default Heap Table

Index-Organized Tables

Index-Organized Tables and Heap Tables

Creating Index-Organized Tables

IOT Row Overflow

Querying dba_views for IOT Information

Using a Mapping Table

Clusters

Cluster Types

Situations Where Clusters Are Useful

Partitioning Methods

Range Partitioning

Hash Partitioning

List Partitioning

Default Partition for List Partitioning

Composite Partitioned Table

Partitioned Indexes for Scalable Access

Partition Pruning

Partition-Wise Join

Statistics Collection for Partitioned Objects 

Optimizing Oracle Block Utilization

Allocation of Extents

Locally Managed Extents

Automatic segment space management

Pros and Cons of Large Extents

Empty space with a segment

The High-Water Mark

Table Statistics

Recovering Space

The Shrink command

Database Block Size

The DB_BLOCK_SIZE Parameter

Small Block Size: Pros and Cons

Large Block Size: Pros and Cons

PCTFREE and PCTUSED

Guidelines for PCTFREE and PCTUSED

Migration and Chaining

Detecting Migration and Chaining

Identifying Migrated Rows

Eliminating Migrated Rows

Optimizing the use of I/O resources

Oracle Processes and Files

Tablespace Usage

Automatic file extension

Distributing Files Across Devices

Multi-block read count

Performance Guidelines

Using Raw devices

Direct I/O

Synchronous and Asynchronous I/O

Choosing a file system

Disks verses Spindles

Using S.A.M.E

Choosing a R.A.I.D configuration

Using Automatic Storage Management (ASM)

Diagnostic Tools for Checking I/O Statistics

The v$filestat view

The V$segment_statistics view

I/O System statistics and I/O waits

Gathering Optimizer Statistics

Managing Statistics

Table Statistics

Collecting Segment-Level Statistics

Querying Segment-Level Statistics

Using Dynamic Sampling

Enabling Dynamic Sampling

Index Statistics

Column Statistics

Histograms

Generating Histogram Statistics

Gathering Statistic Estimates

Automatic Statistic Collecting

Using System Statistics

Gathering System Statistics

Automatic Gathering of System Statistics

Manual Gathering of System Statistics

Import System Statistics Example

Copying Statistics Between Databases

Diagnosing and Resolving Contention

Oracle’s Locking Mechanisms

Latches, Mutexes and Locks

What is a Latch?

What is a Mutex?

Accessing Memory Structures

Parent and child Latches

Immediate mode and willing to wait mode

Latch/Mutex related views, events and statistics

Resolving Latch/Mutex contention

What are Locks?

Data Concurrency

Locking Mechanism

Types of Locks

DML Locks

Enqueue Mechanism

Table Lock Modes

Manually Locking a Table

DML Locks in Blocks

DDL Locks

Possible Causes of Lock Contention

Diagnostic Tools for Monitoring Locking Activity

Guidelines for Resolving Contention

Deadlocks

Application design and contention points

Minimizing Connection Management Load

Performance and resource issues

Listener load Issues

Configuring Multiple Listeners

Distributing connections across Listeners

Configuring Listener Fail over

Monitoring Process Usage

Configuring Pre-spawned servers

Configuring Shared Servers

Configuring Connection Multiplexing

Configuring Connection Manager

Configuring Connection Pooling

Monitoring Shared Servers

Monitoring Dispatchers

Shared Servers and Memory Usage

Troubleshooting 

Managing the Shared Pool

The Shared Pool

The Library Cache

Important Shared Pool Latches

Shared Pool and Library Cache Latches

Tuning the Library Cache

Terminology

Diagnostic Tools for Tuning the Library Cache

Sharing Cursors

Library Cache Guidelines

Invalidations

Sizing the Library Cache

Shared Pool Advisory

Cached Execution Plans

Views to Support Cached Execution Plans

Global Space Allocation

Large Memory Requirements

Tuning the Shared Pool Reserved Space

Keeping Large Objects

Anonymous PL/SQL Blocks

Other Parameters Affecting the Library Cache

Tuning the Data Dictionary Cache

Diagnostic Tools for the Data Dictionary Cache

Measuring the Dictionary Cache Statistics

Tuning the Data Dictionary Cache

Guidelines: Dictionary Cache

Sizing the shared pool 

Optimizing the use of the Buffer Cache

Buffer Cache Characteristics

Buffer Cache Sizing Parameters

Buffer Cache Advisory

Managing the Database Buffer Cache

Tuning Goals and Techniques

Diagnostic Tools

Buffer Cache Performance Indicators

Measuring the Cache Hit Ratio

What does a Cache Hit Ratios really tell us?

Guidelines to Increase the Cache Size

Using Multiple Buffer Pools

Defining Multiple Buffer Pools

Enabling Multiple Buffer Pools

KEEP Buffer Pool Guidelines

RECYCLE Buffer Pool Guidelines

Calculating the Hit Ratio for Multiple Pools

Identifying Candidate Pool Segments

Dictionary Views with Buffer Pool Information

Caching Tables

Free Lists

Diagnosing Free List Contention

Resolving Free List Contention

Automatic Segment Space Management

Auto-Management of Free Space

Multiple DBWn Processes

Multiple I/O Slaves 

Optimizing the Redo Chain

The Redo Log Buffer

Sizing the Redo Log Buffer

Diagnosing Redo Log Buffer Inefficiency

Redo Log Buffer Tuning Guidelines

Redo Log Groups and Members

Online Redo Log File Configuration

Sizing Online Redo Log files

Diagnosing Redo Log File Inefficiency

Reducing Redo Operations

Tuning Instance Recovery

Setting Fast Start MTTR target

Monitor performance impact of  MTTR target

MTTR and Checkpointing

Log file Size and Checkpointing

Diagnose checkpoint and redo issues