This section describes some vendor-specific guidelines for tuning Oracle and SQL Server repository databases.
Currently, MySQLTM databases are only supported in development and for demonstrations.
This section describes guidelines for tuning Oracle repository databases:
The Identity Manager application does not require Oracle database features or options.
If you are using an Oracle repository database and Identity ManagerService Provider or Identity Manager, you might encounter problems with object table fragmentation because Identity Manager uses LONG, rather than LOB, data types by default. Using LONG data types can result in large amounts of “unallocated” extent space, which cannot be made into usable space.
To mitigate this problem, do the following:
Take EXPORT dumps of the Object table and re-import them to free up unallocated extent space. After importing, you must stop and restart the database.
Use LOB data types and DataDirect Technologies’ Merant drivers, which provide a standard LOB implementation for Oracle.
Use Locally Managed Tablespaces (LMTs), which offer automatic free space management. LMTs are available in Oracle 8.1.5.
Identity Manager does not require Oracle init.ora parameter settings for SGA sizing, buffer sizing, open cursors, processes, and so forth.
While the Identity Manager repository is a general-purpose database, it is best described as an object database.
Of the Identity Manager tables, the TASK table-set comes closest to having transaction-processing characteristics. The LOG and SYSLOG table-sets are also exceptional because these tables do not store serialized objects.
If you have performance issues with the Oracle database, check for issues related to poor query plans being chosen for what Identity Manager expects to be relatively efficient queries.
For example, Identity Manager is configured to perform a full table-scan when an index is available for use. These issues are often visible in Automated Workload Repository (AWR) reports provided in the SQL by the buffer gets table. You can also view issues in the Enterprise Manager tool.
Performance problems typically appear to be the result of bad or missing database table statistics. Addressing this problem improves performance for both the database and Identity Manager.
The following articles (available from Oracle) are a good source of information about the cost-based optimizer (CBO) in Oracle:
You might also investigate using SQL Profiles, which are another method for choosing the best query plans. You can use the SQL Advisor within Enterprise Manager to create these profiles when you identify poorly performing SQL.
If you detect unexpected growth in the Oracle redo log, you might have workflows that are caught in an infinite loop with a manual action. The loop causes constant updates to the repository, which in turn causes the size of each TaskInstance to grow substantially. The workflow errors are caused by improper handling of WF_ACTION_TIMEOUT and by users closing their browser in the middle of a workflow.
To prevent problematic workflows, preview each manual action before a production launch and verify the following:
Have you set a timeout?
Have you created appropriate transition logic to handle a timeout for the activity with the manual action?
Is the manual action using the exposed variables tag when there is a large amount of data in the TaskInstance?
Frequently, you can significantly improve Identity Manager performance if you change the CURSOR_SHARING parameter value from EXACT to SIMILAR.
Identity Manager uses prepared statements for some activities (such as inserting and updating database rows), but does not use these statements for most queries.
When you use Oracle, this behavior can cause issues with the library cache. In particular, the large number of statement versions can create contention on the library cache latch. Changing CURSOR_SHARING to SIMILAR causes Oracle to replace literals in SQL statements with bind variables, which greatly reduces the number of versions.
See Prepared Statements for more information.
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:
Use the SQL Server 2005 database.
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://msdn2.microsoft.com/en-us/library/ms188277.aspx.