OVD DB Adapter SQL Query Generated is Slow (Doc ID 1951464.1)

Last updated on JUNE 16, 2023

Applies to:

Goal

Oracle Virtual Directory (OVD) 11g.

Already reviewed the documentation and known issues for Database (DB) adapters, but still experiencing performance issues.

For example, an OVD DB adapter is using two views with several mapped attributes of users.  For any login through OVD, the following problematic query is seen on the DB:

SELECT DISTINCT <DB_SCHMEA>.<DB_VIEW>.NAME,<DB_SCHMEA>.<DB_VIEW>.GUID FROM <DB_SCHMEA>.<DB_VIEW> WHERE
(UPPER(<DB_SCHMEA>.<DB_VIEW>.LOGIN) = UPPER( :1 )) OR
(UPPER(<DB_SCHMEA>.<DB_VIEW>.LOGIN) = UPPER( :2 )) OR
(UPPER(<DB_SCHMEA>.<DB_VIEW>.LOGIN) = UPPER( :3 )) OR
(UPPER(<DB_SCHMEA>.<DB_VIEW>.LOGIN) = UPPER( :4 )) OR
(UPPER(<DB_SCHMEA>.<DB_VIEW>.LOGIN) = UPPER( :5 )) OR
(UPPER(<DB_SCHMEA>.<DB_VIEW>.LOGIN) = UPPER( :6 )) OR
(UPPER(<DB_SCHMEA>.<DB_VIEW>.LOGIN) = UPPER( :7 )) ORDER
BY <DB_SCHMEA>.<DB_VIEW>.NAME

This query takes a long time to perform.

Seems OVD does not know which attribute is responsible for holding the username, thus this problematic query.

Why is this query not constructed where the username is just one single attribute, not the several ones mapped?  Can this query can be changed or tuned?
 

Solution

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