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

Outline:

• 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