Instrumenting Oracle OLTP applications

This is skeleton for the future article.

To instrument application we use DBMS_APPLICATION_INFO package.

dbms_application_info.set_module(‘MODULE’, ‘ACTION’);

To find top modules and actions we query ASH view V$ACTIVE_SESSION_HISTORY manually or indirectly using OEM. This view contains snapshots of V$SESSION taken every second for each active session. And there is a problem here. Duration of database calls should be more than 1 second to guarantee acccurate ASH statistics. But typical OLTP calls are quite short. So, what if you need accurate statistic how much time do your modules and actions take? Does Oracle DB have a native tool to gather such accurate statistic? Something except SQL Trace(10046 event), TRCSESS and TKPROF.

Of course, there are a lot of frameworks which allow you to measure execution time manually. But I’m looking for the native Oracle tool.

Things to consider:

PL/SQL Hierarchical Profiler
Using the PL/SQL Hierarchical Profiler
Instrumentation Library for Oracle (ILO)

Instrumenting Oracle OLTP applications