Security concern in connection pooling
来源：互联网 发布：手柄按键映射软件 编辑：程序博客网 时间：2024/06/09 21:04
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:22140261281764
关于jdbc pool，oralce package-level variable的一些讨论
Submitted on 12-Aug-2004 11:50 Central time zone
Tom's latest followup | Bookmark | Bottom
Last updated 8-Feb-2010 19:53
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 we 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)
connection poolings August 12, 2004 - 1pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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.ThanksKayode.
Tracing of connection Pooled sessions August 12, 2004 - 7pm Central time zone
Bookmark | Bottom | Top
Reviewer: Matt Butler from Australia
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.
Followup August 13, 2004 - 9am Central time zone:
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 August 13, 2004 - 1am Central time zone
Bookmark | Bottom | Top
Reviewer: Douglas from HK
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); }} Followup August 13, 2004 - 10am Central time zone:
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 August 13, 2004 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: reader
Does TAF work with JDBC connection pool? Thanks.
Followup August 13, 2004 - 5pm Central time zone:
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? March 19, 2005 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: marc weinstock from ny ny
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.
Followup March 19, 2005 - 12pm Central time zone:
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.
July 15, 2005 - 7am Central time zone
Bookmark | Bottom | Top
Reviewer: Peter from Klagenfurt Austria
<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?
Followup July 15, 2005 - 8am Central time zone:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:35661775175899
Need some clarification about the parameters to dbms_monitor July 20, 2005 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Peter from Klagenfurt, Austria
In her article Kimberly Floss keeps silent about the parameters someone has to provide to dbms_monitor.I guess in the following callEXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'ACCTG', module_name => 'PAYROLL', instance_name => 'inst1');service_name correponds to v$session.service_namemodule_name correponds to v$session.moduleIs 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 callEXECUTE 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 regardsPeter
Pooling October 12, 2005 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: V from NY
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?
Followup October 12, 2005 - 2pm Central time zone:
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
http://www.oracle.com/technology/oramag/oracle/04-nov/o64asktom.html
see the jdbc quick tip
Long standing open connections October 13, 2005 - 9am Central time zone
Bookmark | Bottom | Top
Reviewer: Rakesh from London, UK
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?
Followup October 13, 2005 - 10am Central time zone:
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 December 1, 2005 - 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: Laxman Kondal from Springfield, VA
Hi TomIn 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. Followup December 2, 2005 - 10am Central time zone:
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 January 19, 2006 - 1pm Central time zone
Bookmark | Bottom | Top
Reviewer: Lou from LA
Is connection pooling a security concern since the connection was already establish and open infinite session?
Followup January 19, 2006 - 1pm Central time zone:
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 ? January 20, 2006 - 5am Central time zone
Bookmark | Bottom | Top
Reviewer: Barry Chase from Shelbyville, KY USA
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 ?
Followup January 20, 2006 - 10am Central time zone:
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 January 20, 2006 - 1pm Central time zone
Bookmark | Bottom | Top
Reviewer: Dana from Phoenix, AZ USA
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.
Followup January 20, 2006 - 2pm Central time zone:
... 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 January 20, 2006 - 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: Dana from Phoenix, AZ USA
February 28, 2006 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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.
Followup March 1, 2006 - 7am Central time zone:
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, March 1, 2006 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
How do you set up a connection pooling?
Followup March 1, 2006 - 1pm Central time zone:
before I answer that - at what level.app serverdatabase (we call is "shared server" basically, very much a connection pool)mod_somethingwhat...
completing my question, March 1, 2006 - 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
connection pooling in app server and in the database.thanks,
Followup March 2, 2006 - 8am Central time zone:
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
July 13, 2006 - 7pm Central time zone
Bookmark | Bottom | Top
Reviewer: Su Baba
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?
Followup July 13, 2006 - 8pm Central time zone:
the database provided the api? dbms_application_info?? not sure what you mean
July 14, 2006 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Su Baba
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.
Followup July 14, 2006 - 12pm Central time zone:
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 August 9, 2006 - 12pm Central time zone
Bookmark | Bottom | Top
Reviewer: Kevin from Connecticut from USA
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.0WEBLogicalso use .NETThanks, Kevin
Followup August 9, 2006 - 4pm Central time zone:
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.
August 10, 2006 - 1pm Central time zone
Bookmark | Bottom | Top
Reviewer: Alexander the ok
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.
Followup August 10, 2006 - 11pm Central time zone:
search for optimistic concurrency controlthat is how you do lost update detection in a stateless environment.
cached execute immediate statements October 3, 2006 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Nolan from pennsylvania
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.
Followup October 3, 2006 - 4pm Central time zone:
question about the timeline of the connection pool handling, do you:a) grab connectionb) run procedurec) get ref cursord) retrieve ref cursore) ungrab connectionor what - what is the sequence in the client of activity?
cached execute immediate statements October 4, 2006 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: Nolan from pennsylvania
That is the correct sequence. The connection is closed before passing the resultset back to the web tier.
Followup October 4, 2006 - 5pm Central time zone:
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 October 5, 2006 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: Nolan from pennsylvania
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 connectionb) run procedurec) get ref cursord) retrieve ref cursor into passed in arrayliste) 'close' connectionf) return to calling class
Followup October 5, 2006 - 1pm Central time zone:
so, d is doing all of the fetch calls right? what is an "arraylist"??
cached execute immediate statements October 5, 2006 - 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: Nolan from pennsylvania
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.
Followup October 5, 2006 - 4pm Central time zone:
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 October 5, 2006 - 5pm Central time zone
Bookmark | Bottom | Top
Reviewer: Nolan from pennsylvania
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.
Followup October 6, 2006 - 8am Central time zone:
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 October 10, 2006 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Nolan from pennsylvania
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?
Followup October 10, 2006 - 8pm Central time zone:
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 ??? October 10, 2006 - 9pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
could you tell me then what is called 3-tier appllication and in which situation, it is used...?Regards,
Followup October 11, 2006 - 8am Central time zone:
typically, you are refering to:client (browser usually)middle tier (application server, web server, whatever)database (oracle of course!)three tiers.
January 31, 2007 - 1pm Central time zone
Bookmark | Bottom | Top
Reviewer: Alexander the ok
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.
Followup January 31, 2007 - 5pm Central time zone:
got code? just a snippet - would like to see an example of the sql and the way you bind. January 31, 2007 - 5pm Central time zone
Bookmark | Bottom | Top
Reviewer: Alexander the ok
<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))
Followup February 1, 2007 - 12pm Central time zone:
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?
February 1, 2007 - 1pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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.
Followup February 1, 2007 - 1pm Central time zone:
(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
February 5, 2007 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Alexander the ok
Followup February 5, 2007 - 11am Central time zone:
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 February 6, 2007 - 5am Central time zone
Bookmark | Bottom | Top
Reviewer: Stew Ashton from Paris, France
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
clearParametersbefore 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.
February 20, 2007 - 1pm Central time zone
Bookmark | Bottom | Top
Reviewer: Alexander the ok
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, March 14, 2007 - 6pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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,
Followup March 15, 2007 - 7am Central time zone:
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.
March 23, 2007 - 1pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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,
Followup March 26, 2007 - 7am Central time zone:
add lots of users so that the flushing of the shared pool is not so straightforward.Close All Cursors June 4, 2007 - 7am Central time zone
Bookmark | Bottom | Top
Reviewer: Yogesh Purabiya from India
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.
Followup June 5, 2007 - 8am Central time zone:
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 June 6, 2007 - 5am Central time zone
Bookmark | Bottom | Top
Reviewer: Yogesh Purabiya from India
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 ?
Followup June 6, 2007 - 1pm Central time zone:
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 June 6, 2007 - 4pm Central time zone
Bookmark | Bottom | Top
Reviewer: Stew Ashton from Paris, France
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.
Followup June 6, 2007 - 9pm Central time zone:
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 June 7, 2007 - 12am Central time zone
Bookmark | Bottom | Top
Reviewer: Yogesh Purabiya from India
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.
Followup June 7, 2007 - 2pm Central time zone:
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 June 7, 2007 - 12am Central time zone
Bookmark | Bottom | Top
Reviewer: Yogesh Purabiya from India
<ResultSet>.close();
<PreparedStatement>.close();
conn.commit();
connectionPool.pushConnection(conn);
RE: JSP & (Oracle) Cursors June 7, 2007 - 7am Central time zone
Bookmark | Bottom | Top
Reviewer: Stew Ashton from Paris, France
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 June 8, 2007 - 1am Central time zone
Bookmark | Bottom | Top
Reviewer: Yogesh Purabiya from India
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.
Followup June 9, 2007 - 10am Central time zone:
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 June 11, 2007 - 4am Central time zone
Bookmark | Bottom | Top
Reviewer: Yogesh Purabiya from India
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 !
Followup June 11, 2007 - 10am Central time zone:
... 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? June 11, 2007 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Stew Ashton from Paris, France
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...
Followup June 11, 2007 - 3pm Central time zone:
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 June 12, 2007 - 3am Central time zone
Bookmark | Bottom | Top
Reviewer: Stew Ashton from Paris, France
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?
Followup June 12, 2007 - 9am Central time zone:
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 July 18, 2007 - 12am Central time zone
Bookmark | Bottom | Top
Reviewer: Arindam Mukherjee from Kolkata, India
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?
Followup July 18, 2007 - 10am Central time zone:
when you use a connection pool, you cannot rely on anything being the same from page to page - think about ita) 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 September 12, 2007 - 12pm Central time zone
Bookmark | Bottom | Top
Reviewer: Maya from Gaithersburg, MD
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.
Followup September 15, 2007 - 4pm Central time zone:
who makes your connection pool, they would be the one to ask....Appears to be some form of Race Condition occurring September 30, 2007 - 5pm Central time zone
Bookmark | Bottom | Top
Reviewer: Barry Chase from Louisville, KY
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 ?
Followup October 3, 2007 - 2pm Central time zone:
does your .nyet application makea) 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 October 5, 2007 - 6am Central time zone
Bookmark | Bottom | Top
Reviewer: Barry Chase from Louisville, KY
.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 November 5, 2007 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: Ana from PA, USA
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 May 2, 2008 - 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: Shaji from Palo Alto, CA USA
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 followingLoop 1000 times1. Open browser2. Call the Web Page3. Feed the data4. Submit the page5. Close the browserclose the loopWhen 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?
Followup May 2, 2008 - 3pm Central time zone:
you have totally misunderstood a connection poolthe 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 May 2, 2008 - 3pm Central time zone
Bookmark | Bottom | Top
Reviewer: Shaji from Palo Alto, CA USA
Thanks for pointing to right direction. Could you provide any link to document relating to connection pool?
Followup May 2, 2008 - 4pm Central time zone:
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 May 5, 2008 - 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: Andrew Markiewicz from Madison, WI
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.
Followup May 6, 2008 - 12am Central time zone:
... Specifically, the shared server connection compared to an OASjava 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 May 6, 2008 - 6am Central time zone
Bookmark | Bottom | Top
Reviewer: Stew Ashton from Paris, France
"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 May 7, 2008 - 9am Central time zone
Bookmark | Bottom | Top
Reviewer: Andrew Markiewicz from Madison, WI
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.
Followup May 8, 2008 - 2am Central time zone:
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 June 30, 2009 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: John Davey
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 ?RegardsJohn Davey
Followup July 6, 2009 - 6pm Central time zone:
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.
June 30, 2009 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: John Davey
" 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 July 9, 2009 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: Sam
Tom:just a few small question on connection pooling using mod_plsql1. 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.
Followup July 14, 2009 - 2pm Central time zone:
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 August 6, 2009 - 11pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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.
Followup August 7, 2009 - 9am Central time zone:
read the documentation looking for n-tier proxy authenticationhttp://download.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 August 13, 2009 - 5pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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?
Followup August 24, 2009 - 7am Central time zone:
correct.mod_plsql October 26, 2009 - 10pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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.
Followup October 27, 2009 - 11am Central time zone:
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://download.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 October 27, 2009 - 1pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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?
Followup October 27, 2009 - 5pm Central time zone:
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 October 27, 2009 - 3pm Central time zone
Bookmark | Bottom | Top
Reviewer: Galen Boyer from Boston
Just so the readers understand, one of the differences betweenconnection pooled vs non-pooled in j2ee is what happens on theconnection.close() and DriverManager.getConnection() calls. Innon-pooled, getConnection() established a brand new connection to thedatabase, and close() actually closes the connection and the sessionis gone from v$session. In connection pooled environment, the appserver overrides the getConnection() and close() methods to get aconnection from the pool and return that connection back to the pool.The close() leaves that connection open, and now available for someother process to call getConnection(). So, as java is apt to do, codethat runs in non-pooled environment can easily now with just a a jndidatasource 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 Oraclecleaned up all the open resources instead of the programmer, inparticular, the ref_cursors that programmer decided to open. So, thejava developer who has been ignorantly coding by getting a ref_cursor,doing stuff with that resultset and then closing that connection neverhas to learn what he should know, which is a ref_cursor is a resource.So, this code gets moved into an application server environment, thatclose() call now puts that connection back on the pool and then lateron, the database errors out with ORA-01000 maximum open cursorexceeded.Hurrah for code portability of java, well, not really.Once again, database independence has morphed into database ignorance!!!!
mod_plsql October 27, 2009 - 7pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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.
Followup October 28, 2009 - 8am Central time zone:
Sam/SMK and now xxxxxxxwe'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://download.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 October 28, 2009 - 8am Central time zone
Bookmark | Bottom | Top
Reviewer: Sam
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.
Followup October 28, 2009 - 8am Central time zone:
done.processes October 30, 2009 - 8pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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 November 1, 2009 - 7am Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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> >>>
Followup November 9, 2009 - 12pm Central time zone:
... 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 November 8, 2009 - 10pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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.
Followup November 11, 2009 - 2pm Central time zone:
sam/smkyou 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 November 30, 2009 - 3pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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??
Followup December 1, 2009 - 3am Central time zone:
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.
December 3, 2009 - 6am Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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.ThanksRajeshkumar
Followup December 4, 2009 - 3pm Central time zone:
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 February 4, 2010 - 3pm Central time zone
Bookmark | Bottom | Top
Reviewer: Ojas from Plano,TX
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?
Followup February 4, 2010 - 4pm Central time zone:
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 February 4, 2010 - 4pm Central time zone
Bookmark | Bottom | Top
Reviewer: Ojas from Plano,TX
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.
Followup February 4, 2010 - 6pm Central time zone:
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 February 4, 2010 - 5pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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#i1020954Basically, 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)
Followup February 4, 2010 - 6pm Central time zone:
httpd@box is not the same as oracle@boxsam - 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 February 4, 2010 - 7pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
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 February 5, 2010 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: Ojas from Plano,TX
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 TYPE3 2147483644 4497 NETWORK?SERVICE 5824552 DB SERVER NAME oracle@DB SERVER NAME (TNS V1-V3) USER0 2147483644 4497 NETWORK?SERVICE 4592:712 APP SERVER NAME APP SERVER NAME w3wp.exe USER3 2147483644 4497 NETWORK?SERVICE 5564:5804 APP SERVER NAME APP SERVER NAME w3wp.exe USER3 2147483644 4497 NETWORK?SERVICE 2981972 DB SERVER NAME oracle@DB SERVER NAME (TNS V1-V3) USER0 2147483644 4497 NETWORK?SERVICE 2248:2240 APP SERVER NAME APP SERVER NAME w3wp.exe USER0 2147483644 4497 NETWORK?SERVICE 3816:316 APP SERVER NAME APP SERVER NAME w3wp.exe USER0 2147483644 4497 NETWORK?SERVICE 3816:3484 APP SERVER NAME APP SERVER NAME w3wp.exe USER0 2147483644 4497 NETWORK?SERVICE 3820:808 APP SERVER NAME APP SERVER NAME w3wp.exe USER0 2147483644 4497 NETWORK?SERVICE 4592:5084 APP SERVER NAME APP SERVER NAME w3wp.exe USER3 2147483644 4497 NETWORK?SERVICE 4817128 DB SERVER NAME oracle@DB SERVER NAME (TNS V1-V3) USER
Followup February 8, 2010 - 7pm Central time zone:
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 February 5, 2010 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: sam
Tom:ORacle@box (CJQ0) = oracle background processYou will not see a username for those.However,oracle@box (TNS V1-V3) is a connection using a db linkTO see that, i log in using sql*plus to schema "A" theni runselect * from dual@schema_b_linkIf you see the session in Schema b you will see mine under oracle@boxname in "inactive" status.
Connection pooling February 10, 2010 - 9am Central time zone
Bookmark | Bottom | Top
Reviewer: Ojas from Plano,TX
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.
- Security concern in connection pooling
- Connection Pooling
- Tuning Up ADO.NET Connection Pooling in ASP.NET Applications
- Connection Pooling Proxool
- Connection Pooling Proxool
- HBase Connection Pooling
- 第三方connection pooling 组件
- 第三方connection pooling 组件
- Database Connection Pooling with Tomcat
- Operators in MXNet-Pooling
- max pooling in caffe
- Best Practice 6 Use JDBC connection pooling
- 连接池的概念(connection pooling)
- J2EE Database Connection Pooling -连接池
- .Net Connection Pooling Explained and Enhancment
- ADO.NET Connection Pooling at a Glance
- Best Practice 6 Use JDBC connection pooling
- ADO.NET Connection Pooling at a Glance
- 服务器应用程序不可用之解决方法
- VB中Winsock控制的UDP协议的使用
- Tomcat 配置
- 兔死狐悲，殃及池鱼，看来国语的星际争霸2是没戏了
- 关于字符转换方法的整理
- Security concern in connection pooling
- 系统中queue的使用
- 微软正式推出Windows Phone 7 Series
- SWT之FormAttachment
- WINCE应用开发笔记四--COM
- C# ArrayList 和 List 集合类型
- 几个重要的JAVA命令详解（转载）
- usaco——castle
- Kscope装好拉
