codemonth.dk

One project every month - making stuff better ...

Lightweight application performance monitoring

Monitoring application performance, is always difficult. Sometimes you want to monitor every aspect (with a minor latency impact) and sometimes you just want to monitor the basics. Like counting how many times a specific business process was called, or measuring how long it took. You also want it to be lightweight, and you want it to be really simple. As in really really simple.

So here is Stats Ninja. A lightweight monitoring tool for just that. To try it out simply download the 3 files in the repository and install them into a schema, that has the following privileges: create session, create procedure and create any context


@stats_ninja_c.context.sql
@stats_ninja.package.sql
@"stats_ninja.package body.sql"

Once installed, there are 2 ways to use this tool. The most simple is for just counting how many times something runs. Simply call the stats_ninja.gs procedure from anywhere within your code, to increment the counter. If the counter name does not exist, it will automatically be created.

So if we imagine we have the following procedure


create or replace procedure my_business_transaction

as

	trx_id	number;

begin

	select 1
	into trx_id
	from dual;

end;
/

and we want to monitor it with this package, we simply add the following line to the code


create or replace procedure my_business_transaction

as

	trx_id	number;

begin

	select 1
	into trx_id
	from dual;

	stats_ninja.gs('trx_monitor_1');

end;
/

That is it. No more code is needed, and nothing is stored in any tables anywhere. All is stored in memory, and is very fast and efficient. I have tested the increased runtime for using this, and it adds roughly 0.003 seconds of runtime.

The other way is when we want to measure how long the code is taking. For that we need to add 2 more lines of code to our procedure


create or replace procedure my_business_transaction

as

	trx_id	number;
	s_time	timestamp; -- we need to mark when we started

begin
	
	s_time := systimestamp;

	select 1
	into trx_id
	from dual;

	stats_ninja.gs('trx_monitor_1', systimestamp - s_time);

end;
/

Again, we do not need to do anything to set it up, everything will just be tracked automatically. The latency for the extended version is about 0.005 seconds. Once we have started tracking our process, we of course want to see the data we collect. So for that we use a table funtion in the package to select the data out again.


SQL> select * from table(stats_ninja.report_gs('trx_monitor_1'));

COUNTER_NAME	     STATISTIC_NAME		    STATISTIC_NUM_VAL
-------------------- ------------------------------ -----------------
trx_monitor_1		 Call count                                    22
trx_monitor_1		 Average calltime (ms)                          4
trx_monitor_1		 Maximum calltime (ms)                       3152
trx_monitor_1		 Minimum calltime (ms)                        275
trx_monitor_1		 Simple Moving Average (ms)                  1185

SQL> 

And this works across sessions, during the lifetime of the instance. If you want to clear a counter you call the reset procedure


begin
	stats_ninja.clear('trx_monitor_1');
end;
/

Tagged in : DBMS_SESSION, Pipelined function, regex_substr, sys_context