• Decrease font size
  • Default font size
  • Increase font size

Oracle Database Performance Tuning

 Print E-mail

Course Length - 5 half-days

In this course students will clearly define the various methodologies one can use to tune an Oracle Database. The course covers the various components of an Oracle Database where potential performance bottlenecks could occur and how to monitor and tune them.

Throughout the course, students experience hands-on exercises in tuning a database.

Who Should Attend

Oracle Database Administrators (DBAs).

Course Outline

Database Tuning Overview

  • Set appropriate tuning goals
  • Apply a tuning methodology
  • Identify potential tuning problems

Database Architecture

  • Understand all database architecture components
  • The Database Architecture
    • Memory Structures
    • The SGA
      • The Database Buffer Cache
      • The Shared Pool
      • Other Buffer Caches
      • The KEEP Cache
      • The RECYCLE Cache
      • Non-Standard Block Size Caches
      • The Large Pool
      • The Java Pool
      • The Streams Pool
    • Background Processes
    • Checkpoints
    • Server Process
    • Client Processes
    • Database Files
    • Database Objects
    • Blocks
  • Understand potential performance bottlenecks

Automatic Shared Memory Management (ASMM)

  • Enable Automatic Shared Memory
    • The SGA_TARGET and STATISTICS_LEVEL Parameters
  • The MMAN Background Process
  • Areas Managed by ASMM
  • Monitoring ASMM Events
    • Dynamic Performance Views
      • V$SGA_DYNAMIC_COMPONENTS
      • V$SGA_DYNAMIC_FREE_MEMORY
      • V$SGA_CURRENT_RESIZE_OPS
      • V$SGA_RESIZE_OPS

Tuning the Shared Pool

  • Diagnose shared pool problems
  • Size the shared pool
  • Size the reserved area
  • Keep objects in the shared pool
  • SQL Tuning and the Shared Pool
  • Listing the SQL Statement Processing Steps
    • Parsing
    • Syntax Check
    • Semantic Check
    • Hard Parse vs. Soft Parse
  • Identifying Means to Minimize Hard Parsing
    • SQL Coding Standards
    • Identifying Identical Statements
    • The CURSOR_SHARING Parameter
      • EXACT, SIMILAR, FORCE Values
    • Using Bind Variables
    • Sizing the Shared Pool Memory Structure
    • Pinning SQL in the Shared Pool
  • Monitoring SQL Usage and Parsing
    • Data Dictionary Views
    • V$SQL, V$SQLAREA, V$SQLTEXT
    • V$SYSSTAT, V$SESSTAT

Tuning the Buffer Cache

  • Describe the buffer cache architecture
  • Size the buffer cache
    • The DB_CACHE_SIZE Parameter
  • Resolve common performance issues related to the buffer cache
  • Use common diagnostic indicators to suggest a possible solution
  • Other Buffer Caches
    • When to use
    • KEEP, RECYCLE, Non-Standard Block Sizes
    • Setting up

Tuning PGA and Temporary Space

  • Private PGA vs Public PGA
  • Diagnose PGA memory issues
  • Size the PGA memory
    • SORT_AREA_SIZE vs PGA_AGGREGATE_TARGET
  • Diagnose temporary space issues
  • Specify temporary tablespace parameters for efficient operation
  • Temporary Tablespace Groups (TTG)
    • What they do and what they don’t do for you
    • Creating a TTG
  • Monitoring Sort Efficiencies
    • Dynamic Performance Views
      • V$SYSSTAT
      • V$SESSTAT
      • AUOTRACE Output

Checkpoint and Redo Tuning

  • Diagnose Checkpoint and Redo Issues
  • Setting Checkpoint Performance Goals
  • Checkpoint Frequency Issues
    • Too Often
    • Not Often Enough
  • The FAST_START_MTTR_TARGET Parameter
  • Other Parameters
    • LOG_CHECKPOINT_TIMEOUT
    • LOG_CHECKPOINT_INTERVAL
    • LOCK_CHECKPOINTS_TO_ALERT
    • FAST_START_IO_TARGET
  • Multiple database writers
  • Parameters
    • DB_WRITERS
    • DBWR_IO_SLAVES
  • Tune the redo chain
  • Monitor Redo Log Group Switches
  • When should we switch
    • Size the redo log files
    • Size the redo log buffer
      • Latch Issues

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 Block Space Usage

  • Tune segment space management
    • Dictionary vs Locally managed tablespaces
    • Automatic vs Manual segment space management
  • Tune block space management
  • Diagnose and correct row chaining
  • Diagnose table fragmentation
  • Compare characteristics of bigfile and smallfile tablespaces
  • Choosing a Block Size
  • Using Multiple Block Sizes in a Database

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

Database Tuning Tools

Statspack

  • Install Statspack
  • Create Statspack snapshots
  • Generate Statspack reports
  • Identify the major sections of the Statspack report
  • Statspack’s Future vs. the AWR and the Advisory Framework

The Automatic Workload Repository (AWR) and the Advisory Framework

  • The Advisory Framework Architecture
    • OEM and Non-OEM Usage
    • Performance Statistics Sources
    • ASH
    • The AWR
    • The Advisors
      • SGA Advisor
      • UNDO Advisor
      • SQL Tuning Advisor
      • The Segment Advisor
      • SQL Access Advisor
  • Create and Manage AWR Snapshots
  • Generate AWR reports
    • The awrinfo.sql script
    • The awrrpt.sql script
    • AWR Data Dictionary Views
  • Create snapshot sets (baselines) and compare periods
  • Generate ADDM reports
    • The addmrpt.sql script

The SQL Trace Facility

  • Setting Up the Appropriate Initialization Parameters
  • Formatting Trace Files with TKPROF
  • Interpreting the Output of the TKPROF Command
  • The TRCSESS Utility