History for OSPRep

Last modified: October 16 2011
v0.4.7 (16.10.2011)
* though segment stats contained the correct TOP_N elements, display order was somehow messed up
* fixed the "empty execution plan tables" on TOP_N SQL with AWR-layer used
* some formatting correction for large values in TOP_N by exec
+ some more context help
+ added pga target advice for end_snapshot to AWR stats (created depending on MK_PGAA)
+ added sga target advice for end_snapshot to AWR stats (created depending on MK_SGASUM)
v0.4.6 (17.09.2011)
* updated documentation
+ added stats$bg_event_summary to AWR compatibility layer (now MK_BGWAITS works)
+ Top-N SQL statements now can be fetched in "AWR mode"
+ config option to cut off overlong SQL statements (as they unnecessarily blow up the report; 10g+)
+ added some "Hints" (HTML Title Attributes) to the columns in Top-SQL (10g+)
! Top 10 wait where not working with AWR due to error in compatibility layer
+ updated context-help with more details
! some Solaris versions didn't like the syntax of tail used
v0.4.5 (09.05.2011)
! TopSQL was broken for Oracle < 10
+ added Module-Column to Top-N SQL for Oracle 9i and 8i(?)
* some more help-file updates
+ added column for "execs per parse" to top-N SQL by parse table
+ added compatibility layer to run reports based on AWR data (10g). Still not complete, and has some minor issues. See install/database/awr_setup.sql for details -- which is also needed to be run before you can access AWR data with OSPRep.
+ added a few "dummies" to the AWR layer for stats not available with AWR (e.g. rollback stats), so scripts don't choke on those but simply get "empty result sets"
! PGAA still chocked with AWR. Fixed up the statement in the "plugin"
* enqueue stats have an additional column in 10g which we did not consider
* catch connection error (wrong credentials or wrong db or TNS not found)
v0.4.4 (05.04.2011)
* center the graph (*_chart.html)
+ support for 11gR2 (untested)
* some minor adjustments and improvements
* some code formatting, including check for (missing) BINDs to be improved
+ added "..by CPU usage" and "..by time elapsed" section to Top-N SQL Statements
+ added columns "Last Active" and "Module" to Top-N SQL on 10g+
+ highlighting of keywords in (Top-N) SQL statements on 10g+ (and hopefully no more "random" spaces within)
+ added "..by Invalidations" to Top-N SQL on 10g+
+ added some more helpful background info (context-help)
v0.4.3 (09.06.2008)
* connecting a 10g DB using a 9i client leads to dbms_output.put_line truncating its output after the 254th character (10g doesn't issue an error message any longer until you reach the limit of 32k chars). Applied a work-around to fix this.
! Datafile Statistics have been broken with 10g - fixed.
! There are no PGA stats available with 8i. If enabled, this broke the script. They are no longer included now with 8i. Same for Instance Recovery (no STATS$INSTANCE_RECOVERY in 8i), Undo (introduced with 9i), Enqueue and some Buffer Pool Stats
! table stats$system_event changed structures with 9i - so top_n_waits (and other waitevent related stats) have been broken with 8i
! 8i doesn't like some alias names - so we changed them
* a little change to make OSPRep work with Oracle 11g
v0.4.2 (09.07.2007)
* spcreate.sh now automatically determines source directory
+ now OSPRep basically works with Oracle 10g R2 Please report if you find anything strange with the 10g reports!!! They are not yet tested in depth ;)
+ Made TOPSQL and ENQ working for 10g R2
+ added missing primary key to istats$datafiles (under certain circumstances, this lead to multiple instances of each datafile reported). Similar to that, added PK to istats$waitobjects. When updating from a previous version, you should check the corresponding files and add the constraints manually to your database.
v0.4.1 (05.07.2007)
* Starting with the v0.4.x branch, the PL/SQL code moves to the database. Instead of constructing an anonymous block using Shell logic on each call, the Shell scripts will only create simple SQL code that calls the procedures of the package. This is for multiple reasons: First, all the separate scripts (the main report, chart generation and FTS module) can share specific code much better, and second - hopefully and the final target for the main report - we break the 1 MByte limit for the dbms_output by calling the separated sections separately from "normal" SQL. But until then, it will take some time...
* chart generation and fts_plan code now moved into a package to be installed into the DB
! chart generation was broken in previous versions: An Oracle error was thrown when no snapshot was taken for (last_snapshot - max_chart_interval). Fixed.
+ fts_plans.sh now gives much more info on the statements (such as executions, disk reads and the module (=program) that ran the query last
+ added online help (hints for evaluation) to fts_plans
+ added more enqueue types and more details for exec2parse to the online help page
+ added "fast-install" script to setup statspack plus extensions (see install/spcreate.sh)
+ updated doc/readme.txt
* more descriptions in the table scans section
v0.3.7 (20.03.2007)
+ added redo buffer allocation retries to the charts
+ added basic support for v10.2 (tnx to David) - UNTESTED, please report!
! fixed a possible "division by zero" bug in LGWR stats
+ added "related information" to LGWR stats
+ added tabscan block (adapted from orarep) to table stats
+ added online help for table stats and moved most of the comments there (so the report gets a bit more compact)
* made the DBWR+LGWR stats more compact (to fit on one page). Additional comments/hints have been moved to online help
* added "related information" to DBWR stats
! fixed a copy-paste error in SQL by Reads (Tnx to Fabrice reporting this)
v0.3.6 (05.10.2005)
+ added LGWR statistics. Switch them on/off with the MK_LGWR parameter in the config file
+ extended stats for DBWR
+ added config parameter SKIP_DBSTART_ID to ignore the DB warmup phase after instance (re)start
* some little reorganization
+ added table stats (config: MK_TABS)
v0.3.5 (24.08.2005)
+ added workaround for incorrect cursor values resulting from Oracle bug 3926058 (you need to install the "plugin procedure" get_sesstat() to make use of this)
! Resource Limits and Segment Stats have never been built (error in SQL clause)
+ made Resource Limits optional (MK_RLIMS in config) plus introduced limits for warn/alert (WR_RLIM/AR_RLIM in config)
+ added more hints on sort ratio, file i/o, and instance efficiency
+ added DBWR statistics. Switch them on/off with the MK_DBWR parameter in the config file.
* print function now splits long strings at spaces (instead of fixed pos 255)
v0.3.4 (12.08.2005)
+ added hints for pool size configuration
! fixed a little error in fts_plans.sh and charts.sh which prevented a connection in some cases
! fixed a numeric issue in charts.sh (now explicitly setting the NLS_NUMERIC_CHARACTERS, since JavaScript requires the '.' as decimal separator)
+ added explanations and hints for the top wait events
! In some cases OSPRep crashed when START_ID and END_ID have been specified (invalid range -- e.g. START_ID > END_ID or Statspack had a gap in its snap_ids)
+ added info on possible incorrect values concerning open cursors (due to Oracle bug 3926058)
v0.3.3 (12.07.2005)
+ some formating updates (fixed '#####' fields)
* little reorganization (moved the shared_pool block to the other SGA info)
* corrected navbar information (top x wait)
+ added pool sizes to shared pool stats block
+ added hints on buffer cache management and the execute-to-parse ratio
v0.3.2 (22.11.2004)
+ script now accepts the "-r" command line parameter to override the report dir
+ OSPRep should now also work with Oracle 8i (reports are welcome!)
+ some formating updates (fixed '#####' fields)
! when -b was defined on command line pointing to a time before the last instance restart, the ID of the end snapshot was setup wrong (absolute) if -e was not specified as well
+ generated HTML code is now HTML 4.01 compliant
! in Datafiles Statistics, the report displayed %Used instead of %Free
+ added the -o command line parameter (to specify an alternative output file)
v0.3.1 (26.07.2004)
! when there've been gaps in the snapshot, ratio charts where partly drawn outside the diagram area
+ added "Checkpoint Incomplete" to cumulative charts (Cumul, CumAvg, Delta)
+ snapshot intervals can now dynamically be evaluated as "the last n days up to END_ID" (new config keywords MAX_REP_INTERVAL and MAX_CHART_INTERVAL)
+ added some Snapshot Level 7 (segment statistic) information to the report (Oracle 9.2 and higher): Top X Logical Reads | Physical Reads | Buffer Busy Waits | Row Lock Waits | ITL Waits per segment
! fixed a problem in the print routine that sometimes caused the message "*!* Problem in print() *!*" to appear instead of the data ;)
v0.3.0 (19.04.2004)
* number formatting improvements for time and file/memory sizes
! some values in Top N SQL where named as "seconds" but represented ms
! formatting updates for "inner tables" (here: execution plans) broke ColorCode
+ fts_plans.sh now supports the "-c" command line switch
! report was broken again for Oracle < v9 due to use of CASE (introduced with Oracle v9)
v0.2.9 (19.03.2004)
+ added "Transactions per Snapshot" to ratio charts
* some cleanup to charts.sh
* some code cleanup, formatting improvements and minor corrections
! scripts crashed when specified END_ID was higher then the latest snapshot
+ added alternative StyleSheet: iceage.css
v0.2.8 (12.03.2004)
+ added FileIO to Ratio charts (phys. Reads/Writes per SnapShot)
+ charts.sh now also supports the "-c" parameter (alternative config file)
+ added physical IO bytes to FileIO and TSIO segments
! fixed HitRatio column in Buffer Pool Statistics
+ added Seconds per Execution to Go4Colors for TOP_N_SQL
! fixed the Latch Activity stats (values have been ordered ASC instead of DESC)
* updated descriptions for Latches and Enqueues with short explanation of the term
+ added help on Instance Activity segment and slightly reworked CSS file
v0.2.7 (08.03.2004)
+ Modularization: Datafile Statistics, TOP_N_SQL, TOP_N_WAITS, Wait Objects, Shared Pool Stats, CacheSizes and Instance Efficiency segments can now be switched off (see config)
+ Reorganized segment grouping: connected stats (as e.g. "Datafile Statistics" and "File IO") are now directly following each other (and are no longer spread across the report)
* if no Enqueue Activities are reported, we do not draw an empty table anymore
* some work on formatting (duplicate rulers when excluding certain segments, etc.)
v0.2.6 (04.03.2004)
! Navigation links for SGA and init.ora Stats have been set even if these stats were turned off via the config
+ Modularization: Dictionary- and Library Cache Statistics, RBS Stats and RBS Storage, Undo Segment Summary, Enqueue Activities, PGA Aggregat, PGA Memory, Buffer Pool, Buffer Waits, Instance Recovery Stats, TS and File IO Stats as well as Execution Plans can now be switched off (see config)
* sorted the entries in the "features" block of the config file to represent the order the segments appear in the report (same for the HTML documentation)
v0.2.5 (02.03.2004)
! cumulative charts drew out-of-range lines when there were gaps in the snapshot range
* made the Delta and CumAvg charts more accurate
+ more modularization: init.ora parameters and SGA Stats can now be switched off (see config)
v0.2.4 (27.02.2004)
+ added some hints to the ratio charts
+ made the values for warnings/alerts (Go4Colors) configurable
* some updates on the HTML documentation and the doc/readme.txt file
* sped up chart pages by creating the appropriate max/avg values with the stats (instead of JS calculation)
* made CumAvg charts more precise
v0.2.3 (24.02.2004)
! cosmetical fix: description of initial diagram of Ratio charts fixed
! fixed division by zero error in buffer pool stats
! cmdline parameter -b and -e have been ignored
+ added Pct ReloadsPerPin, Pct GetHitRatio and Pct Row Cache Ratio to Ratio charts
+ applied Go4Colors to fts_plans.sh
v0.2.2 (19.02.2004)
* M$IE had some problems with formatting of the charts - worked around it
+ added enqueue waits to waitobject gatherer
+ added Delta- & Ratio- charts: (current) Logons and Opened Cursors, Freebuffers inspected/requested, Chained-Fetch-Ratio (Ratio charts). Delta- charts have the same items as cumulative charts.
+ added AvgCharts (cumulative: timeouts per snapshot)
* updated the (HTML) documentation
v0.2.1 (16.02.2004)
+ more verbous explanation for cursor sharing
* some formatting updates for the help files
+ added (cumulative) charts for selected system events (use charts.sh to create them)
v0.2.0 (02.02.2004)
+ added documentation (see doc/html/ directory)
+ added description for "Execute to Parse" ratio ("Efficiency" block)
+ added "Go4Colors Mode" for Datafiles Statistics, Instance Efficiency, Top_N_SQL (FTS in execution plans), TS IO Summary, File IO Summary, Library Cache
v0.1.9 (22.01.2004)
! file names could get messed up when different SID/Connect string was specified on commandline with parameter "-s"
* adapted new command line syntax on fts_plans.sh
* if user provided BEGIN_SNAP_ID (with "-b") but no END_SNAP_ID (with "-e") the report could go nuts; prevented this assuming EID=BID for these cases
* some CleanUp of directory structure (moving all files from the plugins/ directory to install/ [since only needed for install], and all "plugins" to the plugins/ directory for cleaner structs and preparation of modularization
+ blocks that may be not needed on a regular basis can now be excluded (or included) on demand - see the "config" file for details
* Resource Limits table is now only created if content exists for it
+ new command line parameter "-c" to specify alternative config file
! some temporary files have not been cleaned up after run
v0.1.8 (19.01.2004)
+ added some enqueue types to help file
+ added PctMiss and PctSleep to Latch Sleep Breakdown block
+ added PctFail to Enqueue Activity block
+ enhanced command line syntax: now you can override some of the default options from the "config" file (e.g. user, passwd) at the command line (thanx to Manuel Soriano for this suggestion incl. code sent for OraRep)
v0.1.7 (09.01.2004)
* execution plans are now also displayed for statements w/o cost (sometimes the optimizer misses to display costs)
+ added fts_plans.sh to retrieve execution plans for all statements causing Full Table Scans (FTS) - these are mostly indicators for optimization, e.g. index creation etc.
+ updated doc/readme.txt on PlugIns
* increased digits for pctchange in SGA Breakdown
v0.1.6 (05.01.2004)
* one more digit for pctChange on data file growth (may now grow up to 9999.99%)
+ added external help file for rollback segment stats
+ added external help file for undo segment stats
+ more detailled help on latch statistics
+ more detailled help on library cache statistics (external help file now)
v0.1.5 (19.12.2003)
+ new AddOn for datafile growth statistics
! fixed another bug in get_plan() that broke the job if a statement had multiple execution plans
! PctChange for datafile growth statistics had wrong base (files got smaller ;)
* increased number length for buffer waits
* outsourced verbose hints to external help files (and made them even more verbose ;)
+ updated the doc/readme.txt concerning the installation instructions
v0.1.4 (10.12.2003)
! fixed a bug in get_plan() that broke the job if a statement had multiple execution plans (in that case, we make sure to display the latest now)
+ now including full execution plan "tree"
+ formating enhancements for plan table
+ added enqueue type descs plus tuning hints
+ enhanced description for wait events
v0.1.3 (02.12.2003)
+ added hints/comments to Instance Efficiency section
+ more comments/hints on latches
+ added execution plan to Top-N-SQL (when found ;)
+ introduced new config var: EXC_PERF_FOR lists up users whose objects should be excluded from performance statistics (makes no sense to e.g. report about the execution plan for operations on the data dictionary since one either cannot or at least should not change those objects). These settings are only used where applicable. As for now, that is for the execution plans plus for objects causing waits. In the latter place you will find a note what was excluded.
v0.1.2 (25.11.2003)
+ added HitRatio to Buffer Pool Stats
+ additional procedure (and table) for statspack to gather information on wait objects is included now. Table needs to be installed into the perfstat schema, the procedure needs the SYS schema - see docs and files for details. Installing this is optional.
+ enhanced buffer wait stats (including tuning tips)
+ added hints/comments to TS/File IO stats, wait events, Top-N SQL statements, enqueue activity, RBS stats and Library Cache stats
v0.1.1 (16.11.2003)
+ optimization: wrote wrapper for dbms_output.put_line
* moved some hardcoded font formatting to stylesheet
* some code cleanup
! fixed Optim W/A Execs in PGA stats (statement from Oracle Statspack didn't work correctly - replaced by own statement)
v0.1.0 (12.11.2003)
+ added example output
+ added some docu
v0.0.8 (11.11.2003)
+ modularized spreport.sh - it now consists of multiple files. spreport.sh itself remains as a wrapper, the real script is generated at runtime. This is due to different parameter structure in the call to the collector procedure of Oracle StatsPack itself between the versions, so: OSPRep now supports Oracle StatsPack for v9.0 AND 9.2
+ added interface for Oracle v8.1.7 (untested!)
! due to insufficient formatting, hash marks (#) could be displayed instead the real values in some cases
! some Shell variables still remained in a place they could not be replaced any more
v0.0.7 (11.11.2003)
+ added possibility to auto-evaluate the latest possible SnapShot interval
+ -"- up to a specified Snap_End (only Start_ID will be eval'd)
* some descriptions changed (th)
+ Start/End ID can be specified at / overridden from CmdLine
+ added some more comments to the configuration settings of OSPRep
+ created version for Oracle 9.2
! pctSGA (for Dictionary Cache block) was not shown due to error in SQL statement. Fixed.
v0.0.6 (10.11.2003)
+ added SGA stats
+ added Resource Limits
+ added Init Params
! SnapShot Info now regards dbid and instnum
v0.0.5 (07.11.2003)
+ added Buffer Pool Stats
+ added Instance Recovery Statistics
+ added Buffer Wait Statistics
+ added PGA Stats
+ added Enqueue Activity
+ added RBS Stats
+ added Undo Stats
+ added Latch Stats
+ added Cache Stats
v0.0.4 (06.11.2003)
* a little code cleanup
+ added Top n SQL by Reads / Exec / Parse
+ made "n" (for the Top-N Stats) a config var
+ added Instance Activity Stats
+ added TableSpace IO Summary Stats
+ added FileIO Summary Stats
v0.0.3 (05.11.2003)
+ added Top 5 Wait Events
+ added All Wait Events
+ added BG Wait Events
+ added Top n SQL by Gets
v0.0.2 (04.11.2003)
+ collecting statspack report info via statspack.stat_changes
+ added Session and Cursor/Session to SnapShot Info
+ added Cache Sizes and Load Profile
+ added Instance Efficiency Percentages
+ added Shared Pool Stats
v0.0.1 (03.11.2003) initial version
+ Output intro: Common instance information
+ simple SnapShot info (id, time, comment)
Generated by HistView v0.1.7 © 2003-2009 by Itzchak Rehberg & IzzySoft