Our 3-day lab-based Profiling course transforms normal database administrators, application architects, and application developers into real performance analysts who can find and fix performance problems you may not have even realized that you had. People who take this course routinely fix system performance problems that have plagued their systems for months or even years, improving user productivity and reducing IT spend.

Course overview

This course is intended to teach students a scientific method for diagnosing Oracle system performance problems rapidly and accurately, no matter what their root cause. Students learn the importance of a business-task focused approach to Oracle performance problem diagnosis and repair. A repeatable and reliable method for diagnosing performance problems, Method R, is reviewed and used to help students learn how to quickly find and fix Oracle performance problems.

Collecting and using Oracle trace data is covered extensively and students learn how to understand and interpret trace data file contents. Emphasis is placed on collecting properly scope trace data and the use of application instrumentation to make the job easier. Students learn how to create response time profiles for the collected trace data that allows them to pinpoint the root cause of any performance problem quickly and accurately.

The student's attention is focused on how the Oracle database works in order to know how to interpret the response time profiles generated from the collected trace data properly. Case studies are used to give students an opportunity to walk through actual problem scenarios on their own to get real world examples of implementing their knowledge.

This course presents strategies that can be carried forward directly to common problems and customized for solving less common ones. The result is a fortified foundation for diagnosing and repairing Oracle performance problems in their own ever-changing environments.

Pricing

List price: $1,875 USD

Course outline

Why Collect Oracle Trace Data? An Introduction to Method R

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.

Method R

• What is Method R?
• The golden rule of performance optimization.
• The basic steps needed to use Method R.
• 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 the ILO

• What is the ILO?
• Installing the ILO.
• Instrumenting PL/SQL with the ILO.
• BEGIN_TASK and END_TASK
• Examples of using the 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 the 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 Method R 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.

Conclusion

Review

Perspective

Course wrap-up

Using the free Profiler trial