OSPRep

OSPRep is a tool for generating dynamic reports based on statistics gathered by Oracle StatsPack in your database.

OSPRep is an Oracle StatsPack Report Generator, used to generate reports about your database activity statistics in a nice, human readable format - which in this case is HTML -, based on the data collected by Oracle StatsPack and, optionally, some PlugIns shipped with OSPRep. The generated report provides you with information on the physical database design (tablespaces, datafiles, memory), statistical values on your databases efficiency and more. It is highly configurable not only concerning the report elements you need. Due to its modular design, only the needed code (depending on the options you chose in the config file) will be build and executed, to save your database from unnecessary load.

What does OSPRep do?

OSPRep reads its data out of the (level 5 - 7) statistics gathered by the statspack.snap() procedure of Oracle StatsPack and the corresponding procedures from above mentioned plugins. Analogue to the ASCII report one may generate using Oracle StatsPack's spreport.sql, OSPrep generates HTML reports out of these data. The additional information gathered (and reported) by the mentioned PlugIns includes e.g. datafile growth and wait events.

Together with these data, OSPRep gives some descriptions on their meaning as well as ideas what they may point to or what actions are required from the DBA in order to bring the instance to a more efficient state. For some issues, even code pieces for possible required actions are given.

Other useful things to mention here are the AddOns like fts_plans.sh script provided by OSPrep, which can be used to gather execution plans for all SQL statements executed during a certain period of time, having caused full table scans (FTS) - and the charts.sh script, generating some simple charts for selected statistics. This way OSPRep gives you the information on your finger tips to start the tuning of your applications.

What does OSPRep NOT do?

OSPRep will take no (write) actions to the database (only things that OSPRep writes are the HTML report pages and some temporary files; the latter ones are automatically removed when the script finishes). It only does report what it finds in the database plus some general description on those values. It gives no "perfect solution" to problems that may exist with your instance, just hints to what may be helpful. It will not repair or tune your database for you: in order to have this done, you must draw your own conclusions out of the report results.

What is new in this release:

• support for 11gR2 (untested)
• some minor adjustments and improvements
• some code formatting, including check for (missing) BINDs to be improved
• added Top-N SQL Statements sections for "..by CPU usage", "..by time elapsed", and "..by Invalidations" (10g+)
• 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 some more helpful background info (context-help)

What is new in version 0.4.3:

• 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