Weblogic Keepalive Interacts by Using an SQL Query to a VPD table. (Doc ID 1379783.1)

Symptoms

On OTM version 6.2.3

ACTUAL BEHAVIOR
---------------
The weblogic keep alive on 6.2 is using select count(*) from involved_party_qual instead of select 1 from dual.

At the start of a user session the OTM code sets the sys_context of the session so that the following sql statement uses the correct vpd security settings. After the user session has finished it's work the sys_context for the db session is left as it is.  This is not normally a problem as the next user session will update it and the security will again be correct for the subsequent sql statements.

The problem here is that between user sessions weblogic runs a keep alive sql to prevent the database session from expiring. For some reason the sql is select count(*) from involved_party_qual which needs a full table scan to be carried out with the row level security being invoked for each row to check which rows are available to for that database session. When we scale up to having hundreds of sessions against the database from multiple application servers then this scanning of the involved party qualifiers to check whether they belong to the domain of the previous user or not is going to add unecessary load to the database

EXPECTED BEHAVIOR
-----------------------
Expect the session to use select 1 from dual and not to have to keep running a full table scan.

Cause

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