OVD 10g Integrated With OAM 10g - OVD Database Adapter SQL Query Doing A Full Table Scan On The Table, Causing Severe DB Performance Impact (Doc ID 1519520.1)

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.

Symptoms

Oracle Virtual Directory (OVD) 10g, i.e., 10.1.4.3 or higher, integrated with Oracle Internet Directory (OID) and Oracle Database (DB) 11.2.0.3, 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:

SELECT DISTINCT
MYTABLE.INDIVIDUAL.INDIVIDUAL_ID,MYTABLE.INDIVIDUAL.FIRST_NAME,MYTABLE.INDIVIDUAL.LAST_NAME,MYTABLE.INDIVIDUAL.EMAIL_ADDRESS,MYTABLE.INDIVIDUAL.DATE_OF_BIRTH,MYTABLE.INDIVIDUAL.GENDER,MYTABLE.INDIVIDUAL.STATE,MYTABLE.INDIVIDUAL.CODE
FROM MYTABLE.INDIVIDUAL
WHERE
(( UPPER('inetorgperson') = UPPER('inetorgperson') ) AND (
MYTABLE.INDIVIDUAL.INDIVIDUAL_ID IS NOT NULL )) ORDER BY
MYTABLE.INDIVIDUAL.INDIVIDUAL_ID

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:

[2012-12-06 10:50:07,930] conn=87 op=3765 SRCH base="ou=PROD,ou=External,cn=Users,dc=mycompany,dc=com" scope=2 filter="(&(objectclass=inetorgperson)(&(cn=*)(|(obUserAccountControl=ACTIVATED)(!(obUserAccountControl=*)))))"

 
Due to this issue, although clients do not suffer a major impact as of yet, the impact to the DB is very severe.

Changes

 Gradually increase of user entires.

Cause

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