How to Use Linux Perf for Deep Oracle Performance Analysis and Flame Graphs

Perf Overview

Perf is a Linux performance analysis tool that identifies which functions consume the most CPU time, enabling bottleneck detection in applications such as Oracle databases.

Running Environment

The examples were executed on Oracle Linux Server Release 7.2 with kernel version 4.1.12. The installed Perf package is 4.1.12-94.3.5.el6uek.x86_64.

Perf Event Types

Perf categorises events into:

Hardware events – use the processor's Performance Monitoring Unit (PMU).

Software events – generated by the kernel (e.g., cpu‑clock, context‑switch).

Hardware cache events.

Raw hardware descriptors.

Hardware breakpoints.

Tracepoint events (static kernel tracepoints, available on kernels 2.6.3x and later).

Basic Perf Commands

perf top

Similar to the Linux top command, perf top shows functions with the highest CPU usage in real time. Press s and type a function name (e.g., e1000_watchdog) to drill down to the corresponding assembly instructions.

perf record

Use perf record to capture performance events for a specific process. Example for an Oracle session with PID 2794: perf record -e cpu-clock -p 2794 Run the target SQL in the monitored session, stop the collection with Ctrl+C, and generate a report with perf report. The report lists the most CPU‑intensive functions, such as _raw_spin_unlock_irqrestore.

Perf Probes for Oracle 10046 Wait Event

Dynamic tracing of Oracle wait events can be achieved by placing probes on internal kernel functions such as kskthbwt, kskthewt and kews_update_wait_time. The workflow is:

Define probes, e.g.: perf probe -a -d kskthbwt (replace -d with -a to add a probe).

List active probes with perf probe -l.

Run the Oracle session and collect data with perf record as shown above.

After collection, perf script outputs raw wait‑event IDs (e.g., 0x162).

Map the IDs to human‑readable names from the view v$event_name using a formatting script (the original article provides a sample script).

Generating Flame Graphs

Flame graphs visualise CPU consumption across call stacks. Four common types for Oracle workloads are:

Execution‑plan based flame graph

Logical I/O based flame graph

Physical I/O based flame graph

System‑load based flame graph

Required tools:

FlameGraph scripts – https://github.com/brendangregg/FlameGraph

os_explain helper – http://blog.tanelpoder.com/files/scripts/tools/unix/os_explain

Typical workflow:

Identify the Oracle session PID and warm the cache by executing the target SQL.

Start data collection: perf record -a -g -F100000 -p <pid> Execute the SQL again to generate load.

Stop collection with Ctrl+C.

Process the perf.data file:

perf script | ./stackcollapse-perf.pl | ./flamegraph.pl > oracle.svg

The resulting oracle.svg is the flame graph.

Q&A Highlights

Configuration requirements – Basic commands (perf stat, top, record) work out of the box. Probes may require a kernel compiled with tracing support.

Supported platforms – Primarily Linux. Solaris provides similar functionality via DTrace.

Performance impact – perf top and perf record have low overhead. Probes are more intrusive and should be used for research or debugging.

Automation – Data collection can be scripted and integrated into existing monitoring pipelines.