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.
See Repository Table Types and Data Classes for descriptions of the tables, the object types stored in each table, and the general access pattern for each table.
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:
Oracle MetaLink: Note:114671.1: Gathering Statistics for the Cost Based Optimizer
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.