Oracle Database 11g: Performance Tuning

Содержание

Exam Topics:

Basic Tuning Tools

• Diagnose performance issues using top wait events and the time model
• Use dynamic performance views to view statistics and wait events
• Use Enterprise Manager Monitoring
• Identify the key tuning components of the alert logs

Using Automatic Workload Repository

• Create and manage AWR snapshots
• Generate and read AWR reports, analyze Compare Periods reports

Defining Problems

• Identify performance issues & set tuning priorities
• Interpret tuning diagnostics
• Tune for life cycle phase

Using Metrics and Alerts

• Interprete metric views and alerts

Baseline

• Create and manage AWR baselines
• Create AWR baselines for future time periods
• Monitor performance and enable adaptive thresholds

Using AWR-Based Tools

• Tune automatic maintenance tasks
• Analyze ADDM reports
• Analyze Active Session History (ASH) reports

Monitoring an Application

• Configure and manage services
• Use services with client applications, Database Resource Manager and Scheduler
• Configure services aggregation, tracing, and set performance-metric thresholds

Identifying Problem SQL Statements

• Describe SQL statement processing
• View the SQL statement statistics to identify the SQL statements that perform poorly
• Describe and use methods for Viewing Execution Plans

Influencing the Optimizer

• Describe the optimizer's behavior
• Adjust parameters to influence the optimizer
• Influence the optimizer (access methods, join techniques and adjust data structures)

SQL Performance Management

• Manage optimizer statistics
• Configure automatic SQL tuning and SQL profiling
• Set up and use SQL plan baseline

Using SQL Performance Analyzer

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

Using Database Replay

• Identify when and how to use Database Replay
• Use Enterprise Manager to record and replay workloads

Tuning the Shared Pool

• Describe Latches and Mutexes
• Diagnose and change cursor sharing
• Diagnose and resolve shared pool fragmentation
• Manage the results cache and tune the large pool
• Tune the data dictionary cache

Tuning the Buffer Cache

• Describe the buffer cache architecture
• Explain the symptoms that indicate a buffer cache problem
• Tune the buffer cache for performance issues

Tuning PGA and Temporary Space

• Diagnose PGA memory issues and size the PGA
• Diagnose temporary space issues
• Specify temporary tablespace parameters for efficient operation

Automatic Memory Management

• Implement Automatic Shared Memory Management (ASMM)
• Implement Automatic Memory Management (AMM)

Tuning Segment Space Usage

• Tune segment space management
• Use Automatic Segment Space Management
• Diagnose row migration and c haining
• Use segment shrinking and table compression

Tuning I/O

• Diagnose database I/O issues
• Describe the Stripe and Mirror Everything (SAME) concept
• Set filesystemio_options and Choose appropriate I/O solutions
• Tune Automatic Storage Management (ASM)

Appendix B Using Statspack

• Using Statspack