The Oracle MA had the following tables configured:
1.
Main User information table (250000+ items, 18
fields, 790mb)
2.
Multi-value table (250000+ items, 4 fields, 690mb)
3.
Delta table
I followed the usual troubleshooting steps ensuring that:
1.
The Oracle tables were correctly built and
indexed.
2.
The Oracle client version was correct.
3.
The FIM Server was installed, configured and
patched correctly.
4.
All possible bottlenecks were identified and
eliminated between servers in question.
With all these resolved, we did not get a significant
increase in speed, so I did a test of importing the user information excluding
the multi-value table from the configuration and the import completed in less
than 40 minutes. It was in fact the multi-value table causing the import
performance degradation. Although it was correctly indexed, contained only 4
fields, it was almost the same size as the main user table.
We had three options:
1.
Reduce the complexity of the data in the main
and multi-value tables (was not possible in this instance).
2.
Consolidate the data into a single view and pass
the multi-value processing to FIM rules extension.
3.
Write a custom management agent to incorporate
multi-values directly (Due to the work involved in achieving this it was not
really an option).
I then asked the client to consolidate the data into a
single view writing the multi-values delimited to a single value field. I was
then able to still do the import in less than 40 minutes and use a rules
extension to extract the multi-values from the single value table to an array
which I then flowed to the relevant multi-value attributes in the metaverse.
The total process of full import and sync then completed in less than 3 hours.
I am a big advocate of using multi-value tables where
appropriate, but there certain scenarios where using multi-value tables are
just not feasible and other approaches may be needed.
When evaluating whether or not to use a multi-value table it
is important remember that the size and data complexity if the table will
affect performance. My own rule of thumb is once the multi-value table exceeds 33-50%
(based on complexity) of size of the main table there will be a performance
impact on the import of data into the connector space.
