Session 1: INTRODUCTION TO ORACLE PERFORMANCE TUNING
- Tuning Overview
- Resolving Performance Problems
- The Oracle Approach to Tuning
- Items to Evaluate
- Where to Look
- Plan a Routine Monitoring Regime
- Build a New Database for Performance
- Tune an Existing Database for Performance
- Set Suitable Goals
Session 2: DIAGNOSING PROBLEMS
- Diagnostic Sources
- Log a Performance Service Request (SR)
- Tips for Avoiding Problematic Queries
Session 3: TOOLS FOR EVALUATING SQL STATEMENTS
- Overview of SQL Statement Tuning
- Explain Plan and autotrace
- SQL Trace and the tkprof Program
- Interpreting SQL Trace
Session 4: THE OPTIMIZER
- The SQL Optimizer
- The Rule Based Optimizer
- The Cost Based Optimizer
- The Analyze Command
- Manage Statistics with DBMS_STATS
Session 5: INDEXES
- Index Overview
- B*Tree Indexes
- Access Paths with Indexes
- Index Scans
- Conditions which Stop Indexes Being Used
- Parameters Affecting Optimizer Index Choice
Session 6: ADVANCED INDEXES
- Bitmap Indexes
- Key Compressed Indexes
- Index Organized Tables
- Function-Based Indexes
- Descending Indexes
Session 7: TYPES OF TABLE JOINS
- Joining Tables
- Nested Loops Join
- Cluster Join and Hash Join
- Anti Join and Semi Join
- Outer Join
- Star Join
Session 8: METRICS, ALERTS AND BASELINES
- System Generated Alerts
- Threshold Based Alerts
- Event Based Alerts
- Retrieve Threshold Information
- View Alert Data
- Manage Metrics Using EM
- Blackouts
- Baselines and Adaptive Thresholds
Session 9: STATSPACK
- Overview Of Statspack
- Statspack Architecture
- Install Statspack
- Adjust the Statspack Collection Level
- Create, View and Delete Snapshots
- Generate a Report
- Analyze the Reports to Generate an Execution Plan for a Given SQL Statement
- Resolve Wait Events
- Remove Statspack from the Database
Session 10: AUTOMATIC WORKLOAD REPOSITORY
- Automatic Workload Repository
- Create Snapshots and Compare Snapshots
- Use Baselines
- Generate AWR Reports
- Compare AWR Periods Reports
- Generate Active Session History (ASH) Report
- Automatic Database Diagnostic Monitor (ADDM)
- Generate and Analyze ADDM Reports
Session 11: REACTIVE TUNING
- Use Enterprise Manager to Identify Common Problems
- Eliminate Operating System Contentions
Session 12: TUNING THE SHARED POOL
- Overview of the Shared Pool
- Alter and Tune the Shared Pool Size
- Tune the Data Dictionary Cache and the Library Cache
- Shared Pool Advisory Statistics
- Pin objects in the Shared Pool
- Cursor Sharing
- Using the Large Pool
- Latches and Mutexes
Session 13: TUNE THE BUFFER CACHE
- Memory Structures
- Data Block Buffer Pool
- Multiple Buffer Caches
- Manage Free Buffers
- Establish the Buffer Pool Efficiency
- Cache Tables in the Buffer Pools
- Common Performance Issues and the Buffer Cache
Session 14: AUTOMATIC MEMORY MANAGEMENT
- Automatic Memory Management
- Automatic Shared Memory Management
- Memory Allocation Advisors
- The Memory Advisor
- The Shared Pool and Buffer Cache Advisors
Session 15: TUNE PGA AND TEMPORARY SPACE
- PGA Overview
- Automatic PGA Mode
- Configure the PGA for a New Instance
- The Program Global Area Advisor
- PGA Management Data Dictionary Views
- Tune the PGA_AGGREGATE_TARGET
- SQL Operations that Use Sorts
- Sorting to Disk
- Multiple Temporary Tablespaces
- Allocate Temporary Disk Space for Sorting
- Temporary Tablespace Shrink
- Data Dictionary Views and Sort Segments
Session 16: CHECKPOINT AND REDO TUNING
- Checkpoint and Redo Issues
- Instance Recovery Performance Tuning
- Redo Log File Size Advisor
- Size the Redo Log Buffer
- Set Multiple Database Writers
Session 17: TUNE I/O
- Database I/O Management
- Layout the Files Using OS or Hardware Striping
- Manually Distribute Files to Reduce I/O Contention
- When to Separate Files
- Sample Configurations
- Select the Data Block Size
- Synchronous I/O Benefits
- FILESYSTEMIO_OPTIONS Parameter
- Automatic Storage (ASM) Monitoring
Session 18: USE ORACLE BLOCKS EFFICIENTLY
- Determine the Database Block Size
- Tablespace, Table and Index Storage Parameters
- Automatic Segment Space Management
- Detect and Resolve Freelist Contention
- Rebuild Tables and Indexes
- The DBMS_SPACE Package
- Row Migration
- Chained Rows
- Bigfile Versus Smallfile Tablespaces
- Extended ROWID Format
- Convert from Dictionary Managed Tablespaces
- Convert to Automatic Segment Space Management
Session 19: MATERIALIZED VIEWS
- Overview of Materialized Views
- Materialized View Creation
- Materialized View Refresh
- Query Rewrite
- Materialized View Data Dictionary Views
Session 20: PARALLEL EXECUTION
- Overview of Parallel Execution
- Enabling and Using Parallel Execution
- The Degree of Parallelism
- SQL Hints
- Restrictions on the Use of Parallel Execution
- Dictionary Views on Parallel Execution
- Specific Automatic Parallel Query Optimization
- Nologging Options
Session 21: TRACING SERVICES
- End to End Tracing
- Enable and Disable Tracing
- Check for Tracing Enabled
- The trcess Utility
- Session Based Tracing
- Enable and View SQL Tracing with Enterprise Manager
- Analyze Trace Results
Session 22: NEW 11G SQL AND PL/SQL TUNING FEATURES
- SQL Query Result Cache
- PL/SQL Function Cache
- Adaptive Cursors
- Invisible Indexes
Session 23: DATABASE REPLAY
- Overview of Database Replay
- The Database Replay Process
- Capture a Workload
- Pre-process the Captured Workload
- Replay the Workload
- Database Replay Usage
- The DBMS_WORKLOAD Packages
Session 24: SQL PERFORMANCE MANAGEMENT AND ANALYSIS
- Statistics Gathering
- SQL Performance Analyzer
- SQL Plan Management
- Automatic SQL Tuning
- Manual Creation and Use of SQL Tuning Sets
Session 25: ADDM AND AWR NEW FEATURES IN ORACLE 11g
- ADDM New Features in Oracle 11g
- AWR New Features in Oracle 11g
- Adaptive Metric Thresholds
- Automatic SQL Tuning