| Oracle® Collaboration Suite Administrator's Guide 10g Release 1 (10.1.1) for Windows or UNIX Part Number B14476-03 |
|
|
View PDF |
| Oracle® Collaboration Suite Administrator's Guide 10g Release 1 (10.1.1) for Windows or UNIX Part Number B14476-03 |
|
|
View PDF |
This chapter describes how to monitor daily performance and fine-tune the configuration parameters using Grid Control, Oracle Collaboration Suite Control, component administrator GUIs, and command-line tools.
This chapter includes the following sections:
This section discusses performance tuning information for the Oracle Collaboration Suite components. Topics include:
See Appendix A, "Disk Space and Memory" in the Oracle Calendar Administrator's Guide for disk space and memory requirements.
See Appendix B, "Adjusting Calendar Kernel Parameters" in the Oracle Calendar Administrator's Guide for performance tuning information for Oracle Calendar.
There are mainly three sets of configuration parameters that can be used to tune Oracle Discussions: OID connection parameters, database connection parameters, and Discussions in memory caches.
Oracle Discussions shares the same Oracle Internet Directory connection pool used by Oracle WebMail. The Oracle Internet Directory connection pool can be configured by editing the properties located in the file oc4.properties for the OC4J_OCSClient container. The file path is ORACLE_HOME/j2ee/OC4J_OCSClient/config/oc4j/properties. The following properties are available:
oracle.ocs.ldappool.initialsize
oracle.ocs.ldappool.minsize
oracle.ocs.ldappool.maxsize
oracle.ocs.ldappool.shrinkinterval
oracle.ocs.ldappool.timeoutinterval
oracle.ocs.ldappool.incrementsize
oracle.ocs.ldappool.reconnecttime
Please refer to "Oracle WebMail Parameter Recommendations" for details on how to configure the Oracle Internet Directory connection pool.
Oracle Discussions shares the same database connection pool used by Oracle WebMail. The database connection pool can be configured by editing the properties located in the file oc4.properties for the OC4J_OCSClient container. The file path is ORACLE_HOME/j2ee/OC4J_OCSClient/config/oc4j/properties. The following properties are available:
oracle.mail.sdk.esmail.connpool_min_limit
oracle.mail.sdk.esmail.connpool_max_limit
oracle.mail.sdk.esmail.connpool_increment
oracle.mail.sdk.esmail.cache_inactivity_timeout
oracle.mail.sdk.esmail.thread_wakeup_interval
Please refer to "Oracle WebMail Parameter Recommendations" for details on how to configure the database connection pool.
Oracle Discussions caches frequently accessed topics and messages in memory. Oracle Discussions caches can be monitored and configured using Oracle Collaboration Suite Control.
The Oracle Discussions component home page in Oracle Collaboration Suite Control displays the current cache settings and the current cache usage.
To configure cache sizes and behavior:
From the Oracle Collaboration Suite home page for the Applications tier hosting Oracle Discussions, select the J2EE Applications tab.
Select the Discussions link in the list of J2EE applications. The Application: discussions page loads.
Select the Server link in the Web Modules list. The Web Module: server page loads.
Select Environment from the Properties list in the Administration section of the page. The Environment page loads. From this page, you can configure cache sizes and behavior.
For optimal performance, Oracle suggests using the maximum cache size for category, forum, topic, and system, so the cache is never full. Cache sizes and behavior can be configured through the following parameters:
oracle.discussions.cache.containers.info.ttl: In an environment where Oracle Discussions is deployed in more than one OC4J instance, Oracle Discussions will periodically check the message store database for updates by other instances. This time to live parameter will determine how frequently (in milliseconds), for a given forum, Oracle Discussions will check the database for updates by other instances. The recommended values are:
For a single Oracle Discussions instance deployment, use -1 to disable checking, because no periodic check for updates is required.
For a multi- Oracle Discussions instance deployment, use 60000 (milliseconds), so Discussions will check of other instances for updates once every minute.
oracle.discussions.cache.containers.max_size: Maximum size in bytes for the cache's region dedicated to category information, such as display name and description.
oracle.discussions.cache.boards.max_size: Maximum size in bytes for the cache's region dedicated to forum information such as last post, number of topics and messages.
oracle.discussions.cache.threads.max_size: Maximum size in bytes for the cache's region dedicated to topic information such as topic subject, author, number of replies.
oracle.discussions.cache.messages.max_size: Maximum size in bytes for the cache's region dedicated to message information such as subject, author, and message text body. This cache can grow quite large.
oracle.discussions.cache.system.max_size: Maximum size in bytes for the cache's region dedicated to some system information, including popular topics and last posts.
oracle.discussions.cache.user_profile.max_size: Maximum size in bytes for the cache's region dedicated to some user profile information such as user's known-as name, first and last name, and location.
oracle.discussions.cache.user_profile.ttl: Time to Live in milliseconds for the cached user profile information. When the time expires, Oracle Discussions will reload user's profile information from Oracle Internet Directory. A typical value (in milliseconds) is 3600000 (1 hour).
oracle.discussions.cache.system.popular_threads.cache_loader.ttl: Frequency in milliseconds used to reload the list of popular topics from the database. This parameter is related to the refresh rate of the ES_POPULAR_THREADS table, in the ES_MAIL schema. The default refresh rate is 10 minutes.
Oracle Discussions uses the discussions J2EE application for its logging. The log files are located under ORACLE_HOME/j2ee/OC4J_OCSClient/application-deployments/discussions/<oc4j_island>/application.log. The logs are also accessible through Oracle Collaboration Suite Control as part of the logs of the OC4J_OCSClient instance.
To configure logging level:
From the Oracle Collaboration Suite home page for the Applications tier hosting Oracle Discussions, select the J2EE Applications tab.
Select the Discussions link in the list of J2EE applications. The Application: discussions page loads.
Select the Server link in the Web Modules list. The Web Module: server page loads.
Select Environment from the Properties list in the Administration section of the page. The Environment page loads. From this page, you can configure logging level.
Set the level by editing oracle.discussions.log.level. Valid values for the logging level are: fatal, error, assert, warn, info, debug.
This section provides an overview of Oracle Mail architecture, details of the major tunable components, and guidelines for scaling the system. It also includes a section describing typical symptoms of performance problems, how to identify the limiting component, and solutions for solving the problems.
See the Oracle Mail Administrator's Guide for information about monitoring Oracle Mail processes.
This section contains the following topics:
This section provides a brief overview of the Oracle Mail architecture and processes. This information is intended to help you understand the tuning recommendations included in this section.
Oracle Mail Processes
Table 10-1 describes the Oracle Mail processes.
Table 10-1 Oracle Mail Processes
| Process | Description |
|---|---|
|
|
The inbound SMTP process |
|
|
The outbound SMTP process |
|
|
The IMAP process |
|
|
The list server process |
|
|
An underlying technical base used by Web clients and other applications to access the message store. This is not a server. |
|
|
The housekeeping process that cleans up all transient tables within the Oracle Mail schema. |
Figure 10-1 is an overview of the Oracle Mail processes and their interactions with the database.
In Figure 10-1, the ESSMI process is acting in submit-only mode, allowing the outbound SMTP process ESSMO to perform message delivery. The Oracle directory server (part of Oracle Internet Directory), is not represented. The consumers of the Oracle Internet Directory server for user authentication purposes are the list server, the SMTP delivery module (ESSMO), and IMAP processes.
Mail Flow
The following steps describe an overview of the flow for a message entering the system:
An incoming message is inserted by ESSMI into the queue tables, ES_QUEUE and ES_RECIPIENT, and into the tables handling the content and metadata, ES_HEADER, ES_EXT_HEADER, ES_SHELL, and ES_BODY.
The ESSMO process performs an LDAP lookup to determine whether the message recipient(s) are local or not.
If the recipient(s) are local, ESSMO inserts a record into the pointer table, ES_INSTANCE, and updates the recipients' usage in the ES_USER and ES_FOLDER tables.
If the recipient(s) are not local, ESSMO forwards the mail message.
The following steps describe the flow for a user reading a message:
Using a Web, POP3, or IMAP client, a user requests a specific message to read.
The server collects the message body from the ES_BODY table and then finds the metadata (number of attachments, sender, and so on) of the body from the ES_SHELL, ES_HEADER, and ES_EXT_HEADER tables.
The server constructs the message in the format the protocol expects and sends it over the network for the user to view.
The costs of an Oracle Mail system can be broken into three tiers: very costly, moderately costly, and inexpensive. These cost values are relative to other operations on the system. The following descriptions are provided to help you understand how changes in user behavior or application design can impact the hardware tiers.
What is Very Costly
For the database: Opening a folder. The list of all the user's messages must be retrieved from the database. The cost increases linearly with the size of the folder. This cost is not as high when the table is reorganized as an index-oriented table.
For the Applications tiers: IMAP fetch new headers operation. Fetching a new list of headers from the client requires the ESIMAPDS process to parse each message shell, which is an expensive CPU operation. This operation occurs when a user opens their inbox and downloads all unseen headers. This operation for a first-time user will be for all messages in their folder. For subsequent open folder calls, only new messages are downloaded.
What is Moderately Costly
For the database:
Delivering a mail message. New records must be inserted into at least seven tables with the appropriate indexes maintained. Several of the queue tables are updated multiple times as the message moves through various states.
Reading a mail message. The large object (LOB) in ES_BODY must be fetched and the other meta-tables queried to construct the message.
The housekeeping process. The ESGC process marks and then sweeps the entire mail store for de-referenced bodies as well as clean up the queue and instance tables. This is not inexpensive, but the limiting factor can often be physical input/output constraints, as the objects that the housekeeping process is interested in are unlikely to be in the buffer cache.
Performing a get new mail or noop: call. This causes the server to check for new instances for a particular folder identifier.
What is Inexpensive
For the database:
Changing flags of mail messages. This occurs during operations such as marking a mail message read or marking it deleted.
Reading a mail message already in the buffer cache. The message is already in the buffer, so no additional retrieval is needed.
Delivering a message to multiple recipients. A large amount of the necessary mail database work has already been performed for delivering the message to a single recipient. Delivery to any additional recipients, especially in the case of a distribution list, is very inexpensive because only new ES_INSTANCE records are created.
For the Applications tiers:
SMTP accepting a mail message. Given the efficiency of the Net8 Listener and Oracle Mail SMTP code, little processing power is necessary from the ESSMI component to insert a mail message.
SMTP performing message delivery. The cost here is the Applications tier process, not the database. For example, a single E250 with two 300Mhz processors was able to easily handle delivery of 75,000 messages an hour.
Database connection limits have a large effect on system performance. Setting the limit too low can lead to unnecessary client failures. Setting it too high can lead to wasted memory on the Applications tier and less efficient consumption of the physical memory on the back end database due to the size of the shadow processes.
Use the oesmon utility to obtain metric data about the database connections. Use the following command to determine the database connection usage per IMAP instance. For example:
oesmon get rgmum4:um_system:imap.DUMP.DBconnections.dump
The database connection pool algorithm starts at the top of the pool for each request and moves down the list until an available connection is located. Because of this, the database connection usage is pyramidal in shape: The first connection handles a large percentage of the load, the next connection handles a smaller percentage, and so on. The slope of this shape indicates the rate at which the database is servicing requests: A steep slope indicates a quick response, while a flat slope indicates a slow response. When evaluating this data, look for the connection at which the execution count becomes zero. This indicates that the pool at this point is large enough to accommodate the demands made on it. Resize the maximum database connection pool to this value or greater.
The best way to determine the usage statistics for the LDAP connection pool is to set the minimum pool setting to a small number, increment the count by one, and then use the following command to determine how many LDAP connections were created:
netstat -a | grep ldap | grep ESTABLISHED | wc -l
Note that for some operating systems, you must specify the LDAP port instead of ldap in the command. The default LDAP port is 389.
Table 10-2 describes the parameters that affect the performance of Oracle Mail. These parameters can be accessed and edited through Oracle Enterprise Manager 10g. They are located on the server pages listed under Unified Messaging.
Note:
You should use the recommended values to start with. However, the actual values could vary for one deployment to another, depending on the number of users, and mail traffic/load of the deployment. You should start with the default values, monitor your system's performance for a few days, and then adjust the values according to your specific deployment.Table 10-2 Parameter Recommendations for Oracle Mail
| Parameter | Description | Recommended Value |
|---|---|---|
|
LDAP Maximum Connection Pool |
Determines the maximum number of LDAP connections. |
20 |
|
LDAP Minimum Connection Pool |
Determines the minimum number of LDAP connections. Specify the number of connections required to handle 60% of the peak load. |
5 |
|
LDAP Connection Pool Increment |
The size increment by which to increase the pool of LDAP connections. If a large number of requests arrive at the same time, the pool may not be able to grow quickly enough and some connections will be refused. |
1 |
|
LDAP Connection Retry Interval |
The time to sleep (in microseconds) between attempts to make an LDAP connection. If the value is too large, response time can suffer because the process will sleep for too long a time interval even if an LDAP connection is available. |
100000 |
|
LDAP Number of Retry Before Erroring |
Number of time to try making an LDAP connection before ending the query. If the pool is full, the process will sleep for the value specified for the LDAP Connection Retry Interval parameter. |
10 |
|
Get New Mail Interval |
The time to sleep (in seconds) before entering administrative statistics into the database. If the value is too low, the frequent writes to the database will slow system performance. |
600 (7200 for housekeeping processes) |
|
Protocol Server Maximum Threads |
The maximum number of threads available for client connection handling. |
50 |
|
Protocol Server Minimum Threads |
The minimum number of threads available for client connection handling. |
10 |
|
Protocol Server Increment Thread |
The number of threads to add to the client connection pool. |
1 |
|
Maximum Number of Clients |
The maximum number of concurrent connections. If the value is too large, IMAP will attempt to handle connections that the listener is rejecting. If the value is too small, an unnecessary number of IMAP processes will need to be configured. |
1000 |
|
Timeout Interval |
The length of time (in seconds) until the server disconnects idle sockets |
1800 |
|
Submit Only |
The delivery behavior of the SMTP inbound server. Setting this value to FALSE can cause long insert times on the inbound Oracle Mail server. |
TRUE |
Table 10-3 describes the database process parameters that you can change in order to affect the performance of Oracle Mail.
Note:
You should use the recommended values to start with. However, the actual values could vary for one deployment to another, depending on the number of users, and mail traffic/load of the deployment. You should start with the default values, monitor your system's performance for a few days, and then adjust the values according to your specific deployment.Use the sar or vmstat operating system utilities to determine CPU load by averaging the values over ten minute periods. No more than 90% of the CPU resources should be used. Depending on the platform, a Java Virtual Machine might not be able to schedule threads on other processors. Because of this, when running one Java Virtual Machine on a two CPU computer you might only see 50% CPU utilization. However, that Java Virtual Machine might be using 100% of one of the CPUs.
Set the parameter oracle.mail.sdk.esmail.timing to true to display timing data. Use this information to determine whether database or LDAP connection times are slow. If database connection response times are slow, as shown by the timing data and by IMAP client observation, database performance must be tuned.
If LDAP connection times are slow, use the Oracle Internet Directory Statistics Collection tool (oidstats.sh) to generate data required by the Oracle Optimizer to choose an optimal plan to execute the queries corresponding to the LDAP operations. If LDAP connection times are still slow, monitor the CPU usage of the machine and check the network round-trip times.
Set the Oracle WebMail parameter oracle.mail.sdk.esmail.dbpool_timing to true in order to generate a line marked Active in the Oracle WebMail log files. If you set the parameter to true for a single day and then search for the Active lines in the log files, you can determine the number of daily active database connections. By correlating this information with the timestamp, you can determine the peak number of database connections used. Use this information to determine whether the database connection pool is set correctly.
Table 10-4 describes the Oracle WebMail process parameters that you can change in order to affect the performance of your system. These values are located in the file oc4j.properties for the OC4J_UM container.
Note:
You should use the recommended values to start with. However, the actual values could vary for one deployment to another, depending on the number of users, and mail traffic/load of the deployment. You should start with the default values, monitor your system's performance for a few days, and then adjust the values according to your specific deployment.Table 10-4 Parameter Recommendations for Oracle WebMail
| Parameter | Description | Recommended Value |
|---|---|---|
|
|
If |
FALSE |
|
|
Number of connections to add to the |
5 |
|
|
Initial number of connections in the |
30 |
|
|
Maximum number of connections in the |
60 |
|
|
Minimum number of connections in the |
30 |
|
|
Time delay before |
1800 |
|
|
Maximum number of seconds the |
30 |
|
|
Enables or disables debugging JDBC connections |
FALSE |
|
|
Enables or disables debugging OJMA API for Oracle Mail. If enabled, this adversely effects performance. |
FALSE |
|
|
The amount of time in seconds the server waits to reconnect to the Oracle directory server if it is unavailable. |
4 |
|
|
Prints timing data. This is used to determine slow LDAP and database access times. |
FALSE Set to TRUE only when debugging performance issues. See Chapter10, "Monitoring JavaMail API Response Time for Oracle WebMail" for more information. |
|
|
Enables or disables debugging OJMA API for LDAP. |
FALSE |
|
|
Prints the active and total count of database connections. This is used to determine if the database pool is sized correctly. |
FALSE Set to TRUE when sizing the database connection pool. See "Determining the Optimum Database Pool Size for Oracle WebMail" for more information. |
|
|
Number of seconds to wait for a connection before the |
600 |
|
|
Maximum number of connections in the Oracle |
20 |
|
|
Determines the initial or minimum number of connections created in the connection pool. Oracle recommends keeping this limit as low as possible to avoid holding on to unused database connections. |
5 |
|
|
Determines the cache scheme of the database connection pool. A value of 3 sets the parameter to |
3 |
Oracle Content Services Administrator's Guide contains performance tuning information for Oracle Content Services. See the following chapters:
See Appendix A, "Tuning the Web Crawling Process" and Appendix B, "Tuning Query Performance" in the Oracle Ultra Search Administrator's Guide for performance tuning information.
See Chapter 9, "Tuning Performance in OracleAS Portal" in Oracle Application Server Portal Configuration Guide for performance tuning information.
You can monitor Oracle Real-Time Collaboration processes using the rtcctl utility, and by evaluating the Current Conferences reports in the Reports tab of the Oracle Real-Time Collaboration Web Client pages. You can also review Quality of Service reports from the same tab, to evaluate system efficiency and responsiveness. See Chapter 6, "Historical and Statistical Reports for Oracle Real-Time Collaboration" in Oracle Real-Time Collaboration Administrator's Guide for details about evaluating system performance.
If you determine that your system is not responding adequately, you may choose to increase the number of processes listening for client requests for Web conferencing and instant messaging services. You can also increase the number of conferencing server processes. The Multiplexer is the process that listens for Web conferencing client requests to the Oracle Web Conferencing Server, and the Client Connection Manager is the process that listens for instant messaging client requests to the Oracle Presence Server. By default, one multiplexer, one connection manager, one presence server and four conference server processes are started when Oracle Real-Time Collaboration is installed and configured on an Applications tier.
Note:
You cannot increase the number of Oracle Presence Server processes. There can be only one presence server process running at a time for an entire Oracle Real-Time Collaboration system. If the server process goes down, a special high availability process for the presence server will attempt to restart it automatically. If the presence server instance is down, a waiting high availability process for another server process on another Oracle Real-Time Collaboration instance starts that presence server. See "Automatic Process Monitoring" in Chapter 5 of Oracle Real-Time Collaboration Administrator's Guide for details.You use the rtcctl utility to set the number of processes as follows.
$ORACLE_HOME/imeeting/bin/rtcctl rtcctl> setProperty -ct confsvr -pname SrvNumProcs -pvalue 8 rtcctl> setProperty -ct mx -pname SrvNumProcs -pvalue 2 rtcctl> setProperty -ct conngr -pname SrvNumProcs -pvalue 2 rtcctl> exit
The example sets eight conference server processes (confsvr), two multiplexer (mx) processes, and two connection manager (connmgr) processes.
See "Increasing Component Processes and Connections" in Chapter 3 of Oracle Real-Time Collaboration Administrator's Guide for more details about these properties.
In addition to increasing the available number of Client Connection Manager properties as described in the previous section, you can also increase the maximum number of connections each connection manager process can handle. Use the rtcctl utility to set the number of connections as follows:
$ORACLE_HOME/imeeting/bin/rtcctl rtcctl> setProperty -system true -pname IMMaxConnections -pvalue 2000
The example sets the maximum number of concurrent connections to 2,000. See "Increasing Component Processes and Connections" in Chapter 3 of Oracle Real-Time Collaboration Administrator's Guide for more details about these properties.
If you change this property, you should also make sure that the maximum number of open file descriptors allowed on your system matches the number you enter here. See "Increasing the Number of Open File Descriptors Used for Client Connections" for details.
The number of connections the Oracle Real-Time Collaboration Multiplexer and Client Connection Manager processes can handle on a UNIX, Linux, or Solaris system is limited by the number of open file descriptors allowed on the system. You can set this limit using the ulimit command (sh, ksh, bash), the limit command (csh, tcsh), or by changing entries in a configuration file for your operating system, such as /etc/security/limits.conf or /etc/sysctl.conf.
If the number of file descriptors is limited to 1024, a single multiplexer or connection manager process can handle about 1,000 users. If users are consistently unable to connect to multiplexer or client connection manager processes, you may need to increase the number of open file descriptors allowed.
To reset the limit, you must also stop and restart Oracle Real-Time Collaboration processes. Inform your users that you will be stopping Web conferencing and messaging processes before following the next steps.
Stop the Oracle Real-Time Collaboration OC4J_imeeting and core component processes:
$ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=OC4J process-type=OC4J_imeeting $ORACLE_HOME/imeeting/bin/rtcctl rtcctl> stop
Edit the appropriate configuration file for your operating system (such as /etc/security/limits.conf), or use ulimit or limit to set the number of open files or file descriptors a process may have. The method you choose to set this depends on your operating system, and whether you wish to control this at runtime with a command, or to set limits in default configuration files for particular users or groups of users.
For example, to set the limit for members of the group called "users" in your limits.conf file on UNIX to 2000 open file descriptors, you could enter the following:
vi /etc/security/limits.conf # Allow anyone in the 'users' group to open 2000 files. @users hard nofile 2000
Or to set the limit for all users at runtime, you could use ulimit on a standard Bourne shell as follows:
ulimit -n 2000
See your operating system documentation for more information about the best way to set open file descriptors.
Restart the Oracle Real-Time Collaboration OC4J_imeeting and core component processes:
$ORACLE_HOME/opmn/bin/opmnctl startproc ias-component=OC4J process-type=OC4J_imeeting $ORACLE_HOME/imeeting/bin/rtcctl rtcctl> start
See Chapter 13, "Performance Tuning," in Oracle Voicemail & Fax Administrator's Guide for information on tuning the Oracle Voicemail & Fax system.
See Chapter 13, "Server Performance Monitoring" in the Oracle Mobile Collaboration Administrator's Guide for performance monitoring information.
Oracle Workspaces provides system properties for controlling application sizing and performance.
Setting Oracle Workspaces Properties
You can set the sizing and performance properties using Oracle Collaboration Suite Control. These properties are all stored in the web.xml file.
To set Oracle Workspaces environment properties:
From the Applications tier home page, select the OC4J_OCSClient instance. The OC4J_OCSClient home page is displayed.
Click the Oracle Workspaces link in the Deployed Applications table. The Application: Workspaces page is displayed.
Click the workspaces link in the Web Modules table. The Web Module: workspaces page is displayed.
Click the Environment link under the Properties list in the Administration section. The Environment page is displayed.
Make your changes to the Oracle Workspaces environment properties. Under the Environment Entries heading, you can view the various properties and override the default values.
When you are finished making changes, click Apply. Your environment properties changes will be applied. If you want to reject all changes you have made, click Revert.
The Oracle Workspaces properties are described in the following sections:
Oracle Workspaces provides the following system properties to control application sizing.
The following properties determine the initial, minimum, and maximum size of the database connection pool that Oracle Workspaces uses to access and manage workspace metadata. If Oracle Workspaces operations are sluggish, you can increase the values for each of these properties and test the result:
oracle.workspaces.db.connPoolInitialLimit
The default value is 5
oracle.workspaces.db.connPoolMinLimit
The default value is 5
oracle.workspaces.db.connPoolMaxLimit
The default value is -1, indicating unlimited size
The following properties determine the initial and maximum sizes of the pool of LDAP connections. These connections are used to retrieve user information from Oracle Internet Directory. If user lookup operations are sluggish, you can increase the values for each of these properties and test the result:
oracle.ocs.ldappool.initialsize
The default value is 5
oracle.ocs.ldappool.maxsize
The default value is 25
The following property determines the size of the connection pool used to access the Discussions service with administrator privileges. If operations are sluggish, you can increase the value of this property and test the result:
oracle.workspaces.discussions.adminConnPoolSize
The default value is 5
The following property determines the size of the connection pool used to access the Discussions service with administrator privileges on behalf of the Inbox service that is built on top of the Discussions service. If operations such as creating and deleting an Inbox service are sluggish, you can increase the value of this property and test the result:
oracle.workspaces.email.adminConnPoolSize
The default value is 5
Oracle Workspaces provides the following system properties to control application performance.
The following property determines the number of seconds to wait when all cached connections are in use before trying to obtain a connection again:
oracle.workspaces.db.connPoolWaitTimeout
The default value is 3
The following property determines the number of seconds of inactivity before a physical database connection will be closed:
oracle.workspaces.db.connPoolInactivityTimeout
The default value is 120
The following properties determine the minimum and maximum number of calendar connections to maintain in the connection pool. If calendar operations are sluggish, you can these values and test the result:
oracle.workspaces.calendar.connPoolMinUser
The default value is 10
oracle.workspaces.calendar.connPoolMaxUser
The default value is 100
The true/false value of the following property determines whether Oracle Workspaces should wait or fail when attempting to obtain a calendar connection from a pool that is completely in use:
oracle.workspaces.calendar.connPoolBlocking
The default value is true
The following properties determine the minimum and maximum number of connections that the native Calendar service maintains to the calendar master node. If calendar operations are sluggish and you have already tried increasing the connPoolMinUser and connPoolMaxUser parameters, you can increase these values and test the result:
oracle.workspaces.calendar.connPoolMinMasternode
The default value is 0
oracle.workspaces.calendar.connPoolMaxMasternode
The default value is 15
The following property specifies whether JDBC statement caching is turned on or not. By default it is true. Statement caching consumes memory, but improves performance.
oracle.workspaces.db.statementCachingEnabled
The default value is true
The following property specifies the amount of detail written out to the logfile:
oracle.workspaces.log.level
The default value is warn
The following log levels are available:
fatal: Only serious errors are written to the log.
error: All errors detected are written to the log.
assert: All errors and any assertion conditions which fail are written to the log.
warn: All errors, assertion failures, and warnings are written to the log.
info: All errors, assertion failures, and warnings as well as informational messages are written to the log.
debug: A lot of tracing information is written to the log to help debug the application. This setting can be very verbose.
You can gather statistical metrics for Oracle Workspaces and use it to tune performance.
To gather metrics and statistics:
Run the following scripts to collect performance information into two files:
ORACLE_HOME/bin/dmstool –table ocw_webui_request | egrep "Name:|processRequest.avg|maxActive|completed" > /tmp/<file1name> ORACLE_HOME/bin/dmstool -table –count 1 ocw_operation |egrep "Name:|processRequest.avg|maxActive|completed" > /tmp/<file2name> ORACLE_HOME/bin/dmstool -table -count 1 JVM
View the output from the scripts in file1name and file2name by opening them with a text editor. Samples of the output appear as follows:
From <file1name>:
numObjects.completed: 0 ops processRequest.avg: 1439.0 msecs processRequest.completed: 1 ops processRequest.maxActive: 1 threads Name: library.filesFolderList
From <file2name>:
numObjects.completed: 1 ops processRequest.avg: 295.0 msecs processRequest.completed: 1 ops processRequest.maxActive: 1 threads Name: files.listContents
The first place to look is in the file1name for the ocw_webui_request metrics. These are the responses from Web operations within the OC4J. Users will generally accept responses in the three to five second range. If the response takes longer than this, users will typically perceive that the response is slow. Get an overall average of the individual operations averages to get a feel for the full system. You can do this by using the following command:
cat <filexname> | grep processRequest.avg | grep -v 0.0 |awk '{count+=1;sum+=$2; print sum/count}'
If the average performance is poor, there are two possibilities; it could be that the whole system is slow, or it could be an individual component causing delays. Oracle Workspaces is integrated with other Oracle Collaboration Suite components; using the information in these files, it should be possible to isolate the problem component. Once this is done use the second file to see if which operations against the backend are slow (server side responses should take 0.5 seconds or less). Then attempt to improve these areas specifically.
The output from the dmstool -table -count JVM command should appear similar to the following example:
activeThreadGroups.maxValue: 3.0 groups activeThreadGroups.minValue: 1.0 groups activeThreadGroups.value: 3 groups activeThreads.maxValue: 49.0 threads activeThreads.minValue: 7.0 threads activeThreads.value: 49 threads cpuTime.value: not implemented msecs freeMemory.maxValue: 17502.0 kbytes freeMemory.minValue: 1314.0 kbytes freeMemory.value: 17502 kbytes startTime.value: 1117111720253 msecs timeStamp.ts: 1117116037938 milliseconds totalMemory.maxValue: 45380.0 kbytes totalMemory.minValue: 1984.0 kbytes totalMemory.value: 45380 kbytes upTime.value: 4317683 msecs Host: etclbs09.uk.oracle.com Name: JVM Parent: / Process: OC4J_OCSClient:OC4J:12504:6004 iasInstance: ocsapps.etclbs09.uk.oracle.com uid: 1153237058
The main item of interest here is the JVM totalmemory.maxvalue coupled with the freeMemory.value. If the totalmemory.maxvalue is equal to the max value defined in opmn.xml then at some point during operation this maximum has been reached. If, during peak periods, totalmemory.value is close to the maximum and the freeMemory.value is very low, this indicates that the JVM heap memory may need to be increased, dependent on the physical memory available on the host.
Monitoring the health of a database and ensuring that it performs optimally is an important task for a database administrator. This section discusses the features and functionality included in Oracle Database that make it easy to proactively monitor database health, identify performance problems, and implement any corrective actions.
The following topics are covered:
The Oracle Database makes it easy to proactively monitor the health and performance of your database. It monitors the vital signs (or metrics) related to database health, analyzes the workload running against the database, and automatically identifies any issues that need your attention as an administrator. The identified issues are either presented as alerts in Enterprise Manager or, if you prefer, can be sent to you through e-mail.
This section discusses the following topics:
Alerts help you monitor your database proactively. Most alerts are notifications when particular metrics thresholds are crossed. For each alert, you can set critical and warning threshold values. These threshold values are meant to be boundary values that when crossed indicate that the system is in an undesirable state. For example, when a tablespace becomes 97 percent full this can be considered undesirable and have Oracle generate a critical alert.
Other alerts correspond to database events such as Snapshot Too Old or Resumable Session suspended. These types of alerts indicate that the event has occurred.
In addition to notification, you can set alerts to perform some action such as running a script. For instance, scripts that shrink tablespace objects can be useful for a Tablespace Usage warning alert.
By default, Oracle enables the following alerts: Table Space Usage (warning at 85 percent full, critical at 97 percent full), Snapshot Too Old, Recovery Area Low on Free Space, and Resumable Session Suspended. You can modify these alerts or enable others by setting their metrics.
For more information, see "Managing Alerts".
Oracle Database includes a self-diagnostic engine called the Automatic Database Diagnostic Monitor (ADDM). ADDM makes it possible for the Oracle Database to diagnose its own performance and determine how any identified problems can be resolved.To facilitate automatic performance diagnosis using ADDM, Oracle Database periodically (once an hour by default) collects information about the database state and workload it is running. This information is gathered in the form of snapshots, which provide a statistical summary of the state of the system at any given point in time. These snapshots are stored in the Automatic Workload Repository, residing in the SYSAUX tablespace. The snapshots are stored in this repository for a set time (a week by default) before they are purged in order to make room for new snapshots.ADDM examines data captured in AWR and performs analysis to determine the major problems on the system on a proactive basis and in many cases recommends solutions and quantifies expected benefits.
Generally, the performance problems ADDM can flag include the following:
Resource bottlenecks, such as when your database is using large amounts of CPU time or memory, for example as a result of high load SQL statements
Poor connection management, such as when your application is making too many log on attempts to the database
Lock contention in a multiuser environment, when a lock to update data causes other sessions to wait, slowing down the database
For more information about using ADDM, see "Diagnosing Performance Problems".
The Enterprise Manager home page enables you to monitor the health of your database. It provides a central place for general database state information and is updated periodically. This page reports information that is helpful for monitoring database state and workload.
The General section provides a quick view of the database, such as whether the database is Up or Down, the time the database was last started, instance name, host name, and the time of the most recent entry in the alert log.
The Host CPU section shows the percentage of CPU time used in the overall system. This chart breaks down CPU percentage into time used by the database and time used by other processes. If your database is taking up most of the CPU time, you can explore the cause further by looking at the Active Sessions summary. This summary tells you what the database processes are doing, such as which ones are using CPU, or waiting on I/O. You can drill down for more information by clicking a link, such as CPU.
If other processes are taking up most of your CPU time, this indicates that some other application running on the database machine may be the cause of the performance problems. To investigate this further, click the Host link under the General section. This link takes you to machine overview page where you can see some general information about the machine such as what operating system it is running, how long the machine has been up, and any potential problems. Clicking the Performance property page takes you to the machine performance summary page.
If you view the Performance Summary, you can see CPU utilization, memory utilization, and disk utilization over time. Below these graphs, you can also view the top ten processes in the CPU. The type of actions you can take to relieve this kind of load depends on your system, but can include eliminating unnecessary processes, adding memory, or adding CPUs.
On the Home page, the Diagnostic Summary summarizes the latest ADDM performance findings. This section also summarizes any critical or warning alerts listed in the Alerts section.
The Alerts table provides information about any alerts that have been issued along with the severity rating of each. An alert is a notification that a metric threshold has been crossed. For example, an alert can be triggered when a tablespace is running out of space.
When an alert is triggered, the name of the metric causing it is displayed in the Name column. The severity icon (Warning or Critical) is displayed, along with time of alert, alert value, and time the metric was last checked. You can click the message to learn more about the cause. For more information, see "Alerts".
The Performance Analysis section provides a quick summary of the latest ADDM findings, highlighting the issues that are causing the most significant performance impact. This analysis can identify problems such as SQL statements that are consuming significant database time. For more information, see "Performance Self-Diagnostics: Automatic Database Diagnostics Monitor".
The following sections describe how to manage alerts.
Metrics are a set of statistics for certain system attributes as defined by Oracle. They are computed and stored by the Automatic Workload Repository, and are displayed on the All Metrics page, which is viewable by clicking All Metrics under Related Links heading on the Database Home page (and some other pages). Figure 10-2, "All Metrics Page" shows a portion of the All Metrics page, displaying some of the metrics that AWR computes. When you click a specific metric link, a detail page appears, with more information about the metric. Online Help for this page gives you a description of the metric.
For each of these metrics, you are able to define warning and critical threshold values, and whenever the threshold is crossed, Oracle issues an alert.
Alerts are displayed on the Database Home page under the Alerts heading (or Related Alerts for non-database alerts such as a component of Oracle Net) as shown in Figure 10-3, "Alerts Section of Database Home Page".
Setting thresholds is discussed in "Setting Metric Thresholds". Actions you might take to respond to alerts are discussed in "Responding to Alerts".
Figure 10-3 Alerts Section of Database Home Page
When the condition that triggered the alert is resolved and the metric's value is no longer outside the boundary, Oracle clears the alert. Metrics are important for measuring the health of the database and serve as input for self-tuning and recommendations made by Oracle advisors.
Oracle provides a set of predefined metrics, some of which initially have thresholds defined for them. There may be times when you want to set thresholds for other metrics, or you want to alter existing threshold settings.
One means of setting a threshold was introduced in "Modifying a Tablespace", where you could set warning and critical thresholds on the amount of space consumed in a tablespace.
A more general means of setting thresholds is available using the Edit Thresholds page.
The following steps describe how to set metric thresholds:
From the Database Home page, click Manage Metrics under the Related Links heading.
The Manage Metrics page is displayed. It displays the existing thresholds for metrics and any response actions that have been specified. You cannot edit any thresholds on this page.
Click Edit Thresholds.
The Edit Thresholds page, shown in Figure 10-4, "Edit Thresholds Page", is displayed. On this page you can enter new Warning Threshold and Critical Threshold values, or you can modify existing values. In the Response Actions field, you can enter an operating system command or a script that you want executed when an alert is issued because a threshold has been crossed.
For example, to modify the warning threshold for Tablespace Space Used (%) metric, you can enter 87 as the percentage. Under Response Actions, you can optionally specify a fully qualified path to an operating system script that will clean up or increase the size of the tablespace.
Oracle provides a number of alerts that are not enabled by default. You can enable them by specifying threshold values. For example, to enable the alert for Cumulative Logons (for each second), enter 10 for warning and 25 for critical. This will cause the system to warn you when the number of logons per second exceeds 10.
You must click OK to save your changes.
For more comprehensive management of threshold settings for the different alerts, click the radio button in the Select column for that metric, then click Specify Multiple Thresholds.
The Specify Multiple Thresholds: metric_name page is displayed that enables you to add or delete specific metric threshold and response action settings.
For example, to set the Tablespace Space Used (%) metric thresholds for individual tablespaces, select this metric then click Specify Multiple Thresholds. Enter the tablespace name and its warning and critical values. Click Add Another Row to specify additional thresholds. Click OK to save your changes.
You can optionally direct Enterprise Manager to provide notification when events that require your intervention arise. For example, if you specify that you want e-mail notification for critical alerts, and you have a critical threshold set for the system response time for each call metric, then you could be sent an e-mail containing a message similar to the following:
Host Name=mydb.us.mycompany.com Metric=Response Time per Call Timestamp=08-NOV-2003 10:10:01 (GMT -7:00) Severity=Critical Message=Response time per call has exceeded the threshold. See the lattest ADDM analysis. Rule Name= Rule Owner=SYSMAN
The host name is a link to the Database Home page and in the message there is a link to the latest ADDM analysis.
By default, alerts in critical state such as DB Down, Generic Alert Log Error Status, and Tablespace Used are set up for notification. However, to receive these notifications, you must set up your e-mail information.You can do so as follows:
From any Database Control page, click the Setup link, which is visible in the header and footer area.
On the Setup page, select Notification Methods.
Enter the required information into the Mail Server portion of the Notifications Methods page. See the online help for assistance.
There are other methods of notification, including scripts and SNMP (Simplified Network Management Protocol) traps. The latter can be used to communicate with third-party applications.
Note:
So far, you have set up a method of notification, but you have not set up an e-mail address to receive the notification. To do so, complete the following steps.From any Database Control page, click the Preferences link, which is visible in the header and footer area.
On the Preferences page, select General. Enter your e-mail address in the E-mail Addresses section.
You can optionally edit notification rules, such as to change the severity state for receiving notification. To do so, select Notification Rules. The Notification Rules page appears. For more information about configuring notification rules, see Oracle Enterprise Manager Advanced Configuration.
When you receive an alert, follow any recommendations it provides, or consider running ADDM or another advisor, as appropriate to get more detailed diagnostics of system or object behavior.
For example, if you receive a Tablespace Space Usage alert, you might take a corrective measure by running the Segment Advisor on the tablespace to identify possible objects for shrinking. You can then shrink the objects to free space. See "Reclaiming Wasted Space".
Additionally, as a response, you can set a corrective script to run as described in "Setting Metric Thresholds".
Most alerts, such as the Out of Space alert, are cleared automatically when the cause of the problem disappears. However, other alerts such as Generic Alert Log Error are sent to you for notification and need to be acknowledged by you, the system administrator.
After taking the necessary corrective measures, you can acknowledge an alert by clearing or purging it. Clearing an alert sends the alert to the Alert History, which is viewable from the home page under Related Links. Purging an alert removes it from the Alert History.
To clear an alert such as Generic Alert Log Error, from the Home page under Alerts, click the Alert link.The Alert Log Errors page appears. Select the alert to clear and click Clear. To purge an alert, select it and click Purge. You can also Clear Every Open Alert or Purge Every Alert using these buttons.
At times database performance problems arise that require your diagnosis and correction. Sometimes problems are brought to your attention by users who complain about slow performance. Other times you might notice performance spikes in the Host CPU chart on the home page.
In all cases, these problems are flagged by the Automatic Database Diagnostics Monitor (ADDM), which does a top-down system analysis every hour by default and reports its findings on the Database Home page.
ADDM runs automatically every hour to coincide with the snapshots taken by AWR. Its output consists of a description of each problem it has identified, and a recommended action.
Findings are displayed in two places on the home page:
Under the Performance Analysis section on the Database Home page, as shown in Figure 10-5, "Performance Analysis".
You can drill down by clicking the finding. The Findings Details page appears describing the findings and recommended actions.
Below the Diagnostic Summary heading next to Performance Findings shows the number of findings if any. Clicking this link takes you to the ADDM page.
To respond to a performance finding, click the finding and follow the recommended actions, if any. A recommendation can include running an advisor.
For example, Figure 10-5, "Performance Analysis" shows a performance finding of SQL statements consuming significant database time were found, with an impact of 22.85 percent and recommended summary of SQL Tuning.
Clicking the finding link takes you to the Performance Finding Details page. Here the recommended action is to run the SQL Advisor, which you can do by clicking Run Advisor Now. The advisor runs and gives a recommendation in the form of precise actions for tuning the SQL statements for better performance.
ADDM behavior and analysis is based on the Automatic Workload Repository (AWR), which collects system performance statistics and stores the data in the database. After default installation, the AWR captures data every hour and purges data over seven days old. You can modify both the snapshot frequency and the data retention period as desired.
You can view and alter these settings on the Workload Repository page:
The retention period for snapshots. This is initially set to 7 days.
The interval for snapshots. The default and recommended value is one hour.
To navigate to this page, from the Database Administration page, under Workload, select Automatic Workload Repository.
To change either of these settings, click Edit on the Workload Repository page. The Edit Settings page appears. Enter a new Snapshot retention period or new System Snapshot Interval. Click OK.
By default Oracle runs ADDM every hour. Performance findings from the last snapshot are listed on the Oracle Enterprise Manager Home page. This is described in "Performance Self-Diagnostics: Automatic Database Diagnostics Monitor".
You can also invoke ADDM manually. Reasons for doing so include running it as a recommended action associated with an alert, running it in the middle of a snapshot period, or running it across multiple snapshots.
|
Note: If you need more frequent ADDM reporting, you can also modify the default snapshot interval. To do so, see "Modifying Default ADDM Behavior". |
From the Home page, under Related links you can navigate to the ADDM page by clicking Advisor Central, then ADDM. The Create ADDM Task page appears.
Figure 10-6, "Create ADDM Task" is a screen shot of Create ADDM Task page.
Increased session activity shows up as peaks in the graph. To analyze a period across multiple snapshots, select a start time and click OK, then choose an end time and click OK. The ADDM Task page appears detailing any findings.
Advisors are powerful tools for database management. They provide specific advice on how to address key database management challenges, covering a wide range of areas including space, performance, and undo management. In general, advisors produce more comprehensive recommendations than alerts. This is because alert generation is intended to be low cost and have minimal impact on performance, whereas advisors are user-invoked, consume more resources, and perform more detailed analysis. This, along with the what-if capability of some advisors, provides vital information for tuning that cannot be procured from any other source.
This chapter deals primarily with the advisors that can improve performance. These advisors include the SQL Tuning, SQL Access, and Memory Advisors. Table 10-5, "Performance Advisors" describes these advisors.
Caution:
You should not use the SQL Tuning Advisor or the SQL Access Advisor on the Oracle Collaboration Suite Database. The SQL statements used for Oracle Collaboration Suite are already optimized, and the kinds of changes which the SQL Advisors might advise could cause Oracle Collaboration Suite components to stop functioning correctly.Other advisors such as the Undo and Segment Advisors are listed in Table 10-6, "Other Advisors".
For example, the shared pool memory advisor graphically displays the impact on performance of changing the size of this component of the SGA.
Table 10-5, "Performance Advisors" describes the performance advisors that Oracle provides. These advisors are described in this chapter.
Table 10-5 Performance Advisors
| Advisor | Description |
|---|---|
|
ADDM makes it possible for the Oracle Database to diagnose its own performance and determine how any identified problems can be resolved.See "Performance Self-Diagnostics: Automatic Database Diagnostics Monitor" and "Diagnosing Performance Problems". |
|
|
This advisor analyzes SQL statements and makes recommendations for improving performance. You should not use the SQL Tuning Advisor on the Oracle Collaboration Suite Database. |
|
|
This advisor is intended to help tune schema to a given SQL workload. You should not use the SQL Access Advisor on the Oracle Collaboration Suite Database. |
|
|
The Memory Advisor is the main advisor for system memory and is responsible for optimizing memory on the instance as a whole. You have the option of having Oracle auto-tune memory. If you choose not to have Oracle auto-tune memory, then you can invoke the SGA Advisors or the PGA Advisor to obtain optimal settings for the components and total size of the SGA or PGA. See "Using the Memory Advisor". |
Table 10-6, "Other Advisors" describes other advisors Oracle provides. These are described elsewhere in this book.
Table 10-6 Other Advisors
| Advisor | Description |
|---|---|
|
The Segment Advisor provides advice on whether an object is a good candidate for a shrink operation based on the level of space fragmentation within that object. The advisor also reports on the historical growth trend of segments. You can use this information for capacity planning and for arriving at an informed decision about which segments to shrink. See "Reclaiming Wasted Space". |
|
|
The Undo Advisor helps in identifying problems in the undo tablespace and assists in correctly sizing the undo tablespace. The Undo Advisor can also be used to set the low threshold value of the undo retention period for any flashback requirements. See "Using the Undo Advisor". |
You can invoke an advisor from the Advisor Central home page, accessible through a link on the Database Home page. You can invoke advisors in other ways, often through recommendations from ADDM or alerts.
You should not use the SQL Tuning Advisor on the Oracle Collaboration Suite Database. The SQL statements used for Oracle Collaboration Suite are already optimized, and the kinds of changes which the SQL Tuning Advisor might advise could cause Oracle Collaboration Suite components to stop functioning correctly.
You should not use the SQL Access Advisor on the Oracle Collaboration Suite Database. The SQL statements used for Oracle Collaboration Suite are already optimized, and the kinds of changes which the SQL Access Advisor might advise could cause Oracle Collaboration Suite components to stop functioning correctly.
The Memory Advisor helps you tune the size of your memory structures. You can use this advisor only when automatic memory tuning is disabled.
The memory advisor comprises three advisors that give you recommendations on the following memory structures:
Shared pool in SGA
Buffer cache in SGA
PGA
To invoke the Memory Advisors, click Memory Advisor on the Advisor Central page. The Memory Parameters: SGA page appears. This page gives breakdown of memory usage for the system global area (SGA). This memory area is a group of shared memory structures that contain data and control information for a single Oracle instance. The shared pool and buffer cache are part of this area. For more information on these structures, click Help.
The Automatic Shared Memory Management setting should be disabled in order to run the advisor. To run either the shared pool advisor or the buffer cache advisor, click Advice next to the field.
For example, to run the advisor on the buffer cache, click Advice next to the buffer cache field. The Buffer Cache Size Advice graph appears. Refer to Figure 10-7, "Buffered Cache Size Advice".
Relative change in physical reads is plotted against cache size. A lower number for physical reads is better for performance. In this example, the graph tells us that a cache size larger than 284M will not improve performance by much. Thus 284M is the recommended optimal buffered cache size.
To run the PGA advisor, click the PGA property page. Running this advisor is similar to running the SGA advisors. Cache hit percentage is plotted against memory size. Higher hit ratios indicate better cache performance. The optimal zone for cache hit percentage is between 75 and 100 percent.
