Oracle High Performance Tuning

Course Objectives
  • Use the Oracle Database tuning methodology appropriate to the available tool
  • Utilize database advisors to proactively tune an Oracle database
  • Use the tools based on the Automatic Workload Repository to tune the database
  • Use Statspack reports to tune the database
  • Diagnose and tune common database performance problems
  • Use Enterprise Manager performance-related pages to monitor an Oracle database
Introduction
  • Course Objectives
  • Structured Query Language
  • Tasks of an Oracle Database Administrator
Performance Tuning Overview:

  • Write appropriate tuning goals
  • Apply the tuning methodology
  • Balance performance and safety trade-offs
  • Identify common tuning problems
  • Log a performance Service Request with Oracle Support
Statistics and Wait Events:

  • Identify dynamic performance views useful in tuning
  • Identify key tuning components of the alert log file
  • Identify key tuning components of user trace files
  • Use dynamic performance views to view statistics and wait events
Metrics, Alerts, and Baselines:

  • View metrics using the metrics history views
  • Create metric thresholds
  • View alerts
  • Create metric baselines
  • Enable adaptive thresholds
Using 8i/9i Statspack:

  • Install Statspack
  • Create Statspack snapshots
  • Generate Statspack reports
  • Identify the major sections of the Statspack report
Using 10g/11g AWR:

  • Create and manage AWR snapshots
  • Generate AWR reports
  • Create snapshot sets and compare periods
  • Generate ADDM reports
  • Generate ASH reports
Administering Users:

  • Creating a User
  • Privileges
  • Roles
Reactive Tuning:

  • Use Enterprise Manager pages to identify symptoms
  • Eliminate operating system issues
Tuning the Shared Pool:

  • Diagnose shared pool problems
  • Size the shared pool
  • Size the reserved area
  • Keep objects in the shared pool
Tuning the Buffer Cache:

  • Describe the buffer cache architecture
  • Size the buffer cache
  • Resolve common performance issues related to the buffer cache
  • Use common diagnostic indicators to suggest a possible solution
Automatic Shared Memory:

  • Enable Automatic Shared Memory
  • Set auto-tuned memory parameters
  • Set the manually tuned SGA parameters
  • Use the SGA advisor to set SGA target
Checkpoint and Redo Tuning:

  • Diagnose checkpoint and redo issues
  • Implement Fast Start MTTR target
  • Monitor performance impact of Fast Start MTTR target
  • Implement multiple database writers
  • Tune the redo chain
  • Size the redo log file
  • Size the redo log buffer
Tuning I/O:

  • Diagnose database I/O issues
  • Describe the Stripe and Mirror Everything (SAME) concept
  • Explain the benefits of asynchronous I/O
  • Choose appropriate I/O solutions
Tuning PGA and Temporary Space:

  • Diagnose PGA memory issues
  • Size the PGA memory
  • Diagnose temporary space issues
  • Specify temporary tablespace parameters for efficient operation
Tuning Block Space Usage:

  • Tune segment space management
  • Convert from dictionary managed tablespaces
  • Convert to automatic segment space management
  • Tune block space management
  • Diagnose and correct row chaining
  • Diagnose table fragmentation
  • Compare characteristics of bigfile and smallfile tablespaces

Comments are closed.