27 Tuning Connector Performance

This chapter describes how to improve performance by identifying indexes that are required for connector tables and reconciliation tables. It contains the following sections:

Indexes for Connector Tables
Collecting Database Schema Statistics for Reconciliation Performance

27.1 Indexes for Connector Tables

When a connector is imported in Oracle Identity Manager, it creates certain database tables (UD_*) and updates metadata in the Oracle Identity Manager schema. The connector may be further customized to suit processes required in a particular installation with reconciliation rules, data flow, and lookup definitions. After a connector is imported and customized, appropriate indexes must be created. The following procedure describes how to identify tables and index key fields. Additional requirements can be gathered by running a reconciliation and examining database AWR reports.

When Oracle Identity Manager is installed, the necessary indexes are created in the Oracle Identity Manager database schema. However, there can be additional indexes required because of dynamic nature of some of the features in Oracle Identity Manager. This is especially true for reconciliation.

Reconciliation uses matching algorithm to find if the user/account/role/organization for which the change is requested is already existing in Oracle Identity Manager or not. The matching algorithm compares the data in set of columns in Oracle Identity Manager with the data in target horizontal table columns. The columns that contains the matching rules are defined in the reconciliation profile. To improve the performance of the matching operation quickly, there must be correct indexes created on the matching rule columns.

To illustrate the recommended method of identifying the appropriate indexes, a sample Active Directory (AD) user profile present in the Meta Data Store (MDS) repository is taken as an example. In this example, trusted source as well as target resource reconciliation are covered.

Selecting Indexes for Trusted Source Reconciliation

To select indexes based on the matching rule criteria in trusted source and target resource reconciliation:

Open the AD user profile file in a text editor.

Note:
The AD user profile must be imported from the MDS by using the MDS utilities. See "MDS Utilities and User Modifiable Metadata Files" in the Oracle Fusion Middleware Developer's Guide for Oracle Identity Manager for information about the MDS utilities.

Search for ownerMatchingRuleWhereClause for all entities, as shown in the following figure with code sample:

Here, the ownerMatchingRuleWhereClause is the following:

ownerMatchingRuleWhereClause = (((UPPER(USR.USR_LOGIN)=UPPER(RA_ADUSER7.RECON_USERID5A729570)) OR (UPPER(USR.USR_UDF_OBGUID)=UPPER(RA_ADUSER7.RECON_OBJECTGUID))))

After identifying the columns constituting the matching rule in the user entity, create the indexes accordingly.

Note:

If any key field is defined in Oracle Identity Manager as case-insensitive, then a function-based index on that key field must be created. For example, if the connector code internally performs a search for the first name, assuming that FIRST_NAME is a key, then appropriate indexing must be done.
If multiple or composite keys are used for looking up a user, then choose between individual or composite indexes.

Selecting Indexes for Target Resource Reconciliation

To select indexes based on the matching rule criteria in target resource reconciliation:

Open the AD user profile file in a text editor.

Note:
The AD user profile must be imported from the MDS by using the MDS utilities. See "MDS Utilities and User Modifiable Metadata Files" in the Oracle Fusion Middleware Developer's Guide for Oracle Identity Manager for information about the MDS utilities.
Search for account search tag <matchingruleWhereClause>, as shown in the following figure:

Here, the <matchingruleWhereClause> is the following:
```
<matchingruleWhereClause>((UD_ADUSER.UD_ADUSER_OBJECTGUID=RA_ADUSER7.RECON_OBJECTGUID))</matchingruleWhereClause>
```
After identifying the columns constituting the matching rule in the user entity, create the indexes accordingly.

Selecting Indexes for Target Resource Reconciliation With Multi-Valued Data

To select indexes based on the matching rule criteria in target resource reconciliation with multi-valued data:

Open the AD user profile file in a text editor.

Note:
The AD user profile must be imported from the MDS by using the MDS utilities. See "MDS Utilities and User Modifiable Metadata Files" in the Oracle Fusion Middleware Developer's Guide for Oracle Identity Manager for information about the MDS utilities.
For entitlements, search for the <matchingruleWhereClause> tag under <childreconeventdata>.

Here, the <matchingruleWhereClause> tag is the following:
```
<matchingruleWhereClause>((UD_ADUSRC.UD_ADUSRC_GROUPNAME=RA_UD_ADUSRC.RECON_MEMBEROF))</matchingruleWhereClause>
```
After identifying the columns constituting the matching rule in the user entity, create the indexes accordingly.

Note:

Pointers for required indexes can also be taken by monitoring the real-time running of reconciliation process from the database side by using a performance-monitoring tool, such as Oracle Enterprise Manager Console, or through the Automatic Workload Repository (AWR) Reports available in Oracle Database 11g.

27.2 Collecting Database Schema Statistics for Reconciliation Performance

Database statistics is essential for the Oracle optimizer to select an optimal plan in running the SQL queries. Oracle recommends that the statistics are collected regularly for Oracle Identity Manager schema. Because Oracle Identity Manager 11g Release 1 (11.1.1) uses lot of database SQL features for reconciliation process, make sure that the schema statistics are updated before running the reconciliation.

Note:

Other options with DBMS_STATS.GATHER_SCHEMA_STATS API can be used as required, such as DEGREE,ESTIMATE_PERCENT based on the environment, data profile, Oracle DB Edition and underlying hardware capabilities.
See "Database Performance Monitoring" for more information about collecting database schema statistics.

Because Oracle Identity Manager reconciliation process is a data-intensive process and quickly brings in large volume of data, database statistics must also be able to represent the underlying data correctly. To achieve this, refer to the following guidelines:

Make sure that statistics is collected for reconciliation on a fresh setup or with a low volume with no or negligible existing data in the Oracle Identity Manager schema. Maximum row count in relevant Oracle Identity Manager tables must be between 100 and 1000 rows. Examples of tables are USR table for trusted source reconciliation and parent account table for target resource reconciliation.
For the statistics to be a proper representative of data distribution after reconciliation has started and is expected to bring in a large volume of data, such as more than 20000 users or accounts, collect Oracle Identity Manager schema statistics in the following manner:
1. Plan to gather statistics after the initial collection only after reconciliation has started successfully and has been running for a while. To verify this, check the counts of a few key tables from the Oracle Identity Manager schema, such as USR table for trusted source reconciliation and parent account (UD_*) tables for target resource reconciliation.
2. After reconciliation has brought in almost 20000 to 25000 rows in the USR table or in the parent account tables, statistics can be collected.
  Note:
  - Statistics can be gathered concurrently with reconciliation running.
  - The row counts specified in the guidelines are examples and you can determine any other row count for collecting statistics.
After the statistics is collected, the performance might not improve immediately. However, as older SQL Plans are cleared from the shared pool of the Oracle Database, new and more efficient plans are created and performance improves.