ossw (Oracle Session Statistic Watcher)

This tool allows you to watch a given statistic (as in v$statname, v$sysstat, v$sesstat, v$mystat) of all Oracle sessions in the database. When the value of the watched statistic of any session has incremented over the previous sampling, it's shown on screen. Use cases are as follows:

A screenshot of running ossw is as follows.
$ ./ossw
Usage: ossw [-h] -S Statname_or_# -s TNSAlias [-u User -p Password] [-d Delay] [-v] [-n LowerLimit] [-i IncrementBy]
    -h: Help
    -S: (required) Statistic name (must exactly match an entry in v$statname or v$systat case-sensitively)
        or number (as in v$statname, v$sysstat, v$mystat, or v$sesstat)
    -s: (required) Connect identifier
    -u: Alternate user instead of perfstat to login as
    -p: Alternate user's password
    -d: Number of seconds delay between calls (default 5)
    -v: Show sessions even if their stat values are not changing (output marked 'NoChange!')
    -n: Only show sessions whose stat values are greater than this number
    -i: Only show sessions whose stat values have incremented by at least this number since last sampling (default 1)
Common usage examples:
    ossw -S "parse count (failures)" -s mydb -u yong -p yongpassword
    ossw -S 585 -s dbconn -v -i 3
Output is like:
    Output template:
    where 2 is instance ID, 1234 SID, 33 serial#, 5 parse failures so far, 1 increment
    of parse failure over last sampling, DB user from client appserver
*** Oracle Session Statistic Watcher for statistic #585 "parse count (failures)" ***
$ ./ossw -S 'user commits' -s oracp2n -u yong -p yong1234 -i 10 #watch stat 'user commits', show sessions with at least 10 commits between two samplings
Output template:
where 2 is instance ID, 1234 SID, 33 serial#, 5 so far for the tracked statistic, 1 increment
since last sampling, user HR on machine v3besextm001

*** Oracle Session Statistic Watcher for statistic #6 "user commits" ***


The above shows two iterations of statistic watching (aborted with ^C), each sorted on the accumulated value of the stat.note2 You see that session 512 on instance 1 and 2039 on instance 2 have a commit rate of 37 or 36 per 5 seconds (default interval). If you think the rate is way too high for this application, the developer may be unnecessarily paranoid about losing his data!

Before you run ossw on Linux or UNIX, export LD_LIBRARY_PATH in your shell environment. (It would be too late if this variable was set with Perl directive ENV inside the script, so it has to be set before running ossw.) The example shown above was for 11g on Linux. If it's 10g, perl version will be 5.8.3 and LD_LIBRARY_PATH will end with lib32 (perl bundled with 64-bit Oracle is 32-bit; check with `file'). For 11g 64-bit Oracle, perl is likely to be 5.10.0. For 12c, it's 5.14.1/lib. For Windows, it's different. See below.

Another example.
$ ./ossw -S "parse count (failures)" -s oracp8 | perl -pe '$t=localtime;print "$t: "'
Wed Nov  5 13:07:33 2014: Output template:
Wed Nov  5 13:07:33 2014: 2-1234-33:5:1:HR-v3besextm001
Wed Nov  5 13:07:33 2014: where 2 is instance ID, 1234 SID, 33 serial#, 5 so far for the tracked statistic, 1 increment
Wed Nov  5 13:07:33 2014: since last sampling, user HR on machine v3besextm001
Wed Nov  5 13:07:33 2014:
Wed Nov  5 13:07:33 2014: *** Oracle Session Statistic Watcher for statistic #585 "parse count (failures)" ***
Wed Nov  5 13:07:33 2014:
Wed Nov  5 13:07:38 2014:
Wed Nov  5 13:07:43 2014: 7-513-15799:28:15:RSS-DOPWLIGUANA
Wed Nov  5 13:07:43 2014:
Wed Nov  5 13:07:48 2014:
Wed Nov  5 13:07:53 2014:
Wed Nov  5 13:07:58 2014:
Wed Nov  5 13:08:03 2014: 7-2021-50041:10:1:ATS_MEDIA-ATSW3HW05S1
Wed Nov  5 13:08:03 2014:
Wed Nov  5 13:08:08 2014: 7-2021-50041:11:1:ATS_MEDIA-ATSW3HW05S1
Wed Nov  5 13:08:08 2014:
Wed Nov  5 13:08:13 2014: 7-2021-50041:12:1:ATS_MEDIA-ATSW3HW05S1
Wed Nov  5 13:08:13 2014:
Wed Nov  5 13:08:18 2014:
Wed Nov  5 13:08:23 2014: 7-2021-50041:14:2:ATS_MEDIA-ATSW3HW05S1
The above example finds sessions with SQL parse failures, with logon username and password hardcoded in the program so there's no need to specify them on command line. The Perl one-liner prefixes each line with a timestamp to help you see the exact time the error occurs. (It's a generic command line trick; you can even use it to simulate 12c Data Pump logtime option. On Windows, change " to \" and ' to ". You can also build this timestamp feature into ossw.) Once the error occurs, you can logon to the instance, 7 here, as SYS and trace it with exec dbms_system.set_ev(2021,50041,10035,1,''). To stop tracing, exec dbms_system.set_ev(2021,50041,10035,0,''). Then check alert.log for the failed SQL and error. In this particular case, I got error=1031 in the log. If the sessions come and go quickly, you can either create a logon trigger for the schema to set the event
grant alter session to ats_media;
create trigger ats_media.logon_set_event
 after logon on ats_media.schema
 execute immediate 'alter session set events ''10035 trace name context forever''';
or in a wider scope, alter system set events '10035 trace name context forever'. After you have captured an error and you're done reviewing alert.log. drop or disable the trigger, or alter system set events '10035 trace name context forever, level 0'. (If a session logged on when the logon trigger was in effect and stays logged on, the event has to be explicitly disabled for the session. It's harmless to run dbms_system.set_ev(sid,serial#,10035,0,'') for sid even if the event was not set for it.)

To install, save ossw.txt for a single node database, or ossw_RAC.txt for a RAC database, to a text file named e.g. ossw. (The RAC version works for a single node database as well, except it queries gv$ views and always displays the useless instance ID 1.) Once you download it, follow the instruction in the second (for Windows) or third (for UNIX/Linux) paragraph of the script. Since ossw is a purely client side tool, and it connects to the database through Oracle Net (even if it's on the database server), you can install it anywhere just once and run against all databases you have access to; no need to install it on each server.

If your intention is to watch all statistics for a given session, use my Delta Sesstat instead.

note1 Please refer to
parse_failure.txt for more info about this statistic. (Ignore the scripts provided therein, which are deprecated in favor of ossw.) To find the SQL that caused the parse failure, pick a session with increasing parse failure counts, use event 10035 (Write parse failures to alert log file) to trace the session: dbms_system.set_ev(sid,serial#,10035,1,''). The SQL that failed parsing is recorded in alert.log, not the session or process specific trace file. See Note 1353015.1 for more info. To turn off, change level 1 to 0. Some but not all SQLs failing parsing can also be found in the regular SQL trace with event 10046, i.e. dbms_system.set_ev(sid,serial#,10046,4,'') or dbms_monitor.session_trace_enable. Then grep '^PARSE ERROR' session_trace_file. You can also find the SQLs that throw specific parse-related errors, e.g., dbms_system.set_ev(sid,serial#,942,1,'errorstack') to generate a trace file if ORA-00942 occurs to the session. (The event name "errorstack" can be replaced with "context" or null (since "context" is default), but only the non-existing table or view is printed, not the full SQL.)

note2 This cannot be easily changed to sorting on the incremented delta, sorry! But you can easily change it to sort on other columns, i.e. instance, SID, username-machine. See the code.

To my Computer Page