Oracle Database 11g: Performance Tuning

Introduction

• This lesson introduces the Performance Tuning course objectives and agenda

Performance Tuning Overview

• Write appropriate tuning goals
• Apply the tuning methodology
• Identify common tuning problems
• Log a performance Service Request with Oracle Support

Basic Tuning Tools

• Monitoring tools overview
• Enterprise Manager
• V$ Views, Statistics and Metrics
• Wait Events

Using Statspack

•  Install Statspack
• Create Statspack snapshots
• Generate Statspack reports
• Identify the major sections of the Statspack report

Using Automatic Workload Repository

• Managing the Automatic Workload Repository Create AWR Snapshots
• Generate AWR reports
• Create snapshot sets and compare periods
• Generate ADDM reports
• Generate ASH reports
• Real Time SQL Monitoring

Using AWR Based Tools

• Automatic Maintenance Tasks
• ADDM Performance Monitoring
• Active Session History

Defining Problems

• Defining the Problem
• Top SQL Reports
• ADDM Tuning Session
• Performance versus Business Requirements
• Performance Tuning Resources & Filing a Performance Service Request

Using Metrics and Alerts

• Metrics, Alerts, and Baselines
• Limitation of Base Statistics & Typical Delta Tools
• Benefits of Metrics
• Viewing Metric History Information & using EM to View Metric Details
• Statistic Histograms & Histogram Views
• Database Control Usage Model & Setting Thresholds

Using Baselines

• Comparative Performance Analysis with AWR Baselines
• Automatic Workload Repository Baselines
• Moving Window Baseline
• Baselines in Performance Page Settings & Baseline Templates
• AWR Baselines & Creating AWR Baselines
• Managing Baselines with PL/SQL & Baseline Views
• Performance Monitoring and Baselines & Defining Alert Thresholds Using a Static Baseline

Identifying Problem SQL Statements

• SQL Statement Processing Phases & Role of the Oracle Optimizer
• Identifying Bad SQL, Real Time SQL Monitoring
• What Is an Execution Plan? Methods for Viewing Execution Plans & Uses of Execution Plans
• DBMS_XPLAN Package: Overview & EXPLAIN PLAN Command
• Reading an Execution Plan, Using the V$SQL_PLAN View & Querying the AWR
• SQL*Plus AUTOTRACE & SQL Trace Facility
• How to Use the SQL Trace Facility
• Generate an Optimizer Trace

Influencing the Optimizer

• Functions of the Query Optimizer, Selectivity, Cardinality and Cost & Changing Optimizer Behavior
• Using Hints, Optimizer Statistics & Extended Statistics
• Controlling the Behavior of the Optimizer with Parameters
• Enabling Query Optimizer Features & Influencing the Optimizer Approach
• Optimizing SQL Statements, Access Paths & Choosing an Access Path
• Join & Sort Operations
• How the Query Optimizer Chooses Execution Plans for Joins
• Reducing the Cost

Using SQL Performance Analyzer

• Real Application Testing: Overview & Use Cases
• SQL Performance Analyzer: Process & Capturing the SQL Workload
• Creating a SQL Performance Analyzer Task & SPA DBMS_SQLTUNE.CREATE_TUNING_TASK
• Comparison Report & Comparison Report SQL Detail

SQL Performance Management

• Maintaining SQL Performance and Optimizer Statistics & Automated Maintenance Tasks
• Statistic Gathering Options & Setting Statistic Preferences
• Restore Statistics
• Deferred Statistics Publishing
• Automatic SQL Tuning
• SQL Tuning Advisor
• Using the SQL Access Advisor
• SQL Plan Management

Using Database Replay

• The Big Picture & System Architecture
• Capture & Replay Considerations
• Replay Options & Analysis
• Packages and Procedures
• Data Dictionary Views: Database Replay

Tuning the Shared Pool

• Shared Pool Architecture & Operation
• The Library Cache & Latch and Mutex
• Diagnostic Tools for Tuning the Shared Pool
• Avoiding Hard & Soft Parses
• Sizing the Shared Pool & Avoiding Fragmentation
• Data Dictionary Cache & SQL Query Result Cache
• Large Pool & Tuning the Large Pool

Tuning the Buffer Cache

• Oracle Database Architecture: Buffer Cache
• Database Buffers
• Buffer Cache Tuning Goals and Techniques
• Buffer Cache Performance Symptoms & Solutions
• Automatically Tuned Multiblock Reads
• Flushing the Buffer Cache (for Testing Only)

Tuning PGA and Temporary Space

• SQL Memory Usage & Performance Impact
• SQL Memory Manager
• Configuring Automatic PGA Memory & Setting PGA_AGGREGATE_TARGET Initially
• Monitoring & Tuning SQL Memory Usage
• PGA Target Advice Statistics & Histograms
• Automatic PGA and Enterprise Manager & Automatic PGA and AWR Reports
• Temporary Tablespace Management: Overview & Monitoring Temporary Tablespace
• Temporary Tablespace Shrink & Tablespace Option for Creating Temporary Table

Automatic Memory Management

• Oracle Database Architecture, Dynamic SGA & Memory Advisories
• Increasing the Size of an SGA Component
• Behavior of Auto-Tuned & Manually Tuned SGA Parameters
• Using the V$PARAMETER View & Resizing SGA_TARGET
• Disabling, Configuring & Monitoring Automatic Shared Memory Management (ASMM)

Tuning Segment Space Usage

• Space and Extent Management & Locally Managed Extents
• How Table Data Is Stored & Anatomy of a Database Block
• Minimize Block Visits
• The DB_BLOCK_SIZE Parameter
• Small & Large Block Size: Considerations
• Block Allocation, Free Lists & Block Space Management with Free Lists
• Automatic Segment Space Management
• Migration and Chaining, Shrinking Segments & Table Compression

Tuning I/O

• I/O Architecture, File System Characteristics, I/O Modes & Direct I/O
• Enterprise Manager
• Stripe and Mirror Everything
• Using RAID
• I/O Diagnostics
• Database I/O Tuning
• What Is Automatic Storage Management?