Oracle Database Performance Tuning Workshop
Duration: 4 Days
$3,200.00
| This course covers techniques for diagnosing and fixing performance issues within the instance and the database. These include Standard Edition facilities, also facilities available only with Enterprise Edition license and with EE options. Some aspects of SQL tuning are discussed, but generally speaking SQL tuning is dealt with elsewhere in the curriculum. There are three concluding topics: Real Application Testing, Partitioning, and parallel operations. These are optional topics, some or all of which may be covered depending on time, interest, and requisite background knowledge. As with all SkillBuilders courses, this class (if conducted at your company facilities) is highly customizable to your specific training requirements.
The course is run against the current release of the database. Much of the content is applicable to earlier releases, and will be “back-ported” as necessary. |
 Oracle Certified Master John Watson |
 |
The tuning methodology
- What can be tuned?
- The concept of DB Time
- The response time tuning method
- Memory structures: SGA and PGA
- Background processes
- Session architecture
- Multithreaded and multiprocess models
- The SQL parse / bind / execute / fetch cycle
- Use the wait event interface
- Enable session tracing
- Interrogate the Active Session History
- Disc I/O events
- Buffer cache related events
- Shared pool events
- Redo generation events
- Contention events
- Enqueue waits
- Latch and mutex events
- 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
- 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)
- 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
- Define statistics and metrics
- Use the server generated alert system
- Set up alerts on baselines
- Investigate the capabilities of the Autotask system
- 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
- 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
- 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
- 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
- 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
- The issues of realistic testing
- The SQL Performance Analyzer
- Database Replay
- Scenarios for using Real Application Testing
- 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
- When parallel execution is helpful
- When and how to use parallel query
- Parallel automatic tuning features
- Parallel DML and parallel DDL
- Parallel execution plans
- What actually matters? Response time.
- The tuning methodology
- Oracle's top ten performance issues list
