Oracle 11G Performance Tuning

 Introduction

• Cause of Performance Problems
• Setting Performance Goals
• The Tuning Cycle
• ORACLE Architecture
• Logical Storage Structures
• Physical Structures
• Memory Structures
• The Shared Pool
• Processes
• Some Administration Terminology

Design

• Data Design Phase
• Data Model Design
• Online Transaction Processing
• Decision Support Systems
• Multi-purpose Applications

Optimizing SQL

• Physical Retrieval of Data
• Full Table Scan versus Index Reads
• Performance Diagnostic Tools
• Explain Plan
• SQL Trace Facility
• TKPROF
• Autotrace
• Join Methods
• Sort/Merge Joins
• Nested Loops
• Hash Joins
• Hash Join Example Data Access Methods

Indexes

• Basic Indexes
• B-Tree Indexes
• Bitmap Index
• Comparing B-Tree and Bitmap Indexes
• Reverse Key Index
• Index-Organised Tables
• Invisible index
• Creating Monitoring and Maintaining Indexes

Automatic SQL Tuning

• Query Optimizer Modes Types of Tuning Analysis
• Automatic workload repository SQL Tuning Advisor SQL Tuning Sets Top SQL
• Identify high-load SQL Dynamic Performance views

SQL Performance Analyzer

• Describe SQL Performance Analyzer process and benefits
• Use SQL Performance Analyzer

The Optimizer

• The ORACLE Optimizer
• SQL statement parsing
• Initialisation parameters
• Rule Based Optimizer
• Cost Based Optimizer
• Rule / Cost Comparisons
• Choosing an Approach
• Multiple WHERE Clauses
• Using Indexes for Sorts
• Multiple Table Joins
• Disabling Indexes
• Hints
• Sharing SQL Statements
• Sharing Cursors
• Hints
• Sharing SQL Statements
• Sharing Cursors
• Adaptive Cursors Sharing
• Other SQL Tuning Tips

Gathering Statistics

• Analyzing Statistics
• DBMS_STATS
• Automatic Optimizer Statistics Collection
• Histograms
• How to Generate Histograms Statistics

SQL Plan Management

• SQL outlines
• SQL profiles
• SQL Access Advisor
• Set up and use SQL plan baseline

Advanced Tuning

• Star Queries
• Materialized Views
• Refreshing Views
• Materialized View Logs
• SQL Result Cache
• Temporary Tables

Statistics and Wait Events

• Identify dynamic performance views useful in tuning
• Identify key tuning components of the alert log file
• Identify key tuning components of user trace files
• Use dynamic performance views to view statistics and wait events

Metrics, Alerts, and Baselines

• View metrics using the metrics history views
• Create metric thresholds
• View alerts
• Create metric baselines
• Enable adaptive thresholds

Using Statspack

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

Using Statspack

• Create and manage AWR snapshots
• Generate AWR reports
• Create snapshot sets and compare periods
• Generate ADDM reports
• Generate ASH reports