Hotsos - Education Diagnosing Oracle Performance Problems

Diagnosing Oracle Performance Problems

Syllabus

The course is organized into the following segments:

Welcome
Introductions and logistical matters, what you can expect.
Method overview
Prelude, tools for analyzing response time, introduction to Method R.
Target identification
Specifying the performance improvement project, identifying the right task to optimize first.
Tracing Oracle
Collection scope, overview of Oracle tracing mechanisms, data collection challenges, tracing real applications, collection errors, known bugs with extended SQL trace.
Extended SQL trace data
Trace file walk-through, choosing the right SQL trace level, origins of Oracle timing data, response time accounting (creating the "profile by Oracle subroutine"), recursive SQL, choosing the right SQL trace level.
Responding to the diagnostic data
Case study in performance improvement, navigating the profile, Oracle response time components (what they didn't tell you), response time component reference.
Case studies in performance problem diagnosis
CPU service, latch free, SQL*Net message from client, db file scattered read, db file sequential read, buffer busy waits, unaccounted-for, analysis of trace files acquired from prior students, building scalable applications.
Conclusion
Discuss how to overcome common objections and review what you have learned this week.

Target Audience

The course is designed for technicians and technical managers who want to respond faster and more permanently to Oracle system performance problems. The course is most effective for students who will be motivated to solve production Oracle system performance problems immediately upon returning to work after the course.

Prerequisites

Before coming to class, please register for an account at hotsos.com.

Students are expected to have a strong working knowledge of Oracle and SQL and experience as a database administrator or application developer. To maximize the value of the course, we strongly recommend the following pre-course reading:

Oracle Database Server Concepts Guide
Students are expected to be familiar with the content of the Concepts guide for a relevant Oracle release. Material is available online, free of charge, at the Oracle Database Documentation Library. It is listed as "Concepts" in the Getting Started section.
A Better Way to Optimize (Chapter 1–Optimizing Oracle Performance)
Students are expected to have read the first chapter from the course textbook, Optimizing Oracle Performance. Material is available online, free of charge, at Hotsos Education Downloads.

Instructional Format

The three-day course is approximately 80% lecture, 20% instructor-led discussion and in-class exercises on paper to strengthen understanding of the course material. The course is conducted in English.

Materials

Each student receives the following materials:

Textbook
Optimizing Oracle Performance by Cary Millsap with Jeff Holt.
Course Notebook
A printed copy of all the presentation slides used in the course, with extra chapters containing several in-class exercises (with solutions), strace output illustrating the meaning of several Oracle timed events, an strace user guide, and preprinted forms that facilitate the completion of the course exercises.
Tools
Students may request a trial of the Hotsos Profiler after class by contacting sales@hotsos.com.