Course Content
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
- SQL Troubleshooting Tips and Techniques
- Index Review/Tips & Techniques
- Identify good and poor indexes
- Monitor Index Usage
- Using Bit-map Indexes
- A close look at sub-query coding techniques
- Index Review/Tips & Techniques
- 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
- Adaptive Cursors Sharing
- Other SQL Tuning Tips
Gathering Statistics
- Analyzing Statistics
- DBMS_STATS
- Automatic Optimizer Statistics Collection
- Histograms
How to Generate Histograms Statistics
- Tuning the SGA (including new 11g topics)
- Additional Oracle Hash and Sort Trace information
Oracle Internals: How Oracle writes
- Various Space Management Issues discussed
- Monitoring Sorting
- Finding Problem SQL using V$ information
- Tuning the network/Tuning the client
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 AWR
- Create and manage AWR snapshots
- Generate AWR reports
- Create snapshot sets and compare periods
- Generate ADDM reports
- Generate ASH reports
- Using the SQL Tuning Advisor
