Last updated on SEPTEMBER 15, 2016
Applies to:COREid Access - Version 10.1.4 and later
Oracle Virtual Directory - Version 10.1.4.3.0 and later
Information in this document applies to any platform.
Oracle Virtual Directory (OVD) 10g, i.e., 10.1.4.3 or higher, integrated with Oracle Internet Directory (OID) and Oracle Database (DB) 220.127.116.11, and with Oracle Access Manager (OAM) 10g 10.1.4.
This had been working fine before and the OAM and OAM configuration has not been changed.
Gradually have had increase in entries; now up to millions of rows on the DB user table, and noticing a performance degradation.
The problem query in question from OVD is, for example:
(( UPPER('inetorgperson') = UPPER('inetorgperson') ) AND (
MYTABLE.INDIVIDUAL.INDIVIDUAL_ID IS NOT NULL )) ORDER BY
This query is pulling back millions of individuals, in other words, it is doing a full table scan. (In this query OVD does not specify an individual ID, so it takes ~30-45mins. If it did specify an individual_id, did then only one row would return and this query would run in ~0.045 seconds.)
The ID column has only numeric values, plus is a primary key, so the known workaround from <Document 785224.1> and <Document 566569.1>, to create a function base index such as UPPER of INDIVIDUAL_ID, does not resolve this problem.
The query comes originally from OAM, as per the corresponding OVD access.log entry which is as follows, for example:
Due to this issue, although clients do not suffer a major impact as of yet, the impact to the DB is very severe.
Gradually increase of user entires.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms