Oracle Performance Management Using Response Time Profiling

Excerpt from the Course

Want a preview of the course? Watch this short video to see one of the sections of material.

Syllabus

The course is organized into the following segments:

Why Collect Oracle Trace Data?
An Introduction to Response Time Profiling

  • Problems with Conventional Tuning Methods (Method C)

    What is Method C?
    Why doesn't it work consistently?
    How can we solve the performance problem?
    The data collection problem.
    The importance of properly scoped data.

  • Response Time Profiling

    What is Response Time Profiling?
    The golden rule of performance optimization.
    The basic steps needed to use Response Time Profiling
    Creating a basic response-time profile.

  • Target identification

    Specifying the performance improvement project, identifying the right task to optimize first.

What is 10046 Trace Data

  • Overview of Oracle Tracing Mechanisms

    What is 10046 trace?
    The oraus.msg file.
    Why is the trace data so important?

  • Ways to Manipulate Traces

    Parameter setting required and or recommended.
    How to turn on trace (examples from Oracle v8 and up).
         ALTER SESSION/SYSTEM
         DBMS_SUPPORT and DBMS_MONITOR
    Turning off trace, explicitly vs implicitly.
    Finding the trace files and the naming conventions used for them.

  • Reading a 10046 Trace File

    Review of actual trace data
    The database calls and database call stats
    Parsing in cursor
    Parse
    Bind
    Exec
    Fetch
    The "WAIT" events and wait event stats
    Overview of many "popular" wait events (db file scattered_read, db file sequential_read, latch free, SQLNet Message to and from client, etc.)

How to Collect Trace Data with Proper Instrumentation

  • What is properly instrumented trace data?

    Define code instrumentation.
    Why is instrumentation important?
    What's needed for instrumented code to produce a properly scoped trace file?
    What are the collateral benefits of code instrumentation?

  • Instrumentation with Client Identifier

    What is a Client Identifier?
    Setting the Client Identifier with DBMS_SESSION in PL/SQL.
    Examples of setting Client Identifier using JDBC, OCI, ODP.NET.
    Setting Default Client Identifier using Logon Trigger.
    Turn on/off tracing using DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE/DISABLE.
    Querying the Data Dictionary.
    Client Identifier collateral benefits DBMS_MONITOR.CLIENT_ID_STATE_ENABLE/DISABLE.

  • Instrumentation with DBMS_APPLICATION_INFO

    What is Module and Action?
    What values to set Module and Action?
    Examples of setting Module and Action.
    Flaws of using DBMS_APPLICATION_INFO in a procedure.
    A better way to set Module and Action in PL/SQL.
    Turn on/off tracing using DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE/DISABLE.
    Module and Action collateral benefits.
    DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE/DISABLE.
    Data Dictionary Views.

  • Instrumentation with HOTSOS_ILO

    What is Hotsos ILO?
    Installing Hotsos ILO.
    Instrumenting PL/SQL with Hotsos ILO.
         BEGIN_TASK and END_TASK
    Examples of using Hotsos ILO.

  • Special Considerations

         Shared Server

              What is Shared Server?
              Special considerations for tracing in a Shared Server environment.
              Location of trace files.
              Use of Oracle trcsess.
         
         Parallel Query

              What is Oracle Parallel Query Option?
              Special Considerations for tracing PQO.
              Location of trace files.
              Use of Oracle trcsess.

How to Profile Trace Data

  • Manually Profiling Trace Data

    What is a good profile?
    Doing the math, what numbers to use, how to add them up.
    Defining the unaccounted for time.

  • Using Hotsos P5 Profiler to Profile Trace Data

    How to use Profiler to construct a resource profile.
    Walk through use of Profiler output and how to navigate.
    How to use Response Time Profiling without Profiler.
    How to use TKPROF.
    Compare and contrast TKPROF output with response time profile from Profiler.

Responding to Profiled Trace Data

  • Review common problem events exposed in response time profiles
         Unaccounted-for
         CPU Service
         db file % read
         SQL*Net message % client
         Latch free
         Buffer busy wait          ... and more ...

  • Analyze profile output and learn to make recommendations for corrective actions.

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 Optimizing Oracle Performance. Material is available online, free of charge, at Hotsos Education downloads.

Instructional Format

The three-day course is approximately 60% lecture, 40% hands-on exercises to strengthen understanding of the course material. The course is conducted in English.


Materials

Each student receives the following materials:

  • Course Notebook
    A printed copy of all the presentation slides used in the course.
  • Tools
    The Hotsos Profiler will be demonstrated, and students will use response-time profiles from the Profiler during class. Special pricing to purchase the Profiler will be available to attendees for up to 30 days after class completion. Students may also request a trial of the Hotsos Profiler after class by contacting sales@hotsos.com.

Class Forum

For questions and discussion about the course, please visit our forum.

Pricing

  • List Price: $1875.00
  • Alumni Pricing:
    $1250.00 - Available to prior attendees of the Diagnosing Oracle Performance Problems course.
    $500.00 - Available to prior attendees of this course.

Schedule

Please visit our Calendar of Events to find when this course will be offered next.