Oracle Database Performance Tuning Workshop

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

 

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