Configuration
Since the configuration file is executed as a shell script, we have to stick to Shell rules here – i.e., no spaces left or right to the equal sign: the syntax is strictly "var=value". Do not ever remove or comment out any configuration lines, even if you want to use OSPRep's defaults, since this may lead to misbehaviour. The script relies on the settings are made in the config file.
Required Settings
The configuration shipped with OSPRep already contains useful default values for most settings. However, there are some values the developer cannot know or set up to auto-run on every system, such as usernames, passwords, the database name or the target directory to store the reports in. So these are the minimum of settings you have to adjust in order for OSPRep to run properly:
VariableExplanation
ORACLE_SID As already known from the Oracle setup, the ORACLE_SID is the Service IDentifier for a given database instance. In the context of OSPRep, the SID is additionally used to generate the file names for the database report files, which will be called <ORACLE_SID>.html (for the report generated by the sreport.sh script), <ORACLE_SID>_fts.html (for the separate FTS reports generated by fts_plans.sh) and <ORACLE_SID>_chart.html (for the chart pages created by the charts.sh script). If you did not specify a separate connection string (see the command line parameter "-c" in the Usage section of this documentation), this value is also used to connect to the database.
user The user and password to connect to the database. Although you may override these settings using the command line parameters "-u" and "-p", this is not recommended for the password on manual calls, since the entire command line is kept in the systems process list and can be made visible by any user e.g. with the ps command, and thus the password can be sniffed. So better specify it inside the script and protect the file against unauthorized access, e.g. using the shell command "chmod 0700 <config_file>". If you need different user/password combinations, the recommendation is to use different config files, which you then can specify on the command line using the "-c" switch.
password
TMPDIR Directory to place temporary files into (these files will be removed at the end of the run of the script). The default setting, /tmp, should be fine for most systems. Just ensure that the /tmp directory is available on your system ;-)
REPDIR The directory where the reports (HTML) should be written to. Below this directory you created the "help" directory when following the installation instructions supplied with this documentation.
CSS Name of the StyleSheet the HTML reports should use, with relative path (if any). An example StyleSheet file, containing all classes needed/used by OraRep, is provided in the reports/ directory of the archive.
Optional Settings
Once you are familiar with the reports generated by OSPRep, you may want to fine-tune it a bit to better suit your requirements. For this issue, the config file provides some more settings which are to be explained here:
VariableExplanation
SQL
TOP_N_SQL How many items to list up in the "Top N" blocks of the report for Wait objects and SQL statements
TOP_N_WAITS
EXC_PERF_FOR This can be used to exclude objects of given users from some statistics. For example, it makes no sense to print long execution plans for statements generated by the system and just using system objects, since we will never go to tune these. In most cases, the preset of "SYS SYSTEM" will be fine. If you need to change this, beware its case sensitivity (must be all uppercase for current Oracle releases)
START_ID These two values specify the snapshot interval to consider. Setting the value of zero for both instructs OSPRep to automatically evaluate the interval - it will then find the latest snapshot available and consider all snapshots since the last instance startup for the report. Leaving the START_ID on zero but specifying a different END_ID will take the largest possible interval up to the given END_ID. Setting both values to a value <> 0 will take the specified interval. Be careful not to set START_ID to a non-zero value but leaving END_ID on zero - this would mean just to use the same Snapshot for both, start and end.
These two values can be overridden on the command line, using the parameters -b (for the "Begin" snapshot) and -e (for the "End" snapshot).
END_ID
SKIP_DBSTART_ID Skip data from the snapshot taken within the first hour after DB startup. Useful to ignore the load from the warmup phase of the DB. This value will be ignored when you explicitely set the START_ID e.g. at the command line.
MAX_REP_INTERVAL These two values as well specify the snapshot interval to consider, but in a different manner. First, the MAX_*_INTERVAL only applies if START_ID=0. In this case OSPRep considers the snapshot interval from the END_ID value and subtracts the amount of days specified by MAX_*_INTERVAL to find the starting point. Configure this feature with the *_REP_* keyword for the reports, and with the *_CHART_* keyword for the charts. Setting the value of zero instructs OSPRep to evaluate the snapshot interval as described above using START_ID and END_ID settings.
MAX_CHART_INTERVAL
Features
MK_INSTEFF The "Instance Efficiency Percentages" provide an overview of some important ratios, such as "Buffer HitRatio", "Library HiRatio" and "In-Memory Sort" and should normally be included with the report.
MK_TOPWAITS Whether to display the "Top N Wait Events" block.
MK_ALLWAITS Whether to display the "All Wait Events" and/or "Background Waits" segment. In normal cases, you will not need the "All Wait Events" and "Background Waits" segments (that's why they are turned off by the default configuration), but they may be useful for closer investigations.
MK_BGWAITS
MK_WAITOBJ Whether to list up the Top N Wait objects (see TOP_N_WAITS above). This is only possible if you installed the additional collector plugin shipped with OSPRep - otherwise the setting of MK_WAITOBJ will have no visible effect.
MK_TOPSQL List up the Top N SQL statements at all? If set to MK_TOPSQL=1, four blocks will be included: Top N SQL by Gets, by Reads, by Executions and by Parse Calls. If MK_TOPSQL=0, the setting for MK_EP will have no effect at all (since execution plans are always connected to SQL statements).
MK_TABS Generate basic table statistics
MK_EP If your snapshots are taken with level 6 or higher, the execution plans are captured with each snapshot and can be displayed together with the respective SQL statement in the TOP_N_SQL. block. With the MK_EP parameter you can suppress these execution plans even if they are available (by setting it to MK_EP=0). However, if you only collect level 5 data with your snapshots (or if you set MK_TOPSQL=0, see above), MK_EP=1 will have no effect at all.
MK_TSIO The "TableSpace IO Summary Statistics" and "File IO Summary Statistics" generally list up the same values, as long as each TableSpace consists of exactly one datafile. In this case, you may want to turn off one of these two stats (which normally will be the TSIO, since FIO additionally lists up the file names).
The only other difference is: TSIO stats are ordered by IOs, while FIO stats are ordered by TS and file names.
MK_FIO
MK_DBWR Here you define whether you want to include (1) the DBWriter/LogWriter statistics in your report or not (0). These statistics may help you if you assume problems with the DBWR/LGWR, since they give more details on their activity.
MK_LGWR
MK_SEG_LR These switches are for the segment statistics (Statspack Level 7) introduced with Oracle 9.2 and will have no effect for Oracle < 9.2 reports. Valid settings are 0 and 1 to switch the selected block on or off in the report: LR = Logical Reads, PR = Physical Reads, BUSY = Buffer Busy Waits, LOCK = Row Lock Waits and ITL = ITL Waits per segment. These reports are always ordered by waits DESC.
MK_SEG_PR
MK_SEG_BUSY
MK_SEG_LOCK
MK_SEG_ITL
MK_INSTACT The "Instance Activity" segment of the report can be boring sometimes, so you may suppress it here and just activate it if you really need it ;-)
MK_RECO The "Instance Recovery Stats" segment gives you information on estimated recovery times, blocks etc..
MK_SPSTAT Whether to include the short common Shared Pool Stats
MK_BUFFP The "Buffer Pool Statistics" segment gives statistical information for all available pools (e.g. Default, Keep, etc.), while the "Buffer Wait Statistics" report on waits for the different classes (such as "data block" or "undo header").
MK_BUFFW
MK_PGAA Whether to include the "PGA Aggregat" and "PGA Memory" segments of the report.
MK_PGAM
MK_ENQ The "Enqueue Activity" segment of the report contains information on for which queue types have been waits encountered
MK_USS Except for special occasions, the "Undo Segment Statistics" (MK_USSTAT) will not be needed (especially when using automatic undo management). The "Undo Segment Summary" table can also be turned off, if not needed, with the MK_USS=0 statement.
MK_USSTAT
MK_RSSTAT Whether to create the "RBS Stats" and "RBS Storage" segments of the report. These Rollback statistics are quite useless if your database uses automatic Undo management.
MK_RSSTOR
MK_LACT "Latch Activity" will only be needed for closer investigation, so this option is turned off by the default configuration. When you will need this, the hints/online help of OSPRep will indicate it to you.
MK_LMS The "Latch Miss Sources" report is only needed by Oracle staff. The reason for this is quite easy: they are the only possible people to understand it, if anybody does. So you may turn it on just for fun (to see how it looks like), but for your normal reports you can leave it off. Besides: if you already turned the "Latch Activity" (MK_LACT) off, the "Latch Miss Sources" will be suppressed anyway.
MK_CACHSIZ As introduction to the Cache Stats below (see MK_DC and MK_LC), this switch enables a short Cache Size table.
MK_DC Whether to create the "Dictionary Cache" and "Library Cache" segments of the report, containing statistics and ratios on usage of these areas.
MK_LC
MK_SGASUM This is for the "SGA Memory Summary" and "SGA Breakdown Difference" segments. If you set MK_SGASUM=0, the setting for MK_SGABREAK will be ignored and handled as if it was set to 0 as well.
MK_SGABREAK
MK_RLIMS With the MK_RLIMS statement you can switch the Resource Limits Report on (1) or off (0). If switched off, of course no resource limits reports will be generated at all. If set to 1, this report segment will be built only if you have resource limits enabled.
MK_IORA Whether the "Initialization Parameters" segment should be included with the report. This block lists up all initialization parameters as used with the init.ora file, and the changes made between the first and the last snapshot (e.g. with the ALTER SYSTEM command).
Go4Colors
WR_* These are the values used for warnings (WR_*) and alerts (AR_*) in the Go4Colors mode (see there for details).
AR_*
w3cOSPRep © 2003-2011 by Itzchak Rehberg & IzzySoftIzzySoft