Oracle Database Performance Tuning

This course covers all Oracle 10g performance related features along tuning application code such as SQL or PL/SQL.

Overview

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.

In addition, the course covers all Oracle 10g performance related features along tuning application code such as SQL or PL/SQL.

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

Audience

Oracle Database Administrators (DBAs).

Outline

Database Tuning Overview

  • Set appropriate tuning goals
  • Apply a tuning methodology
  • Identify potential tuning problems – Class Exercise

Database Architecture Overview

Tuning the Shared Pool Overview

Tuning the Buffer Cache Overview

Automatic Shared Memory Management (ASMM)

  • Feature Overview and Pros and Cons
  • Enable Automatic Shared Memory
  1. The SGA_TARGET and STATISTICS_LEVEL Parameters
  • The MMAN Background Process
  • Areas Managed by ASMM
  • Monitoring ASMM Events
  1. Dynamic Performance Views

Tuning PGA and Temporary Space

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

Checkpoint and Redo Tuning

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

Tuning I/O Overview

Tuning Block Space Usage

  • Tune segment space management
  1. Dictionary vs Locally managed tablespaces
  2. 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

Database Tuning Tools

Statspack

The Automatic Workload Repository (AWR) and the Advisory Framework

  • The Advisory Framework Architecture
  1. OEM and Non-OEM Usage
  2. Performance Statistics Sources
  3. ASH
  4. The AWR
  5. The Advisors
  • Create and Manage AWR Snapshots
  • Generate AWR reports
  1. The awrinfo.sql script
  2. The awrrpt.sql script
  3. AWR Data Dictionary Views
  • Create snapshot sets (baselines) and compare periods
  • Generate ADDM reports
  1. The addmrpt.sql script

The SQL Trace Facility


Tuning SQL and PL/SQL

Database Architecture and Processing SQL

  • Database Architecture related to Processing SQL
  • SQL Internal Processing Steps and Bottlenecks
  • Listing the SQL Statement Processing Steps
  1. Parsing
  2. Syntax Check
  3. Semantic Check
  4. Hard Parse vs. Soft Parse
  • Identifying Means to Minimize Hard Parsing
  1. SQL Coding Standards
  2. Identifying Identical Statements
  3. The CURSOR_SHARING Parameter
  4. Using Bind Variables
  5. Sizing the Shared Pool Memory Structure
  6. Pinning SQL in the Shared Pool
  • Monitoring SQL Usage and Parsing
  1. Data Dictionary Views
  2. V$SQL, V$SQLAREA, V$SQLTEXT
  3. V$SYSSTAT, V$SESSTAT

 

The Optimizer Overview

Indexes

  • Index Types
  1. Unique and Non-Unique B-Tree
  2. Function-Based
  3. Bitmap
  4. Bitmap Join
  5. Reverse Key
  6. Cluster
  7. Local Partition
  8. Global Partition
  • Pros and Cons of each index type
  • Identifying Row Access Methods
  • Creating B-Tree Indexes
  • Understanding B-Tree Index Access and Index Merging
  • Index Skip Scanning
  • How DML affects Indexes
  • Negating Index Usage
  • Monitoring for Unused Indexes
  • ALTER INDEX …MONITORING USAGE;
  • The V$OBJECT_USAGE View
  • Index Do’s
  • Index Don’ts

Inefficient Indexes (Detecting and Correcting)

Access and Join Methods

  • Index Access Paths
  • FULL Table Access Path
  • Join Methods
  1. Nested Loop
  2. Sort-Merge
  3. Hash
  4. Pros and Cons of Each Join Method

Hints

  • Hints Usage
  • Hint Types
  1. Optimizer
  2. Access Path
  3. Join
  4. Miscellaneous