OVD 10g Integrated With OAM 10g - OVD Database Adapter SQL Query Doing A Full Table Scan On The Table, Causing Severe DB Performance Impact
Last updated on FEBRUARY 16, 2018
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) 18.104.22.168, 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
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms