High-Performance Oracle9i Tuning
by Donald K. Burleson

Donald Burleson is a leader in Oracle9i tuning and Oracle9i tuning books and Oracle9i performance tuning training.

Creating a Self-Tuning Oracle Database Book Automating Oracle9i Dynamic SGA Performance Donald K. Burleson Buy now and receive immediate online access to the code depot! Only $9.95      Buy it Now!

High-Performance Oracle9i Tuning Training Course

This intensive four-day course is designed to provide an in-depth overview of Oracle internal structures and show how to tune Oracle’s internal structures for maximum high performance. 

This is not your run-of-the-mill Oracle tuning course.  Unlike the boilerplate Oracle tuning courses that deliver obsolete and low-impact tuning techniques, this course shows real-world techniques that can cut overall response time by more than 50%. 

From his insider experience as Editor-in-Chief of Oracle Internals, the world’s premier Oracle journal, you will learn the secrets of Oracle tuning from one of the foremost experts in the industry.

Donald Burleson’s landmark book “High-performance Oracle8 Tuning” has been a driving force in the advancement of Oracle tuning.  Don's latest Oracle Press books, "Oracle High-performance Tuning with STATSPACK" and "Oracle High-performance SQL Tuning" set the standard for the tuning of Oracle databases.

Don Burleson remains one of the world’s leading Oracle authors, with 12 books and more than 70 database tuning articles in many magazines, including Oracle Magazine and Oracle Internals.

Course times are from 9:00 AM to 4:00 PM with a one-hour lunch break. This course is now available for only $1,995 per student with a 10 student minimum.  The course fee includes an autographed copy of Oracle High Performance Tuning with STATSPACK and UNIX Commands for the Oracle DBA.

 While the course is topical in nature, the course emphasizes the central theme of database monitoring and the proper use of alert mechanisms.  The course shows the attendees how to use the Oracle STATSPACK utility to monitor virtually every aspect of their Oracle database.

DAY1 – Tuning Strategy and external environment tuning

The first part of this section explores an overall tuning strategy and show the student where to begin their tuning activities, how to plan the next steps, and how to document your findings and performance gains.

The next section will focus on identifying and correcting external influences including RAM memory shortages, disk I/O bottlenecks, and CPU shortages.  The external environment will be monitored using the vmstat and iostat utilities.  This section will discuss the iterations of recommendations relating to environmental tuning such as OS dispatching priorities, swap space allocation, and other OS-specific issues. 

           vmstat utility – Interpreting the runque, page-in and the CPU metrics.

iostat utility – Using the iostat utility to determine disk I/O access patterns and I/O bottlenecks.

This section will also include4 an easy method for capturing environmental statistics inside Oracle tables for long-term analysis.

DAY 2 - Database System Tuning

This area will examine the behavior of the Oracle instance and look into the behavior of the Oracle background process and the SGA memory.  The topics for this section will include iterations of recommendations for changes to init.ora parameters, changes to redo log and rollback segments, and changes of physical file locations on disk. 

This section will also explore how to measures changes to the SGA performance and how to document the performance improvements.

Redo Logs – This section to introduce the performance characteristics of the Oracle online and archived redo logs.

Rollback segments – This section explores the interaction between the Oracle rollback segments and the performance of the Oracle database.

Data Buffer management - Buffer management issues will include the performance of Oracle with various values of db_block_size and the proper settings for db_block_buffers.  This section will also look at allocation of the DEFAULT, KEEP and RECYCLE pools.

Shared pool issues – This section will discuss the management of the components of the shared pool with a focus on the management of activity in the library cache.

Sorting issues – This section will describe the management of the sort_area_size parameter and how to determine the optimal setting for disk versus memory sorting.

Default SQL optimizer mode – This section will explore the optimizer_mode initialization parameter and how the default parameter can effect the database performance.

Miscellaneous Initialization parameters – Other init.ora parameters such as cursor_space_for_time will be discussed.

Concurrency Management - This will describe Oracle's serialization and locking schemes and show how to manage locks.

DAY 3 - Database Object Tuning

This area will include recommendations relating to the creation of new indexes, removing chained rows and the re-sequencing of popular tables into index order to reduce I/O.   The focus will be on the tradeoff between efficient space management and high performance of select and insert SQL statements

Oracle index internals – This section will explain the relative benefits for b-tree, bitmapped and function-based indexing.  This section will also explain how to analyze indexes to determine those indexes that require re-building.

Oracle replication – This section will explore Oracle replication and explain the ramifications of replication versus database links for distributed data.

Oracle segment internals – This includes the high-water make and the effect on full-tale scans after deletes.  This section also includes a section on the pctused parameter and the effect on space re-use and the performance of subsequent insert statements.  There will also be a discussion of pctfree and how pctfree can be used to avoid row chaining.  The freelists parameter will also be discussed with respect to buffer busy waits, and the freelist_groups parameter will be discussed for Oracle parallel Server.

Object access patterns – A technique will be introduced to explain all of the SQL in the library cache.  This technique is indispensable for identify long-table full-table scans, identifying small tables for caching.

DAY 4 - SQL Tuning

This area will examine all of the SQL within the Oracle library cache and determine the best opportunities for tuning of SQL.  The focus on the SQL tuning will be to tune the most frequently executed statements first, and move down the list as time permits.  The areas for SQL tuning will be to optimize execution plans by removing unnecessary full-table scans, fully utilizing indexes, and implementing advanced hints to derive the optimal execution plan for each query.  This may also involve replacing native SQL with PL/SQL procedures.  Tuning SQL statements using Oracle “hints” will be explained and techniques for testing SQL in a separate instance to determine the optimal execution plan and minimal run time will be explored.

Cost-based vs. Rule-based optimization – See the relative benefits of cost-based vs rule based optimization and see when to use rule and cost hints

Tuning with indexes – Learn a technique for identifying unnecessary full-table scans

Tuning sub-queries –See the optimal execution plans for subqueries with the IN clause

Tuning with hints – See the techniques for using the use_hash, use_aj and other important Oracle tuning hints.

Tuning with Parallel query - This section describes how parallel query works, and how to best provide parallelism for Oracle.