Oracle Performance Tuning

Exploring the Oracle Database Architecture

Introduction to Performance Tuning

• List the architectural components of Oracle Database

• Performance Planning

• Instance Tuning

• Performance Principles.

• Baselines.

• The Symptoms and the Problems.

• When to Tune.

• Proactive Monitoring

• Bottleneck Elimination

• SQL Tuning

• Query Optimizer and Execution Plans

Introduction to Performance Tuning Features and Tools

• Automatic Performance Tuning Features.

• Additional Oracle Database Tools

• Performance Views

2 Designing and Developing for Performance

Oracle Methodology

• Understanding Investment Options

Understanding Scalability

• What is Scalability?

• System Scalability

• Factors Preventing Scalability

System Architecture

• Hardware and Software Components

• Hardware Components.

• CPU.

• Memory

• I/O Subsystem.

• Network

• Software Components.

• Managing the User Interface

• Implementing Business Logic

• Managing User Requests and Resource Allocation

• Managing Data and Transactions

• Configuring the Right System Architecture for Your Requirements.

Application Design Principles

• Simplicity In Application Design.

• Data Modelling

• Table and Index Design.

• Appending Columns to an Index or Using Index-Organized Tab

• Using a Different Index Type.

• B-Tree Indexes.

• Bitmap Indexes.

• Function-based Indexes

• Partitioned Indexes

• Reverse Key Indexes.

• Finding the Cost of an Index.

• Serializing within Indexes

• Ordering Columns in an Index

• Using Views

• SQL Execution Efficiency.

• Implementing the Application

• Trends in Application Development

Workload Testing, Modelling, and Implementation

• Sizing Data.

• Estimating Workloads.

• Extrapolating from a Similar System

• Benchmarking.

• Application Modelling

• Testing, Debugging, and Validating a Design

Deploying New Applications

• Rollout Strategies.

• Performance Checklist.

3 Performance Improvement Methods

The Oracle Performance Improvement Method

• Steps in the Oracle Performance Improvement Method

• A Sample Decision Process for Performance Conceptual Modelling.

• Top Ten Mistakes Found in Oracle Systems.

Emergency Performance Methods

• Steps in the Emergency Performance Method.

4 Configuring a Database for Performance

Performance Considerations for Initial Instance Configuration

• Initialization Parameters.

• Configuring Undo Space

• Sizing Redo Log Files

• Creating Subsequent Tablespaces

• Creating Permanent Tablespaces – Automatic Segment-Space

• Creating Temporary Tablespaces.

Creating and Maintaining Tables for Optimal Performance

• Table Compression

• Estimating the Compression Factor

• Tuning to Achieve a Better Compression Ratio

• Using Attribute-Clustered Tables

• Reclaiming Unused Space.

• Indexing Data.

• Specifying Memory for Sorting Data.

Performance Considerations for Shared Servers

• Identifying Contention Using the Dispatcher-Specific Views

• Reducing Contention for Dispatcher Processes.

• Identifying Contention for Shared Servers.

5 Measuring Database Performance

About Database Statistics

• Time Model Statistics.

• Active Session History Statistics

• Wait Events Statistics.

• Session and System Statistics.

Interpreting Database Statistics

• Using Hit Ratios

• Using Wait Events with Timed Statistics

• Using Wait Events without Timed Statistics.

• Using Idle Wait Events.

• Comparing Database Statistics with Other Factors

• Using Computed Statistics.

6 Gathering Database Statistics

About Gathering Database Statistics

• Automatic Workload Repository.

• Snapshots

• Baselines

• Fixed Baselines.

• Moving Window Baselines

• Baseline Templates.

• Single Baseline Templates

• Repeating Baseline Templates

• Space Consumption.

• Adaptive Thresholds.

• Percentage of Maximum Thresholds

• Significance Level Thresholds

Managing the Automatic Workload Repository

• Enabling the Automatic Workload Repository

• Managing Snapshots

• User Interfaces for Managing Snapshots

• Creating Snapshots.

• Creating Snapshots Using the Command-Line Interface.

• Dropping Snapshots

• Dropping Snapshots Using the Command-Line Interface

• Modifying Snapshot Settings.

• Modifying Snapshot Settings Using the Command-Line Interface

• Managing Baselines.

• User Interface for Managing Baselines.

• Creating a Baseline.

• Creating a Baseline Using the Command-Line Interface.

• Dropping a Baseline

• Dropping a Baseline Using the Command-Line Interface

• Renaming a Baseline

• Renaming a Baseline Using the Command-Line Interface

• Displaying Baseline Metrics

• Displaying Baseline Metrics Using the Command-Line Interface

• Resizing the Default Moving Window Baseline.

• Resizing the Default Moving Window Using the Command

• Managing Baseline Templates.

• User Interfaces for Managing Baseline Templates.

• Creating a Single Baseline Template

• Creating a Single Baseline Template Using the Command-Line Interface

• Creating a Repeating Baseline Template.

• Creating a Repeating Baseline Template Using the Command-Line Interface

• Dropping a Baseline Template

• Dropping a Baseline Template Using the Command-Line Interface

• Transporting Automatic Workload Repository Data.

• Extracting AWR Data

• Loading AWR Data.

• Using Automatic Workload Repository Views

Generating Automatic Workload Repository Reports

• User Interface for Generating an AWR Report.

• Generating an AWR Report Using the Command-Line Interface.

• Generating an AWR Report for the Local Database.

• Generating an AWR Report for a Specific Database

• Generating an Oracle RAC AWR Report for the Local Database

• Generating an Oracle RAC AWR Report for a Specific Database

• Generating an AWR Report for a SQL Statement on the Local

• Generating an AWR Report for a SQL Statement on a Specific

Generating Performance Hub Active Report

• Overview of Performance Hub Active Report.

• About Performance Hub Active Report Tabs

• About Performance Hub Active Report Types

• Command-Line User Interface for Generating a Performance Hub A

• Generating a Performance Hub Active Report Using a SQL Script

7 Automatic Performance Diagnostics

Overview of the Automatic Database Diagnostic Monitor

• ADDM Analysis

• Using ADDM with Oracle Real Application Clusters

• Real-Time ADDM Analysis.

• Real-Time ADDM Connection Modes.

• Real-Time ADDM Triggers

• Real-Time ADDM Trigger Controls.

• ADDM Analysis Results.

• Reviewing ADDM Analysis Results: Example

Setting Up ADDM.

Diagnosing Database Performance Problems with ADDM

• Running ADDM in Database Mode

• Running ADDM in Instance Mode

• Running ADDM in Partial Mode.

• Displaying an ADDM Report

Views with ADDM Information

8 Comparing Database Performance Over Time

About Automatic Workload Repository Compare Periods Reports

• Generating Automatic Workload Repository Compare Periods Reports

• User Interfaces for Generating AWR Compare Periods Reports

• Generating an AWR Compare Periods Report Using the Command

• Generating an AWR Compare Periods Report for the Local Database

• Generating an AWR Compare Periods Report for a Specific Database

• Generating an Oracle RAC AWR Compare Periods Report

• Generating an Oracle RAC AWR Compare Periods Report for a

Interpreting Automatic Workload Repository Compare Periods Reports

• Summary of the AWR Compare Periods Report

• Snapshot Sets.

• Host Configuration Comparison

• System Configuration Comparison.

• Load Profile

• Top 5 Timed Events

• Details of the AWR Compare Periods Report

• Time Model Statistics

• Operating System Statistics

• Wait Events.

• Service Statistics.

• SQL Statistics

• Top 10 SQL Comparison by Execution Time.

• Top 10 SQL Comparison by CPU Time.

• Top 10 SQL Comparison by Buffer Gets.

• Top 10 SQL Comparison by Physical Reads.

• Top 10 SQL Comparison by Executions

• Top 10 SQL Comparison by Parse Calls.

• Complete List of SQL Text.

• Instance Activity Statistics.

• Key Instance Activity Statistics

• Other Instance Activity Statistics

• I/O Statistics

• Tablespace I/O Statistics.

• Top 10 File Comparison by I/O

• Top 10 File Comparison by Read Time.

• Top 10 File Comparison by Buffer Waits.

• Advisory Statistics.

• PGA Aggregate Summary.

• PGA Aggregate Target Statistics.

• Wait Statistics

• Buffer Wait Statistics

• Enqueue Activity

• Undo Segment Summary

• Latch Statistics

• Segment Statistics

• Top 5 Segments Comparison by Logical Reads

• Top 5 Segments Comparison by Physical Reads

• Top 5 Segments Comparison by Row Lock Waits.

• Top 5 Segments Comparison by ITL Waits.

• Top 5 Segments Comparison by Buffer Busy Waits

• In-Memory Segment Statistics

• Dictionary Cache Statistics.

• Library Cache Statistics

• Memory Statistics

• Process Memory Summary

• SGA Memory Summary.

• SGA Breakdown Difference.

• Streams Statistics.

• Supplemental Information in the AWR Compare Periods Report.

• init.ora Parameters

• SQL Text

9 Analyzing Sampled Data

About Active Session History

Generating Active Session History Reports

• User Interfaces for Generating ASH Reports.

• Generating an ASH Report Using the Command-Line Interface

• Generating an ASH Report on the Local Database Instance

• Generating an ASH Report on a Specific Database Instance.

• Generating an ASH Report for Oracle RAC

Interpreting Results from Active Session History Reports

• Top Events.

• Top User Events.

• Top Background Events

• Top Event P1/P2/P3

• Load Profile

• Top Service/Module

• Top Client IDs.

• Top SQL Command Types.

• Top Phases of Execution

• Top SQL.

• Top SQL with Top Events.

• Top SQL with Top Row Sources.

• Top SQL Using Literals.

• Top Parsing Module/Action.

• Complete List of SQL Text

• Top PL/SQL

• Top Java.

• Top Sessions

• Top Sessions

• Top Blocking Sessions

• Top Sessions Running PQs

• Top Objects/Files/Latches

• Top DB Objects.

• Top DB Files.

• Top Latches

• Activity Over Time

10 Instance Tuning Using Performance Views

Instance Tuning Steps

• Define the Problem.

• Examine the Host System

• CPU Usage.

• Non-Oracle Processes.

• Oracle Processes.

• Oracle Database CPU Statistics

• Interpreting CPU Statistics.

• Identifying I/O Problems

• Identifying I/O Problems Using V$ Views

• Identifying I/O Problems Using Operating System Monitoring

• Identifying Network Issues

• Examine the Oracle Database Statistics

• Setting the Level of Statistics Collection.

• V$STATISTICS_LEVEL.

• Wait Events.

• Dynamic Performance Views Containing Wait Event Statistics.

• System Statistics.

• V$ACTIVE_SESSION_HISTORY

• SYSSTAT

• FILESTAT

• ROLLSTAT

• ENQUEUE_STAT

• LATCH.

• Segment-Level Statistics.

• Implement and Measure Change.

Interpreting Oracle Database Statistics

• Examine Load

• Changing Load

• High Rates of Activity

• Using Wait Event Statistics to Drill Down to Bottlenecks.

• Table of Wait Events and Potential Causes.

• Additional Statistics

• Redo Log Space Requests Statistic

• Read Consistency

• Table Fetch by Continued Row.

• Parse-Related Statistics

Wait Events Statistics

• Changes to Wait Event Statistics from Past Releases.

• buffer busy waits

• Causes

• Actions

• segment header.

• data block.

• undo header.

• undo block

• db file scattered read

• Actions

• Managing Excessive I/O.

• Inadequate I/O Distribution.

• Finding the SQL Statement executed by Sessions Waiting for I/O

• Finding the Object Requiring I/O.

• db file sequential read

• Actions

Tuning Instance Recovery Performance: Fast-Start Fault Recovery

• About Instance Recovery.

• Cache Recovery (Rolling Forward).

• Transaction Recovery (Rolling Back)

• Checkpoints and Cache Recovery

• How Checkpoints Affect Performance.

• Fast Cache Recovery Trade-offs

• Configuring the Duration of Cache Recovery: FAST_START_MTTR_TARGET

• Practical Values for FAST_START_MTTR_TARGET.

• Reducing Checkpoint Frequency to Optimize Run-Time Performance

• Monitoring Cache Recovery with V$INSTANCE_RECOVERY

• Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor.

• Calibrate the FAST_START_MTTR_TARGET.

• Determine the Practical Range for FAST_START_MTTR_TARGET

• Determining Lower Bound for FAST_START_MTTR_TARGET

• Determining Upper Bound for FAST_START_MTTR_TARGET

• Selecting Preliminary Value for FAST_START_MTTR_TARGET

• Evaluate Different Target Values with MTTR Advisor

• Enabling MTTR Advisor.

• Using MTTR Advisor

• Viewing MTTR Advisor Results: V$MTTR_TARGET_ADVISORY

• Determine the Optimal Size for Redo Logs

11 Database Memory Allocation

About Database Memory Caches and Other Memory Structures

Database Memory Management Methods

• Automatic Memory Management.

• Automatic Shared Memory Management.

• Manual Shared Memory Management.

• Automatic PGA Memory Management.

• Manual PGA Memory Management.

• Using Automatic Memory Management

• Monitoring Memory Management

12 Tuning the System Global Area.

Using Automatic Shared Memory Management

• User Interfaces for Setting the SGA_TARGET Parameter

• Setting the SGA_TARGET Parameter in Oracle Enterprise Manager

• Setting the SGA_TARGET Parameter in the Command-Line Interface

• Setting the SGA_TARGET Parameter.

• Enabling Automatic Shared Memory Management.

• Disabling Automatic Shared Memory Management

13 Tuning the Database Buffer Cache

About the Database Buffer Cache

Configuring the Database Buffer Cache

• Using the V$DB_CACHE_ADVICE View.

• Calculating the Buffer Cache Hit Ratio.

• Interpreting the Buffer Cache Hit Ratio

• Increasing Memory Allocated to the Database Buffer Cache

• Reducing Memory Allocated to the Database Buffer Cache.

Configuring Multiple Buffer Pools

• Considerations for Using Multiple Buffer Pools

• Random Access to Large Segments.

• Oracle Real Application Cluster Instances

• Using Multiple Buffer Pools

• Using the V$DB_CACHE_ADVICE View for Individual Buffer Pools.

14 Tuning the Shared Pool and the Large Pool

About the Shared Pool

• Benefits of Using the Shared Pool.

• Shared Pool Concepts.

• Library Cache Concepts

• Data Dictionary Cache Concepts

• SQL Sharing Criteria.

15 Tuning the Result Cache

About the Result Cache

• How Results are Retrieved in a View.

• Client Result Cache Concepts.

• Benefits of Using the Client Result Cache

• Understanding How the Client Result Cache Works.

16 Tuning the Program Global Area.

About the Program Global Area

• Work Area Sizes.

Sizing the Program Global Area

• Configuring Automatic PGA Memory Management.

• Setting the Initial Value for PGA_AGGREGATE_TARGET.

• Monitoring Automatic PGA Memory Management.

• Using the V$PGASTAT View

• Using the V$PROCESS View.

17 I/O Configuration and Design

About I/O

I/O Configuration

• Lay Out the Files Using Operating System or Hardware Striping.

• Requested I/O Size.

• Concurrency of I/O Requests

• Alignment of Physical Stripe Boundaries with Block Size Bound

• Manageability of the Proposed System.

• Manually Distributing I/O

18 Managing Operating System Resources

Understanding Operating System Performance Issues

• Using Operating System Caches.

• Asynchronous I/O

• FILESYSTEMIO_OPTIONS Initialization Parameter

• Limiting Asynchronous I/O in NFS Server Environments.

Resolving Operating System Issues

• Performance Hints on UNIX-Based Systems.

• Performance Hints on Windows Systems

• Performance Hints on HP OpenVMS Systems

Understanding CPU

Resolving CPU Issues

• Finding and Tuning CPU Utilization.

• Checking Memory Management.

• Paging and Swapping.

• Oversize Page Tables.

• Checking I/O Management.

• Checking Network Management.

• Checking Process Management.

• Scheduling and Switching.

• Context Switching.

• Starting New Operating System Processes.

• Managing CPU Resources Using Oracle Database Resource Manager

• Managing CPU Resources Using Instance Caging