Skip to Main Content
  • Questions
  • Limit the number of opening and closing oracle sessions.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, maciek.

Asked: April 04, 2017 - 9:05 am UTC

Last updated: April 05, 2017 - 3:43 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi all,

Issue:
Large number of opening and closing Oracle database sessions (on average 100k per hour).App is loading files into database by using sqlldr and for each file creating a new session. After loading session is closed. Probably it has no major impact to resources and performance of Oracle database
however KPI does not look satisfactorily. Important ! - App cannot be modified.

Is there any way to somehow reduce that number (only on Oracle side) so that app will load files by using some pool of connections instead of creating every time a new one and so on? Perhaps there is a module or mechanism that would be appropriately configured to be useful?

Let's assume the best scenario:
App first check if there is some active available session (for this process), if so then use it, if not then create a new one. It will definitely decrease the number of opening and closing session per hour.

Regards,
MS

and Connor said...

Perhaps take a look at the DB Resident Connection Pool, eg

SQL> exec dbms_connection_pool.start_pool;

PL/SQL procedure successfully completed.


and then you sqlldr programs would look like:

sqlldr userid=scott/tiger@//localhost:1521/DB122:POOLED

to access the connection pool

More details here

https://docs.oracle.com/database/121/ADMIN/manproc.htm#GUID-BB76E57C-3F16-4C85-AEF6-BA14FC1B4777

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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library