Oracle9i Performance Tuning: Strategies and Techniques

In this course you are introduced to the strategies and techniques used for tuning the Oracle9i server.

This is primarily online training This is an online eLearning or CBT training program Contact Serebra Learning Corporation for more information

Duration: 8 hours Training Presented in: English

Audience

This intended audience for this course are Database Administrators.

Objective

• Match the tuning goals with the appropriate job role.
• Identify common tuning problems that directly affect database performance.
• Identify appropriate tuning goals.
• Identify key information recorded by the alert log.
• Identify the parameters that control the location and format of the alert log file.
• Identify the different methods available for generating user process trace files.
• Identify the functions of the DBA views used to provide data storage statistics.
• Match the dynamic performance views with the statistics they provide.
• Match the V$ views used to collect systemwide statistics with their functions.
• Identify the factors taken into consideration in selecting the physical structure for data.
• Match the index types with their benefits.
• Identify the key usage of index organized tables.
• Identify the features of the Database Resource Manager.
• Identify the guidelines used to administer resource consumer groups.
• Identify the processes implemented by resource plans and resource plan directives.
• Identify the measures that can be taken to tune memory performance.
• Identify the I/O tuning areas that can improve overall system performance.
• Identify the CPU tuning practices that can improve overall system performance.

Topics Include

Unit 1: Tuning the Oracle9i Server: Overview

• Match the tuning goals with the appropriate job role.
• Identify common tuning problems that directly affect database performance.
• Identify appropriate tuning goals.
• Sequence the steps associated with tuning an Oracle9i database.

Unit 2: Alert Log Trace Files and Events

• Identify key information recorded by the alert log.
• Identify the parameters that control the location and format of the alert log file.
• Identify the different methods available for generating user process trace files.
• Identify the parameters that control the location and format of user process trace files.
• Identify the V$ views used to obtain wait event information.
• Identify key statistics found in event views.

Unit 3: Utilities and Dynamic Performance Views

• Identify the functions of the DBA views used to provide data storage statistics.
• Match the dynamic performance views with the statistics they provide.
• Match the V$ views used to collect system-wide statistics with their functions.
• Match the session-related views with the statistics they provide.
• Identify the functions performed by the utlbstat.sql and utlestat.sql scripts.
• Match sections of the report.txt output with potential problems and areas of focus for tuning.
• Identify the functions of the STATSPACK package.
• Locate Oracle supplied scripts used to provide additional statistics.
• Order the key steps in the Oracle Expert Tuning Methodology.
• Match the performance manager charts with their monitored information.

Unit 4: Considerations for Tuning Applications

• Identify the factors taken into consideration in selecting the physical structure for data.
• Match the index types with their benefits.
• Identify the key usage of index organized tables.
• Match the cluster types with their implementation.
• Identify the key usage of histograms.
• Identify the functions of materialized views.
• Identify the features of OLTP.
• Identify appropriate storage allocation requirements for an OLTP system.
• Identify appropriate index and cluster implementations for an OLTP system.
• Identify application issues associated with an OLTP implementation.
• Identify the features of DSS.
• Identify appropriate storage allocation requirements for a DSS implementation.
• Identify appropriate index and cluster implementations for a DSS implementation.
• Identify considerations associated with access path optimization for DSS implementation.
• Identify the features of hybrid systems.
• Identify appropriate parameters required for hybrid systems.
• Identify application issues associated with hybrid systems.

Unit 5: Managing a Mixed Workload

• Identify the features of the Database Resource Manager.
• Identify the guidelines used to administer resource consumer groups.
• Identify the processes implemented by resource plans and resource plan directives.
• Grant resource privileges required to administer the Database Resource Manager.
• Identify the features of the temporary workspace used by the Database Resource Manager.
• Create the resource consumer groups used by the Database Resource Manager.
• Create resource plans and resource plan directives used to distribute processing resources among different users.
• Retrieve specific information on existing objects being used by the Database Resource Manager.

Unit 6: Other Tuning Considerations

• Identify the measures that can be taken to tune memory performance.
• Identify the I/O tuning areas that can improve overall system performance.
• Identify the CPU tuning practices that can improve overall system performance.
• Identify the benefits of the GET_DDL function of the DBMS_METADATA package.
• Match the performance areas with their focus areas.

Duration

6

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media

• Oracle9i Performance Tuning: Managing Memory and Disk I/O
• Oracle9i Performance Tuning: Optimizing Sorts and Minimizing Contention