The Art and Craft of Oracle Tracing and Profiling

The Art and Craft of Oracle Tracing and Profiling

July 29, 2015

Presentation Abstract:

Tracing activities are an important part of the tuning process. This presentation explains how to enable various types of tracing including extended (aka 10046); how to trace in case of connection pools before the sessions are established, how to pull specific sessions from the mass of traces, how to trace in RAC, how to consolidate masses of traces to make a meaningful analysis and how to read and interpret the seemingly arcane trace files. While tracing is useful for singular query sessions, it’s useless in PL/SQL routines where a few lines of code may generate thousands of lines of query. Profiling helps in this case by exposing the portion that takes the most time. This presentation will describe the profiling techniques in PL/SQL, and how to use the PL/SQL profiler to identify the elapsed time in each call, or sub-call. Both the traditional and hierarchical profilers will be explained. All of the tips and techniques will be shown with live examples.

About the Presenter (Arup Nanda):

Arup Nanda has been working as an Oracle DBA for the past 17 years. Arup trains and mentors DBAs, has coauthored 4 books, written some 300 published articles (including the most popular 11g and 10g New Features Series on the Oracle Technology Network) and given numerous presentations. He is an OCP DBA, an Oracle ACE Director and a member of the Oak Table Network. In 2003, he won the coveted Oracle DBA of the Year award. Arup blogs at arup.blogspot.com.

Presentation Materials

Arup Nanda: Tracing Presentation

Arup Nanda Tracing and Profiling Scripts

Arup Nanda: Profiling Presentation