Oracle9i Database Performance Tuning

What you will learn:

Audience:

• Database Administrators
• Technical Consultant

Prerequisites:

Required Prerequisites:

• Oracle9i Database Administration Fundamentals II

Course Objectives:

• Design and configure an Oracle database with an emphasis on good performance
• Perform day-to-day monitoring on an operational database using Oracle Enterprise Manager or Statspack
• List the important steps in a tuning methodology
• Employ Oracle tools to diagnose performance problems
• Perform tuning tasks with Oracle Enterprise Manager
• Perform tuning tasks using the command line interface
• Reconfigure file structures for performance
• Identify and resolve I/O, data storage and database configuration problems
• Optimize sort operations
• Configure Oracle Shared Server
• Configure Resource Manager to control resource usage

Course Topics:

Overview of Oracle 9i Database Performance Tuning

• Tuning questions, phases, and goals
• Common performance problems
• Tuning during development
• Tuning steps for a production database
• Performance and availability trade-offs

Diagnostic and Tuning Tools

• Log and trace files
• Tuning with Oracle Enterprise Manager
• Statspack
• Statistics, troubleshooting data, and performance data in the data dictionary
• Database and self-built performance statistics collection
• Collecting performance statistics with assorted tools

Database Configuration and I/O Issues

• Distributing files across devices
• Tablespace considerations
• Full table scan performance
• Checkpoint performance
• Redo management and performance
• Analyzing I/O activity
• Analyzing checkpoint performance

Tuning the Shared Pool

• Shared pool components and latches
• Monitor and tune the library cache
• Manage SQL statements and PL/SQL blocks to improve library cache performance
• Monitor and tune the data dictionary cache
• Reserved pool and large pool management
• Using diagnostic tools to monitor and tune the shared pool

Tuning the Buffer Cache

• Buffer cache sizing parameters
• Buffer cache advisory
• Buffer cache tuning views and statistics
• Buffer caches for different purposes and non-standard block sizes
• Automatic segment space and free space management
• Multiple database writing processes
• Monitoring and tuning database buffer cache with available diagnostic tools

Dynamic Instance Resizing

• Dynamic allocation units in the SGA
• Resize SGA components
• Resizing the buffer cache and the shared pool

Sizing Other SGA Structures

• Manage redo log buffer
• Control redo operations
• Manage Java pool memory
• Managing the redo log buffer

Tuning the Oracle Shared Server

• Use and performance of shared server processes
• Shared servers and memory usage
• Monitor and troubleshoot shared server activity

Optimizing Sort Operations

• Automatic PGA memory management
• PGA sizing advisor
• Sorts and temporary segments
• Tuning, diagnostics, and guidelines for sorts
• Reduce sorting
• Configuring and monitoring sort space options
• Configuring automatic PGA memory management

Using Resource Manager

• Database Resource Manager concepts
• Administering the Database Resource Manager
• Define resource plans limits
• Assigning users to consumer groups
• Manual and automatic consumer group switching
• Examine resource plan information in the data dictionary

SQL Statement Tuning

• Optimizer modes
• Hints
• Plan stability and stored outlines
• SQL tuning diagnostic tools
• Interpret results from SQL tuning diagnostic tools

Managing Statistics

• Automatic segment-level statistics collection
• Dynamic sampling
• Table statistics, index statistics, and index tuning wizard
• Column statistics and histograms
• Estimated statistics and automatic statistics collection
• Optimizer cost model
• System statistics
• Copying statistics between databases

Using Oracle Blocks Efficiently

• Database storage components
• Extent management
• High water-mark
• Space management for tables
• Index management

Using Oracle Data Storage Structures Efficiently

• Data storage structures
• Selecting physical storage characteristics for performance
• Data access methods and structures

Application Tuning

• Role of the database administrator
• Table movement and redefinition
• Creating and managing indexes and index-organized tables
• Statistics collection
• Considerations for OLTP systems
• Considerations for decision support systems and data warehouses
• Considerations for hybrid systems

Using Materialized Views

• Create and refresh materialized views
• Nested materialized views
• Query rewrites
• The DBMS_MVIEW package

Monitoring and Detecting Lock Contention

• Locking mechanisms and management
• Data concurrency
• Enqueues and lock types
• Lock monitoring and resolution of conflicts
• Deadlocks
• Use diagnostic tools to monitor lock contention

Tuning the Operating System

Workshop Overview

• Workshop configuration
• Workshop procedure
• Workshop scenarios