Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kayode.

Asked: August 12, 2004 - 11:50 am UTC

Last updated: October 11, 2021 - 10:57 am UTC

Version: 8.0.5

Viewed 100K+ times! This question is

You Asked

Tom,
What is connection pooling ? Please, can you give an example(s) that show thorough understanding of the subject matter as related to either ODBC OR JDBC application connections to the oracle database. Your site is more important and most value to the little understading and experience I have had as DBADMIN.

Thank,
Kayode

and Connor said...

connection pooling is generally the practice of a middle tier (application server) getting N connections to a database (say 20 connections).

These connections are stored in a pool in the middle tier, an "array" if you will. Each connection is set to "not in use"

When a user submits a web page to the application server, it runs a piece of your code, your code says "i need to get to the database", instead of connecting right there and then (that takes time), it just goes to this pool and says "give me a connection please". the connect pool software marks the connection as "in use" and gives it to you.

You generate the page, format the html whatever -- and then return the connection to the pool where someone else can use it.

In this fashion, using 20 connections to the database, you can avoid the connect/disconnect overhead and (more importantly perhaps) you can service a community of hundreds of users simultaneously using just 20 connections! (as opposed to client server where by hundreds of simultaneous users would take hundreds of connections!)

On my site, I use mod_plsql, mod_plsql is reuseing these connections -- at most, I have between 10 and 15 connections out there (the connection pool shuts them down after a period of inactivity and allocates new connections as needed)



Rating

  (120 ratings)

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

Comments

connection poolings

A reader, August 12, 2004 - 1:21 pm UTC

As always , your thought process and explanation regarding any issues or problem is of tremedous help not only to me,but to many people like me who wants to know the rudimental meanings of each words in Oracle.

Thanks

Kayode.

Tracing of connection Pooled sessions

Matt Butler, August 12, 2004 - 7:06 pm UTC

Consider an application that uses connection pooling. When it does some long running processing it may switch server processes, that is, it may using different connections in the pool. When tracing such an application, the required trace output can be scattered through many files.

1) How is it possible to identify each of these trace files?
2) How is it possible to merge these trace files so that thet may be tkprofed?

One solution I've thought of avoid these issues and involves an application change. The connection code would need to be changed to switch tracing on prior to connection and off before disconnection, then on again prior to reconnection etc.

Another solution which again avoids the issue is to try and force the application to use a dedicated server process. This is not always possible.

3) Are there any approaches or tools out there that will enable the identification, and merge of the correct traces that may help?

I'm interested to hear what you have to say.

Cheers.

Tom Kyte
August 13, 2004 - 9:42 am UTC

why would a long running process switch connections? that long running process is a transaction, cannot imagine switching connections?

10g has the ability to trace over "multiple connection pool connections" as a single logical session.

Connection pool in multithreaded application

Douglas, August 13, 2004 - 1:27 am UTC

I have been trying to use the OCCI Connection Pool class for my multithreaded server application, aiming to eliminate the long time required for connection and disconnection. However, I found that the program crashes when I commit the connection get from the connection pool. Why is it happening? Thanks!

Belows is the code I used:


void testConnPool(const string &rstrUsr, const string &rstrPwd, const string &rstrConn)
{
Environment *pEnv = NULL;
ConnectionPool *pConnPool = NULL;
Connection *pConn = NULL;
Statement *pStmt = NULL;
try
{
pEnv = Environment::createEnvironment(Environment::THREADED_MUTEXED);
pConnPool = pEnv->createConnectionPool(rstrUsr, rstrPwd, rstrConn, 1, 2, 1);
pConn = pConnPool->createConnection(rstrUsr, rstrPwd);

pConn->commit();
// No problem if changed to the following 2 lines
// pStmt = pConn->createStatement("COMMIT");
// pStmt->executeUpdate();
}
catch (SQLException ex)
{
cout << "exception thrown: " << ex.getErrorCode() << ": " << ex.getMessage() << endl;
}

if (pStmt != NULL)
{
pConn->terminateStatement(pStmt);
}
if (pConn != NULL)
{
pConnPool->terminateConnection(pConn);
}
if (pConnPool != NULL)
{
pEnv->terminateConnectionPool(pConnPool);
}
if (pEnv != NULL)
{
Environment::terminateEnvironment(pEnv);
}
}


Tom Kyte
August 13, 2004 - 10:01 am UTC

sorry -- i don't program C++ personally, and this is not an entire "program" and I don't know what options you used to compile with, nor what platform, nor what compiler, etc....

if you give me a complete -- yes concisely tiny -- full example, I might be able to have someone look at it if I don't see the issue right off myself.

TAF and JDBC

reader, August 13, 2004 - 11:32 am UTC

Does TAF work with JDBC connection pool? Thanks.

Tom Kyte
August 13, 2004 - 5:50 pm UTC

if you use a connection pool and software that supports it, sure. "jdbc connection pool" is like saying "human being". there are lots of them and they are all "unique" -- but they have similar underlying DNA.

See the jdbc developers guide on otn.oracle.com

Cleaning out connection?

marc weinstock, March 19, 2005 - 10:53 am UTC

1. If for some reason the developer does not commit or rollback as the last statement, do you know if the connection get clean out or the data sits in the connection till the next persons commits or rollbacks?

2. My conern is that I did a statspack and it says the applicatin did 200,000 rollbacks in a small amount of time. We looked at the logs and there was not 200k application rollbacks. I believe the rollbacks were done when returning the connection back to the pool.

thanks.

Tom Kyte
March 19, 2005 - 12:54 pm UTC

1) depends on who wrote your connection pool and what the documentation for your connection pooling software says it does when you give a connection back.

2) you'd have to ask the maker of your connection pool. a rollback of nothing is "ok" however.

Peter, July 15, 2005 - 7:28 am UTC

<quote>
10g has the ability to trace over "multiple connection pool connections" as a
single logical session.
</quote>

How do I do this? Can you give us the syntax in an example?
Can I enable tracing a connection pool for other sessions like I can do with dbms_system.set_sql_trace_in_session?

Need some clarification about the parameters to dbms_monitor

Peter, July 20, 2005 - 11:36 am UTC

In her article Kimberly Floss keeps silent about the parameters someone has to provide to dbms_monitor.
I guess in the following call

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'ACCTG', module_name => 'PAYROLL', instance_name => 'inst1');

service_name correponds to v$session.service_name
module_name correponds to v$session.module

Is this right?

What content do I have to provide to the parameter instance_name?

In the Oracle documentation it is documented that in the following call

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id => 'OE.OE');

the client_id corresponds to v$session.client_identifier.
Is this true?
This column contains only nulls on our environment (oracle10g release 2, red hat linux, dedicated server), so the function would be of no use for us but I guess it's exacly what I would need.
Or does it depend on an DB initialisation parameter?

Would you also please be so kind as to give a short explanation on the parameters "waits" and "binds".

Thank you in advance and kind regards

Peter




Pooling

V, October 12, 2005 - 11:02 am UTC

Our app is using a connection pool into the dB. I am seeing statements being parsed/executed/closed in my trace files probably because of this type of interface. If I set my SESSION_CACHED_CURSORS to a value other than 0, you believe it may reuse these cursors?

Tom Kyte
October 12, 2005 - 2:12 pm UTC

the parsed queries are being reused (thats a softparse)

however, you can make it a softer soft parse (not removing the parse) by using session_cached_cursors, yes.

jdbc 3.0 allows you to do statement caching as well
</code> https://www.oracle.com/technetwork/issue-archive/2014/14-nov/o64asktom-2298498.html <code>

see the jdbc quick tip

Long standing open connections

Rakesh, October 13, 2005 - 9:32 am UTC

Hi Tom,

continuing the theme of connection pooling I had a question:

By default, Java app developers tend to configure connection pools very simply. One example is that no expiry is set on the connection so its possible that a connection, whether used or not, stays around for a long time (days/weeks/months). In fact, probably won't be recreated until an application restart.

Can you see any issues with this? Is it worth taking the time to set expiry times for connections? If so, any guidelines?


Tom Kyte
October 13, 2005 - 10:55 am UTC

I'd set the expiry date just to avoid possible memory leaks in the connection pool itself - but no, in general it is perfectly acceptable to have the connections there.

Username in connection pooling

Laxman Kondal, December 01, 2005 - 2:31 pm UTC

Hi Tom

In connection pooling once the connection is established how to find user name who is calling any proc or executing query.

Based on this info I need to set VPD/FGAC. In connection pooling there may be more then one user at a time and I used dbms_application_info.set_client_info( sys_context('USERENV','SESSION_USER')) in logon trigger which sets the application info for actual user being logged into database and later when any proc/sql is executed its not the same.

Is there any way to identify the actual user in connection pooling who is doing DML.

Thanks and regards.

Tom Kyte
December 02, 2005 - 10:36 am UTC

what username do you mean. there is "USER" - the currently logged in user. if by username you mean "the person running the application currently" - you will need to have the middle tier application figure out how it wants to pass you that information (probably by setting an application content in a procedure that verifies the username associated with the connection pool is the thing doing the calling).

In a connection pool, there is only ONE USER at a time. You grab connection, set identity, use connection, give connection back. You grab connection........


You would not, could not just use a login trigger here since it would not fire each time.

Security concern in connection pooling

Lou, January 19, 2006 - 1:29 pm UTC

Is connection pooling a security concern since the connection was already establish and open infinite session?

Tom Kyte
January 19, 2006 - 1:56 pm UTC

if the middle tier abuses it or levels behind things in the session state that another session should not be able to see - sure.

*everything* is a security concern.

Same connection reused, are the sessions separate ?

Barry Chase, January 20, 2006 - 5:38 am UTC

I am not sure I understand completely. If I am reusing the database connection from a previous session, then how our my variables kept separate from other user sessions ? Say I have a web application that permits the submittal of data to an oracle stored proc. each user is updating different information but it uses the same stored proc. Some variables are in the header of the package and are set during the users transaction submittal. How do I keep user A from impact user B ? Is this done automatically ? Or am I confusing session with connection ?

Tom Kyte
January 20, 2006 - 10:26 am UTC

they are not kept separate, if you share a session you share a session.


if your middle tier leaves (sorry, said levels before) behind stuff in the session state that it should not, that is going to be a problem for you.

you can reset package states, as if you logged out and logged in without doing so (mod_plsql does this)

dbms_session.reset_package

Connection pool and ORA-04031 - Oracle 9.2.0.6

Dana, January 20, 2006 - 1:12 pm UTC

If I understand correctly, the connection pool also caches SQL for the duration of the connection.
I assume an application using lots of literal SQL would have a greater tendency to generate ORA-04031.
When trying to develop a test for this I used the following:

declare p_rc sys_refcursor;
v_id number;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 3275710
loop
open p_rc for
'SELECT * FROM product_table WHERE ( PRODUCT_ID = '||i||')';
close p_rc;
end loop;
dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| ' seconds...');
end;
/
Executes from SQL*Plus, not a web server with connection pooling.
I executed count(*) from v$sql to see how many statements were in cache; it leveled out around 8000.

It sounds like the connection pool would exacerbate the problem and force a ORA-04031?
And yes, I am setting a test of this. I was interested in your viewpoint.


Tom Kyte
January 20, 2006 - 2:46 pm UTC

... the connection pool also caches SQL for the duration
of the connection. ....

depends. plsql cached cursors might (but they close all by themselves and are closable when memory is pressed). but in general, the client itself would have to enable statement caching in JDBC or whatever language you are using.


a lot of literal sql will be something I would suspect with big ora-4031 problems, but a connection pool doesn't neccessarily have anything to do with it.

ref cursors are never cached in plsql by the way, so the statement caching just isn't happening at all with that, only static sql is cached in plsql (execute immediate statements in some cases will be in 10g - but not ref cursors)

Thanks

Dana, January 20, 2006 - 2:54 pm UTC


A reader, February 28, 2006 - 11:37 am UTC

Tom,

We started stress testing our application. This increased the number of connections to the database and looks like the database further rejected new connections. Connection pooling was not setup currently. My question is: In a client/server environment, there is no connection pooling. Huge number of connections to database is balanced by increaing the initialization paramater value and proper mechanism to disconnect promptly. This is a j2ee based webapplication. Is there a specific way to know whether the sudden increase in the number of connections to database is an application issue or because of the lack of connection pooling? This being a commercial product we have no access to source code. Thanks.

Tom Kyte
March 01, 2006 - 7:37 am UTC

In client server - there is connection pooling, we do it in the database.

If this is a j2ee based web application, this is NOT client server.


So - confusing information here, what are we really dealing with (and some "numbers" would be useful - number of connections, size of server, etc etc)


Oh, and if this is a commercial product, did they not help you size your system for your anticipated workload? That is part of "their responsibility"

basic question,

A reader, March 01, 2006 - 11:09 am UTC

How do you set up a connection pooling?

Tom Kyte
March 01, 2006 - 1:46 pm UTC

before I answer that - at what level.

app server
database (we call is "shared server" basically, very much a connection pool)
mod_something

what...

completing my question,

A reader, March 01, 2006 - 2:29 pm UTC

connection pooling in app server and in the database.

thanks,

Tom Kyte
March 02, 2006 - 8:33 am UTC

read your documentation for the app server - there are hundreds of implementations.

likely "shared server" in the database, but ONLY for client server, if you are using an app server, you would typically connection pool there and you don't connection pool a connection pool

Su Baba, July 13, 2006 - 7:24 pm UTC

In the case of the application server getting connections to the database, what's the typical practice of setting up the module and action in v$session? Does an application server usually provide an API that allows the developers to set up these parameters in the database?

Tom Kyte
July 13, 2006 - 8:31 pm UTC

the database provided the api? dbms_application_info?? not sure what you mean

Su Baba, July 14, 2006 - 11:52 am UTC

What I meant was since application servers are usually a third party tool, do they allow us to hook in a dbms_application_info call when it gets a connection to the database?

I'm not too familiar with middle tiers. We're using DBCP (Jakarta) to implement connection pooling and I was told that it doesn't provide the flexibility to make a call to the database when a connection is established for connection pooling.

Tom Kyte
July 14, 2006 - 12:47 pm UTC

application servers let you write code.

you can write any code you want. When your coders make the call to the connection pool software to get a connection, they will execute this stored procedure.

connection pool managers and session state

Kevin from Connecticut, August 09, 2006 - 12:27 pm UTC

Cool stuff.

There is an outstanding question in my office on session state and connection pools. Suppose we create a global temporary table with on commit preserve rows. After filling the table with data and commiting, we quit. Can you validate the following for us?

I know that if we are using Oracle's connection pool manager then Oracle will clean up the GTT for us because even though there has been no hard disconnect/connect event combo., Oracle will reset session state before another user is allowed reuse the logical connection.

But, if we are not using Oracle's connection pool manager, then how are dirty session states avoided? Is it possible for these PRESERVED rows to be visible to the next user?

I don't need a solution to dirty sessions yet, just want to know if it is possible.

Indeed the same would be true for anything that relied on session state I presume.

Oracle 9.2.0.6.0
WEBLogic
also use .NET

Thanks, Kevin


Tom Kyte
August 09, 2006 - 4:06 pm UTC

I would not rely on the behaviour.

If you are using a session based global temporary table GTT, I would clean it out before using it in a connection pool environment.

but, in a connection pool environment - perhaps using session based gtt's would be an all around bad idea from the very beginning.

Alexander the ok, August 10, 2006 - 1:25 pm UTC

Tom,

I wondering how you can prevent against lost updates using connection pooling. If a user reads out a row for update, it will grab a connection from the pool to retrieve the row then return it to the pool. It would then require another connection to do the update. I'm not sure if reading the row out with a select for update would work in this case. How is this typically accomplished? Thanks.

Tom Kyte
August 10, 2006 - 11:18 pm UTC

search for

optimistic concurrency control

that is how you do lost update detection in a stateless environment.

cached execute immediate statements

Nolan, October 03, 2006 - 11:08 am UTC

I have a question about your followup from a previous question:
"ref cursors are never cached in plsql by the way, so the statement caching just isn't happening at all with that, only static sql is cached in plsql (execute immediate statements in some cases will be in 10g - but not ref cursors)"
We're using Jboss app server with connection pooling and executing the same pl/sql procedure 150-200 times a day on oracle 10.1.0.2.0 on 2 node RAC. The connection pool connects to the warehouse node of the rac. The procedure takes a clientid and a date range as parameters. The procedure uses 2 separate global temp tables and an execute immediate to copy the contents from one gtt into the other gtt. The first line of the procedure is a COMMIT - to clear out the gtts from any previous run. I use dbms_application_info.SET_CLIENT_INFO to 'pass' the clientid to a row level security function that filters data for that client running the procedure. The procedure then NULLs the clientid at the end of the procedure. On 4 occasions one client has gotten another client's data. This is not reproducable. This ran fine for years until we recently upgraded the database boxes - more horsepower same SGA and init parameters. Can you see a hole in the way the procedure is collecting the data? If you need sample code, I can probably write a simplified example.

Tom Kyte
October 03, 2006 - 4:41 pm UTC

question about the timeline of the connection pool handling, do you:

a) grab connection
b) run procedure
c) get ref cursor
d) retrieve ref cursor
e) ungrab connection

or what - what is the sequence in the client of activity?

cached execute immediate statements

Nolan, October 04, 2006 - 10:46 am UTC

That is the correct sequence. The connection is closed before passing the resultset back to the web tier.

Tom Kyte
October 04, 2006 - 5:17 pm UTC

the connection is closed BEFORE????

list it out like I did please. using a, b, c, d.....

Mine shows the connection closing AFTER all processing, you said "that is correct sequence", but then say you close it before?

cached execute immediate statements

Nolan, October 05, 2006 - 10:33 am UTC

The application query class is last in the call stack on the app side. So when it returns after it closes the connection, it is returning the resultset in an arraylist.
a) get connection
b) run procedure
c) get ref cursor
d) retrieve ref cursor into passed in arraylist
e) 'close' connection
f) return to calling class

Tom Kyte
October 05, 2006 - 1:18 pm UTC

so, d is doing all of the fetch calls right? what is an "arraylist"??

cached execute immediate statements

Nolan, October 05, 2006 - 2:40 pm UTC

The application that is calling the procedure is a java class that is looping through the refcursor and storing each attribute from the cursor row into an array of lists - an arrarylist. When it hits the end it falls out of the loop, closes the connection and returns.

Tom Kyte
October 05, 2006 - 4:24 pm UTC

and what happens if the ref cursor returned has nothing in it, are these arraylists "properly reset". Is the logic "sound" in this java routine.

cached execute immediate statements

Nolan, October 05, 2006 - 5:32 pm UTC

Each report run is a new instance of a report bean - all objects in the bean are new instances. We have about 50 reports that were developed this way and have been running for 2 years. This is the first time anything like this has happened and it has happened to only this report - this is the most popular report and this is the only report with a dynamic sql to copy rows from one gtt to another. Hence my interest in "cached execute immediate". Are the results cached or just the query? We've seen these scenarios: Client A gets client B's data. Client A gets a merge of his data and client B's. Client A gets client B's data that's been filtered with another parameter from this run. (the filtering logic is in the procedure) In all cases I was able to identify client B and determine that a report had been run previously for that client with the same date range.

Tom Kyte
October 06, 2006 - 8:19 am UTC

Results are not cached - no.

And with ref cursors, ref cursors are never cached like that, they are always parsed again.



cached execute immediate statements

Nolan, October 10, 2006 - 11:30 am UTC

If I have a local variable in the procedure that has a default value defined in the declaration of the variable, will this be initialized each time the procedure is run? Also, we have started to 'flush' the connection pool once every minute to try to avoid any caching of previous runs. However, we are still getting the same Oracle sessionid. Is there a way we can force these sessions to go away? Maybe DBMS_SESSION.RESET_PACKAGE?

Tom Kyte
October 10, 2006 - 8:04 pm UTC

yes, locals are setup each time correctly, it would be a bug any other way.

how does flushing once a minute FIX ANYTHING AT ALL?????

not sure what you are running into, but I suggest really to work with support on this. I'm leaning towards "there is a bug in the code that accesses oracle" myself - but you need to look into this more deeply then we can here (without a test case in any case..)

Then what is the 3-tier application ???

A reader, October 10, 2006 - 9:15 pm UTC

could you tell me then what is called 3-tier appllication and in which situation, it is used...?

Regards,


Tom Kyte
October 11, 2006 - 8:02 am UTC

typically, you are refering to:

client (browser usually)
middle tier (application server, web server, whatever)
database (oracle of course!)

three tiers.

Alexander the ok, January 31, 2007 - 1:42 pm UTC

Thank God you are back.

I have a problem with a jdbc application with a 10.2 RAC database. Our problem is that the application will encounter

java.sql.SQLException: ORA-01008: not all variables bound

For a small percentage of the records this application loads from some other system, scrubs the data, inserts via prepared statements (one at a time I'm pretty sure.) We know the variables are bound correctly.

The app uses about 10 threads to grab a connection out of the connection pool to do parallel processing to achieve the performance they wanted (no need to explain why this is dumb, it could probably all be replace with a sql statement but the java folks went in a different direction).

We already tried to work with support, the said it's the clients fault basically. We enabled tracing for that specific error (alter system set events.....) and received nothing.

Questions

1.) If we did not see any errors in the database, where is coming from? I looked in the jdbc error library in the docs, they all are 17,000+ error number.

2.) Above you mentioned a new 10g connection pool tracing thing. What is it called, where can I find info about this? Does it have to do with the connection manager?

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:22140261281764#22157979898056

3.) Please give me any thoughts, ideas, gut feelings, suggestions from what you've heard because we've tried absolutely everything to try and track down why this is happening. We've worked with IBM (web sphere appserver) and Oracle support. We have about a dozen other environments and it is not reproducible anyway expect production. Thank you very much.
Tom Kyte
January 31, 2007 - 5:01 pm UTC

got code? just a snippet - would like to see an example of the sql and the way you bind.

Alexander the ok, January 31, 2007 - 5:25 pm UTC

It's not very complete but here are a few snippets. One is the code, and the other is the error stack from the IBM guys effort.

<snip>
stat = conn.prepareStatement(SQL);
stat.setString(1, request.getProvider().getTIN());
stat.setString(2, request.getProvider().getTIN());
stat.setString(3, request.getProvider().getName().get_value());
rs = stat.executeQuery();
</snip>
<snip>

// if not, skip storing the request and response
else {
sqlStmt =
new StringBuffer("INSERT into ").append(tableName).append(" (SESSION_ID, SESSION_COUNT, SESSION_TYPE, SESSION_DATE, CREATED_BY, CURRENT_OWNER, CURRENT_STATUS, PIN, SUMMARY, MISC) values(?,?,?,?,?,?,?,?,?,?)");

pstmt = connection.prepareStatement(sqlStmt.toString());
pstmt.setString(1, sessionID);
pstmt.setInt(2, sessionCount);
pstmt.setString(3, sessionType);
pstmt.setDate(4, sessionDate);
pstmt.setString(5, createdBy);
pstmt.setString(6, currentOwner);
pstmt.setString(7, currentStatus);
pstmt.setString(8, pin);
pstmt.setString(9, sessionSummary);
pstmt.setString(10, sessionMisc);
}
if (logger.isDebugEnabled())
logger.debug(">>sql: " + sqlStmt.toString());

pstmt.setQueryTimeout(DEFAULT_QUERY_TIMEOUT);
pstmt.executeUpdate();
</snip>
****************************************************

......
[1/15/07 13:59:57:308 EST] 000000a0 WSRdbManagedC 3 Matching Prepared Statement found in cache: oracle.jdbc.driver.T4CPreparedStatement@5ab23cf3
...
[1/15/07 13:59:57:308 EST] 000000a0 WSJdbcPrepare > <init> Entry
oracle.jdbc.driver.T4CPreparedStatement@5ab23cf3
com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@3792fc9d
DEFAULT CURSOR HOLDABILITY VALUE (0)
PSTMT: INSERT into EBR.SESSION_CR (SESSION_ID, SESSION_COUNT, SESSION_TYPE, SESSION_DATE, CREATED_BY, CURRENT_OWNER, CURRENT_STATUS, PIN, SUMMARY, MISC) values(?,?,?,?,?,?,?,?,?,?) 1003 1007 0 0 0 ....
[1/15/07 13:59:57:308 EST] 000000a0 WSJdbcPrepare 3 current fetchSize is 10
...
1/15/07 13:59:57:308 EST] 000000a0 WSJdbcPrepare 3 setString #1
[1/15/07 13:59:57:308 EST] 000000a0 InternalOracl 3 string length: 36
[1/15/07 13:59:57:308 EST] 000000a0 WSJdbcPrepare 3 setInt #2
[1/15/07 13:59:57:308 EST] 000000a0 WSJdbcPrepare 3 setString #3
...
[1/15/07 13:59:57:310 EST] 000000a0 WSJdbcPrepare 3 setString #10
[1/15/07 13:59:57:310 EST] 000000a0 InternalOracl 3 string length: 0
...
[1/15/07 13:59:57:310 EST] 000000a0 WSJdbcPrepare > executeUpdate Entry
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement@382d3c9d
...
[1/15/07 13:59:57:311 EST] 000000a0 WSJdbcPrepare < executeUpdate Exit
Exception
[1/15/07 13:59:57:311 EST] 000000a0 GenericDataSt > mapExceptionHelper Entry ...
[1/15/07 13:59:57:312 EST] 000000a0 OracleDataSto > findMappingClass for : Entry
java.sql.SQLException: ORA-01008: not all variables bound

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java(Compiled Code))
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java(Compiled Code))
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java(Inlined Compiled Code))


Tom Kyte
February 01, 2007 - 12:56 pm UTC

I do not see anything obvious.

Is this representative of the code in general? That they:

a) prepare
b) bind
c) execute
d) close

over and over - could they

if (first_time)
then
a) prepare
end if
b) bind
c) execute

/* d) close <<<=== never */

instead?

A reader, February 01, 2007 - 1:16 pm UTC

Yeah we are pretty confident it is not a problem with the code because it was tested very throughly, and it works on a bunch of other environments. But to answer your question, yes I think that is how it is typically written. I also think if they did not close, we might start hitting max # of cursors.

I was hoping you might have heard of something like this in your travels because it's so obscure in nature, it's impossible to look up anything about it. We are thinking somehow it's grabbing a bad connection, or something is corrupt, somewhere a line is geting crossed type of thing.

Anything thoughts about what to look at? Did you get a chacne to look at the other questions I was wondering about? Thanks a lot.
Tom Kyte
February 01, 2007 - 1:26 pm UTC

(it is a problem with some code somewhere - regardless of how many places you have tested...)

if they did not close, they would well - run faster. There must be some set of cursors this load program should just open once and have there.


the "connection pool tracing" lets you trace connections from a connection pool. dbms_monitor is used.

the 1008 might be fired in the client before it is transmitted to the database, it would know then that "something is wrong" - insufficient binds. Meaning, it might not be traced in the server trace

Alexander the ok, February 05, 2007 - 11:07 am UTC

Tom, I forgot to mention that recycling one of the RAC instances temporarily fixes the problem. Before we discovered this, the problem was persistent, but since we recycled the instance it only happened once. Doesn't that lead you to believe that the problem does not exist in the code? You seem very confident that it is a coding problem, as opposed to a bad connection or whatever. Just wondering how you can be so sure and if you were a betting man, would you bet on it. Thanks.
Tom Kyte
February 05, 2007 - 11:18 am UTC

I said with "some code somewhere" - could be the jdbc driver.

bouncing the instance just temporarily made the problem not manifest.

Websphere has a cache of Prepared Statements

Stew Ashton, February 06, 2007 - 5:35 am UTC

Websphere Application Server (WAS) provides a cache of Prepared Statements. When the application says

a) prepare

the underlying code says

if (first_time)
then
a) prepare
end if

This is what is meant by the following line of the trace file:
Matching Prepared Statement found in cache:
Also, the "close" doesn't really close the cursor, it just frees the cache element for the next user. I believe all this is now defined in JDBC 3.0. By the way, I recently learned that the cache should be configured to the number of discrete statements multiplied by the maximum number of pooled connections.

We had this same symptom a few years ago, but it was definitely our fault: we were using the same piece of java code with different statements and we were not clearing out the bind variables that were no longer needed. As a shot in the dark, you might try using the method
clearParameters
before doing the sets. By the way, you snipped out the lines that would have "proved" that the proper number of variables were bound.

As an aside, you are using the default fetch size of 10, whereas Tom recommends 100. I don't suppose it matters here, but for SELECTs using 100 will definitely speed things up.

Alexander the ok, February 20, 2007 - 1:42 pm UTC

I didn't notice your followup until now Stew, thank you. We are still having problems so maybe this will help.

I came back to ask Tom if maybe I could use the technique he used here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9497064796920

(at the bottom, the statement that was parsed, but invalid) To find thesql that is failing? Or do you think it's not even being parsed in my case? Thanks.

connection pooling,

A reader, March 14, 2007 - 6:18 pm UTC

As a DBA how to find out whether connection pool exists for a database? How many connections the pool has for various users?

I see in one of our database there were hundered's of process logging in to the database. Just wondering is that because of connection pool.

Thanks,


Tom Kyte
March 15, 2007 - 7:43 am UTC

connection pools do not "exist" for a database.

connection pools are entirely an application server sort of phenomena.

you might be able to see that an application server has one by looking in v$session and seeing lots of sessions using the same username, that have been logged in for a long time, and many of them are inactive.

the application server establishes N connections to the database, keeps them open for a long time, and uses them as needed (not all of the time)

so, lots of sessions
with long connect times
that are idle (usually) much of the time


but, as a DBA, one would certainly hope you understand the clients connecting to the database, what the application infrastructure is and you would obviously just know if the application used a connection pool or not.

A reader, March 23, 2007 - 1:30 pm UTC

Tom:


I am trying to simulate ORA-04031 error.However I cannot on my small instance of 100M shared pool. After a while my sqlarea gets flushed. I am wondering how can we get ORA-04031 error?

Thanks,
Tom Kyte
March 26, 2007 - 7:06 am UTC

add lots of users so that the flushing of the shared pool is not so straightforward.

Close All Cursors

Yogesh Purabiya, June 04, 2007 - 7:16 am UTC

Oracle 8.1.7 - Java 2 (JSP)

Is there any way to close all the open cursors of a session ?

We are using java stack for connection pooling. When some web page pushes back the connection, we want to close all the open cursors.
Tom Kyte
June 05, 2007 - 8:40 am UTC

no you don't.

A program will open then when it needs them, close them when finished.

A program might never finish with a cursor (that would be a good thing, when you close, you have to reparse to open again!)

If program is leaking cursors and hitting max open cursors (because it opens them and loses the handle to them), you have what is known as "a bug in the developed code" that needs to be fixed.

Closing All curosrs of a session

Yogesh Purabiya, June 06, 2007 - 5:43 am UTC


Thanks for the reply.

We have to close all the cursors of a web-page when its processing is over - is that right ?

If so, we need to close all of them at the end, without any other thought - is that right ?

If so, how to do that - that is what I want to know.

By the way, I need your help for one more point - even if I use "ResultSet.close();" in JSP - the v$Open_Cursor view shows the cursors. What can be the reason ?
Tom Kyte
June 06, 2007 - 1:42 pm UTC

you have to close cursors (like files and any other resource you allocate in your well written, professional code) when you are done with them.

are you totally done with them at the end of each call (answer is not necessarily YES, it many times is NO). If so, close them.


You write software
Writing software involves.... THOUGHT



RE: Close all cursors

Stew Ashton, June 06, 2007 - 4:37 pm UTC


Yogesh, may I ask what piece of code is managing your connection pool? Is it a J2EE Server (Weblogic or Websphere)? Or is the application calling the JDBC driver directly? What versions are you using?

The reason why I ask is that connection pool handlers may also have PreparedStatement caches, which can keep the cursors open even though you "closed" them.

You do realize that when you "close" a pooled connection, the connection is not really closed, it is simply released back into the pool. In the same way, "closing" a PreparedStatement may simply be releasing it back to a cache where it can be reused, in which case the cursor is still open. This could explain why you see the cursor in v$Open_Cursor.

"How" to do this right depends entirely on whether you have such a cache available or not.
Tom Kyte
June 06, 2007 - 9:32 pm UTC

all goes back to knowing what you are doing, writing good code requires a bit of THOUGHT (and knowledge and experience and ......)

sigh.

JSP & (Oracle) Cursors

Yogesh Purabiya, June 07, 2007 - 12:24 am UTC

Thanks Tom !
And, thanks Stew Ashton !

We are using Apache Tomcat 5.5.16, Sun J2SDK 1.5.0 and Oracle 8.1.7

We have made our own Coonection Pool - using Java Stack at Application Level.
We do not close the connections - we push connection from and pull the connection to the Application.Stack

JSP is calling PreparedStatement direcly - using connection
Class.forName("oracle.jdbc.driver.OracleDriver"); and
conn = DriverManager.getConnection("jdbc:oracle:thin:@:<ip-addr>:<Port-No>:<OraSID>", "<user_id>", "<password>");

After getting the required data we give

ResultSet.close();
PreparedStatement.close();
conn.commit();
connectionPool.pushConnection(conn);

But the v$Open_Cursor shows them in the list. We have checked the v$Open_Cursor.SQL_Text against the JSP as well.
Tom Kyte
June 07, 2007 - 2:42 pm UTC

and if the application keeps running, does v$open_cursor for that session keep getting filled and eventually error out.

it is normal to see things in v$open-cursor (plsql and perhaps even your jdbc driver - if it is used correct!) CACHE them on purpose.

have you identified that you are seeing a "cursor leak" that results in a program failure.

JSP & (Oracle) Cursors

Yogesh Purabiya, June 07, 2007 - 12:31 am UTC

To be precise - we give

<ResultSet>.close();
<PreparedStatement>.close();
conn.commit();
connectionPool.pushConnection(conn);

RE: JSP & (Oracle) Cursors

Stew Ashton, June 07, 2007 - 7:40 am UTC


Yogesh,

If you have written your own connection pool, write your own PreparedStatement cache too. Look at the JDBC 3.0 specification to see what interfaces they provide, it will give you a first idea of what needs to be done.

There is also a brief discussion of this elsewhere in AskTom. See
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:249532300346363309#253968200346993803
and what follows. Good luck, because I think you will fail. If you had a real J2EE Server (not Tomcat) or a more recent JDBC driver, they would do this for you. Sorry, I don't know what drivers might work with Oracle 8.1.7.

Tom, yes, this merits an n-tier sigh.

JSP & (Oracle) Cursors

Yogesh Purabiya, June 08, 2007 - 1:02 am UTC


Thanks to Tom and Stew !

(1)if the application keeps running, does v$open_cursor for that session keep getting filled and eventually error out ?
--- Yes

(2) it is normal to see things in v$open-cursor (plsql and perhaps even your jdbc driver - if it is used correct!) CACHE them on purpose.
--- I knew it is normal about PL/SQL - but about JDBC ? It's new for me.

(3) have you identified that you are seeing a "cursor leak" that results in a program failure ?
--- Yes. (e.g., use of try/catch/finally, etc.)

(4) If you have written your own connection pool, write your own PreparedStatement cache too. Look at the JDBC 3.0 specification to see what interfaces they provide, it will give you a first idea of what needs to be done.
--- The term "PreparedStatement cache" is new for us. Need to study further.

(5) There is also a brief discussion of this elsewhere in AskTom
--- will surely go througth it

(6) <New Point>

I came to know about v$Open_Cursor from this page only.
While viewing the data therein, I saw cursors for inser / update / delete statements as well. I was aware of cursor for select statement only.

(6.1) The cursor points to the resultset in case of select statement - Am I right ?

(6.2) What do the cursors for other statements point at ? Is it No. of Rows affected ? (SQL%Count)

Thanks again.
Tom Kyte
June 09, 2007 - 10:23 am UTC

1) your program has a leak then, what you say you are doing - IS NOT BEING DONE somewhere by someone.

2) jdbc can and does do statement caching (jdbc 3.0 introduced this) as mentioned above.

3) you are missing some, look at the sql in v$open cursor and prove that the coder is actually closing it - you will find - they are not.

4) given you use 8.1.7 that is not surprising :)

6) the 'cursor' points to sql, all sql is 'a cursor' in effect.

JSP & (Oracle) Cursors

Yogesh Purabiya, June 11, 2007 - 4:55 am UTC

Thank you for the detailed reply.

We could find the error / mistake on our side.
And, your reply (the 'cursor' points to sql, all sql is 'a cursor' in effect) also supports what we have found.

We thought cursor is associated with the ResultSet; but, recently, during debugging, found that it is associated with the Statement (PreparedStatement). Your reply has also confirmed it.

The code previously given by me was a simplified version. Actually we were closing PreparedStatement only at the end of the JSP.

(i) prepare statement
(ii) execute
(iii) fetch-phase - get the result-set
(iv) close the result-set
(v) close prepared statement

The last step (v) - closing of statement - was executed only after the last - 3rd - statement execution; though we were closing result-set after each and every fetch-phase in step (iii).

If there are 3 SQL statements to be executed, we would close the result-set all the three times; but close the Prepared-Statement after the the third (last) statement.

Closing the statement after each and every fetch-phase has solved the problem.

Every time we did

pStmt = conn.prepareStatement (query);

a new (instance of) statement was created; but the previous one remained open. Thus, only the last one got closed.

Thanks for the kind support !
Tom Kyte
June 11, 2007 - 10:35 am UTC

... Closing the statement after each and every fetch-phase has solved the problem. ....

ugh, some days I just feel like giving up....

out of frying pan
right into the flame



What about stored procedures?

Stew Ashton, June 11, 2007 - 11:51 am UTC


Tom, trying to learn something from this :)

Suppose I am a DBA trying to "protect" my data from programmers intent on executing the database rather than SQL statements: what would happen if I just wrapped the SQL in stored procedures and only let the programmers use them? (Now where have I heard that idea?)

1) PL/SQL would open / close / cache the SQL cursors properly, as long as I configured the cursor cache correctly, right? Should I define the cursors at the package level?

2) I would have to return REF CURSORS from SELECTs and there would be more "softer" soft parses; how much impact could this have on the database? Again, would it help to define the REF CURSORS at the package level?

3) Suppose the programmers opened, executed and closed the "begin stored_proc; end;" statement every time? How badly would this hurt the database? I don't really know what work is involved in the "parsing" of a call to a stored procedure...
Tom Kyte
June 11, 2007 - 3:59 pm UTC

I hate 'table apis', they are a waste, mostly what they accomplish is to double the number of SQL modifications in the shared pool - for every insert/update/delete there is a begin/end block now!!


1) sure, BUT THE PROGRAMMER still has to do the right caching on their side, you cannot fix a parse problem with a table API.

If the programmer parses, binds, executes and closes a call to a stored procedure 1,000 times to insert 1,000 rows - plsql caching that single insert won't make a difference (you still have 1,001 parses!!!)

2) package or not, it would be the same.

3) as bad as an insert or update or anything would be, parsing is parsing.

More (or less) about stored procedures

Stew Ashton, June 12, 2007 - 3:04 am UTC


Your answer seems very clear; now I'm trying to grasp the implications for good application design.

Suppose I'm a programmer trying to do the right thing: I minimize parsing through connection pooling, statement caching and bind variables; also, when I can I fulfill requirements with single SQL statements rather than procedural code.

I gather from your answer that when a single SQL statement suffices to "do the job", it would be better from a performance and scalability standpoint to just execute the SQL from the program. If I wrap it in a stored procedure, I will parse more (especially for SELECTs, due to ref cursors) and I will basically double the number of cursors.

On the other hand, stored procedures allow for better security, better instrumentation, more reuse, easier maintenance, etc.

So is it possible to come up with general guidelines about when to used stored procedures and when to use straight SQL? I was leaning toward generalizing the use of stored procedures and now I'm not so sure.

I do realize your answer dealt specifically with stored procedures that just wrap SQL statements without adding value, but if I generalized stored procedures I would have some of those, wouldn't I?
Tom Kyte
June 12, 2007 - 9:59 am UTC

If I had my way - there would be a law against using SELECT, INSERT, UPDATE, DELETE and MERGE in any language other than....

PL/SQL.

period.

Connection Pool and Global Temporary Table

Arindam Mukherjee, July 18, 2007 - 12:28 am UTC

Sir,

Oracle 10g database connection is dedicated and there is one middle tier (JBOSS) using connection pool with 5 connections every time. I have created one global temporary table with "ON COMMIT PRESERVE ROWS". Using web site (Intranet) data is inserted into that table through PL/SQL Procedure and displayed on the screen through JSP. If the user clicks the same button again for that function - data insertion and display, data gets double in that temporary table.
My conception is that connection gets back to connection pool after every event and again web page gets connected to database when the web page sends request for database connection. So the number of record can never be double in the global temporary table as it is session specific.
But my concept is wrong in this case even connection.close() is issued explicitely. Could you kindly help me get this dilemma?

Tom Kyte
July 18, 2007 - 10:12 am UTC

when you use a connection pool, you cannot rely on anything being the same from page to page - think about it

a) you grab a connection, it uses session 1
b) you do something
c) you give the connection back
d) you grab a connection again for the next page, it uses session 2


You cannot use "on commit preserve rows" when you use a connection pool like that, you cannot use plsql global variables, you cannot use anything that is related to 'session state' across connection pool grabs.

Just think about how the connection pool is implemented (designed to be SHARED). If you are sharing, you cannot use a resource like that.

Connection pooling when database goes down

Maya, September 12, 2007 - 12:30 pm UTC

We implemented connection pooling in our web application. It looks like we have to restart web server every time we restart the database, otherwise users can not connect to the application because all the connections in the connection pooling are gone. Any ideas on how to re-establish database connections without restarting web server?
Thank you.

Tom Kyte
September 15, 2007 - 4:55 pm UTC

who makes your connection pool, they would be the one to ask....

Appears to be some form of Race Condition occurring

Barry Chase, September 30, 2007 - 5:45 pm UTC

I am trying to understand what is causing a current issue we have... poor implementation or bug.

We have a frontend .Net application which receives XML transactions from an outside vendor. These transactions are then parsed for values and then passed to an Oracle Stored Procedure within a package. This procedure makes some additional calls to other packages in the system to process the data. There are local variables and package spec level variables in play. The environment utilizes connection pooling. Ultimately the data is then applied to our HR Payroll system which is an Oracle applications environment on a 10gR2 database. The ODP.net in play currently is a version 9i driver.

What is happening is that when multiple transactions arrive within a minute or two of each other, they will sometimes bleed over into the other somehow... this results in erroneous processing and lot of rework to correct the improper processing of the inbound transaction.

We have been able replicate at will the problem also. Our temporary band-aid is for this particular inbound transaction set, we have turned off connection pooling. In the end we would prefer that it remain on.

So if poor implementation... what things could cause this behavior... if bug... anyone know of issues around connection pools and 9i driver and 10g databases ?
Tom Kyte
October 03, 2007 - 2:23 pm UTC

does your .nyet application make

a) one call to a stored procedure that does everything from start to finish

or

b) a call to one procedure and then
c) another call to another procedure that relies on the state setup by b)



I suspect you are doing b) and c) and therefore:

.... and package spec level variables in play. ...

would be problematic, as the developers probably

1) grab connection, run proc1, ungrab connection
2) grab connection, run proc2 - which can be in another session, ungrab connection

Calling single stored proc from .Net

Barry Chase, October 05, 2007 - 6:00 am UTC

.Net application makes a single call to a single stored procedure which internally routes to subroutines (other packages) until completed.

In a nutshell...

Vendor sends transaction containing Candidate information to an XML Transaction Engine (custom built). This receives the XML transaction set and parses out the identified parameter values. It then passes those values to an Oracle Stored Procedure called PROCESS_APPLICANT_FEED in a core applicant tracking package. This package validates those parameters to ensure we are not getting goofy data. Validation routines exist in a validation package which is maintained separately from the core package. Once past the validate routines, then it determines appropriate routing and sends the transaction data to the desired package code pertaining to the type of candidate... e.g. Newhire, Rehire, Transfer, Contractor to Hire, etc. Each of these respectively are separate packages, but only one is used. Once completed with that routed process, we return control back to the core utility to finish out the process and respond to any collected user defined warnings/notifications/errors by generating the necessary email to support personnel.

The process has been remarkably successful albeit for this one 'feature'. We have temporarily resolved the issue by turning off connection pooling for this particular transaction... until such time we resolve the issue

connection pooling and failed_login_atttempts

Ana, November 05, 2007 - 11:54 am UTC

Failed_login_attempts that defaults to 10 in 10g and above might need to be increased or accounts lock down pretty soon as one real failed login attempt translates to several attempts from the pool.


Connection Pool and Package Global variables

Shaji, May 02, 2008 - 2:35 pm UTC

I was going through the queries and your answers on the connection pool and was intrigued and thought of asking you this(Please don't consider as a new question and delete it). I am using a global package variable (with a default value) in of the PL/SQL APIs and it gets concatenated during the process(one of the package procedures does this). BPEL invokes this API as part of the web request. Everything is hunky dory until our performance testers pictched in. The test script they created, does the following

Loop 1000 times
1. Open browser
2. Call the Web Page
3. Feed the data
4. Submit the page
5. Close the browser
close the loop

When it reaches the 5th iteration, the global variable reaches its limits and throws PL/SQL numeric value error.
My understading was, each time the browser is open, from the connection pool, it picks up available connection and establishes a new session and invokes the PL/SQL API and reset with the default value and concatenates down the line.

Was my understanding wrong with connection pool?
Tom Kyte
May 02, 2008 - 3:08 pm UTC

you have totally misunderstood a connection pool

the entire goal/concept of the connection pool is to REUSE sessions, not create a new one.


mod_plsql is different, we programmed that one with a "reset session state" call by design, but a java connection pool - they just keep the sessions open and let the stuff hang about as it may.

Connection Pool and Global variables

Shaji, May 02, 2008 - 3:55 pm UTC

Thanks for pointing to right direction. Could you provide any link to document relating to connection pool?
Tom Kyte
May 02, 2008 - 4:05 pm UTC

you'd have to read the documentation provided by the vendor of your connection pool - there is no "connection pool standard and set of rules to follow"

suffice to say - with the exception of mod_plsql - every connection pool I've seen caches sessions - they connect and logon to the database - and then stay there, keep that session and session state forever, until they close that connection in the pool (if they ever do, nothing says they will)


Shared Server vs App Server Connection pool

Andrew Markiewicz, May 05, 2008 - 2:41 pm UTC

Tom,
What are the advantages/disadvantages to using one type of connection pooling over another? Specifically, the shared server connection compared to an OAS java connection pool.

Also, in the 10g DB Concepts document describing the shared server connection it states this:

"All session-related information is contained in the SGA. Each shared server process needs to be able to access all sessions' data spaces so that any server can handle requests from any session. Space is allocated in the SGA for each session's data space. You can limit the amount of space that a session can allocate by setting the resource limit PRIVATE_SGA to the desired amount of space in the user's profile."

If I understand that correctly, it seems under an Oracle shared server connection, the dispatcher somehow keeps track of what session you are when you make a request and all the shared server processes can access each individual session state since the variable states are now in the SGA instead of the PGA.
Doesn't this give you the best of both worlds, a "stateless" web request while being able to maintain package variable session state? Or am I understanding that incorrectly.


Tom Kyte
May 06, 2008 - 12:50 am UTC

... Specifically, the shared server connection compared to an OAS
java connection pool.
..

well, that is like comparing apples to flying toaster ovens in a way. In a middle tier application - you almost certainly want to use a connection pool in order to minimize the effect of connect/disconnect - to remove it in fact.

The shared server configuration was introduced to allow you to squeeze on a few more concurrent users in a client server configuration. Shared server introduces considerable overhead to the process of performing sql (if you want the details on why that is, Expert Oracle Database Architecture spends some time on it). In short, shared server executes a lot more code to get the work done - the client sends a request to a dispatcher, dispatcher puts it in queue in SGA, shared server finds request, processes request, places response back in queue, dispatcher sees response and reads it off queue and returns it. As opposed to dedicated server whereby the client sends request to dedicated server, it reacts, gets answer and sends it back.

Now, you might use both at the same time (doubtful - but possible). If you have say 10 application servers and each has a connection pool of 300 (for 3,000 connections) you might find that is just too much - also you might find that at most it appears that 50 of the connections are active. So, you could configure shared server with say between 50 and 100 shared servers - reducing the process count on the server from 3,000 to 100 (albeit at the same time introducing a larger code path for each request but since the machine is so less loaded with processes - the overhead of the extra code is more than offset)

and into the future with DRCP (database resident connection pooling) - we'll find this to be more efficient as DRCP removes even the overhead of the shared server code while providing the same process reduction (available for PHP right now, other connection pools in the future)

@ Andrew re: stateless vs. stateful

Stew Ashton, May 06, 2008 - 6:02 am UTC


"Stateless" is all about reusing resources in order to scale at least cost.

In a typical connection pool, the Oracle "user" is generic and does not represent the actual user; this allows maximum reuse of the connection itself and of the Oracle session.

I believe the "cheapest" way to start having state is to "use the CLIENT_IDENTIFIER predefined attribute of the built-in application context namespace, USERENV, to capture the application user name" and use it with a global application context.
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#DBSEG33101
To go further, you would need to setup proxy authentication, which would actually create different sessions for each user. The 11g JDBC guide talks about proxy authentication with connection caches, but it looks like pretty advanced stuff to me. In any case, it would require more memory and the server would spend more time creating, switching and dropping sessions. Also, there would be more soft parses since each session has to parse every SQL statement "the first time".

Very interesting subject, though; imagine doing Web pagination just by fetching from an open cursor like client/server.

Legacy code

Andrew Markiewicz, May 07, 2008 - 9:53 am UTC

I can see that an app server connection pool and shared server serve different purposes. I suppose it depends on the type of app you are writing, external web app for potentially millions of users or enterprise applications for a known number of users.

My inquiries about shared server stem from the issues I see with the stateless web application and legacy enterprise applications written in plsql relying on state of those packages. It doesn't make for an easy transition (without having to recode to store that package data persistently) if you suddenly need a web app but can not depend on the state of packages. With the shared server storing state in the SGA, I was wondering if that would be a viable option for enterprise intranet applications. You could get the stateless web interface format, but retain the package session state on the database.

Tom Kyte
May 08, 2008 - 2:51 am UTC

you would not get a stateless web interface, you would have a STATEFUL web interface.

You would need to KEEP the connection to the database from start to finish, you would have a session in the database from beginning to end.

with a stateless environment, you only have a session in the database while you are generating a page - then you give it up.

if you wanted to maintain a state in a plsql package, you would have to have a STATEFUL connection to the database - you would NOT give up your connection, your session (regardless of whether it was shared server or dedicated server). You would be exactly like a client server application was in the past.

Not quite clear

John Davey, June 30, 2009 - 10:17 am UTC

Tom

I'm not quite clear about the impact of connection pooling. Either that, or I am clear and just disappointed.

We have an app running on Tomcat that is pooling two database sessions. That number is low currently as we are under development.

I want to create a view based upon the application user identity (ie not the database user) via a call to sys_context. So my user requests a look at the view, and displays it to the web page. That is all they do, in a single packaged procedure call. The first action of the packaged procedure call is to set the application context via a call to DBMS_SESSION.set_application_context.

However it is incredibly important that each user strictly sees the data it is entitled to see. What gives me the creeps about this approach is that it is not obvious to me that my entire packaged procedure call will be allowed to complete before the connection is reused for another purpose. That raises the possibility that other users may be able to see th wrong data. Or am I getting the relationship between sessions and and connections completely wrong ?

Am I right or wrong about the risks here ?

The alternative seems to be to use a global application context. This would appear to be a bit of work but is eminently achievable.

However as we are unlikely to have more that five - maybe ten concurrent users - although over a hundred separate identities in all - I am wondering whether the easiest approach is to just dump connection pooling and use dedicated thin client connections with timeouts.

Our hardware is stacked - 64GB of memory, most of which is not used.

What do you think ? Am I getting it wrong about the risks, and if not why not use dedicated connections even if it's a web application ?

Regards
John Davey


Tom Kyte
July 06, 2009 - 6:46 pm UTC

it is totally in the control of........

the application, the developer of the application controls this.


If the application developer codes:

start page generation code

grab connection from pool
call dbms_session
ungrab connection from pool

... more code in application

grab connection from pool
select ....
ungrab connection

finish page generation code




then the fear you have is founded. If on the other hand the developer codes:



start page generation code

grab connection from pool
call dbms_session

... more code in application

select ....
ungrab connection

finish page generation code



So, since you are using the "single big user with application developer managing identification, authorization and authentication" - you have to rely on the application developer to do it correctly.


The global application context would have THE SAME EXACT ISSUE - the developer must - after each and every "grab a connection from the pool" push the identity down into the database. If they fail to do that even once - just once - all bets are off.

You have made a decision to have the application do the Identification and Authentication/Authorization - you must rely on them to do it correctly NO MATTER HOW YOU CONNECT to the database.


Using dedicated thin clients with their own persistent connection (no pooling) is possible but you are still relying on the application to do the right thing.

They can do the right thing in a connection pool (or not)
They can do the right thing in a stateful connection (or not)

they can fail with both, they can succeed with both. They just need to know what they heck they are doing in both.

John Davey, June 30, 2009 - 10:18 am UTC

" We have an app running on Tomcat that is pooling two database sessions. That number is low
currently as we are under development."

No "sessions", obviously - "connections"

connection pool

Sam, July 09, 2009 - 10:02 am UTC

Tom:

just a few small question on connection pooling using mod_plsql

1. If you have a pool of connections between mod_plsql and database how do those get established.
You must have a database userid/password to connect to database.
Do they use some default account or database assume mod_plsql is a trusted entity for connections.


2. the DAD in mod_plsql has userid/password defined. Does that mean every web page i run (stored proc)
i connect (new connection) to the database using that userid/password. Would not that be expensive on performance.

3. PHP does not support conection pooling. would the application be real slow without it or it depends on the web site traffic? I would assume you would never use a language that does not support connection pooling because you system will not sale well.
Tom Kyte
July 14, 2009 - 2:47 pm UTC

1) mod_plsql pools connections itself. the apache module keeps open a pool of connections. You manage the size and shape of that pool by setting mod_plsql settings in the apache configuration.


You use a DAD (database access descriptor) to tell mod_plsql "how to" connect to the various databases.


2) it MAY have - it doesn't have to have - it may have a user/password.


and it pools connections, it does not create a new connection for each web page.

3) read about DRCP database resident connection pooling. New in 11g, designed for the php space.

http://www.oracle.com/pls/db111/search?remark=quick_search&word=drcp

pool

A reader, August 06, 2009 - 11:38 pm UTC

Tom:

I am still confused on the concept of connection pool in a web environment.

Let us say you have 100 database user accounts. When a user logs in using his db account, should not you use his userid/password to establish a connection.

If you pick it from a pool then thant connection has already been established using some userid/password.

unless you grab any connection and just verify that his userid/password is valid database acccount.

can you explain.
Tom Kyte
August 07, 2009 - 9:36 am UTC

read the documentation looking for n-tier proxy authentication
http://docs.oracle.com/docs/cd/B19306_01/java.102/b14355/proxya.htm#CHDHHAAD

if you wanted to use database accounts, you would likely be using that. the connection pool would have N connections to the database logged in as some "user"

That "user" would have been granted "connect through" to your 100 user accounts.

That user would have a connection and a session established and when the application grabbed a connection from the pool - it would create a new session in that connection - connecting through to the real user - and do whatever it needs and then uncreate that session.

Hence the connection is always there (that is the expensive bit, creating the connection). The "user" that is granted connect through always has a session. And the application would just create a new session in the already established connection under that "user" session.



pool

A reader, August 13, 2009 - 5:29 pm UTC

Tom:

So the 100s of connections would be established using one connect account.

Then when scott/tiger logs in he would grab any connection into the database. Database first verifies the user/password and then establish a new session for "Scott". The database will then enforce all the security assigned to "Scott" for that session.

is this correct?
Tom Kyte
August 24, 2009 - 7:14 am UTC

correct.

mod_plsql

A reader, October 26, 2009 - 10:59 pm UTC

Tom:

1. How do you usually size the correct number for "processes" for a web application.

2. I ran into ORA-000200 with mod_plsql. I look into V$process and there were 200 processes from the web server/mod_plsql.

This never happened before. Could anything happend on the DAD setting or database that might cause processes not to expire and get that ORA-00020 error.

I thought oracle will expire the processes after some time to allow new processes be created.
Tom Kyte
October 27, 2009 - 11:17 am UTC

1) how many processes do you want to have? how many concurrent sessions can you live with? are you using shared server, dedicated server?

It basically boils down to - what will you set sessions to - how many concurrent sessions can you deal with before the machine is toast. How many concurrent sessions do you need to support? If the number of concurrent sessions is really really high - maybe you need to use shared server (to decrease the number of processes needed for so many sessions)


2) You had a flurry of activity. We open a new connection in response to inbound requests.

You can control this

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14190/servproc.htm#sthref205



.... I thought oracle will expire the processes after some time to allow new
processes be created. ...

we would not need to expire them if they are being used and if they are being used we cannot expire them.

suggest you look at that sentence and parse it a bit :) Why would it matter if we did or did not expire a process after some time? Why would we expire an old process to create a new one? Why not just use the old one? Therefore - if you exceeded 200 - you had a flurry of activity (maybe someone hit you with a really nasty web crawler for example, or your application got popular, or your application started running the query from heck and your users got bored and started hitting stop+reload over and over)

mod_plsql

A reader, October 27, 2009 - 1:22 pm UTC

Tom:

Is processes=200 considered low or high? I assume a max of 200 users using the web application.


<<<Therefore - if you exceeded 200 - you had a flurry of activity (maybe someone hit you with a really nasty web crawler for example, or your application got popular, or your application started running the query from heck and your users got bored and started hitting stop+reload over and over) >>>


Is there a way to tell what the actual cause is. I looked into V$process and that shows me 199 processes from web server. Do i need to to look at the web server log to see who originated those?

Your link above is for HTTP server processes. V$process is for the database. Are not those two different things?

The way i understand this is there is a connection pool between MOD_PLSQL and database. Each connection has a corresponding process in V$process.

When someone sends a URL MOD_PLSQL will grab a connection from the pool and hit the database. Let us say all connections are taken and someone hits a URL, then MOD_PLSQL will open a NEW connection and create a new PROCESS. If the process is not used anymore after a few minutes, ORacle will expire it automatically. Is this how it works?

Tom Kyte
October 27, 2009 - 5:10 pm UTC

on a single cpu machine with 512mb of ram, 200 would be high.
on a 16 cpu machine with 32gb of ram, 200 might be low to medium

as with all things sam, aka SMK - it depends (you should actually see that answer "it depends" coming by now?)


... Do i need to to look at the web
server log to see who originated those?
...

that would be a good start if you don't have your own audit trail (if you used apex - you would - but you won't use apex sigh...)


... Your link above is for HTTP server processes. V$process is for the database.
Are not those two different things?
..

think about is sam, if you limit the number of threads the http server will open - before it starts queuing up requests.... that will by definition limit the number of concurrent database requests/connections possible wouldn't it.


... When someone sends a URL MOD_PLSQL will grab a connection from the pool and hit
the database. Let us say all connections are taken and someone hits a URL, then
MOD_PLSQL will open a NEW connection and create a new PROCESS. If the process
is not used anymore after a few minutes, ORacle will expire it automatically.
Is this how it works?
....

modules are run by apache, you control the number of module instances that can be alive at one time. So, you send a url to apache, apache finds a module instance to deal with it and if there are not any AND you gave apache room to create one more - it will - eventually it will hit your limit and it won't be able to create the module instance and it will queue the request - waiting for one of the existing ones to become free.

When you get to the module instance, it'll have it's connection and use that. (hence you control the connections to the database by controlling the number of apache module instances).

And when apache gets bored - it'll kill off some of those module instances and they will in turn of course release their database connection.




Java connection pools

Galen Boyer, October 27, 2009 - 3:11 pm UTC

Just so the readers understand, one of the differences between
connection pooled vs non-pooled in j2ee is what happens on the
connection.close() and DriverManager.getConnection() calls. In
non-pooled, getConnection() established a brand new connection to the
database, and close() actually closes the connection and the session
is gone from v$session. In connection pooled environment, the app
server overrides the getConnection() and close() methods to get a
connection from the pool and return that connection back to the pool.
The close() leaves that connection open, and now available for some
other process to call getConnection(). So, as java is apt to do, code
that runs in non-pooled environment can easily now with just a a jndi
datasource being entered, vhalla, run in pooled environment. So,
isn't this just handy dandy? Portable code!!! Well, in a nutshell,
NO!!!

The problem is the close() in non-pooled environment meant that Oracle
cleaned up all the open resources instead of the programmer, in
particular, the ref_cursors that programmer decided to open. So, the
java developer who has been ignorantly coding by getting a ref_cursor,
doing stuff with that resultset and then closing that connection never
has to learn what he should know, which is a ref_cursor is a resource.
So, this code gets moved into an application server environment, that
close() call now puts that connection back on the pool and then later
on, the database errors out with ORA-01000 maximum open cursor
exceeded.

Hurrah for code portability of java, well, not really.

Once again, database independence has morphed into database ignorance!!!!

mod_plsql

A reader, October 27, 2009 - 7:13 pm UTC

Tom:

<<that would be a good start if you don't have your own audit trail (if you used apex - you would - but you won't use apex sigh...) >>

The applications logs the URLs that hit the DAD which I beleive APEX is doing. However, that requires a valid PL/SQL procedure so the insert happens. If he hits the web server without a valid stored procedure it wont be loged in. Can't he hose the web server that way?

Is not a PROCESS = CONNECTION.

It seems anyone can write a small program with infinite loop that keeps sending HTTP requests to the web server and that would hit the DB processes limit and shut down the database. Is that correct? I guess that is a DOS attack.

How do you protect your site from someone doing this.


Tom Kyte
October 28, 2009 - 8:48 am UTC

Sam/SMK and now xxxxxxx

we've had many discussions about APEX in the past, you and I have.

APEX uses mod_plsql - but provides a huge, comprehensive framework to manage session state, perform auditing, hundreds of things - just so you don't have to reinvent the proverbial wheel over and over and over again.

APEX would control accessing the database in the same way I've told you to - by setting up the http module so as to have only so many concurrent requests. I'm hard pressed here to understand why this is so elusive? configure the module to allow say 50 concurrent requests and guess what? You'll have at MOST 50 database connections from that (web) server.

is not a process = connection

No, it is not. (if you want the entire story, get Expert Oracle Database Architecture).


A connection to the database may or may not have a dedicated process.
A process on the server may or may not have a single connection associated with it.


The relationship between a connection and a process is actually many to many. The answer to "is not a process=connection" is "it depends entirely on the configuration" (as always.

... It seems anyone can write a small program with infinite loop...

will you please read the documentation I pointed you to. For example, this one looks mighty interesting

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14190/servproc.htm#i1005664

<quote src=apache manual>

Syntax: MaxClients number
Default: MaxClients 256
Context: server config
Status: core

The MaxClients directive sets the limit on the number of simultaneous requests that can be supported; not more than this number of child server processes will be created. To configure more than 256 clients, you must edit the HARD_SERVER_LIMIT entry in httpd.h and recompile.

Any connection attempts over the MaxClients limit will normally be queued, up to a number based on the ListenBacklog directive. Once a child process is freed at the end of a different request, the connection will then be serviced.
</quote>

mod_plsql

Sam, October 28, 2009 - 8:34 am UTC

Tom:

I appreciate the feedback. I will read the link in more depth.

Can you take out the first line of your last response for privacy concerns.

Thank you.
Tom Kyte
October 28, 2009 - 8:49 am UTC

done.

processes

A reader, October 30, 2009 - 8:15 pm UTC

We upped this to 500 and we had same issue yesterday.

strange we did not have any change to production database for a while.

I do not have access to web logs. I can only see the oracle views.
Would i be able to tell from those who is doing what and what actually caused as it could be intentional thing.
Would V$SQL tell me the SQL sent to the DB? Could be a config change to ther HTTP web server?

Is there a permanent fix for this to prevent it?

The thing I do not understand is if you run a procedure it would be served by a process and that is it.
The process should be able to server new requests. Why is it telling the next user you cant connect.
Is the process supposed to go away or stay there waiting for clients to serve.

apache

A reader, November 01, 2009 - 7:21 am UTC

Tom:

I read what you said a 3rd time, and it finally sinked in. You are a Genius.

I think the problem is at the HTTP server. Someone might have changed something there.

WOuld not this problem happend if Apache does not release the database connections??? What would cause apache to do that?

OR

The maxclients directive is set to large number (say 1000) and oracle processes is set to 200. this will cause this issue would not it. They should be in sync together?

DO you agree?

Is there a way to see what those config variables set at in database or via PL/SQL?


<<<And when apache gets bored - it'll kill off some of those module instances and they will in turn of course release their database connection.>>>


<<<The MaxClients directive sets the limit on the number of simultaneous requests that can be supported; not more than this number of child server processes will be created. To configure more than 256 clients, you must edit the HARD_SERVER_LIMIT entry in httpd.h and recompile.

Any connection attempts over the MaxClients limit will normally be queued, up to a number based on the ListenBacklog directive. Once a child process is freed at the end of a different request, the connection will then be serviced.
</quote> >>>

Tom Kyte
November 09, 2009 - 12:04 pm UTC

... I think the problem is at the HTTP server. ...
that is sort of what I've been saying, yes


... WOuld not this problem happend if Apache does not release the database
connections??? ...

apache is not supposed to release them, but, if you have 10 apache servers that are allowed to have 300 connections, you will have to be able to support 3,000 connections and their processes.

apache is supposed to keep the connections
you have to configure apache to not have TOO MANY connections, more than you can handle.

pool

A reader, November 08, 2009 - 10:31 pm UTC

Tom:

I hope you enjoyed your vacation last 10 days!

These is part of V$session at 2:00 PM that shows some http sessions still active. I have more of those.
But i want to get you the idea of the problem. These are simple web pages.
1. DO you think MOS_PLSQL is not restting state or some bug somewhere?
This is part of what is causing the maximum number of connections be exceeded.

12710392 34 23 06-nov-2009 11:15:43 ts8 NSSADMIN ACTIVE oraS10AS httpd@ts8 (TNS V1-V3)
1859860 18 9 06-nov-2009 11:15:59 ts8 NSSADMIN ACTIVE oraS10AS httpd@ts8 (TNS V1-V3)
7053730 35 8 06-nov-2009 11:16:09 ts8 NSSADMIN ACTIVE oraS10AS httpd@ts8 (TNS V1-V3)

2. **MAny inactive sesions stay there for hours. Why MOD_PLSQL is not cleaning up every 15 minutes.

10260896 187 8 06-nov-2009 13:05:04 ts9 SEGADMIN INACTIVE oraS10AS httpd@ts9 (TNS V1-V3)
13660548 189 8 06-nov-2009 13:05:04 ts9 SEGADMIN INACTIVE oraS10AS httpd@ts9 (TNS V1-V3)
10621338 188 7 06-nov-2009 13:05:04 ts9 SEGADMIN INACTIVE oraS10AS httpd@ts9 (TNS V1-V3)
12591534 190 12 06-nov-2009 13:05:06 ts9 SEGADMIN INACTIVE oraS10AS httpd@ts9 (TNS V1-V3)
10637494 191 7 06-nov-2009 13:05:06 ts9 SEGADMIN INACTIVE oraS10AS httpd@ts9 (TNS V1-V3)

3. Can you me about your own settings for MOD_PLSQL and Apache. We are using mostly default settings which was working until now.
Tom Kyte
November 11, 2009 - 2:14 pm UTC

sam/smk

you might want to check the calendar on asktom.oracle.com in the future. That vacation you talk of included six cities in six days over 13 flights from Norfolk VA, to Edmonton Canada, to St Louis MO, to Richmond VA, to Birmingham AL, to Toronto Canada to back home...

Yes, I did actually take the weekends off.


1) we've been over this so many times, I give up. What you see is NORMAL, what you see is EXPECTED. What you see is what you want to see.

A web server like apache will cache modules (mod_plsql) so it does not have to start/stop them over and over and over again.

What you see is that apache started some modules - and right now - they are inactive waiting to get a request.

No, I will not give you my settings - why not? Not because I'm mean - but because YOU ARE NOT RUNNING ASKTOM, you are not running apex.oracle.com which hosts asktom. If you were - then my settings would make sense. but you are not.


I've tried to tell you I don't know how many times - you need to configure your apache instances to not create so many modules, I've pointed you to documentation, I've put it in my own words - all to no avail. I'm done.

mod_plsql

A reader, November 30, 2009 - 3:25 pm UTC

Tom:
Thanks for you response.
I see your point about changing the config parameters for apache but still this might not be a permanent solution.

The apache web server (one SSL port) is used for numerous databases (dev, test, production) using mod_plsql.

Even if you config apache to allow 400 connections only to match the 400 nubmer of processes for the one problematic production database, new requests will be queued by apache. Basically URLS will be getting an apache error message instead of an oracle error afterwards after the apache connection limit is reached.

I have a strong feeling that when the web server is shut down without bouncing the database, this problem occur.
If you have 300 inactive http session showing in oracle and someone shuts down the web server and restarts it, would apache clean up those. I think those stay there and the cleanup thread is not cleaning those becaue the web server was restarted.

Do you think that *might* be the cause and do you agree with my comment on changing apache configuration??



Tom Kyte
December 01, 2009 - 3:25 am UTC

sam/smk

.. I have a strong feeling that when the web server is shut down without bouncing
the database, this problem occur.
...

why? (you must have known I would say that)

Stop 'feeling', start 'showing'.

If you firmly believe that the sessions you see in your database are orphaned, enable dead client detection.


But, I think you'll find that "they are not", apache would shutdown the modules. they in turn would close their connections.




A reader, December 03, 2009 - 6:33 am UTC

Hi tom,
Ur explanation is so usefull. Can u tell more about connection pool recycling. Where exactly it is there in the Web server code. How to force the connection pooling/recycling old connections.

Thanks
Rajeshkumar
Tom Kyte
December 04, 2009 - 3:00 pm UTC

you would have to refer to the documentation for whatever connection pooling software you are using - the database doesn't have anything to do with how that works in the middle tier.

Connection pool time-out's

Ojas, February 04, 2010 - 3:24 pm UTC

Tom,

Much needed clarity on connection pooling. Thanks for that!

One of our environments is setup with applications on IIS servers hitting Oracle database. While researching some issues on performance, I saw that there were a number of Inactive sessions on Oracle. The obvious reason for this was the connection pool. For most of the Inactive connections, the "Machine" variable in V$SESSION gave the name of the application server machine name that must've initiated the connection.

However, there were plenty of Inactive connections where the machine name was the oracle database machine name. Why would that be? For that particualar DB user, we only used application server for connections. There were two things that came to my mind:

1. If the ASP code from app server calls PL/SQL, does that spool it's own connection? Likely not but I wanted to see what your point of view was.

2. Could it be because of time-out differences between the the app server and db server. The connection timeout on app server is 60 minutes which means that after 60 minutes of inactivity, a connection would be dropped. However, the IDLE_TIME on DB for that Id is set to 120 minutes. Would that cause the application server to drop a connection and DB to still hold on to it for another 60 minutes. If that is the case, are we causing any potential issues in terms of connection availability?
Tom Kyte
February 04, 2010 - 4:38 pm UTC

where they inactive - or just not active (status was what in v$session)? could they be shared servers? parallel execution servers? etc - what was the program associated with them.

Connection pooling

Ojas, February 04, 2010 - 4:47 pm UTC

The status was INACTIVE within v$SESSION.
The program associated was: oracle@<unix server names>(TNS V1-V3)
We do not use Shared server here, I reconfirmed in v$paramater.
And none of the queries I reviewed use parallell hints.

Tom Kyte
February 04, 2010 - 6:32 pm UTC

those are background processes of some sort I would think - the oracle@machine is what we use (our process is called Oracle...)

how many of them? It would not be a time out thing, we assign the machine name when the session is established


print out more of the v$session info, especially the 'type' column

connection pool

A reader, February 04, 2010 - 5:09 pm UTC

Tom:

The above user is talking about the same issue I had except I use MOD_PLSQL (not IIS). That session is for a db link from the schema web server connects to because the direct web server session would show as "httpd@box".

I think apache/mod_plsql keeps those sessions in inactive mode for 15 minutes until they get cleaned up.

I realized that connection pooling in MOD_PLSQL is really built in the unix apache process only.
If you send a request for another page and it is server by P2 it will establish a new connection.

http://download.oracle.com/docs/cd/B15897_01/web.1012/b14010/concept.htm#i1020954

Basically, if the system is inactive overnight, there will be no processes and all the new morning activity will start creating new connections. right.

I assume you would call that connection pooling at the process level (not like a big pool that any process can grab a connection from)



PID SID SER# LOGON_TIME BOX USERNAME STATUS OS_USER PROGRAM
--------- ----- ----- -------------------- ------------ ---------- -------- ------------ -----------
5726934 54 1204 04-feb-2010 16:15:04 ts11 TSSADMIN INACTIVE oraS10AS httpd@ts11 (TNS V1-V3)
7635236 89 485 04-feb-2010 16:15:06 ts11 TSSADMIN INACTIVE oraS10AS httpd@ts11 (TNS V1-V3)
8856084 72 466 04-feb-2010 16:19:55 ts11 TICADMIN INACTIVE oraS10AS httpd@ts11 (TNS V1-V3)
10670124 56 1323 04-feb-2010 16:20:05 ts11 TSSADMIN INACTIVE oraS10AS httpd@ts11 (TNS V1-V3)
7155814 77 617 04-feb-2010 16:30:09 ts11 TSSADMIN INACTIVE oraS10AS httpd@ts11 (TNS V1-V3)
9384544 60 726 04-feb-2010 16:33:30 ts55n TICADMIN INACTIVE oraS10AS oracle@ts55n (TNS V1-V3)
10768440 37 1205 04-feb-2010 16:50:10 ts11 TSSADMIN INACTIVE oraS10AS httpd@ts11 (TNS V1-V3)
6013460 63 241 04-feb-2010 17:03:35 ts11 TICADMIN INACTIVE oraS10AS httpd@ts11 (TNS V1-V3)
7414276 35 838 04-feb-2010 17:04:13 ts11 TICADMIN INACTIVE oraS10AS httpd@ts11 (TNS V1-V3)
7385606 50 537 04-feb-2010 17:11:33 ts11 TICADMIN INACTIVE oraS10AS httpd@ts11 (TNS V1-V3)

Tom Kyte
February 04, 2010 - 6:45 pm UTC

httpd@box is not the same as oracle@box

sam - it is not the same. I tried *every single way* I could think to tell you what was happening, I said it as many ways as I could. You did not follow me then, you will not follow me know, so you and I are done discussing it. Really.


You are not even REMOTELY similar to the person above, not even a little bit.

session

A reader, February 04, 2010 - 7:09 pm UTC

TOm:

you know i follow you all the time.

sometimes, i do not understand what you say - but that does not mean i dont follow what you say !

THanks,

Connection Pooling

Ojas, February 05, 2010 - 10:27 am UTC

I am posting some selected columns from v$SESSION here. Do not know how that will come out but to answer specific questions first:
There are a big number of Inactive sessions like that. Almost the same number of Inactive sessions originating from the database server as the total number of inactive sessions originating from the application servers.

So in all, if we have 130 to 150 inactive sessions at one point, almost half of them seem to be originating from the DB server.
The type for all sessions is set to USER.

COMMAND OWNERID TADDR LOCKWAIT SCHEMA# OSUSER PROCESS MACHINE TERMINAL PROGRAM TYPE
3 2147483644 4497 NETWORK?SERVICE 5824552 DB SERVER NAME oracle@DB SERVER NAME (TNS V1-V3) USER
0 2147483644 4497 NETWORK?SERVICE 4592:712 APP SERVER NAME APP SERVER NAME w3wp.exe USER
3 2147483644 4497 NETWORK?SERVICE 5564:5804 APP SERVER NAME APP SERVER NAME w3wp.exe USER
3 2147483644 4497 NETWORK?SERVICE 2981972 DB SERVER NAME oracle@DB SERVER NAME (TNS V1-V3) USER
0 2147483644 4497 NETWORK?SERVICE 2248:2240 APP SERVER NAME APP SERVER NAME w3wp.exe USER
0 2147483644 4497 NETWORK?SERVICE 3816:316 APP SERVER NAME APP SERVER NAME w3wp.exe USER
0 2147483644 4497 NETWORK?SERVICE 3816:3484 APP SERVER NAME APP SERVER NAME w3wp.exe USER
0 2147483644 4497 NETWORK?SERVICE 3820:808 APP SERVER NAME APP SERVER NAME w3wp.exe USER
0 2147483644 4497 NETWORK?SERVICE 4592:5084 APP SERVER NAME APP SERVER NAME w3wp.exe USER
3 2147483644 4497 NETWORK?SERVICE 4817128 DB SERVER NAME oracle@DB SERVER NAME (TNS V1-V3) USER




Tom Kyte
February 08, 2010 - 7:53 pm UTC

see below, does that make sense - could these be a loopback database link? if so, the answer would be "stop doing that" or "explain why you are doing that"

processes

sam, February 05, 2010 - 11:33 am UTC

Tom:

ORacle@box (CJQ0) = oracle background process

You will not see a username for those.

However,

oracle@box (TNS V1-V3) is a connection using a db link

TO see that, i log in using sql*plus to schema "A" then
i run

select * from dual@schema_b_link

If you see the session in Schema b you will see mine under oracle@boxname in "inactive" status.




Connection pooling

Ojas, February 10, 2010 - 9:51 am UTC

Thanks!

I think that is the reason because a lot of our queries do use database links. The reason is that the application in question uses data from two varied databases. We are working on a parallell ETL project to bring over data from one of the databases on to another but while that happens, I suppose we will need to keep using links.
Tom Kyte
February 16, 2010 - 8:03 am UTC

alter session close database link - in the session that uses them when it is done, would clean them up if you wanted - OR - you can use database link concentration via shared server to have a pool of them to be used and reused (both would be 'nicer' on the remote system, would either remove the session from the remote system OR have a finite, small pool of them)

connnection pool

Sam, February 16, 2010 - 10:45 am UTC

Tom:

That is interesting comment you made about closing db links!

Would this also apply for pl/sql stored procedures called/invoked by mod_plsql.

shall i add this statement at the end of the program to remove any sessions created by db links.

alter session close database link
Tom Kyte
February 17, 2010 - 8:09 am UTC


Sam - answer this yourself. Think about this in a critical fashion. Apply logic.


Here, start with this:


a) do you have an open database link in your session when you are done?
b) would you like this database link to be closed?
c) then call alter session.

close link

A reader, February 16, 2010 - 2:32 pm UTC

Tom:

I tried the alter session close db link using 2 sql*plus sessions.

In schema A, I did

A> select * from dual@b_link

in B, I saw the session.

then in A I did

A> alter sesssion close database connection b_link
ORA-02080 - database link is in use

it did not work in client server test.

Tom Kyte
February 17, 2010 - 8:38 am UTC

did you even think about looking up that error code Sam?????????


ops$tkyte%ORA10GR2> create database link ora10gr2@loopback connect to ops$tkyte identified by foobar using 'ora10gr2';

Database link created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from dual@ora10gr2@loopback;

D
-
X

ops$tkyte%ORA10GR2> select * from v$dblink;

DB_LINK                                                                                                                            OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH
-------------------------------------------------------------------------------------------------------------------------------- ---------- --- --- ------ ------------ --- --- ---------------------
ORA10GR2.COM@LOOPBACK                                                                                                                   592 YES YES UNKN              0 YES NO                      1

ops$tkyte%ORA10GR2> alter session close database link ora10gr2@loopback;
ERROR:
ORA-02080: database link is in use


ops$tkyte%ORA10GR2> !oerr ora 2080
02080, 00000, "database link is in use"
// *Cause: a transaction is active or a cursor is open on the database link
//  given in the alter session close database link <link> command.
// *Action: commit or rollback, and close all cursors
//

ops$tkyte%ORA10GR2> COMMIT;

Commit complete.

ops$tkyte%ORA10GR2> alter session close database link ora10gr2@loopback;

Session altered.

ops$tkyte%ORA10GR2> select * from v$dblink;

no rows selected



links

A reader, February 17, 2010 - 9:44 am UTC

Tom:

yes, you are right. I should have checked the error description.

I have never seen any application code that closes db links but it can make the system more scalable if you have hundreds of users as your sessions will close.


I am not sure if there will a performance hit since the link is a connection and if you close it you will have to establish a new connection eveytime you use the remote database.

Do you usually recommend using that in every stored program that uses links (web or client/server)?

Tom Kyte
February 17, 2010 - 11:02 am UTC

... I have never seen any application code that closes db links but it can make the
system more scalable if you have hundreds of users as your sessions will close. ..

or to use database link concentration as mentioned - a feature of the database for many many releases now - designed to help with "many sessions, a few of which are active, wanting to connect to a remote database"

... Do you usually recommend using that in every stored program that uses links
(web or client/server)?
...

"it depends". I would first start with "how can we remove the need for this database link in an end user executed procedure" and go from there.

Connection pooling and session_per_user

suhail, May 21, 2010 - 8:19 am UTC

Hi Tom,

I want to go back to the original question. When we define a connection pooling, does it matter if we set session_per_user to say 100 to support multiple concurrent sessions? Does high number of session_per_user degrades the connection pooling performance? Should I worry about setting session_per_user to high?

Thank you
Suhail Ahmad
Tom Kyte
May 24, 2010 - 12:37 pm UTC

how does that relate to the original question please?


sessionS_per_user - a profile attribute - would limit the number of concurrent sessions for a given user in the database. If you are using a connection pool with a single username, and you used sessions_per_user, you would just limit the number of connections that connection pool could have (a connection in a connection pool translates into "database session").

I don't see the connection between it and your connection pool.

Alternates to Global Application Context for a Session Less model on RAC

Sunny J, May 18, 2011 - 4:57 am UTC

Tom,

What we have is an Oracle application that uses a session-less model i.e. connection pooling.
Implementing data masking using VPD is one of the thing we are exploring and you had clarified a few things on the same a month back.

Now, due tothe nature of the application, the application User info needs to be passed onto the database and Global Application Context is the option for the same.

Having read the document on the same, it seems it isn't available on RAC.

Now, going forward, we would need to scale-oyut using RAC 10g so wondering what are the option in that case.

Thanks in advance.
Tom Kyte
May 18, 2011 - 10:16 am UTC

In 11g, global application contexts can be used with real application clusters. So one option is "11g"

Else - you'd have to use a session table of your own design (a real table) to persist the data database wide.

Bingo!

Sunny J, May 19, 2011 - 8:47 am UTC

I was planning to use a GT to avoid any additional I/O not that I/O would be too much but to avoid it completely.
thats good.

11G on the other hand is not an option because that would led to major upgrade so i guess a normal table or GT with ON commit preserve should do the job.

Thanks.
Tom Kyte
May 19, 2011 - 9:01 am UTC

what is GT?

if you mean a global temporary table (typically referenced as a GTT - but acronyms stink in professional, technical discussions without a definition anyway...) then you are going down the WRONG track.

A GTT is visible to exactly one session and one session only - it will not work to share data. You would have to use a 'real' table.

connection pooling

A reader, May 19, 2011 - 12:43 pm UTC


Connection pooling

arnab, May 26, 2011 - 6:54 am UTC

Hi Tom - Per you "On my site, I use mod_plsql, mod_plsql is reuseing these connections -- at most, I have
between 10 and 15 connections out there (the connection pool shuts them down after a
period of inactivity and allocates new connections as needed)".
(1)What happens if there are 16 (more than the 15 available in your pool) simultaneous call to the db utilising each connection in the pool.
(2)What happens in this above scenario does the site errors out or it sleeps before trying.?
(3)What happens if you have million users fetching simultaneously from the db?


Thanks
Tom Kyte
May 26, 2011 - 8:38 am UTC

1) they queue up in the client - as they should.

2) they queue up

3) trick question. You cannot have millions of users fetching simultaneously from a single database - it would not work. What can you - and what you will have - is some sort of queueing software (we used to call them TPMs - transaction processing monitors - like CICS and Tuxedo) - a connection pool for example in an application server - to manage the work load.

Wonderful

A reader, May 28, 2011 - 12:17 am UTC

Thanks Tom. Wonderful as ever.

connection pooling needed at db level ?

A reader, February 06, 2012 - 11:00 am UTC

hi tom,
i am on 10g linux.

- today, i have some problem connecting to my db , the processes max out.

- all connection to the db are on dedicated mode

- there is a tomcat apache server connecting to the db
- there is connection pooling for that server already. about 70 connections.

---------------------------------------

I read about you saying "you dont connection pool a connection pool"

q1) so i am thinking implementing shared server will only help those in direct client -> server environment (like me and a few other dba/developers connecting to the db directly and doing short statements) am i right ?

q2) i am wondering how does connection pooling works in the middle layer. is it like the following below

T1) user need a piece of data
T2) application grab a connection and send the query/get the result
T3) application return the connection immediately back to the pool

or does the application hold on to the connection until the user quit ? (that i think defeats the purpose of connection pooling)

q3) does the application actually control when the connection is return to the pool ? or it is returned immediately once the sql query has ended. i am just wondering if those setting up the tomcat server will actually wait till a user to end the web session before returning the connection ?

q4) lastly, beside sga memory considerations when setting up shared server, will there be any problem doing a trace ?

e.g
I run a PL/SQL block, inside it has 18 query. This 18 queries i suppose will be put into a queue and service by different shared server. So will i have any problem tracing what happen in this PL/SQL block as a whole ?

Hope to hear your advice
Thanks.
Regards,
Noob



Tom Kyte
February 06, 2012 - 9:04 pm UTC

do you have at least 35 cpus? If not, your connection pool in the middle tier is almost certainly WAY TOO LARGE.

think about it - how many concurrently active sessions can your server handle? Probably about 1x, 2x, maybe 4x the number of cpu's you have (assuming you do physical IO). If you don't do much physical IO - then you'll be in the 1x range. If you do a lot of physical IO, you'll be in the 4x (or maybe even a tiny bit more) range.

So, why do they have 70 connections to a machine that probably has 4 or 8 cores?


q1) you can do that, however it would be a lot less efficient than just having the middle tier do the right thing. You would be using shared server to limit the maximum number of possibly concurrent active sessions to a reasonable number. But you would be using database cpu cycles to accomplish that. Better to queue at the middle tier and wait for an available connection rather than queue on the database server...

q2) the application SHOULD be grabbing the connection for a very short period of time. they grab it, do something, and give it right back. For a given user session, this process should happen many times over - grab ,use, release: grab, use, release: and so on.


q3) the programmer handles that detail, it is in their code - they grab, they use it, they release it.


q4) you'd have to set an identifier (the client code has to do this). Then you can use dbms_monitor.session_trace_enable/disable using that session identifier set by the application. It can be done, it isn't 'easy'.

Sizing connection pool

Keith, February 06, 2012 - 5:00 pm UTC

Tom:

I was hunting for a case study that I believe you referenced in regard to how decreasing the size of the connection pool can actually improve performance. If you have this reference, I would be very grateful. I have a client that is asking about connection pool sizing and believing they should increase the size of the connection pool to a size much greater than the number of CPU threads available. I believe the size of the connection pool should have a relationship with the number of CPU threads available.


Tom Kyte
February 06, 2012 - 9:07 pm UTC

Holding connections in a connection pool

Stew Asthon, February 07, 2012 - 7:36 am UTC


I agree completely with Tom that most applications open too many connections and underuse each connection. However, this is not entirely the programmer's fault.

In a J2EE environment the programmer doesn't control everything. Websphere and Weblogic by default make connections "shareable". The programmer says "close connection" and the J2EE Server does *not* return the connection to the pool; rather, it holds on to it in case the program wants it again. See this thread where I quote IBM: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:981835183841#3684749500346628649 It's not enough to reduce the number of connections; you have to make each connection more useful. This requires both good programming practice and proper configuration of the J2EE Server. The programming practice is grab (in one thread), use (and commit if changed made), release. If the "using" is done in several functions, pass the open connection as a parameter to each function; otherwise you are not in the same transaction. I suspect "shareable" is there so lazy programmers don't have to pass the connection as a parameter...
Tom Kyte
February 08, 2012 - 1:21 am UTC

this is not entirely the programmer's fault.

sure it is. The developer and the architects pick the tool(s). They are responsible.


Just as the DBA is responsible for the configuration of the tool they manage.
And the sys admin is for their area.
And so on.

is there so lazy programmers don't have to pass the connection as a parameter...

further evidence that it is (their fault) :)

sizing connection pool

Keith, February 07, 2012 - 9:09 am UTC

Thanks, Tom!

A reader, February 07, 2012 - 2:25 pm UTC

thanks tom and ashton!

"The developer and the architects pick the tool(s). They are responsible."

Stew Ashton, February 08, 2012 - 10:38 am UTC


I agree since you added "the architects" to "the developers".

My point remains: if a J2EE Server is being used, bad Datasource configuration may cause problems that neither Oracle nor the Java code can fix. The J2EE people, developers and architects, need to be aware of that. The subtlety of "shareable" vs. "unshareable" connections is one example of things to look out for.

Tom Kyte
February 08, 2012 - 12:15 pm UTC

The J2EE people, developers and architects, need to be aware of that.,

I agree, that is in fact their job description. If they are not - they are not J2EE people, architects and the like ;)

Always change the default number of connections

Galen Boyer, February 08, 2012 - 12:58 pm UTC

First thing I tell the java programmers is, in their local development envs,
don't go over 2 connections, ever, and try and to have only 1, until its clear
you need 2. You might need more than one because you need background queries to
happen or something. If you don't have that already, I would bet that soon after
they do that, your developers will immediately report finding some bug in
handling of connections. The default of 25 connections just masks all sorts of
issues. Any issue with a connection, app server just gives you another one.
Long before the time your local env might get close to 25, you have shutdown and
restarted your local server. You are bouncing your local server, all day long.

In the same vein, in a more concurrent env like UAT, only give 3 or 4 maybe 5.
Really really test your connection layers.

Hey Stew,

Here is a true issue that gets masked with the connection pooling vs straight
connection being so easy to "switch in and out of". Java made it really easy to
write an app that makes connections to the database, opens up resources and then
closes the connection and then, with a couple of magical deployment
incantations, this exact codebase can then be put in an app server env and
vhalla, the connection calls now come from a pool. How awesome is that? The
java programmer is now "insulated" from all that extraneous and senseless
resource handling that comes with working with something outside the JVM, like
databases. Well, when you actually close a connection in Oracle, Oracle
releases all the resources so the app developer never actually had to close any
database resource he acquired, like a cursor. The app works just great on
somebody's desktop. So, then, the time comes and the java folks say, "Hey, lets
productionize this and move it to a connection pool". Soon after that,
ora-01000. Why? Because the conn.close() nows "hands the connection back to
the pool". Perfect example of this is BIRT, part of the eclipse framework, so
therefore deemed worthy by many really smart J2EE architects and developers, is
out there and said to be a great reporting environment. They actually have rs =
NULL; in an catch block in their codebase! Yup, the JVM's garbage collector
removes the rs, but, Oracle is sitting there holding it. BIRT is what is
Actuate touts. You know what else they toute? A whole host of purchased
solutions that solve all the problems in BIRT.
Tom Kyte
February 08, 2012 - 2:29 pm UTC

but, Oracle is sitting there holding it.

don't say it that way, it makes it sound like it is "our" fault.

The truth is - no one told us the resource wasn't necessary anymore. Java doesn't work that way (I have no idea *why* it doesn't work that way - it would solve a lot of programmers bugs if we could clean up - but we cannot)


you cannot ?

Sokrates, February 09, 2012 - 5:25 am UTC

why not ?
java is in the hand of Oracle Corp., isn't it ?
Tom Kyte
February 09, 2012 - 5:49 am UTC

not really, there is a huge standards body out there that controls the language itself.

why it does work that way

Sokrates, February 09, 2012 - 6:38 am UTC

after some thinking the answer to the question
"why it does work that way"
seems pretty obvious to me:

the garbage collection mechanism of java doesn't know which method to call on an Object which is due to gc in order to release external resources (e.g. an open cursor on a database server).

the jvm just cannot call
Statement.close()
on a Statement before garbage collecting it (it would have to know that external resources are released by this method and that it is sufficient to call this method in order to release all external resources), the developer is responsible for that.


what is rs ?

Sokrates, February 09, 2012 - 7:08 am UTC

by the way, what is rs ?
a ResultSet (as the name suggests) ?
setting it to null in order to release resources is not a bug, just bad practice
Tom Kyte
February 09, 2012 - 9:50 am UTC

not sure what bit you are referring to on this page.

sorry

Sokrates, February 09, 2012 - 10:16 am UTC

I was referring to


Galen Boyer
...
They actually have rs =
NULL; in an catch block in their codebase! Yup, the JVM's garbage collector
removes the rs, but, Oracle is sitting there holding it.
...


TK

...Java doesn't work that way (I have no idea *why* it doesn't work that way - it would solve a lot of programmers bugs if we could clean up ...

Tom Kyte
February 09, 2012 - 7:31 pm UTC

yup, i agree, exceedingly bad practice.

I would call it a bug

Galen Boyer, February 09, 2012 - 12:40 pm UTC

rs was a ResultSet in the code.

How would rs = NULL and nothing closing the resources be just bad
practice? That is a bug in my opinion.

I would spec out something like the implementation of the Connection
Hierarchy keeping track of outside resources it is holding. One uses
the Connection to get a Statement and then a Statement to get a
ResultSet. It should know to keep track of what are external
resources. Then, a separate method like closeSessionResources().
Then, close() calls closeSessionResources() and so does the
application server's close().

@Galen

Sokrates, February 10, 2012 - 1:37 am UTC


... How would rs = NULL and nothing closing the resources ...


from the API doc

http://tinyurl.com/6o87ojk
:
... A ResultSet object is automatically closed when the Statement object that generated it is closed ...

Or have a look at the JDBC 4 - Spec (I hope, every JDBC developer has read it and especially everyone building APIs on top of JDBC ),
( available for instance on
http://download.oracle.com/otndocs/jcp/jdbc-4.0-fr-eval-oth-JSpec/
), 13.1.4
:

....Closing a Statement object will close and invalidate any instances of ResultSet
produced by that Statement object. The resources held by the ResultSet object
may not be released until garbage collection runs again, so it is a good practice to
explicitly close ResultSet objects when they are no longer needed....

@Galen again

Sokrates, February 10, 2012 - 1:50 am UTC

...How would rs = NULL and nothing closing the resources ...

create or replace and compile java source named leaktest as

import java.sql.*;

public class Leak
{
  static Connection conn;
    
  private static void showCountOpenCursors() throws SQLException
  {     
     if (conn == null) 
        conn = (new oracle.jdbc.OracleDriver()).defaultConnection();
  
     PreparedStatement stmt = 
        conn.prepareStatement(
           "select count(*) from v$open_cursor where sid=(select sid from v$mystat where rownum=1) and sql_text = 'select user from dual'");
     ResultSet rs = stmt.executeQuery();
     rs.next();

     int co = rs.getInt(1);

     rs.close();
     stmt.close();

     System.out.println("#open cursors=" + co);  
  }
  
  public static void leak() throws SQLException
  {     
     if (conn == null) 
        conn = (new oracle.jdbc.OracleDriver()).defaultConnection();
     
     PreparedStatement stmt = conn.prepareStatement("select user from dual");
     ResultSet rs = stmt.executeQuery();
     rs = null;
     stmt = null;
     System.gc();
     
     showCountOpenCursors();
  }    
  
  public static void noleak() throws SQLException
  {     
     if (conn == null) 
        conn = (new oracle.jdbc.OracleDriver()).defaultConnection();
     
     PreparedStatement stmt = conn.prepareStatement("select user from dual");
     ResultSet rs = stmt.executeQuery();
     rs = null;
     System.gc();
     stmt.close();
     
     showCountOpenCursors();
  }    
  
}
/

create or replace package leaktest is
procedure leak;
procedure noleak;
end leaktest;
/


create or replace package body leaktest is
procedure leak as language java name 'Leak.leak()';
procedure noleak as language java name 'Leak.noleak()';
end leaktest;
/

alter session set session_cached_cursors = 0;
set serverout on
exec dbms_java.set_output(1000)

SQL> exec leaktest.noleak
#open cursors=0

PL/SQL procedure successfully completed.

SQL> exec leaktest.noleak
#open cursors=0

PL/SQL procedure successfully completed.

SQL> exec leaktest.leak
#open cursors=1

PL/SQL procedure successfully completed.

SQL> exec leaktest.leak
#open cursors=2

PL/SQL procedure successfully completed.

SQL> exec leaktest.leak
#open cursors=3

PL/SQL procedure successfully completed.

Seems we do have a bug.

Galen Boyer, February 10, 2012 - 8:47 am UTC

You say setting a resultSet object to NULL when you are done is not a
bug because the GC will close it at some future garbage collection
point. Well, seems we have a bug in the spec now don't we? Move what
is non-buggy code into an app server connection pooled environment and
now you have resultset that do not get closed because the connection
does not get closed. That is a bug.

no

Sokrates, February 10, 2012 - 2:08 pm UTC

... and
now you have resultset that do not get closed because the connection
does not get closed...


It gets closed when the Statement it came from gets closed, I don't care about the Connection.
No spec bug here.

When the developer missed to close the Statement , it's his bug.

Connection / Session leak

VLS, November 06, 2012 - 6:34 am UTC

Hi Tom,

You had a wonderful RWP session in India and would like to thank you for your efforts.

I had a question on Session Leak, which you touched upon, during your demo. Can you help me out with few examples or scenarios that can cause this leak.

Thanks
VLS
Tom Kyte
November 06, 2012 - 7:53 am UTC

when you have a bit of code like this:

try 
{
   grab a connection
   prepare statement
   execute statement
   close statement
   prepare statement
   execute statement    <<<<<===== assume this fails
   close statement
   release connection
} 
catch (Exception e)
{
   system.out.println( "bummer!" );
}




it is called "swallowing an exception" - it is like having a when others then null - or just "when others <not followed by raise or raise_application_error>" in plsql.

you grab a connection, you do something, something fails, and you fly over the bit of code that released the connection back to the pool. The connection handle goes out of scope, never to be seen again.

Alexander, April 26, 2013 - 3:06 pm UTC

Tom,

With regard to the problem your Real World Performance crew demonstrates about overloading a database with connections, I'm wondering what is the impact to the application when we tune down the number of database connections? How can we do this without effecting their performance? Does this mean they will be waiting for connections more?
Tom Kyte
April 26, 2013 - 4:36 pm UTC

well, if you watch the video

http://tinyurl.com/RWP-OLTP-CONNECTIONS


what happened there is the queueing in the middle tier decreased, the time spent in the database decreased, the number of transactions per second increased, the amount of cpu used on the database server decreased.

In short:

o shorter execution times
o reduced cpu utilization
o more transactions per second


we affected their performance - that was our goal. Our goal is to affect the performance of the middle tier applications, in a positive way.


question: Should the middle tier expect to wait for a connection to the database?
answer: sometimes, YES, please wait, for if you do not wait - you'll have taken the server over the edge, increased response times for everyone, introduced huge variability into the response times.




think about it - a machine can only do so much work, not a bit more (given a certain transaction type). If you try to jam more onto it - everyone will take a hit, everyone.


having hundreds of connections to a machine with a handful of cores is a really bad idea.

We knew this in the days of mainframes, we understood this. We built CICS and other TPM's (transaction processing monitors) explicitly for this - to take thousands of users and funnel them down into a very very very small number of concurrent transactions. The goal: get in, get on the cpu, run without waiting for anything and get the heck out of there. And things ran really really well.

Someone forgot all of that- now we have application tiers fanning user frequests OUT (using multiple connections in some cases for an individual). taking hundreds of uses and turning them into thousands of users.


watch that video again...

want to understand connection pools in context of run time load balancing

Akhil Mahajan, June 19, 2013 - 8:00 am UTC

Hello Tom,

As usual, your responses are tremendous. While I was reading about run time connection load balancing, I learned that run time load balancing allows load balancing at transaction level rather than session level and that run time load balancing is facilitated by connection pools.

Please let me know whether my following understanding about connection pools configured to use run time load balancing is correct or not:
When an initial request is made from user, a connection (established with the instance providing best service level) is picked from the connection pool and user's select/DML request is completed. If a new request such as select/DML comes again from the same application user session, will a check be made again whether the current connection can provide best service or not? If the connection does not provide best service, will another connection (made to another instance) be picked to service same application user's request?

Regards, Akhil Mahajan
Tom Kyte
June 19, 2013 - 7:49 pm UTC

depends on the connection pooling software. If you are using our connection pool - it is RAC aware and it understands how loaded each server is in the cluster currently and will load balance over the available connections using that knowledge.

if you are using some other connection pooling software, it may well not be aware of RAC or clusters - it just sees a database connection and any connection is as good as any other so it'll just return one, without considering the current load on the server.

want to understand connection pools in context of run time load balancing

Akhil Mahajan, June 20, 2013 - 4:54 am UTC

Let's suppose it is Oracle's connection pooling software which is RAC aware, I want to understand whether connection switching between instances will happen for same application user session. Run time load balancing states that load balancing occurs at transaction level and not at session level (please throw some light on this). That was my original question, I am quoting it again:

"When an initial request is made from user, a connection (established with the instance providing
best service level) is picked from the connection pool and user's select/DML request is completed.
If a new request such as select/DML comes again from the same application user session, will a
check be made again whether the current connection can provide best service or not? If the
connection does not provide best service, will another connection (made to another instance) be
picked to service same application user's request?"


Also, since I was not aware that there are other connection pooling softwares that are not RAC aware, does it mean that such connection pools will not be able to take advantage of run time load balancing (that uses Load Balancing advisory)?

It's my request that you answer my above 2 questions. I shall be thankful to you.
Tom Kyte
June 20, 2013 - 2:25 pm UTC

... I
want to understand whether connection switching between instances will happen
for same application user session. ...

I'm having to guess what a "application user session" is here. I'm assuming:

a) a user session is a connection in a connection pool.
b) a "application user session" as you call it is a connection your application has grabbed, and is using. it owns this connection until it releases it back to the connection pool.


once established, a connection in the connection pool will be to an instance, it will not switch instances as that would require a logoff logon process.

when you go to grab a connection from the connection pool (your "application user session")- we'll know what instance each connection is connected to - and we know the relative load on that instance and we'll go for the connection to the instance with the lightest load.


A connection, once you grab it, will be to an instance and your "application user session" will be to a particular instance (unless you are using transparent application failover - TAF - and the instance you are connected to fails - then we'll automatically reconnect you to another instance).


So, if your application grabs a connection from the pool and issues multiple DML statements and then commits/rollsback and releases the connection - all of the DML/commit/rollback will be taking place on a given instance.


for as long as you "own" that connection, it'll be to an instance.


other connection pools that are not RAC aware can use connect time load balancing - but after that - they don't know, they don't have any insight into, what each connections instance load is. Therefore they might overload a particular instance - ignoring the other instances - just because they consider a connection to a database to just be to a database, they don't know there are multiple instances, they don't have pmon telling them what the relative loads are, they don't know how to hand out the connections when requested by the application in a load balancing manner.

want to understand connection pools in context of run time load balancing

Akhil Mahajan, June 21, 2013 - 10:01 am UTC

Excellent. What little doubt I had is not clear. Thanks a ton!!
By the way, I do not get emails when you update my request. The site asks for email id when a question is put initially; however, it does not update me when the question is answered. I have bookmarked the thread so that I can check for updates. Thanks anyways.
Tom Kyte
July 01, 2013 - 3:58 pm UTC

only the original poster gets an email response when the post is updated. the reviews do not...

Memory Leak with connection pool

Vaz, April 01, 2014 - 4:40 pm UTC

Hi Tom,

I've an application that has WebLogic as the Application Server and Oracle Database 11.2.0.1 on Solaris SPARC. Application Server has 20 JVMs running. Application server has connection pool with initial size set to 30, so on start up it creates around 600 connections to the database and keeps in the connection pool.
Now my problem is that PGA memory on database is increasing continuously and never gets released. Over a period of time on heavy load of 6-7 hours (around 2000 application users), my database memory of 100GB is full and DB goes down.

My understanding is that oracle will release the PGA memory after sometime in a connection pooling environment even if the actual connection is not disconnected. Or is it that Oracle will release the memory ONLY after the session is disconnected?
Tom Kyte
April 02, 2014 - 5:28 am UTC

first of all - 30 connections per JVM is probably 10x as much as it should have. 600 connections on a machine with less than say 60 cores is not really good math.

http://tinyurl.com/RWP-OLTP-CONNECTIONS

It may not be able to release PGA, it depends on what the PGA is being used for. If your sessions use plsql packages - and those packages maintain a state (global variables), those will "live forever" - until the session ends.


How much memory do you have allocated to the SGA? what else is running on the machine? What is the PGA memory usage over time (AWR report will tell you that).



Controlling Session specific data from web

Samar Kumar, October 14, 2014 - 2:27 pm UTC

Hi Sir,
  CREATE GLOBAL TEMPORARY TABLE TEMP_test
   ( id number,status varchar2(100)
   ) ON COMMIT PRESERVE ROWS ;
   
  
   create or replace  procedure temp_insert(p_user_id in varchar2,c_data out sys_refcursor)
   is begin
   insert into temp_test values(100,p_user_id);
   insert into temp_test values(101,p_user_id);
   insert into temp_test values(102,p_user_id);
   INSERT INTO temp_test VALUES(103,p_user_id); 
   COMMIT;
   open c_data for select * from  temp_test;
   end;  
   
     --session 1 by first user
 var c_data refcursor;
 set autoprint on;
 BEGIN
 temp_insert('USER1',:c_data);--getting 4 rows in oracle session
 END;

   --session 2 by second user  

 var c_data refcursor;
 set autoprint on;
 BEGIN
 temp_insert('USER2',:c_data);--getting 4 rows oracle session
 END;
 


I have created a gtt table which has been used in procedure as above mentioned.When I execute from the different sessions in oracle then it is returning 4 rows for each session.
But from the Web page if the same is executed then it is returning 8 rows for both the users. Now I realized that the same session is used by both the user from the web page
(Connection Pool behaviour). Currently we are controling by using where clause in the cursor to filter users perspective data.But the same thing can be achieved with the normal
table also as there is no purpose of using gtt table here.But there should be some way how the gtt table is behaving in oracle sessions the same can be achieved through the web applications.


Regards,
Samar

sushil, January 12, 2017 - 10:27 pm UTC

hi..just wanted to know how hundreds of users can be served with 20 connections at a time ?
If 20 users take 20 connections at a time then max 20 users can be served right ?
Connor McDonald
January 13, 2017 - 1:44 am UTC

20 users all *running* something at the *same* time yes.

But a typical user is this:

a- load web page (maybe 0.1 seconds of db time)
b- read page, scroll, etc etc (maybe 10 seconds)
c- click a link (which takes us back to 'a' above)

So the amount of time a real user is actually *using* the database is often a tiny percentage of real world time.

If (on average) its 1% of the real clock time, then 20 connections could server 20*100 users.

Alex A, June 19, 2018 - 6:31 pm UTC

Hi team,

We have a situation where one of our applications is leaving a transaction open and blocking other transactions against the same rows. The blocking session we can see 'SQL*Net message from client', and the last executed SQL is just a health check 'select sysdate from dual';.

Because of connection pooling and our inability to see the real blocking SQL, we can't track this down. Is there anyway to retroactively trace a session to see everything that was executed? Or do you have any other suggestions? I feel like this situation must be fairly common.

Thanks.
Connor McDonald
June 20, 2018 - 12:20 am UTC

"SQL*Net message from client" is because the session is now idle.

It is like my doing:

- delete from MY_TABLE where COL = 1;
- then 12 other queries
- then going out to lunch

That blocking lock might have been run minutes, hours or days ago.

You could run something like this:

SELECT  /*+ ORDERED */
        substr(decode(substr(upper(s.action),1,2),'PD',s.action,s.osuser),1,12) osuser,
        S.SID,
        L.TYPE,
        L.ID1,
        case when l.type != 'UL' then substr(o.name,1,22) end name,
        DECODE(L.LMODE, 0, 'NONE', 1, 'NULL', 2, 'SS', 3, 'RX', 4, 'S', 5, 'SRX', 6, 'X', '?') holding,
        DECODE(L.REQUEST, 0, 'NONE', 1, 'NULL', 2, 'SS', 3, 'RX', 4, 'S', 5, 'SRX', 6, 'X', '?') wanting,
        l.ctime how_long,
        decode(l.block,0,null,2,null,'YES') is_blocking
FROM V$SESSION S, V$LOCK L, sys.obj$ o
WHERE L.SID=S.SID
AND S.USERNAME is not null
and l.type != 'AE'
and l.id1 = o.obj#


which will show you the locks currently outstanding and the objects involved. You can work from there to work out what part of the code might have performed the operation.

Enq:Row lock contention & Connection pooling

AmitB, December 15, 2018 - 7:14 pm UTC

Atter understanding connection pooling in Java and a similar problem posted by someone here I too would like to seek guidance. We have a Java application (Connection pooling used) deployed on Websphere. Min thread setting is 30 and Max thread setting is 500 connections. We always face enq:- Row lock contention issue and many a times Db CPU stays 60 to 70 % while row lock contention show 98% of Db time and also as top event. Ultimately we have to take a db reatart. On further analysis I found during such sessions there are around 100+ session in WAITERs and 10 sessions as HOLDERS. Interesting thing is all HOLDERS are in INACTIVE state. Upon mining AWR data I can see that HOlDER sessions have executed same DMLs which WAITER sessions are waiting for. How to troubleshoot this further 1. I mean how to get information from v$open_cursors for possible memory leak ? 2. How to help Java developer in identifying memory leak? 3. Is there any time out setting at jdbc level to snap INACTIVe session? I have change Max idle time out setting at db profile to 20 min in order to snap IDLE connection in order to minimize INACTIVE HOLDING session problem. But it's not helping . Can you please guide n further steps.
Connor McDonald
December 17, 2018 - 2:38 am UTC

A session being Inactive *and* Holding is indicative of a coding issue (someone not ending a transaction). Simple example:

--
-- Session 1
--
SQL> create table t as select * from dual;

Table created.

SQL> delete from t;

1 row deleted.

--
-- Session 2
--
SQL> conn / as sysdba
Connected.
SQL> select sid, status, last_call_et
  2  from   v$session
  3  where  username = 'MCDONAC';

       SID STATUS   LAST_CALL_ET
---------- -------- ------------
       858 INACTIVE           83


You can see that session 1 initiated a transaction and never committed it, so it has been inactive (idle) for 83 seconds but *still* holds a lock. If another session tries to delete from t, they'll be blocked (they'll be a waiter and session 1 will becomes a holder).

I'm not suggesting that your Java apps are just doing nothing - what is perhaps more likely is something like:

- commences a db transaction (ie, locks some resource)
- stops using database and is now doing work in the middle tier (eg talking to a webservice, or computation etc etc).

So from the database perspective, you are "inactive" but probably doing some other sort of work. But that database lock is still there.

It is *possible* that this is indeed correct operation, but normally, when you're using a connection pool, you would *not* expect this kind of behaviour because the idea of connection pool is that you grab a connection, do *everything* you need to do with this connect you received and then release it. Because the next call to the database might get you a different connection - so leaving an uncommitted transaction in a session (in a connection pooling environment) is not a sensible thing to do.

Enq Row lock contention & Connection pool Continued...

AmitB, December 17, 2018 - 1:16 pm UTC

Thanks for your inputs. Infact I had simulated the same test case which you explained. But the problem is I am not able to find why session is staying INACTIVE. Is there any way for us DBAs to find that ,why a session which didn't commit or roll back is staying INACTIVE. Cause such session become blockers later on. What pointers Can we get from v$ open_cursors ?? Also is there any way to find if Java thread is not getting closed or any memory leak anywhere ?
Connor McDonald
December 18, 2018 - 1:56 am UTC

v$transaction will show you open transactions and the sessions that initiated them.

But of course, in a connection pooling environment, that is hard to tie back to the calling program because they used the session and then left.

What you can do is enable tracing on the pool using DBMS_MONITOR and attempt to track which programs used the pool and left an open transaction.


A reader, March 27, 2019 - 3:25 pm UTC

We have a java program in app server which calls a database stored procedure and this stored procedure in turns calls gather schema stats. We use universal connection pooling (UCP) connect to database from app server. If we run this procedure from SQL*Plus it takes about 40 minutes to finish. When we run the java program from app server about 25 minutes the java purposely closes the connection to the database to avoid disconnected by firewall so we can reuse the connection. We observed different behaviors in 11g (11.2.0.4.0) and 12c (12.2.0.1.0). In 11g the stored procedure continue runs in the database after the java close the connection and finish gather stats on all objects in that schema. While 12c the stored procedure stops running at the time java closes the connection so some objects' stats got updated while others stats stays old. We would like to know if this behavior is confirmed by any Oracle users. And this is a new feature of 12c?

Thanks so much!
Chris Saxon
April 03, 2019 - 10:05 am UTC

The real question here is:

Why are you using a Java program to gather stats?!

In most cases you should use the default stats gathering job. If for some reason you need something hand-crafted, submit a database (scheduler) job.

If you must trigger this from the Java program, you can get this to submit a job. Instead of calling dbms_stats directly.

Desupport of Oracle Net Connection Pooling

David D., April 03, 2019 - 1:34 pm UTC


Hello Masters,

I read here https://docs.oracle.com/database/121/UPGRD/deprecated.htm#UPGRD60061
that in 12.1 Oracle Net Connection Pooling is desupported.

Does it mean that, now, we cannot use a server to manage N connexions from an application to the database but we have to directly connect the applciation to the database?

David D.

Connor McDonald
April 04, 2019 - 4:14 am UTC

No, this is not connection pooling in total, *only* the POOL=ON attribute within the DISPATCHERS parameter.

There are several connection pooling methods still available, for example UCP, ODP.Net, and DRCP.

JDBC connection to Oracle

Shirley, April 04, 2019 - 5:01 pm UTC

Thank you for your response.

Agree on you point regarding gather stats. The reason that we run gather stats from java is that we have java programs which make a lot of data changes and after data changes we would like the stats get updated so the application will not perform slowly. We just want to confirm the different behavior between 11g and 12c. In addition we have some other long running procedures that we would like to know if the procedures will be completed successfully after the connection is closed. Thanks a lot!
Connor McDonald
April 09, 2019 - 1:31 am UTC

I suspect its an improvement in the detection of "broken connections".

Easy fix - submit your gather stats as a job (via dbms_job or dbms_scheduler). Your java program will return immediately and the stats will be gathered in the background

JDBC Universal connection pool to Oracle

Shirley, April 25, 2019 - 2:57 pm UTC

Thanks for your suggestions on gather stats. Gather stats is just one of our long running batch processes running from our app server. Some processes are process input files and others generate reports and all those processes are configured in app server. We are using universal connection pool to connect to Oracle databases. Any suggestions are greatly appreciated. Thanks,


Connor McDonald
April 30, 2019 - 2:47 am UTC

For UCP I don't think you'll have any dramas, but obviously pool sizing comes into play. If you expect long running jobs to consume "n" connections concurrently, then your total pool size need to accomodate that plus the number you'd expect for more rapid fire activities.

Some sites have dedicated (pooled or otherwise) connections for long running tasks - not sure if thats possible in your case.

Need some info in proxy user inactive session issue

Sid, June 16, 2020 - 12:20 pm UTC

Hi Tom,

I am testing kerberos with the following settings.

my connection property looks like this:

driverType=thin;connectionProperties={oracle.net.authentication_services=(KERBEROS5),oracle.net.kerberos5_mutual_authentication=true,oracle.net.kerberos5_cc_name=/tmp/local_kerberos_cc_name,oracle.jdbc.proxyClientName=APP_SCHEMA};loginTimeout=30;url=jdbc:oracle:thin:@ (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = servername)(PORT = 6666)) (LOAD_BALANCE = on) ) (CONNECT_DATA = (SERVICE_NAME = DB_Instance)))


It works fine, but the proxyClientName=APP_SCHEMA sessions are being created and its not closing afterwards. I tried finding the connectionProperties but unable to fine.

I am not too sure what option I need to use to close the sessions which I can see in v$sessions table.

Could you please let me know if you have ever seen this ?

Thanks
Sid
Chris Saxon
June 16, 2020 - 2:56 pm UTC

No, not something I've seen sorry.

The configuring Kerberos section in the security guide may help:

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-kerberos-authentication.html#GUID-DF84261F-457A-4B9F-AE41-CDE6FE9178C4

A reader, October 08, 2021 - 5:22 pm UTC

Connor, Cris - I am unable to see the connection pool video highlighted in the discussion. Could you please enable it again?