> Tools and Products >

  OraPub System Monitor (OSM) Toolkit (v16c 12-Aug-2015)


Product Code: OSM

To download click here.

Note: My ASH scripts have been put into their own toolkit, called BloodHound. It's also free and can be downloaded HERE.

The OSM is a complete set of Oracle Database tuning tools based upon Time Based Analysis and Unit of Work Time Based Analysis. The OSM is sqlplus based, non-graphic, and comes with absolutely no warranty....and you'll love it!

The OraPub System Monitor (OSM) is the tool kit referenced in OraPub Online Institute Seminars, Shallahamer's book, Oracle Performance Firefighting, used during OraPub consulting engagements, technical research, and during our training classes.

There are a few unique tools bundled within the OSM tool kit. Here are the main ones:
  • readmeEventHist.txt provides details about the collection, reporting, and charting of wait times.
  • osm.sql is the OSM menu. Yes, the menu is a SQL script. Run it and you'll see the listing of all the script along a short description and their command line parameters.
  • as.sql is a real time single session sampler. You repeatedly run the script (think: press /) and you can watch a session's activity.
  • rss.sql is my Real-Time Session Sampler. It is a simple but very power learning and analysis tool. Your selected active session details are written in real-time to a /tmp file, which allows you to stream the output by doing a "tail -f" on the file. On the command line you enter the filtering condition and the sample frequency. I use this tool in my presentations, training events and also in my OraPub Online Insitute video seminar, Tuning Oracle Using An AWR Report.
  • sqlelget[11].sql gathers SQL elapsed times so you don't get caught only using the average elapsed time. This is detailed in my OraPub Online Institute video seminar, Using Skewed Performance Data To Your Advantage.
  • swhist[x].sql reports wait time details from v$event_histogram so you don't get caught only using the average wait time. This is detailed in my OraPub Online Institute video seminar, Using Skewed Performance Data To Your Advantage. The data can be used in conjunction with my Wait Event Time Distribution Analysis Tool.
  • swgettimes.sql gathers individual wait times in real-time for a specific wait event so you don't get caught only using the average wait time. This is detailed in my OraPub Online Institute video seminar, Using Skewed Performance Data To Your Advantage. On a super busy system you may need to swhistx.sql instead.
  • ttpctx.sql shows both wait time details along with CPU consumption merged into a single report giving you a better understanding of the time situation...key to doing an Oracle Time-Based Analysis.
  • rtpctx.sql shows time with a response time focus. Like ttpctx.sql it shows total time, but it also shows time related to a single buffer get, physical IO read, commit, block change, etc. allows to do a more advanced Unit Of Work Time-Based Analysis (UOWTBA).

If a tool crashes, trying adding the Oracle release to the end of the script. This is how we keep the older scripts working and available. For example, if rtsysx.sql crashes, try rtsysx11.sql then rtsysx10.sql or perhaps rtsysx8.sql.

If you would like to receive a short email when we update or post a new tool, please subscribe to our Tool Update emails. There is a subscription form on the main Tools Web page.
What has changed by version and date.

v16c 12-Aug-2015. Put back in the missing title scripts and added both the database name and instance name in the title. Allowed tmcomp.sql to be run without first running osmsetvar.sql.

v16b 4-Aug-2015. Added tmcomp.sql which compares/contrasts Oracle time-based and sample-based (ASH) analysis for a given Oracle session.

v16a 28-July-2015. Added as.sql, removed dbms_lock from swEhVdGet, removed the "historical" script so there is no longer a historical or interactive subdirectory, cleaned up the menu program osm.sql and lots of other little changes.

v15o 22-May-2015. Updated wait event classification to better handle wait events containing "I/O". See table o$event_type.
v15n 14-May-2015. Cleaned and fixed up the swVd*.sql and swVd.r tools.
v15m 13-May-2015. Cleaned and fixed up the swVd*.sql and swVd.r tools.
  • To install, do the typical "tar xvf osmXX.tar". A new osm directory will be created and all the scripts dumped into either the interactive or the historical directory. There is a readme.txt file to get you started. Installing the tools is very simple.
  • Probably the best interactive tools are rss.sql, sqlelget[11].sql, ttpctx.sql, rtpctx.sql, iosumx.sql, swpctx.sql, objfb.sql, tp.sql, sessinfo.sql, swhistx.sql, ashsqlpcte.sql and some of the ASH reports.

  • Version 13j cleans up some stuff. The Real-time Session Sampler, rss.sql sometimes skipped a sample number when displaying data. But the data has always been correct.

    Version 13i contains a very cool new script, rss.sql - Realtime Session Sampler.

  • Version 13g contains a number of updates new tools.
    • swname.sql shows wait events names and their 12c display name.
    • rtpctx.sql shows total time (cpu + wait) in a standard type wait event report and also shows how time relates to work using a number of typical workload statistics.
    • ttpctx.sql is the same as rtpctx.sql above except it does NOT contains the workload details. This makes the report quicker to run.
    • sqlelget11.sql was added because sqlelget.sql referenced a 12c column...sorry about that. As with all osm script, try the non version specific tool (sqlelget.sql). If it doesn't work, but the next most receipt version (sqlelget11.sql), repeat until it works. If still doesn't work, contact OraPub.
  • Version
  • 13f contains misc updates and two new tools, swgettimes.sql and sqlelget.sql
    Version 13e contains many updates and the first set of changes for 12c.

  • Version
  • 13b contains an many updates.
    • rtpctx.sql. It was cleaned up and more workload metrics added. It's become one of my favorite scripts because you can use it not only for firefighting but for advanced analysis (near predictive analysis).
    • ASH scripts. Many of the ASH scripts, they all start with ash, have been updated to better communicate what is displayed. There were no true technical changes.
    • swhist[x].sql. This script shows wait event histogram details. It has been enhanced to more easily take the data and place directly into our Wait Event Time Distribution Analysis Tool. It's a very cool tool!
    • iosum[x].sql. This script gathers Oracle instance IO read and write details, in both MB/s and IOPS, plus in R, W, and R+W. The script has been enhanced to pull the MB/s data from the v$sysstat statistics, physical [read|write] total bytes. It's cleaner than using the related IO requests stats.
  • Version
  • 13a contains a new script and two significantly updated scripts.
    • latchchild.sql.. The new script, latchchild.sql, lists all the buffers associated with a specific latch's child latches (address). It can be used to determine why a specific child latch is unusually active/popular. This was detailed in Craig's November 2011 blog posting entitled, CBC Latch Activity Patterns.
    • rtpctx.sql. The script rtpctx.sql, has been significantly updated to show time from a response time and also a wait event only perspective. In addition, the workload activity portion of the script has been expanded to include the arrival rate, service time, and queue time based on each of the workload types shown. This makes is much quicker to model a poor performing system, especially when used in conjunction with OraPub's M-Solver.
    • sessinfo.sql. The sessinfo.sql script is an old script with some additional life placed into it! Based on your session selection criteria, in addition the existing output we added the current SQL_ID, CPU consumption, and pl/sql elapsed time.
  • Version
  • 12y contains the updated ashrt.sql report, which is an ASH based instance response time report. Sometimes the CPU numbers were not correct. This has been fixed so the numbers are correct all the time (assuming of course, Oracle provides the correct information).
  • Version
  • 12x Two new reports: rtpctx.sql is a response time report that looks like our classic wait event report, swpctx.sql but it includes CPU time, shows various workload metrics, and also includes the sample interval time. The other new report is latch_class.sql which details information about, you guessed it, latch classes. Latch classes are used when you want to adjust the spin count just for a specific latch. And finally, the wait event classification script, event_type.sql has been updated with 11gR2 in mind.
  • Version
  • 12w various updates including an additional reporting column in topdml.sql.
  • Version
  • 12v various updates including a new script to help spot the top DML statements, topdml.sql.
  • Version
  • 12u updates both the instance level response time reports (rtsysx.sql and rtsysx8.sql). Their service time (CPU consumption) categorization now more correctly includes only sever process time and background process time. The total parse time and recursive sql time is also shown, but because these crossover each other, they cannot be segmented into perfectly separate classifications. (I talk more about this in my classes and in my upcoming book.)
  • Version
  • 12t has two new wait event reports. The reports are near-copies of the swpct.sql (since instance start) and swpctx.sql (delta) reports, except they only show the IDLE/bogus events. The reports are swpctidle.sql and swpctxidle.sql. This is a good way to spot events that are idle and yet very important (which is sometimes true). Also, you can always re-classify the events, that are reflected in all OSM tools, by modifying the event_type.sql (10g+) and the event_type_nc.sql (pre 10g) scripts and then re-running the script.
  • Version
  • 12s has adding an ASH based Oracle response time report (new ashrt.sql), a SQL ranking based upon wait event (updated ashsqlpcte.sql), and also a SQL ranking based upon CPU consumption (new ashsqlpctcpu.sql). They fit nicely together since after you perform the response time analysis you will most like want to know the SQL related to both CPU (service time) and the wait time (queue time). Then you can re-use the same response time report to perform a SQL statement response time analysis. It's like profiling a SQL statement. The response time report as not as detailed as the classic rtsysx.sql, but it does a very nice job.
  • Version
  • 12r has two main areas of change. First some of the wait event reports have been cleaned up as a result of their inclusion in Shallahamer's book, Oracle Performance Firefighting. The scripts are swsessid.sql, swsid.sql, and swswp.sql. The main response time report, rtsysx.sql, has also been updated... but honestly, we forgot what we changed (it wasn't major). The second area is we added a new series of IO reports to aid in understanding how much IO Oracle processes are reading and writing; expressed in both MB/s and IO Operations. This is a big deal when forecasting. The new scripts are iosum.sql, iosum9.sql, iosumx.sql, and iosum9x.sql. We think you'll really like these!
  • Version
  • 12q focused on the response time report, rtsysx.sql and the associated rtsysx8.sql. RTSYSX.SQL is now based on the system time model utilizing the v$sys_time_model view as much as possible. Because this view is used, the script will only work with 10g and above. Also, the CPU or service time now has breakouts for parse time, recursive SQL time, background process time, and other! RTSYSX8.SQL will work with any Oracle release. It also has an enhanced CPU time details parse time, recursive SQL, and other. But because v$sys_time_model is not being used, their the background time becomes part of the "other" category.
  • Version
  • 12p is a major OSM update. Here's the list:
    • sessinfo.sql (lists session details based upon a number of criteria) as additional columns added (like service_name) and also filters on your input of the service_name, module, and client_identifier
    • ASH scripts added centered on the client_identifier. Since the SID is becoming less useful in modern Oracle systems, we have added four new scripts based upon the client_identifier. This allows you to view wait event, SQL, and their combinations based upon a client_identifier.
    • rtsysx.sql (system level RTA) now reports workload activity during the delta, gathers cpu time based upon v$sys_time_model and not v$sysstat, rtsysx.sql works on all 10g and 11g releases so rtsysx10.sql is no longer necessary, and the script has been simplified and requires less resources itself to run.
    • rtsess.sql (session level profile) is now based upon the client_identifier, not the SID. It has also been simplified and requires less overhead to run. Contained within the script is also lots of documentation about how the script works. If you to profile a session based the SID, use rtsess9.sql
  • Version
  • 12n contains a new script, oscpux[10].sql, which shows operating system CPU utilization. It's based on v$sostat. Some event categories were also fixed up (minor). A new rtsysx.sql script rtsysx10.sql was added to detail with 10g differences compared to 11g. If you are running 10g or 9i use rtsysx10.sql, for 11g run rtsysx.sql, and for anything less than 9i run rtsysx8.sql.
  • Version
  • 12m contains a number of miscellaneous updates. Nothing shocking, but I recommend upgrading.
  • The
  • 12k release does a bunch of new and updated stuff. All wait categories have been aligned and fixed up with Oracle 8, 9, 10, and 11. The main response time report (rtsysx.sql) has been enhanced in a number ways including breaking down service time into parse time and other. There is a new simple response time (used loosely) report (rtc[x].sql) based upon Oracle wait event categories. Most of the scripts have been tested in 11g and appear to be working correctly.
  • The
  • 12j release fixes up some botched wait event categorization for both pre-10g, 10g, and 11g.
  • The
  • 12i release updates rtsysx8.sql so it works nicely in an Oracle 8 environment. I also added mkodo_ouch.sql which inserts 10k rows into a table, but commits after each one. It's great for testing!
  • The
  • 12h release looks to have completely fixed all issues with both the delta (blue line) wait event (swpctx.sql) and response time reports (rtsysx8.sql, rtsysx.sql) for all Oracle 9-10gR1/2 releases. The objfb.sql script is also amazingly faster now thanks to a trickly solution by one our students!
  • The
  • 12g has a few minor updates, but the rtsysx.sql report has been fixed for 10gR2. In R2, Oracle changed some v$osstat, so rtsysx.sql had to be modified to support R1 and R2. Don't forget, there is an rtsysx8.sql that works for Oracle 8 and Oracle 9.
  • The
  • 12f updates most of the ASH reports to include a session's serial number where appropriate. Also, the response time report, rtsysx.sql, has a fix in the operating system CPU calculations. That's it.
  • The
  • 12e release contains a few bug fixes for release 12d. There was a problem with osmprep.sql running the old response time view creation script which caused problems with some of the enhanced reports (namely rtsysx.sql). Simply tar xvf the distribution and re-run osmprep.sql and you'll be all set!
  • The
  • 12d release is truly a major OSM release. Make sure to re-run osmprep.sql!! Here's what new:
    • Includes an entirely new set of active session history (ASH) based reports, which all start with the letters ash. Just run the menu script, osmi.sql to see the list.
    • The system level response time report (rtsysx.sql) has essentially been entire re-written to increase the data gathering speed, be more readable, and be more accurate. It also also includes additional sections about Oracle CPU consumption (v$sys_time_model) and OS CPU utilization (v$osstat). If you're Oracle release can't handle the new views, just run rtsysx8.sql (which is the older version). This is a wonderful report!
    • There are now reports about OS CPU utilization gathered from Oracle (oscpux.sql) and also Oracle CPU consumption (oracpu.sql)
    • Wait event time histogram information reports (swhist[x].sql) allow statements like "90% of the scattered reads over the last 10 minutes where less than 8ms."
    • Those crazy response time views I created are almost completely gone now with the exception of rtsess.sql (session level RT profile).
    • Even swpctx.sql has been upgraded to run quicker in a heavily loaded system.
    • The sessinfo.sql script now shows additional v$session columns like module, client_identifier, and blocking_session.
  • The
  • 11m release has significant fixes to only the interactive portion of OSM. To ensure the new enhancements take affect, if you are upgrading run osmprep.sql again. The following scripts have significant changes/enhancements; rtsesss.sql (session level response time profile) and rtsysx.sql (system level response time analysis profile). A few other scripts have some minor changes aimed at helping you to understand the tool.
  • The
  • 11g release has some minor fixes to timechk.sql and a couple of other files.
  • The
  • 11f release has some general fixes, but nothing substantial (that I can remember).
  • The
  • 11d release has some very significant upgrades. The 11e upgrade had some minor 11d to 11e changes.
    • All time is now gathered from v$sysstat's CPU used by this session so it is NO longer necessary to set the init.ora parameter timed_os_statistics. This affected a number of scripts, which have all been updated.
    • You do NOT need to connect as user SYS to install the tools anymore! This required a re-write of some of the tools, but they are better than ever!
    • We made a number of performance improvements in scripts that gather data for delta reporting. For example, swpctx.sql and rtsysx.sql.
    • The top process script (tp.sql) for pre-10g releases (tp9.sql) has been updated and verified (Oracle changed a stat number in 10g, which caused some confusion).

Share your knowledge of this product with other customers... Be the first to write a review

Browse for more products in the same category as this item:

Tools and Products

view my cart my account my support contact us about us        

                                Built with Volusion                                                                 To receive OraPub's Newsletter »