Issues with Risk Authentication Database Query

book

Article ID: 209738

calendar_today

Updated On:

Products

CA Advanced Authentication - Strong Authentication (AuthMinder / WebFort)

Issue/Introduction

Facing performance issues while performing the Risk Authentication. DB is having almost 1 Million Users in ARUDSUSER table.

There is a lot of time spent while fetching the details from the Database.

The query is going for the FULL TABLE SCAN even if the index is present on USERID, ORGNAME columns. Looks like the DB column for user ID is VARCHAR and the application is referring it with NVARCHAR hence the Oracle is forced to do the Implicit Type casting (evident by the Explain plan of the query SYS_OP_C2C). Please arrange to resolve this issue as to avoid performance bottlenecks.

SQL Query - SELECT ARUDSUSER.USERREFID, ARUDSUSER.USERID, 
EMAILADDR,TELEPHONENUMBER,ARUDSUSER.DATECREATED,ARUDSUSER.DATEMODIFIED, 
     CASE WHEN STARTLOCKTIME Is NULL AND ENDLOCKTIME Is NULL THEN 
ARUDSUSER.STATUS           WHEN STARTLOCKTIME Is NOT NULL AND 
ENDLOCKTIME Is NULL THEN (CASE WHEN  SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) 
 < STARTLOCKTIME THEN 1 ELSE 2 END)           WHEN STARTLOCKTIME Is NOT 
NULL AND ENDLOCKTIME Is NOT NULL THEN (CASE WHEN  
SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)  BETWEEN STARTLOCKTIME AND 
ENDLOCKTIME THEN 2 ELSE 1 END)      END AS 
USERSTATUS,FNAME,MNAME,LNAME,PAM,PAMURL, INFOLIST  FROM ARUDSUSER WHERE 
ARUDSUSER.USERID =:V1 AND ARUDSUSER.ORGNAME = :V2

 

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |  4965 (100)|          |
|*  1 |  TABLE ACCESS FULL| ARUDSUSER | 10597 |  1252K|  4965   (1)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter((SYS_OP_C2C("ARUDSUSER"."USERID")=:V1 AND 
              "ARUDSUSER"."ORGNAME"=:V2))

 

Environment

Product Version - Advanced Authentication 9.1 CP1

Tomcat Version - 9.0.36

Adopt Open JDK Version - 8.0.252.09

Oracle DB Version - 19c

OS Version - Windows Server 2016 SE

Resolution

 Post changing the column type to nvarchar the issue is resolved and there are no performance issues seen,