ossw (Oracle Session Statistic Watcher)

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:

After a complicated production deployment of new code and database object changes, application servers start to make connections to the database. Before users catch any error, you may run ossw to watch the 'parse count (failures)' statistic from any session. If the app server sessions consistently increase on this stat, the deployment may have missed or misspelled a table, column, or synonym, a sign of mismatch between the database and app server code. You may want to trace the session to find the failed SQL. In fact, this was the original motive of this tool, previously written as parse_failure.pl, which watches that statistic only. Now it's expanded to a more general tool called ossw.
Session non-idle waits are normally checked as the first level assessment of users' slowness problem. But occasionally the real busy sessions may not present themselves as having standard non-idle waits, partly because they are on CPU most of the time, i.e. not waiting at all. As a supplementary diagnostic procedure, finding the sessions that incur the fastest growing 'session logical reads' statistic is helpful. You could check v$sess_io for this purpose, if you already have in your mind a few suspect sessions. If not, ossw is your friend, as it checks all sessions for you.
Of course ossw allows you to watch any statistic of all sessions: 'redo size' to see which sessions are aggressively making changes, 'opened cursors current' to see who has a cursor leak, 'user commits' for possibly over-zealous commits, 'transaction rollbacks' for sessions that violate unique or foreign key constraints, 'consistent gets' or 'CPU used by this session' for general server load contributors, just to name a few. See Oracle documentation for a partial list of the statistics (in Oracle 11.2.0.3, actual 638, listed 318).

A screenshot of running ossw is as follows.

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib $ ./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: 2-1234-33:5:1:user-appserver 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: 2-1234-33:5:1:HR-v3besextm001 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" *** 1-512-54894:1637:37:EVC-DCPWPEVC 4-305-4390:16073:10:APP1-dcpwpaembv1 2-2039-1319:17944:37:EVC-DCPWPEVC 3-879-23032:2127535:11:RS_OWNER-d1prlign3.example.com 4-827-26334:2127535:11:RS_OWNER-d1prlign3.example.com 1-512-54894:1673:36:EVC-DCPWPEVC 2-2039-1319:17981:37:EVC-DCPWPEVC 4-160-1283:31384:11:APP1-dcpwpaembv1 ^C

The above shows two iterations of statistic watching (aborted with ^C), each sorted on the accumulated value of the stat.^note1 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
^C

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 ". I could 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_SID.log for the failed SQL and error. 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
begin
 execute immediate 'alter session set events ''10035 trace name context forever''';
end;
/

or set it for the whole instance, 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 off'. (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.)^note2

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 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.

^note2 Please refer to parse_failure.txt for more info about the "parse count (failures)" statistic. (Ignore the scripts provided therein, which are deprecated in favor of ossw.) See Note 1353015.1 for more info about event 10035. Some, but not all, SQLs that fail in 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.)

To my Computer Page