Home > Tools / Products >

  OraPub System Monitor (OSM) Toolkit (v13b for Ora 7-11g, 8-Jan-2012)



 
Free


Product Code: OSM

Description
 
You can view the actual scripts one at a time here. The OraPub System Monitor (OSM) is the tool kit referenced in Shallahamer's book, Oracle Performance Firefighting, used during OraPub consulting engagements, technical research, and during our training classes. The OSM is a complete set of performance diagnostic tools based upon response time analysis. It also contains a fantastic historical analysis pack (which will be eventually replaced with Statspack/AWR based reports). The OSM is sqlplus based, non-graphic, and comes with absolutely no warranty....and you'll love it!

While we try and keep all the tools working as Oracle releases progress, sometimes they do not work right and sometimes they just don't work the way we want. With older Oracle releases (<10), we do not have a system to test or fix a tool. When this occurs, you are on your own. We will gladly help you when we can and offer advice, but do not expect support from a free tool kit that comes with no guarantee and no warranty.
Features
  • The OraPub System Monitor (OSM) is the tool kit referenced in Shallahamer's book, Oracle Performance Firefighting, used during OraPub consulting engagements, research, and during our training classes. The OSM is a complete set of performance diagnostic tools based upon response time analysis. It also contains a fantastic historical analysis pack (which will be eventually replaced with Statspack based reports). The OSM is sqlplus based, non-graphic, and comes with absolutely no warranty...and you'll love it!
  • OSM Historical (OSM-H) is two tool kits in one. The first part periodically gathers performance data from Oracle, the application, and the operating system. It is also extremely easy to incorporate your own data gathering script into the main driver program (named "rock"). The second part is reporting. Most of the data that is gathered has an associated reporting tool. When you select a range of data gathering periods to report on, you get one or more line of output for each data gathering period. In statspack you only get one. This allows you to see much, much more. OSM-H is rarely updated and we will probably de-support in the future because Statspack is so widely used.
  • 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 rtpctx.sql, iosumx.sql, swpctx.sql, objfb.sql, tp.sql, sessinfo.sql, swhistx.sql, ashsqlpcte.sql and some of the ASH reports.
  • 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 / Products


Forecasting Oracle Performance Book (Apress) $25.95
Oracle DB Server Stress Identifier (v2a) $49.95