Skip to Main Content
  • Questions
  • Java Connection Pooling with Oracle VPD

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 22, 2016 - 10:37 pm UTC

Last updated: September 23, 2016 - 2:18 am UTC

Version: 11G

Viewed 1000+ times

You Asked

Hi Tom,

We have a 3-tier application that is built on Java and Oracle. In our application, we extensively make use of Oracle VPD policies for setting contexts and managing the data. Now, we are building in Java something on top of Oracle. We hit an issue with Connection Pooling.

Below is the list of tasks that we are doing.

1. Login to application and set an Oracle context which sets Oracle VPD.
2. Invoke a process either through Java
3. As part of the process, there a lot of transactions that they invoke in Java.
4. Initially, at the start of first transaction, we open a DB connection and set the Oracle context which sets the VPD.
5. Execute some transactions in Java
6. After that we are still executing transactions on the same DB connection, but the Application server is giving us different connection from the available ConnectionPool (Ex: Consider a connection pool contains 10 connections. Set VPD on Connection 1, execute 3-4 transactions on that connection. For the 5th transaction the application server is providing Connection 5 for execution)
7. So, this connection doesn’t seem to have the context at DB level set or a wrong context is set (because VPDs are not set on this connection yet)
8. Due to this, every time there is a connection switch happening we need to set the VPD at the DB level for our queries to work.

I am aware that setting VPDs and Oracle context are heavy load transactions on the DB side. So, we would like to minimise setting VPDs and Oracle context multiple times but we are hit into this issue due to Connection Pooling and Switching.

Is there anyway we can handle this? The Application is being built to handle multiple users and multiple parallel transactions because of which setting ConnectionPool to 1 is not an option (which initially we tested to be sure that Connection switch is happening or not)

Request your advise on this scenario.

Regards
Kiran

and Connor said...

I'm not sure what you mean by setting context is expensive /

SQL> create context my_ctx using proc;

Context created.

SQL> create or replace
  2  procedure proc is
  3  begin
  4    dbms_session.set_context('MY_CTX','ATTRIB','123');
  5  end;
  6  /

Procedure created.

SQL> set timing on
SQL> begin
  2    for i in 1 .. 100000 loop
  3       proc;
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.98


That's around 100,000 per second (on my laptop). The only way setting a context is expensive is if your code makes it so (ie, lots of complex SQL's to evaluate what to set the context to...)

You could perhaps consider global contexts,so it is set globally once, and then sessions come back with their client identifier to pick up the global context

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.