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.
$ 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
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
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; /
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