Skip to content

Files

Latest commit

Nov 12, 2015
1a3aab8 · Nov 12, 2015

History

History

ORA_KStackProfiler

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
Nov 12, 2015
Nov 9, 2015
Nov 12, 2015
Nov 9, 2015
Nov 12, 2015
ora_kstackprofiler - a simple kernel stack profiler extended with the 
                     option of sampling Oracle wait event information from SGA
                     
Author: Luca.Canali@cern.ch
Created: November 2015
        
Use this tool to investigate Oracle processes spending most of their time in wait events and/or in system calls.        
What it does: Sampling of Linux kernel stack and OS state from /proc/<pid>/stack and /proc/<pid>/state
              Optional sampling of Oracle wait event information from memory X$KSUSE.KSUSEOPC and X$KSUSE.KSUSETIM
This tool is intended to be used together with flame graph visualization: https://github.com/brendangregg/FlameGraph
              
Notes: 
 - consider this tool as proof of concept/study material
 - the overhead on the measured system is expected to be low when used with care
 - for userspace and CPU-bound processes consider perf events for stack profiling
 - stacks are captured "in flight" using the /proc filesystem this minimizes the risk but also reduces accuracy
 - stack values  process state and wait event deails are read in three different steps, this can introduce errors
 - the tool traces only one process (although it could be adapted to trace multiple pids)
 - the tool does not support threads. It is mainly focused to tracing Oracle single-threaded processes
 - the tool does not provide userspace traces
                
Example of usage:

1. Run "make" to build the executable: ora_kstackprofiler

2. Run the scripts in the oracle_scripts directory 
     - run eventsname.sql using sql*plus to generate the file eventsname.sed translating event# into event names 

     - run find_pid_ksustim_ksuseopc.sql using sql*plus to find the pid of the Oracle session to trace
       Example: 

SQL> @find_pid_ksustim_ksuseopc.sql
This script finds the OS pid and address of X$KSUSE.KSUSEOPC and X$KSUSE.KSUSETIM
for a given Oracle session. To be used together with ora_kstackprofiler
Run as user SYS

Enter Oracle SID to be investigated: 145
OS pid = 1234, ksuseopc = 5796061762, ksusetim = 5796061792

3. Gather stack profiling extended with Oracle wait event data:

Example of the command line:

./ora_kstackprofiler --pid 1234 --delay 100 --count 1000 --ksuseopc 5796061762 --ksusetim 5796061792 > stack_profile.txt

4. Process the output with flame graphs for visualization - https://github.com/brendangregg/FlameGraph

Examples :
cat ora_stack_slob_test.txt| ../FlameGraph/stackcollapse-stap.pl|../FlameGraph/flamegraph.pl > stack_profile_vanilla.svg

Additional data processing for improved graphs:

cat ora_stack_slob_test.txt| grep -v 0xffffffffffffffff | sed -f oracle_scripts/eventsname.sed | \
../FlameGraph/stackcollapse-stap.pl | \
../FlameGraph/flamegraph.pl --title "Kernel stack, OS State and Wait event profiling" > stack_profile_edited.svg