Remote daemon autot

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:

  1. You have a pretty small  web server which keeps connection/s with the same database as your main session.
  2. 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).
  3. Your daemon keeps your “main session” v$sesstat snapshots and gives you back “delta” results.

You can download the daemon from here:

https://github.com/drema201/RDAutot/tree/master/build/distributions

to run the daemon you need:

RDAutot.bat scott/tyger@localhost:1521:ORCL  [optional port]

and to use it from sqlplus:

@http://127.0.0.1:80/stats/sid=&SID/statname=(parse)/

select count(1) from dba_tables;

@http://127.0.0.1:80/stats/sid=&SID/statname=(parse)/

PS

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)

 

TNS protocol decypher – 1st execution

CURSOR_SELECT_1ST

Here you can see a network packet for the 1st execution of the cursor:

  • cursor number =0 – as no cursor has been opened on server side so far
  • 0x0193 = 403 – a length of my SQL statement (SELECT /* ….. FROM YYYTEST …..)
  • 0x0A = 10 – prefetch size for the cursor (default JDBC prefetch size)
  • 0x28 = 40 – a size of my bind variables array, i.e. :f1, :f2, …., :f40

and after the sql text itself you can see a section of bind definitions:

CURSOR_SELECT_1ST_2