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 OraRep'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 OraRep 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 make in order for OraRep to run properly:
VariableExplanation
Database Information
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 OraRep, the SID is additionally used to generate the file names for the database report files, which will be called <ORACLE_SID>.html. 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 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. 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
Files and Directories
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 OraRep, 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
Warnings
TPH_NOLOG OraRep will issue a warning (yellow table cell background) when there was no log file available (e.g. the "log file switch(*)" event) more often per hour than specified by this value. The value specified here should be low, since on the specified events all COMMITs wait until the event is done. See online help on these events for details.
WPH_NOLOG Similiar to the idea of TPH_NOLOG above, this value is not used for timeouts but for the total waits. I.e., the value specified here should be higher than the one above.
WR_BUFF Issue a warning when the Buffer Pool Ratio (see "Buffer Pool Statistics" section of the report) exceed the WR_BUFF, and an alert if it exceeds the AR_BUFF value.
AR_BUFF
WR_FILEUSED For a datafile having auto extend turned off, issue a warning when more than WR_FILEUSED % and an alert when more than AR_FILEUSED % of the file are in use (see the "Data Files" section of the report).
AR_FILEUSED
SQL
TOP_N_WAITS How many items to list up in the "Top N" blocks of the report for Wait objects and tables (the latter in the Miscellaneous block at the end of the report)
TOP_N_TABLES
Features
MK_USER The "User Information" segment consists of two tables: while one lists up all users on the instance together with their account information, default tablespaces, profiles and initial resource group, a second table lists up all users that have admin privileges. This second table can be important for security checks.
MK_DBLINK Whether to display all DBLinks and to check their status.
MK_RSRC Resource plans are a useful feature or Oracle to manage your resources - e.g. to insure that administrators will gain the needed resources such as CPU even when the database is under high load. However, if you do not make use of these, you probably don't want to read reports on this topic (i.e. what roles exist and what rules apply to them), so you may disable it by setting the value to zero (default). The initial resource group for each user is nevertheless displayed in the "User Information" block.
MK_TSQUOT Whether to display all TableSpace quotas (default: 1, display quotas).
MK_DBAPROF A user's profile limits database usage and instance resources. The report lists available profiles with their settings concerning resources and limits in this block. If you don't use profiles, or do not want to report on them, you may disable it here.
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_LOADPROF The LoadProfile shows some throughput information on a per second and per transaction base.
MK_TABS Generate basic table statistics
MK_FILES Whether to report on the data files of the instances
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_RBS Include Rollback Segment Statistics with the report. This is somehow useless if you use automatic undo management (available since Oracle release 9)
MK_MEMVAL This controls the "Memory Values" block which just lists all memory areas and their sizes (not the pool statistics - see next item)
MK_POOL The Pool Statistics list the available pool areas with their sizes, the free space available at the time the report is generated as well as some statistics like cache hit ratios.
MK_BUFFRAT The "Buffer Wait Ratios" block helps you to identify the type of the buffer busy waits (if any) that are listed in the "Selected Wait Events" block. You should activate this if you encounter many buffer busy waits and want to know what tuning actions are reasonable.
MK_SYSSTAT The "SYSSTAT Info" segment lists up some ratios calculated from values of v$sysstat (as the name suggests), such as the Chained-Fetch-Ratio and the percentage of disk sorts. Since this gives you a general idea of the systems performance, it is recommended to enable this segment.
MK_WTEVT The "Wait Events" segment created when this option is enabled displays selected wait events from v$system_event together with some descriptions and help. I consider this as one of the central segments and thus recommend to enable it.
MK_FLC The "FreeList Contention" block should help you to identify tables having problems with the PCTFREE and PCTUSED values. You may try to enable this block if the report indicates it may be useful. See the online help on this topic for more details.
MK_ENQ The "Enqueue Waits" block may be useful for closer investigation if other results indicate problems concerning enqueues, but may not be needed for the daily reports in most cases.
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_INVOBJ Database objects, such as Views, Stored Procedures, Packages etc.pp., can become invalid under certain conditions. While this should be no problem for views in most cases (they may recompile without problems if the underlying objects are fine), it can be a security issue for triggers. This segment lists up all invalid objects together with some additional details on them.
MK_TSCAN In the Miscellaneous segment of the report, we list up a summary for the table scans. You may suppress this by setting this variable to 0.
MK_NEXT Also in the Miscellaneous block, we display the Top N tables that have less than 10% free blocks left and will need a new extent shortly (if there are any). Disable this by setting this variable to 0.
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.
Go4Colors
ParameterKey WarningAlert
Instance Efficiency
Buffer NoWait*_IE_BUFFNW< 80< 60
Redo NoWait*_IE_REDONW< 90< 75
Buffer Hit*_IE_BUFFHIT< 50< 30
In-Memory Sort*_IE_IMSORT< 85< 70
Library Hit*_IE_LIBHIT< 80< 60
Soft Parse*_IE_SOFTPRS< 80< 60
Parse CPU to ELA*_IE_PRSC2E< 80< 60
Latch Hit*_IE_LAHIT< 85< 70
Resource Limits*_RLIM< 80< 90
Redo Wastage Percentage*_RWP< 20< 30
TOP_N_SQL
FTS in execution planAR_EP_FTSCost > 1.000
Seconds per Execution*_ET> 3600> 7200
Datafiles Statistic
Size Change in %/7d*_DF_CHNG> 50> 100
TableSpace & File IO Summary
Avg Blks/Rd
measured by
DB_FILE_MULTIBLOCK_READ_COUNT
*_TS_BLKRD> 80%> 100%
AvgRd (ms)*_TS_RD> 50> 80
Library Cache
PctMiss*_LC_MISS> 15> 40
Reloads/GetRequests*_LC_RLPRQ> 10> 30
Invalidations/GetRequests*_LC_INVPRQ> 10> 20
w3cOraRep © 2003-2005 by Itzchak Rehberg & IzzySoftIzzySoft