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 19.29, actual 2056, listed 817).

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 default_user to login as -p: Alternate user's password; if not specified when you have -u, you'll be prompted -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 -i 10 #watch stat 'user commits', show sessions with at least 10 commits between two samplings Password: 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) on a 4-node RAC database. 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!

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 of the default user hardcoded in the script 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 progress of OSSW. (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 the ossw program.) Once the error occurs, check alert_SID.log to see if more info is recorded. If SQL ID is recorded, logon to the instance, 7 here, as SYS and find the SQL, sometimes only part of the SQL, with select kglnaobj from x$kglob where kglobt03='SQL_ID'.^note

To install OSSW, save ossw.txt to a text file named e.g. ossw, and chmod 755 ossw. (If your database is single-node, the session SID will be prefixed with a useless 1-.) Once you download it, modify the #! line and ORACLE_HOME. 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 any database you have access to; no need to install it on each server. It can also be run on Windows, but if you choose to be prompted for the password, the password will echo to your screen and you'll get a harmless error about stty.

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

___________________
^note If alert_SID.log does not show the SQL, try tracing the session with exec dbms_system.set_ev(2021,50041,10035,1,''). Once OSSW shows the session has incremented on the parse failure stat again, stop tracing by 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.) 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.

To my Computer Page