• Home
• Course Catalogue
• Certifications
• Onsite Training
• Scheduled Training
• E-Learning
• Managed Services
• Consulting
• Company
• Special Offers
• News
• Links
• Enquiry And Feedback Form

Training Course Outline

IT Vendor Courses   Oracle DBA Training Courses

Oracle 9i Database Performance Tuning

View course dates

Course Duration: 5 days

Course Code: OCO9IPT

Course Description:

This class will cover Oracle9i and Oracle9i Release 2 features. Database performance tuning is critical to any effective information system. Reducing wait times, increasing users' productivity and maximizing the performance of your Oracle database are critical to success. Learn from the experts the value of intelligent database design and methods for tuning an Oracle database for maximum performance.

 

This course will introduce participants to the importance of good initial database design, and the method used to tune a production Oracle9i database. The focus is on Database and Instance tuning rather than specific operating system performance issues. Participants will gain practical experience tuning an Oracle database. Using the available Oracle tools, such as Oracle Enterprise Manager (with the Diagnostics and Tuning Packs) and STATSPACK participants also learn how to recognize, troubleshoot and resolve common performance related problems in administering an Oracle database.

 

This course counts towards the Hands-on course requirement for the Oracle 9i Database Administrator Certification. Only instructor-led inclass or instructor-led online formats of this course will meet the Certification Hands-on Requirement. Self Study CD-Rom and Knowledge Center courses DO NOT meet the Hands-on Requirement.

 

Audience:
Database Administrators
Technical Consultant

 

Course Objectives:
- Design and configure an Oracle database with an emphasis on good performance
- Perform day-to-day monitoring on an operational database using Oracle Enterprise Manager or Statspack
- List the important steps in a tuning methodology
- Employ Oracle tools to diagnose performance problems
- Perform tuning tasks with Oracle Enterprise Manager
- Perform tuning tasks using the command line interface
- Reconfigure file structures for performance
- Identify and resolve I/O, data storage and database configuration problems
- Optimize sort operations
- Configure Oracle Shared Server
- Configure Resource Manager to control resource usage

Prerequisites:

Oracle 9i DBA Fundamentals

Prerequisite courses:

DBA Fundamentals

Relevant Certifications:

Oracle9i Database Administrator

This course includes the following modules:

Overview of Oracle 9i Database Performance Tuning

• Tuning questions, phases, and goals, Common performance problems, Tuning during development, Tuning steps for a production database Performance and availability trade-offs

Diagnostic and Tuning Tools

• Log and trace files, Tuning with Oracle Enterprise Manager, Statspack, Statistics, troubleshooting data, and performance data in the data dictionary, Database and self-built performance statistics collection, Collecting performance statistics with assorted tools

Database Configuration and I/O Issues

• Distributing files across devices, Tablespace considerations, Full table scan performance, Checkpoint performance, Redo management and performance, Analyzing I/O activity, Analyzing checkpoint performance

Tuning the Shared Pool

• Shared pool components and latches, Monitor and tune the library cache Manage SQL statements and PL/SQL blocks to improve library cache performance, Monitor and tune the data dictionary cache, Reserved pool and large pool management, Using diagnostic tools to monitor and tune the shared pool

Tuning the Buffer Cache

• Buffer cache sizing parameters, Buffer cache advisory

Dynamic Instance Resizing

• Dynamic allocation units in the SGA, Resize SGA components, Resizing the buffer cache and the shared pool

Sizing Other SGA Structures

• Manage redo log buffer, Control redo operations, Manage Java pool memory, Managing the redo log buffer

Tuning the Oracle Shared Server

• Use and performance of shared server processes, Shared servers and memory usage, Monitor and troubleshoot shared server activity

Optimizing Sort Operations

• Automatic PGA memory management, PGA sizing advisor, Sorts and temporary segments, Tuning, diagnostics, and guidelines for sorts, Reduce sorting, Configuring and monitoring sort space options, Configuring automatic PGA memory management

Using Resource Manager

• Database Resource Manager concepts, Administering the Database Resource Manager, Define resource plans limits, Assigning users to consumer groups, Manual and automatic consumer group switching, Examine resource plan information in the data dictionary

SQL Statement Tuning

• Optimizer modes, Hints, Plan stability and stored outlines, SQL tuning diagnostic tools, Interpret results from SQL tuning diagnostic tools

Managing Statistics

• Automatic segment-level statistics collection, Dynamic sampling, Table statistics, index statistics, and index tuning wizard, Column statistics and histograms, Estimated statistics and automatic statistics collection, Optimizer cost model, System statistics, Copying statistics between databases

Using Oracle Blocks Efficiently

• Database storage components, Extent management, High water-mark, Space management for tables, Index management

Using Oracle Data Storage Structures Efficiently

• Data storage structures, Selecting physical storage characteristics for performance, Data access methods and structures

Application Tuning

• Role of the database administrator, Table movement and redefinition, Creating and managing indexes and index-organized tables, Statistics collection, Considerations for OLTP systems, Considerations for decision support systems and data warehouses Considerations for hybrid systems

Using Materialized Views

• Create and refresh materialized views, Nested materialized views, Query rewrites, The DBMS_MVIEW package

Monitoring and Detecting Lock Contention

• Locking mechanisms and management, Data concurrency, Enqueues and lock types, Lock monitoring and resolution of conflicts, Deadlocks, Use diagnostic tools to monitor lock contention

 

To make an enquiry for this course to be run ONSITE at your location, click here or call Sales on +44 (0) 20 7620 0033.

Perpetual Solutions are a global provider of onsite training solutions throughout London, UK and the rest of the world. For scheduled training our UK partner network provides an unrivalled choice of courses.

Save as PDF

Print this course