test

Oracle Waveset 8.1.1 System Administrator's Guide

Tuning Your Repository Database

Waveset relies on a repository database to store and manage its identity and configuration data. For this reason, database performance can greatly influence Waveset’s performance.

Note –

Detailed information about performance tuning and managing databases is beyond the scope of this document because this information is dataset-specific and vendor-specific. In addition, customer database administrators (DBAs) should already be experts on their own databases.

This section characterizes the Waveset application and provides general information about the nature of Waveset data and its typical usage patterns to help you plan, tune, and manage your databases.

This information is organized into the following sections:

Understanding the Waveset Repository

You must understand the Waveset repository database scripts and how Waveset uses that content before you can effectively tune the Waveset repository.

This section provides an overview of the database and the information is organized into the following topics:

Repository Table Types

The Waveset repository contains three types of tables, and each table has slightly different usage characteristics.

XML Columns

Every object table contains an XML column, which is used to store each serialized object except the LOG table-set. Certain LOG table-set optional attributes are stored in the XML column if these attributes are present. For example, if digital signing is enabled.

Data Classes

You can roughly divide Waveset data into a number of classes that exhibit similar properties with respect to access patterns, cardinality, lifetime, volatility, and so forth. Each of the following classes corresponds to a set of tables in the repository:

Object IDs

Waveset generates globally unique identifiers (GUIDs) for objects by using the VMID class provided in the JDK software.

These GUID values exhibit a property that gets sorted by its string representations, based on the order in which the objects are created. For example, when you create new objects with Waveset, the newer objects have object IDs that are greater than the older objects. Consequently, when Waveset inserts new objects into the database, the index based on object IDs can encounter contention for the same block or blocks.

Prepared Statements

Generally, Waveset uses prepared statements for activities such as inserting and updating database rows, but does not use prepared statements for queries.

If you are using Oracle, this behavior can create issues with the library cache. In particular, the large number of statements versions can cause contention on the library cache latch.

To address this contention, change the Oracle CURSOR_SHARING parameter value from EXACT to SIMILAR. Changing this value causes Oracle to replace literals in SQL statements with bind variables, thereby reducing the number of versions.

Character Sets and Encodings

Because Waveset is a Java application that generally reads and writes character data rather than bytes, it does not restrict which encoding the database uses.

Waveset only requires that the data is sent and returned correctly. For example, the data does not become corrupted when written or reread. Use an encoding that supports multi-byte characters and is appropriate for the customer’s data. Generally, UTF-8 encoding is sufficient, but enterprises with a large number of true multi-byte characters, such as Asian or Arabic, might prefer UTF-16.

Most database administrators prefer to use an encoding that supports multi-byte characters because of the following:

Configuring the Waveset Repository

This section describes how to configure some commonly configured properties in the Waveset repository.

Caution – Caution –

Do not modify properties in the Waveset repository unless you are very familiar with repository databases and understand the consequences of making these changes.

Disabling Automatic Connection Pooling

If you are using a DataSource, set the connectionPoolDisable attribute to true in the RepositoryConfiguration object to disable automatic internal connection pooling in the Waveset repository.

For example, setting <RepositoryConfiguration connectionPoolDisable=’true’> allows you to avoid having two connection pools (one for Waveset and one for your application server).

Note –

You can see the current connectionPoolDisable setting in the ObjectRepository JMX MBean.

Using Inline Attributes

You can edit the RepostioryConfiguration object to enhance the performance of searches against specific, single-valued attributes. For example, you might edit this object to add an extended attribute, such as employeeID, that is used to search for Users or as a correlation key.

The default RepositoryConfiguration object looks like the following example:


<RepositoryConfiguration ... >
   <TypeDataStore Type="User" ... attr1="MemberObjectGroups", 
attr2="lastname" attr3="firstname" attr4="" attr5="">
   </TypeDataStore>
</RepositoryConfiguration>
Note –

The ellipses represent XML attributes that are not relevant here.

Each of the attr1, attr2, attr3, attr4, and attr5 XML attributes specifies a single-valued attribute to be copied into the waveset.userobj table. The waveset.userobj table can contain up to five inline attributes. The attribute value named by attr1 in RepositoryConfiguration will be copied into the “attr1” database column in this table.

Inline attributes are stored in the base object table for a Type (rather than as rows in the child attribute table).

Using inline attributes improves the performance of repository queries against those attributes. (Because inline attributes reside in the main “object” table, queries against inline attributes are faster than those against non-inline attributes, which are stored in the child “attribute” table. A query condition against a non-inline attribute requires a “join” to the attribute table.)

By default, Waveset uses the MemberObjectGroups, lastname, and firstname inline attributes.

Limiting Concurrent Connections

Every public repository method acquires a connection when the method begins and then releases that connection when the method exits. This connection is almost always a JDBC connection because the repository almost always accesses a DBMS. The method releases the connection whether it completes successfully or unsuccessfully.

You can configure the number of concurrent connections by modifying the maxConcurrentConnections value in the RepositoryConfiguration object.

If the repository reaches the configured limit, calls to the repository will wait until the number of connections being used by the repository falls back below the limit. The repository might appear to slow down, or even stop, if this is necessary to stay within the specified number of connections.

Note –

Any limit on the number of concurrent connections applies whether the repository pools connections or not. In other words, maxConcurrentConnections is unaffected by connectionPoolDisable.

    To configure the maxConcurrentConnections value, you must edit the RepositoryConfiguration object from the System Settings debug page (http://host:port/idm/debug/session.jsp),

  1. Select Configuration from the menu located next to the List Objects button.

  2. Click the List Objects button to view all of the Configuration-type objects.

  3. Locate RepositoryConfiguation and click Edit.

  4. When the Checkout Object: Configuration, #ID#REPOSITORYCONFIGURATION page displays (similar to the following example), locate maxConcurrentConnections and modify the existing value as needed.


    <?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Configuration PUBLIC 'waveset.dtd' 'waveset.dtd'>
<!-- MemberObjectGroups="#ID#Top" extensionClass="RepositoryConfiguration"
id="#ID#RepositoryConfiguration" name="RepositoryConfiguration"-->
<Configuration id='#ID#RepositoryConfiguration' name='RepositoryConfiguration'
lock='Configurator#1251229174812' creator='getRepositoryConfiguration(boolean)'
createDate='1249052436640' repoMod='1249048790203' type=
  'RepositoryConfiguration'>  <Extension>
    <RepositoryConfiguration instanceOf=
     'com.waveset.repository.RepositoryConfiguration'
lockTimeoutMillis='300000' maxConcurrentConnections='0' blockRowsGet='1000'
blockRowsList='10000' maxAttrValLength='255' maxLogAcctAttrChangesLength='4000'
maxLogMessageLength='255' maxLogXmlLength='2147483647' 
maxLogAcctAttrValueLength='128'maxLogParmValueLength='128' 
maxXmlLength='2147483647' maxSummaryLength='2048' optimizeReplaceAttributes=
'true' maxInList='1000' maxDelSet='1000' mcDBCall='10' mcDeleteAttrVal='3' 
mcInsertAttrVal='15' mcUpdateAttrVal='20' connectionPoolDisable='false'
changeScanInterval='5000' changePropagationDelay='3000' changeAgeOut='60000'>
      <RelocatedTypes>
        <TypeDataStore typeName='User' deleteDestroyInterval='1' 
attr1='MemberObjectGroups' attr2='lastname' attr3='firstname'>
        </TypeDataStore>
      </RelocatedTypes>
    </RepositoryConfiguration>
  </Extension>
<MemberObjectGroups>
  <ObjectRef type='ObjectGroup' id='#ID#Top' name='Top'/>
</MemberObjectGroups>
</Configuration>L

  5. Save your changes.

Note –

For more information about which object types are stored in each set of tables, see Data Classes.

Using preferPreparedStatement

JDBC supports execution of two types of SQL statements:

Generally, a PreparedStatement is more efficient for the DBMS because the DBMS server can cache and reuse a PreparedStatement for multiple executions, avoiding the cost of SQL parsing and optimizer selections. To get the benefit of this caching, Waveset associates a PreparedStatement with a JDBC connection. When Waveset is going to issue the same SQL statement multiple times using the same connection, it automatically uses a PreparedStatement.

However, most of the JDBC operations performed by Waveset do not share a connection between JDBC calls. Most JDBC operations are the result of the application server processing an HTTP request through a JSP, so that the processing thread acquires a JDBC connection when needed and releases that connection when it is done. Consequently, you must do some additional work to get the benefits of using PreparedStatements.

Many application servers provide DataSource implementations that provide both connection pooling and implicit statement caching. Implicit statement caching is the ability of the DataSource or connection pool to implicitly cache JDBC statements and associate them with the connection.

If you configure Waveset to use an application server DataSource, you must have both of these features to benefit from using Waveset's preferPreparedStatement repository option. If you are not using an application server DataSource, if the DataSource is not configured to use a connection pool, or if the connection pool does not support implicit statement caching, using preferPreparedStatement causes decreased performance because the DBMS cannot reuse the statement.

Waveset code is not structured to associate a statement with a connection, except in a few circumstances, so Waveset depends on the DataSource or connection pool to perform this caching. If you configure the Waveset repository to use a DataSource or connection pool that performs implicit statement caching, and you enable preferPreparedStatement=true in the RepositoryConfiguration configuration object, Waveset can use PreparedStatement for object lock/unlock operations and most get operations, which potentially results in performance improvements. The Waveset ObjectRepository has a JMX MBean that indicates the setting of the preferPreparedStatement switch and the number of Statement/PreparedStatement requests made by the Waveset server.

Note –

You can see the current preferPreparedStatement setting in the ObjectRepository JMX MBean.

General Guidelines for Tuning a Repository Database

This section describes some general guidelines for tuning a repository database:

Vendor-Specific for Tuning a Repository Database

This section describes some vendor-specific guidelines for tuning Oracle and SQL Server repository databases.

Note –

Currently, MySQL databases are only supported in development and for demonstrations.

Oracle Databases

This section describes guidelines for tuning Oracle repository databases:

Waveset does not require Oracle init.ora parameter settings for SGA sizing, buffer sizing, open cursors, processes, and so forth.

SQL Server Databases

Some customers who used an SQL Server 2000 database as a repository reported that as concurrency increased, SQL Server 2000 reported deadlocking problems that were related to SQL Server’s internal use of pessimistic locking (primarily lock escalation).

These deadlock errors display in the following format:


com.waveset.util.IOException:
  ==> com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 51) 
was deadlocked on lock | communication buffer resources with another 
process and has been chosen as the deadlock victim. Rerun the transaction.

    To prevent or address deadlocking problems, do the following:

  1. Use the SQL Server 2005 database.

  2. Configure the READ_COMMITTED_SNAPSHOT parameter by formatting the command as follows:

    ALTER DATABASE waveset SET READ_COMMITTED_SNAPSHOT ON

    Enabling the READ_COMMITTED_SNAPSHOT parameter does the following:

    • Removes contention during the execution of SELECT statements that can cause blocks, which greatly reduces the potential for deadlocks internal to SQL Server.

    • Prevents uncommitted data from being read and guarantees that SELECT statements receive a consistent view of committed data.

    For more information about the READ_COMMITTED_SNAPSHOT parameter, see:http://msdn.microsoft.com/en-us/library/ms188277.aspx.