OID 11g Performance Affected Due to Password Policy Refresh when Lots of User Entries Have pwdpolicysubentry Attribute (Doc ID 1512300.1)

Last updated on MARCH 06, 2023

Applies to:

Symptoms

High number of DB password policy refresh execution when lot of user entries has pwdpolicysubentry attribute. This event will trigger OID to refresh its passwordPolicySubentry cache, which will cause a huge SQL query requests to the database if there are lots of such users, this issue is causing an intermittent ldap query delay.
  
Following is the SQL query that the oidldapd process is running every few seconds and that is causing a lot of traffic:
  
 SELECT /*+ USE_NL(store) INDEX(store EI_ATTRSTORE) FIRST_ROWS */ store.entryid,store.attrname, NVL(store.attrval,' '), NVL(store.attrstype, ' ') FROM ds_attrstore store WHERE store.entryid in (SELECT /*+ INDEX( at1 ST_pwdpolicysubentry ) */ at1.entryid FROM CT_pwdpolicysubentry at1) AND store.entryid >= :entryThreshold AND store.attrkind = 'u'

Cause

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