25 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:

25.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, 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.

To identify connector tables and index requirements:

Note:

In the following procedure, the Sun Java System Directory connector has been used as an example.

All the key fields used for field mappings must be indexed from the UD_* table or the process definition table.

  1. Figure 25-1 shows the process definition table for the Sun Java System Directory connector in Oracle Identity Manager Design Console. For this connector, double-click the iPlanet User provisioning process, and then click the Reconciliation Field Mappings tab to view the field mappings.

    Figure 25-1 Key Fields of a Process Definition Table

    Description of Figure 25-1 follows
    Description of "Figure 25-1 Key Fields of a Process Definition Table"

  2. Figure 25-2 shows the reconciliation field mappings for the Sun Java System Directory connector. In this figure, the table name and the key field are highlighted in red. For this connector, the UD_IPNT_USR_USERID column must be indexed.

    Note:

    This is a mandatory step during connector deployment.

    Figure 25-2 Reconciliation Field Mappings

    Description of Figure 25-2 follows
    Description of "Figure 25-2 Reconciliation Field Mappings"

Note:

if multiple (composite) keys are used for looking up a user, then composite indexes should be created.

The following are the guidelines for indexing key fields:

  • The key fields from the child tables must also be indexed. In Figure 25-2, the key fields for child tables are highlighted in blue. For the Sun Java System Directory connector, the UD_IPNT_ROL_ROLE_NAME and UD_IPNT_GRP_GROUP_NAME columns should be indexed.

  • If the connector contains any user-defined field and the attribute value is used for searching users in the Oracle Identity Manager database, then the corresponding database field should be indexed.

  • If any key field is defined in Oracle Identity Manager as case insensitive, then a function-based index on that key field should be created. For example, if the connector code internally performs a search for the first name (assuming that FIRST_NAME is a key), then the indexing should be performed as follows:

    CREATE INDEX FDX_USR_FIRST_NAME ON USR(UPPER(FIRST_NAME))

  • While creating indexes, consider using the COMPUTE STATISTICS clause, so that statistics are generated for the index.

  • After configuring a connector and creating indexes with above process, you should generate database table and index statistics (or schema statistics).

25.2 Indexes for Reconciliation Tables

For performance tuning, you must create the indexes for the reconciliation tables, as shown in Table 25-1:

Table 25-1 Indexes on Reconciliation Tables

Type of Reconciliation Table Index

Identity reconciliation

Target horizontal table

Owner matching Rule column
   

RE_KEY
 

USR

Owner matching rule column
 

RECON_EVENTS

Composite index on RB_KEY and RE_STATUS
 

RECON_USR_MATCH

RE_KEY

Account reconciliation

Parent target horizontal table

Account matching rule column
   

Entity matching rule column
   

RE_KEY
 

Child target horizontal table

Child matching rule column
   

RE_KEY
 

USR

Entity matching rule column
 

RECON_EVENTS

Composite index on RB_KEY and RE_STATUS
 

RECON_ACCOUNT_MATCH

RE_KEY
 

RECON_CHILD_MATCH

RE_KEY

To collect the database statistics:

  1. Login to SQL*PLUS as SYS user.

  2. Run the following command for the first time:

    SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'oimadmin',
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE=>8,
OPTIONS=>'GATHER AUTO',NO_INVALIDATE=>FALSE);

    Note:

    If the matching rule uses the UPPER clause, then create a functional index on the column. For example, for trusted source reconciliation from LDAP store:

    For user reconciliation from LDAP store:

    • On the RA_LDAPUSER table, create an index on RECON_ORCLGUID and RE_KEY.

    • On the USR table, create an index on USR_LDAP_GUID