My Oracle Support Banner

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 NOVEMBER 01, 2019

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
<TABLE>.<SCHEMA>.<ID_COLUMN>,<TABLE>.<SCHEMA>.<FIRST_NAME_COLUMN>,<TABLE><SCHEMA>.<LAST_NAME_COLUMN><TABLE>.<SCHEMA>.<EMAIL_COLUMN>,<TABLE>.<SCHEMA>.<DATE_OF_BIRTH_COLUMN>,<TABLE>.<SCHEMA>.<GENDER_COLUMN>,<TABLE>.<SCHEMA>.<STATE_COLUMN>,<TABLE>.<SCHEMA>.<CODE_COLUMN>
FROM <TABLE>.<SCHEMA>
WHERE
(( UPPER('inetorgperson') = UPPER('inetorgperson') ) AND (
<TABLE>.<SCHEMA>.<ID_COLUMN> IS NOT NULL )) ORDER BY
<TABLE>.<SCHEMA>.<ID_COLUMN>

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=<OU>,ou=<OU2>,cn=Users,dc=<COMPANY>,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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.