By using our website you agree to our use of cookies.

Oracle Database 10g DBA Performance & Tuning

4 Day Course
Hands On
Code O10GDBAPT

This course has been retired. Please view currently available Oracle DBA Training Courses.

Modules

Collapse all

Introduction to Oracle Performance Tuning (9 topics)

  • Tuning Overview
  • Resolving Performance Problems
  • The Oracle Approach to Tuning
  • Items to Evaluate
  • Where to Look
  • Plan a Routine Monitoring Regime
  • Build a New Database for Performance
  • Tune an Existing Database for Performance
  • Set Suitable Goals

Diagnosing Problems (3 topics)

  • Diagnostic Sources
  • Log a Performance Service Request (SR)
  • Tips for Avoiding Problematic Queries

Tools for Evaluating SQL Statements (4 topics)

  • Overview of SQL Statement Tuning
  • Explain Plan and autotrace
  • SQL Trace and the tkprof Program
  • Interpreting SQL Trace

The Optimizer (5 topics)

  • The SQL Optimizer
  • The Rule Based Optimizer
  • The Cost Based Optimizer
  • The Analyze Command
  • Manage Statistics with DBMS_STATS

Indexes (6 topics)

  • Index Overview
  • B*Tree Indexes
  • Access Paths with Indexes
  • Index Scans
  • Conditions which Stop Indexes Being Used
  • Parameters Affecting Optimizer Index Choice

Advanced Indexes (5 topics)

  • Bitmap Indexes
  • Key Compressed Indexes
  • Index Organized Tables
  • Function-Based Indexes
  • Descending Indexes

Types of Table Joins (6 topics)

  • Joining Tables
  • Nested Loops Join
  • Cluster Join and Hash Join
  • Anti Join and Semi Join
  • Outer Join
  • Star Join

Metricss, Alerts and Baselines (8 topics)

  • System Generated Alerts
  • Threshold Based Alerts
  • Event Based Alerts
  • Retrieve Threshold Information
  • View Alert Data
  • Manage Metrics Using EM
  • Blackouts
  • Baselines and Adaptive Thresholds

Statspack (9 topics)

  • Overview Of Statspack
  • Statspack Architecture
  • Install Statspack
  • Adjust the Statspack Collection Level
  • Create, View and Delete Snapshots
  • Generate a Report
  • Analyze the Reports to Generate an Execution Plan for a Given SQL Statement
  • Resolve Wait Events
  • Remove Statspack from the Database

Automatic Workload Repository (8 topics)

  • Automatic Workload Repository
  • Create Snapshots and Compare Snapshots
  • Use Baselines
  • Generate AWR Reports
  • Compare AWR Periods Reports
  • Generate Active Session History (ASH) Report
  • Automatic Database Diagnostic Monitor (ADDM)
  • Generate and Analyze ADDM Reports

Reactive Tuning (2 topics)

  • Use Enterprise Manager to Identify Common Problems
  • Eliminate Operating System Contentions

Tuning the Shared Pool (8 topics)

  • Overview of the Shared Pool
  • Alter and Tune the Shared Pool Size
  • Tune the Data Dictionary Cache and the Library Cache
  • Shared Pool Advisory Statistics
  • Pin objects in the Shared Pool
  • Cursor Sharing
  • Using the Large Pool
  • Latches and Mutexes

Tune the Buffer Cache (7 topics)

  • Memory Structures
  • Data Block Buffer Pool
  • Multiple Buffer Caches
  • Manage Free Buffers
  • Establish the Buffer Pool Efficiency
  • Cache Tables in the Buffer Pools
  • Common Performance Issues and the Buffer Cache

Automatic Memory Management (5 topics)

  • Automatic Memory Management
  • Automatic Shared Memory Management
  • Memory Allocation Advisors
  • The Memory Advisor
  • The Shared Pool and Buffer Cache Advisors

Tune PGA and Temporary Space (12 topics)

  • PGA Overview
  • Automatic PGA Mode
  • Configure the PGA for a New Instance
  • The Program Global Area Advisor
  • PGA Management Data Dictionary Views
  • Tune the PGA_AGGREGATE_TARGET
  • SQL Operations that Use Sorts
  • Sorting to Disk
  • Multiple Temporary Tablespaces
  • Allocate Temporary Disk Space for Sorting
  • Temporary Tablespace Shrink
  • Data Dictionary Views and Sort Segments

Checkpoint and Redo Tuning (5 topics)

  • Checkpoint and Redo Issues
  • Instance Recovery Performance Tuning
  • Redo Log File Size Advisor
  • Size the Redo Log Buffer
  • Set Multiple Database Writers

Tune I/O (9 topics)

  • Database I/O Management
  • Layout the Files Using OS or Hardware Striping
  • Manually Distribute Files to Reduce I/O Contention
  • When to Separate Files
  • Sample Configurations
  • Select the Data Block Size
  • Synchronous I/O Benefits
  • FILESYSTEMIO_OPTIONS Parameter
  • Automatic Storage (ASM) Monitoring

Use Oracle Blocks Efficitently (12 topics)

  • Determine the Database Block Size
  • Tablespace, Table and Index Storage Parameters
  • Automatic Segment Space Management
  • Detect and Resolve Freelist Contention
  • Rebuild Tables and Indexes
  • The DBMS_SPACE Package
  • Row Migration
  • Chained Rows
  • Bigfile Versus Smallfile Tablespaces
  • Extended ROWID Format
  • Convert from Dictionary Managed Tablespaces
  • Convert to Automatic Segment Space Management

Materialized Views (5 topics)

  • Overview of Materialized Views
  • Materialized View Creation
  • Materialized View Refresh
  • Query Rewrite
  • Materialized View Data Dictionary Views

Parallel Execution (8 topics)

  • Overview of Parallel Execution
  • Enabling and Using Parallel Execution
  • The Degree of Parallelism
  • SQL Hints
  • Restrictions on the Use of Parallel Execution
  • Dictionary Views on Parallel Execution
  • Specific Automatic Parallel Query Optimization
  • Nologging Options

Tracing Services (7 topics)

  • End to End Tracing
  • Enable and Disable Tracing
  • Check for Tracing Enabled
  • The trcess Utility
  • Session Based Tracing
  • Enable and View SQL Tracing with Enterprise Manager
  • Analyze Trace Results

Prerequisites

Delegates should have a working knowledge of SQL*Plus, PL/SQL and Oracle Database Administration. This can be obtained by attendance on the Oracle SQL and Oracle Database 10g DBA Part I courses. Some operating system experience is essential.

Oracle DBA Training Courses

Course PDF

Print

Sections