Weblogic Keepalive Interacts by Using an SQL Query to a VPD table.
Last updated on MARCH 08, 2017
Applies to:Oracle Transportation Management - Version 6.2.3 and later
Information in this document applies to any platform.
***Checked for relevance on 19-Sep-2013***
On OTM version 6.2.3
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
Expect the session to use select 1 from dual and not to have to keep running a full table scan.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms