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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s