Oracle Training Overview
This Oracle Database Performance Tuning training course teaches attendees techniques for diagnosing and fixing performance issues within the instance and the database.
Note: The course uses the current release of the database, but can be delivered in previous versions if needed.
Location and Pricing
Accelebrate offers instructor-led enterprise training for groups of 3 or more online or at your site. Most Accelebrate classes can be flexibly scheduled for your group, including delivery in half-day segments across a week or set of weeks. To receive a customized proposal and price quote for private corporate training on-site or online, please contact us.
In addition, we offer some courses as live, instructor-led online classes for individuals.
Objectives
- Understand the need for and the limitations of database tuning
- Identify performance bottlenecks
- Tune instance memory structures
- Understand the options for database physical storage
- Optimize performance with various table and index structures
- Use supplied facilities for gathering and interpreting performance metrics
- Learn techniques for stabilizing and re-optimizing SQL execution plans
Prerequisites
All students must have an understanding of Oracle database and instance architecture, familiarity with SQL and basic PL/SQL, and be able to work with Linux command-line tools.
Outline
Expand All | Collapse All
Introduction
The Tuning Methodology
- What can be tuned?
- The concept of DB Time
- The response time tuning method
Server Architecture and SQL Statement Processing Review
- Memory structures: SGA and PGA
- Background processes
- Session architecture
- Multithreaded and multiprocess models
- The SQL parse / bind / execute / fetch cycle
Diagnostic Tools and Wait Events
- Use the wait event interface
- Enable session tracing
- Interrogate the Active Session History
Frequently Encountered Wait Events
- Disc I/O events
- Buffer cache related events
- Shared pool events
- Redo generation events
- Contention events
- Enqueue waits
- Latch and mutex events
Using Statspack and AWR Reports
- Understand the automatic snapshot mechanism
- Use the DBMS_WORKLOAD_REPOSITORY package
- Create snapshots
- Create baselines
- Modify settings
- Generate and interpret AWR reports
- Generate AWR SQL reports
- Install and use Statspack
Tuning Memory Structures
- Investigate the SGA memory structures
- Diagnose and fix issues with sizing of the SGA components
- Use automatic shared memory management (ASMM)
- Manage the aggregate PGA
- Understand the need for PGA memory
- Correctly size the aggregate PGA
- Work with temporary tablespaces
- Enable automatic memory management (AMM)
Database Physical Structures
- Understand when and why RAID and/or ASM is needed
- Give Oracle information about disk performance
- Investigate the space allocation algorithms
- Contrast smallfile and bigfile tablespaces
- Describe and evaluate the use of Automatic Storage Management
Metrics, Alerts, and Autotasks: the “Self-Managing” Database
- Define statistics and metrics
- Use the server generated alert system
- Set up alerts on baselines
- Investigate the capabilities of the Autotask system
Indexes
- Understand the purpose of indexes
- Describe the types of index
- B-tree indexes
- Function based indexes
- Reverse key indexes
- Bitmap and bitmap join indexes
- Create and maintain indexes
- The Oracle Text domain index type
Tables
- Heap Tables
- Partitioned Tables
- Index-Organized Tables (IOT)
- Clustered tables
- Temporary Tables
- Datatype selection for SQL performance
- Constraints vs. Triggers
- Table Compression
- Multiple Blocksize Support
- The NOLOGGING Option
- Re-organizing and shrinking tables
- Statistics
- Understand the importance of statistics
- Object statistics
- System statistics
- Dictionary and fixed objects statistics
- Build histograms
- Type and purpose of histograms
- Extended (or “column group”) statistics
- Use dynamic sampling
- Manage SQL directives
Statistics
- Understand the importance of statistics
- Object statistics
- System statistics
- Dictionary and fixed objects statistics
- Build histograms
- Type and purpose of histograms
- Extended (or “column group”) statistics
- Use dynamic sampling
- Manage SQL directives
SQL Execution Plan Stability
- Learn how to stabilize execution plans
- Explain the adaptive reoptimization processes
- Statistics feedback
- Adaptive execution plans
- Contrast SQL Directives with SQL Profiles
- Understand the importance of bind variables
- Impact of bind variables on parsing and the library cache
- Use of adaptive cursor sharing
The Advisors
- Review the Management Packs and the AWR
- The Automatic Database Diagnostic Monitor
- SQL Tuning Advisor
- The SQL Access Advisor
- The Autotask tuning advice functionaility
- SQL Profiles
Real Application Testing
- The issues of realistic testing
- The SQL Performance Analyzer
- Database Replay
- Scenarios for using Real Application Testing
Partitioning
- Why to use partition: performance, manageability, scalability
- Impact on memory and disc IO
- Partitioning strategies: range, hash, list
- Single level and composite partitioning
- Partition tables, indexes, or both
- Performance tuning aspects: partition pruning, partition-wise joins
Parallel Operations
- When parallel execution is helpful
- When and how to use parallel query
- Parallel automatic tuning features
- Parallel DML and parallel DDL
- Parallel execution plans
Common Performance Issues
- What actually matters? Response time.
- The tuning methodology
- Oracle's top ten performance issues list
Conclusion
Training Materials
All Oracle training students receive comprehensive courseware and a related textbook.
Software Requirements
- Intel dual core i5 or faster processor on a 64-bit operating system with at least 8 GB RAM
- A complete installation of Oracle
- Please contact us for detailed setup instructions if you have purchased this class.
