When you do performance troubleshooting and investigation it is quite common to measure some “deltas” (for example v$sesstat deltas) . It is also important that your measurement do not posses a “quantum” effect :), i.e. doesn’t influence your measured process. Sometimes you can neglect such effects, sometimes (for example for parsing investigations) when you measure a tiny effect it can be quite annoying.
So I decided to implement (and just did it) a small daemon to measure session stats on behalf of my “main” session.
The idea is quite simple:
- You have a pretty small web server which keeps connection/s with the same database as your main session.
- You communicate with the daemon with a very underutilized sqlplus feature – “remote scripting” , i.e. you can call *.sql (or other file) not only from your local machine but rather from any other place on the network (like http://localhost:7777/some.sql).
- Your daemon keeps your “main session” v$sesstat snapshots and gives you back “delta” results.
You can download the daemon from here:
to run the daemon you need:
RDAutot.bat scott/tyger@localhost:1521:ORCL [optional port]
and to use it from sqlplus:
select count(1) from dba_tables;
statname takes as input REGEXP_LIKE expression and unfortunately you need to do URLEncode by yourself 😦 (Oracle http client inside sqlplus seems very primitive and in many cases just truncates http request after unknown character)