Free Oracle Tips

HTML Text

Home
E-mail Us
Oracle Articles

Oracle Training
Oracle News
Oracle Forum
Class Catalog

Our Staff
Our Prices
Help Wanted!

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
UNIX
Oracle UNIX
Linux
Oracle Linux
Monitoring
Remote help
Remote plans
Remote services
Oracle C++
Oracle Java
Apache
JDeveloper
App Server
Applications
Oracle Forms
Oracle Portal
11i Upgrades
SQL Server
Oracle Concepts
HTML-DB Tips
Software Help
Remote Help
Development
Implementation

Financials Training
Oracle 11i
Oracle Apps 11i
Oracle Workflow
Oracle AR 11i Class
Oracle AP 11i class
Oracle GL 11i class
Oracle HR 11i class
Oracle FA 11i class
11i Project Mgt
11i procurement
11i collections

Oracle Posters
Oracle Books
Oracle Tuning Book
Oracle RAC Book
Oracle Security
Easy Oracle Books
Oracle Scripts
SQL Server DBA
SQL Design Patterns
WISE
Excel-DB

BC Oracle News

Rednecks!
Dress code
Arabian Stallion
Burleson Arabians
Guide Horses
Don Burleson Blog
Golf & Travel

Privacy Policy

Oracle Performance Tuning Training

This course is taught at your Company site with up to 20 students.

Click here for on-site course prices

Optional supplemental mentoring

* Understand the common causes of performance problems.

* See how data caching improves access speed.

* Learn secrets for optimizing SQL.

* Use Oracle secrets to optimize the SGA.

* See how to quickly locate and relieve database bottlenecks.

* Understand proven techniques for Oracle health checks.

* See how Oracle data repository allows for proactive database tuning.

This intensive Oracle tuning training 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 “Oracle Tuning: The Definitive Reference” 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 34 books and more than 100 database tuning articles in many magazines, including Oracle Magazine and Oracle Internals.

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.

Oracle 10g Tuning
Oracle10g Time-series Optimization with the Automatic Workload Repository

Donald K. Burleson

ISBN: 0-9744486-2-1

This course is designed for the working Oracle professional and the amount of previous experience with Oracle is incidental.

Previous experience with relational database management and SQL is helpful, but this class is self-contained and has no formal prerequisites.

This course was designed by Donald K. Burleson, an acknowledge leader in Oracle database administration. Author or more than 30 database books, Burleson was chosen by Oracle Press to write five authorized editions, including Oracle High-Performance SQL tuning. Burleson has over a decade of real-world DBA experience in Oracle features and shares his Oracle secrets in this intense hands-on Oracle tuning training.

Oracle Performance Tuning Training Course
Syllabus

Introduction to Oracle Tuning

This section introduces participants to a series of tuning steps that can be used to improve the performance of the Oracle Server. The focus is on database issues rather than specific operating system performance issues. Students will learn how to recognize, troubleshoot, and resolve common performance-related problems in administering an Oracle database.

In conjunction with the other four courses in the Oracle Performance Tuning Certification Series, this course helps students prepare for Oracle Certified Professional Exam number 1Z0-014, Oracle: Performance Tuning, part of the Oracle DBA certification track.

Learning Objectives - After completing this course, students will be able to:

Apply sound database design principles to ensure high performance
Identify appropriate metrics and tuning goals for measuring and monitoring performance
Identify the critical performance tuning steps
Interpret Oracle alert files
Use Oracle trace files
Use Oracle utilities for performance tuning
Use the V$ utilities for tuning
Employ tuning techniques for different types of applications

Tuning the Oracle instance and objects

This section takes an in-depth look into the memory internals of the Oracle SGA. Students will learn how to identify and diagnose Oracle memory performance problems, including problems in the library cache, data buffer cache and data dictionary cache. Once students complete the second course, they’ll be able to handle any SGA-related slowdown, and will have a set of pre-written SQL scripts that they can use to quickly identify performance problems.

Learning Objectives - After completing this course, students will be able to:

Tune the Oracle shared pool
Tune the data buffer cache
Tune Oracle rollback segments
Tune Oracle redo mechanisms Monitor and detect lock contention
Identify Oracle sort operations

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.

Oracle Instance Tuning

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.
UNDO (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.

Oracle object tuning

This area will include recommendations relating to the creation of new indexes, removing migrated/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.

Tuning Oracle Data access

This section takes an in-depth look into the concepts and skills needed to tune Oracle with SQL Students will learn about the broader issues of tuning with SQL, and then delve into tuning with the Optimizers, the Explain Plan Utility, Hints, and other tuning techniques. After completing this course, students will be able to tune all Oracle SQL and take the actions required to ensure optimal SQL performance.

Learning Objectives - After completing this course, students will be able to:

Define the major features of Oracle SQL
Tune using ANSI extensions
Tune using the Explain Plan Utility
Understand SQL Optimizer modes
Tune using the rule-based optimizer
Tune using the cost-based optimizer
Tune using SQL hints

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.

Tuning the external environment

This section will teach students about external influences on Oracle performance, how to measure them, and how to ensure that the external environment is properly configured for Oracle. Students will learn how to take advantage of all the UNIX-based tools for monitoring, configuring, and tuning the external environment. The course also covers tuning issues related to the operating system, tuning for effective CPU usage, reorganizing tables to improve I/O performance and more.

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 includes an easy method for capturing environmental statistics inside Oracle tables for long-term analysis.

In conjunction with the other four courses in the Oracle Performance Tuning Certification Series, this course helps students prepare for Oracle Certified Professional Exam number 1Z0-014, Oracle Performance Tuning, part of the Oracle DBA certification track.

Learning Objectives - After completing this course, students will be able to:

Define the major features of Oracle SQL
Tune using ANSI extensions
Tune using the Explain Plan Utility
Understand SQL Optimizer modes
Tune using the rule-based optimizer
Tune using the cost-based optimizer
Tune using SQL hints

Advanced Oracle Tuning Concepts

This section introduces students to advanced Oracle performance tuning tools and concepts associated with Oracle, and is designed to give students the skills to maximize the performance of their Oracle database.

Learning Objectives - After completing this course, students will be able to:

Use data blocks efficiently to improve I/O performance
Tune using the new Oracle Data Structures
Use the Data Dictionary to monitor performance
Define and use Oracle table and index partitions
Tune with Oracle indexes for optimal performance
Tune Oracle for Web Applications
Describe and implement tuning techniques for Distributed Databases
Tune the Oracle Parallel Server (OPS)

Prerequisites

In order to get the most from this course, students should have a basic understanding of the UNIX operating system, PL/SQL coding techniques, and the following Oracle database concepts:

Oracle tables and tablespaces
Oracle instances
Oracle data dictionary

Please note that while the knowledge gained from this Oracle training may be valuable when preparing for Oracle certification exams, the content of this course is not for Oracle Certification, including the Certified Professional (OCP) or Oracle Certified Associate (OCA) programs.