Oracle8i Performance Tuning: Strategies and Techniques

Audience

Database Administrators System Administrators Network Administrators and Technical Support Professionals. Participants should have completed the Oracle8i Database Administration Series (courses 63311-63315) prior to taking this course and experience using Oracle Enterprise Manager (OEM). This course will help candidates prepare for the Oracle8i Certified Database Administrator (DBA) Track Exam 4 1Z0-024.

Objective

• Identify the roles of individuals involved in the tuning process.
• Identify different tuning goals.
• Define the steps associated with the tuning process.
• Identify the location and usefulness of the alert log file.
• Identify the location and usefulness of the background and user process trace files.
• Identify different types of events and manage the event system using OEM.
• Collect statistics using the dynamic troubleshooting and performance views.
• Diagnose statistics using the UTBSTAT/UTLESTAT output report.
• Identify appropriate OEM tuning tools.
• Use the available data access methods to tune the logical design of the database.
• List the primary steps for operating system tuning.
• Identify similarities between operating system and database tuning.

Topics Include

Unit 1: Tuning the Oracle8i Server: Overview

• Match the tuning goals with the appropriate job role.
• Identify appropriate tuning goals.
• Sequence the steps associated with tuning an Oracle8i 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 purpose of generating background trace files.
• Identify the parameters that control the location and format of background trace files.
• 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.
• Locate Oracle supplied scripts used to provide additional statistics.
• Order the key steps in the Oracle Expert Tuning Methodology.

Unit 4: Considerations for Tuning Applications

• Identify the key usage of b-tree indexes.
• Identify the key usage of bitmap indexes.
• Identify the key usage of reverse-key indexes.
• Identify the key usage of index organized tables.
• Identify the key usage of index clusters.
• Identify the key usage of hash clusters.
• Identify the key usage of histograms.
• 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.

Duration

8

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