Skip Ribbon Commands
Skip to main content

ORA10g203 - Oracle Database 10g: Performance Tuning


Duration: 4 Days




Delivery Method:

Software Assurance Value:

Microsoft CPE:

Course Information

Course Description

​The purpose of this Oracle Database 10g training course is to equip database administrators to recognize, diagnose and remedy performance problems which might arise in the operation of the Oracle database server. Beyond reactive measures, proactive actions which can be taken to avoid performance issues before they arise or are recognized are also considered.

Course Objectives

​The objective of this Oracle Database Administration training course is to consider present a comprehensive consideration of data warehouse features that exist within the Oracle database.

Major subject areas to be explored are:
• Utilization of advanced self-tuning and self-management capabilities, including the use of the database Advisors and other components of the Management Framework.
• Performance monitoring, problem troubleshooting, and resolution.
• Detailed scrutiny of database activity using statistics and wait events.
• Manual tuning using Statspack.
• Tuning of the shared pool, buffer cache and shared memory
• Tuning recovery structures such as checkpoints and redo data
• Tuning physical storage structures such as file I/O and database blocks
• Tuning specialty structures such as the PGA and temporary space

Course Audience

​Target audience for this Oracle Database training course is database administrators, data warehouse administrators and application developers who will be responsible for implementing and using data warehouse technology.

Course Outline

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 Statspack
• Install Statspack
• Create Statspack snapshots
• Generate Statspack reports
• Identify the major sections of the Statspack report
Using AWR
• Create and manage AWR snapshots
• Generate AWR reports
• Create snapshot sets and compare periods
• Generate ADDM reports
• Generate ASH reports
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
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
• 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

Course Prerequisites

Before attending this Oracle Database training course, students must have:
  • Completed ora10g008, Oracle Database 10g: Introduction to SQL I, or equivalent.
  • Completed ora10g009, Oracle Database 10g: Introduction to SQL II, or equivalent.
  • Completed ora10g101, Oracle Database 10g: Program With PL/SQL, or equivalent.
The following prerequisites are recommended:
  • Completed ora10g201, Oracle Database 10g: Administration I, or equivalent.
  • Completed ora10g003, Oracle Database 10g: SQL Tuning, or equivalent.
  • Completed ora10g204, Oracle Database 10g: New Features for Administrators, or equivalent.

Suggested Next Course

  • ora10g304, Oracle Application Server Discoverer 10g: Develop An EUL
Course Schedule
This course is not scheduled yet.