6
Charting and Monitoring

This chapter describes how to collect and view Oracle Email system statistics using the oesmon monitoring utility and the oeschart charting utility.

This chapter contains the following topics:

• Using OESMON
• Using OESCHART

Using OESMON

The oesmon utility obtains raw metric data directly from Oracle Email server processes and provides a summary of the mail system's statistics. Each statistic is represented by either an ASCII string or a number, rendered in keyword-value pairs.

oesmon provides the syntax options listed in Table 6-1.

Table 6-1 oesmon Syntax Options

Command	Description
oesmon	Returns the usage message
oesmon targets	Lists all possible targets for Oracle Email servers
oesmon names target	Lists all of the metric names for a particular target
oesmon get target metric_name	Returns the statistics

A target is a concatenation of:

host:um_system:service_type

where:

• host is the name of the host where the service is running
• service type of the following:
  • gc: housekeeping process
  • imap: IMAP4 server
  • list: list server
  • pop: POP3 server
  • smtp_in: SMTP process for inbound mail routing
  • smtp_out: SMTP process for outbound mail routing
  • nntp_in: NNTP inbound news server
  • nntp_out: NNTP outbound news server
  • vs: virus scrubber

All statistics and managed objects have names. Names are case sensitive and contain only alphanumeric characters and the underscore character. Statistic names are hierarchical and separated by a period (.). A request for a managed object returns all of the managed objects and statistics beneath it.

See Also: Appendix E, "Server Statistics" for a complete list of the available statistics

Consider the following example:

oesmon  get  mycomputer:um_system:pop .um.admin

In this case, two values are returned for the two metric objects in the hierarchy:

• process identifier
• date and time the service was started

  
  .um.admin.os_pid = 8239
  .um.admin.uptime = Wed Jan 29 14:17:36 2003
  .um.admin.log.discard
  .um.admin.log.total
  
  

where:

• .um.admin.log.discard is the number of log messages discarded when high log levels cause log messages to be generated faster than they can be written to disk.
• .um.admin.log.total is the total number of log messages logged by the processes, including the ones that are discarded and written to disk.

  oesmon  get  mycomputer:um_system:pop  .um.admin.os_pid
  
  

Only the process identifier is returned.

.um.admin.os_pid = 8239

Using OESCHART

Oracle Email servers track a range of metrics that are periodically stored in a set of mail statistics tables. The oeschart utility generates charts and images that can be used to publish reports and Web pages; providing a company with a graphic picture of the status of the mail system implementation.

This section contains the following topics:

• Setting the Statistics Collection Interval
• Cleaning Up Mail Statistics
• Mail Statistics Schema
• Creating Graphs

Setting the Statistics Collection Interval

You can set the frequency by which metrics are sampled and recorded using the oidadmin administration tool provided by Oracle Internet Directory.

Note: The collection interval parameters are not configurable in Oracle Enterprise Manager.

The orclMailAdminCollectionInterval parameter specifies the number of seconds that elapse between statistics collecting. A setting of zero (0) seconds stops the service from logging statistics altogether.

You should set the orclMailAdminCollectionInterval parameter at the process level, rather than the instance level. If you set the value at the target instance level, statistics collection occurs at different intervals for each instance of the same type of mail service.

Collecting statistics at the same interval for all server types is not recommended. The collection process utilizes different amounts of resources and collects statistics of varying degrees, depending on which mail service is engaged in collecting the statistics. For example, the housekeeping collection process is more resource intensive and collects fewer dynamic statistics than the SMTP server collection process.

The recommended collection intervals for the different types of servers are:

• Housekeeper: 3600 (one hour)
• IMAP: 600 (ten minutes)
• POP: 600 (ten minutes)
• SMTP_IN: 600 (ten minutes)
• SMTP_OUT: 600 (ten minutes)
• List: 600 (ten minutes)
• NNTP_IN: 600 (ten minutes)
• NNTP_OUT: 600 (ten minutes)

Cleaning Up Mail Statistics

Mail statistics can be cleaned up through the housekeeping process, by enabling the Statistics Cleanup parameter and setting the Tertiary Storage Age Threshold parameter to the number of days you want to retain sample data.

Mail Statistics Schema

Each mail process has a default store database. Processes supporting multiple stores, such as the IMAP server, have a default store that is set in the orclMailAdminStoreDN parameter during the installation of each middle tier.

Statistical information is stored in tables in the esperftbl tablespace in the default mail store of the process. Because these tables can expand indefinitely, you should monitor the esperftbl tablespace and delete or export data as needed.

The schema consists of the following:

• es_perf_process Table
• es_perf_metric Table
• es_perf_timestamp Table
• es_perf_sample Table
• es_perf_data View

es_perf_process Table

The es_perf_process table is a list of the process instance records. The column process_dn is the complete distinguished name (DN) of the process, found in Oracle Internet Directory. The processes that produce metric data insert records into this table as needed.

Table 6-2 es_perf_process Table

Column Name	Valid Values	Description
process_id	Number (not null)	An internal assigned unique number for each process
process_dn	Varchar2 (500)	The DN of the process in Oracle Internet Directory

es_perf_metric Table

Table 6-3 es_perf_metric Table

Column Name	Value	Description
metric_id	Number (not null)	Internally assigned unique number for each metric
metric_name	Varchar2 (100)	Name of the metric
metric_type	Number (not null)	If 1, the metric is numeric If 2, the metric is a string, and defines which column is important in the es_perf_sample table

es_perf_timestamp Table

The es_perf_timestamp table records each time metric data is stored into the tables. This table, along with the es_perf_data table grows without bound over time. You must clean out historic data that is no longer needed for charting or analysis at their installation. This can be done by configuring the housekeeping process.

Table 6-4 es_perf_timestamp Table

Column Name	Value	Description
timestamp_id	Number	Internally assigned unique number for each time period that a process enters a statistics time stamp
date	Date	Time the data was inserted

es_perf_sample Table

The es_perf_sample table records each metric at each timestamp. This table increases over time, so you must delete data that is no longer required in order to keep the table size manageable.

There are two possible columns that store the sample data, depending upon whether the data is numerical (nvalue) or a string (svalue).

Table 6-5 es_perf_sample Table

Column Name	Value	Description
process_id	Number (not null)	Corresponds to the process_id row in es_perf_process
metric_id	Varchar2 (100)	Corresponds to the metric_id row in es_perf_metric
timestamp_id	Number (not null)	Corresponds to the timestamp_id row in es_perf_timestamp
nvalue	Number	Numeric value of the metric (if the metric is numeric)
svalue	Varchar2 (1000)	String value of the metric (if the metric is a string)

es_perf_data View

es_perf_data is a view of the tables described in this section: es_perf_process, es_perf_metric, es_perf_timestamp, and es_perf_sample.

Table 6-6 es_perf_data View

Column Name	Value	Description
process_dn	Varchar2 (500)	The DN of the process in Oracle Internet Directory
metric_name	Varchar2 2 (100)	Name of the metric
metric_type	Number (not null)	If 1, the metric is numeric If 2, the metric is a string, and defines which column is important in the es_perf_sample table
timestamp	Date	Time the value was sampled
nvalue	Number (not null)	Numeric value of the metric (if the metric is numeric)
Svalue	Varchar2 (1000)	String value of the metric (if the metric is a string)

The SQL script that defines this view is:

CREATE VIEW es_perf_data AS
SELECT process_dn, metric_name, metric_type, timestamp, nvalue, svalue
FROM es_perf_process p, es_perf_metric m, es_perf_timestamp t, es_perf_sample s

where:

s.process_id = p.process_id 
s.metric_id = m.metric_id ]
s.timestamp_id = t.timestamp_id;

Creating Graphs

oeschart creates graphs take a single parameter which points to a property file. The property file is a text file with keyword value pairs defining the information the utility needs to generate the graph. A valid property file would have the following mandatory and optional parameters.

The following tables describe mandatory entries and optional values:

Table 6-7 oeschart Mandatory Properties

Parameter	Description
server	Host name of the statistics database
port	Database listener port
sid	SID or service name for the server
username	Account user
password	Account password
process_dn	Query used to gather statistics, such as process_dn=%value_in_ini_file%, which retrieves all processes that follow this DN pattern. This lets you graph a specific process, a set of processes, or the entire system by specifying the level of detail
metric_name	Metric to query
graph_type	Type of graph, such as xy and bar
image_file_name	Name of the file being generated
image_title	Title to display on the graph
number_of_hours	Number of hours, starting from the present, to graph

Table 6-8 oeschart Optional Properties

Parameter	Default	Description
encode_type	gif	Possible values are gif and png
image_dir	./	Directory where graphs are stored
aggregate_time_period	600	Time span in which multiple logging processes are grouped together and the metrics combined to show an aggregate value. As an example, consider two running IMAP servers, IMAP1and IMAP2. IMAP1 logged its statistics at 3:00pm and IMAP2 logged its statistics at 3:02 pm. The servers log statistics at intervals specified in seconds using oidadmin relative to when they started; in this case, IMAP2 must have been started 2 minutes after IMAP1. To show the total number of sockets on the system, combine the values from IMAP1 and IMAP2. aggregate_time_period defines what is an acceptable window for different process statistics to be combined. This should be the same as the submit period specified in oidadmin for this process type.
max_lifetime	300	Number of seconds until the program terminates
show_statistics	FALSE	Number of data points, minimum, maximum, average, and median, at the bottom of the graph
debug	FALSE	Provides a detailed output of the utility

The following is an example of an IMAP property file:

server=testdb.us.oracle.com
sid=test 
port=1521
process_dn=test1:um_system:imap:
metric_name=.ES_SPS.socket.currload
graph_type=xy_current
image_file_name=socketcount
image_title=Socket count on test1 
image_dir=./
number_of_hours=24

encode_type=png
show_statistics=true
aggregate_time_period=600
debug=false
max_lifetime=120

oeschart obtains information from the es_perf schema and generates one of four possible types of charts. Three of these are variations of scatter graphs. The fourth is a bar chart.

By executing oeschart in regular intervals, you are provided with a current view that can be published on a company Web site or within Oracle Enterprise Manager.

For example, you can schedule the creation of graphs that show information such as the number of connected sockets, the log in response time, and the number of queued outbound messages, and publish the results in a custom HTML page or in Oracle Enterprise Manager.

Displaying Graphs in Oracle Enterprise Manager

To display graphs in Oracle Enterprise Manager:

1. Modify the target metadata definition

   The $ORACLE_HOME/emdw/sysman/admin/metadata directory contains a list of target metadata definition files.

   For a particular target definition, add the following elements to the InstanceProperties section:

         <!--This property specifies the total number of statistic charts to be 
   displayed -->
         <InstanceProperty NAME="totalNumberOfStats" CREDENTIAL="FALSE"
          OPTIONAL="TRUE">
            <Display>
               <Label NLSID="totalnumberofstats">Total Number of Statistics</Label>
            </Display>
         </InstanceProperty>
   
        <!--This property specifies the header title for the first charting picture 
   -->
         <InstanceProperty NAME="Title0" CREDENTIAL="FALSE"
          OPTIONAL="TRUE">
            <Display>
               <Label NLSID="stat0">Statistic Number 0</Label>
            </Display>
         </InstanceProperty>
   
        <!--This property specifies the tool tips string for the first charting 
   picture, coded to Section 508 standards-->
         <InstanceProperty NAME="ToolTips0" CREDENTIAL="FALSE"
          OPTIONAL="TRUE">
            <Display>
               <Label NLSID="tooltips0">This is tooltips 0 for ADA</Label>
            </Display>
         </InstanceProperty>
   
        <!--This property specifies the relative picture path under the servlet for 
   the first charting pictyre.-->
         <InstanceProperty NAME="PicPath0" CREDENTIAL="FALSE"
          OPTIONAL="TRUE">
            <Display>
               <Label NLSID="picpath0">Picture Path 0</Label>
            </Display>
         </InstanceProperty>
   
        <!--This property specifies the physical path for the first charting 
   picture. The admin code will test if the file exists according to the path 
   below-->
         <InstanceProperty NAME="PicPhysicalPath0" CREDENTIAL="FALSE"
          OPTIONAL="TRUE">
            <Display>
               <Label NLSID="picphysicalpath0">Picture Physical Path 0</Label>
            </Display>
         </InstanceProperty>
   
   

To increase the number of charts displayed, change the value of the totalNumberOfStats parameter in the targets.xml file accordingly, and the additional picture properties must be defined using the following naming standard:

Title[N], ToolTips[N], PicPath[N], PicPhysicalPath[N]

where N is a non-negative natural number.

2. Modify the targets.xml file to specify the property instance values.
3. Add the following properties to the specific target section in $ORACLE_HOME/emdw/sysman/emd/targets.xml:

     <Property NAME="totalNumberOfStats" VALUE="1"/>
     <Property NAME="ToolTips0" VALUE="My First Statistic Tool Tips"/>
     <Property NAME="PicPhysicalPath0" 
   VALUE="<...>/sysman/webapps/emd/ias/umsg/es/images/pic1.gif"/>
     <Property NAME="PicPath0" VALUE="/emd/ias/umsg/es/images/pic1.gif"/>
     <Property NAME="Title0" VALUE="My First Statistic Header"/>
   
   

If any of the following situations occur, the charting picture is skipped and not displayed in Oracle Enterprise Manager.

• totalNumberOfStats is missing, zero, or not a number
• Title[N] is missing for the particular chart
• ToolTips[N] is missing for the particular chart
• PicPath[N] is missing for the particular chart
• PicPhysicalPath[N] is missing for the particular chart
• The picture file specified under PicPhysicalPath[N] does not exist