Tom,

I take your point. I'm afraid I don't have much knowledge of the ins and outs of PHP! I guess the two approaches are either:
a) Using Oracle, the PL/SQL web toolkit, mod_plsql, Apache
OR
b) Using PHP, the Oracle8 Call Interface (OCI8, standard part of PHP, apparently - </code> http://www.php.net/manual/en/ref.oci8.php <code> and Apache.

I agree that there does appear to be something wrong with the PHP driver, but I guess that's what you get for using unsupported code :-(

Sorry I did make a typo - I meant "ORA-01012: not logged on in", of course!

You say "it is infinitely faster to use a connection pool". What do you really mean by this - infinity is quite a big number!

"The slowest thing in a web page is generally connect/disconnect"

What evidence do you have?

Tom,

Do you'll use Dead Connection Detection..If yes can you elaborate a little more on that..

Thanks.

You know it's kinda funny reading this when I figured one of your mantras was to always benchmark to prove a point.

You write "This is one of those commonly accepted things that is actually true", so I guess that gives the green light for not benchmarking if you don't feel like it when you think something's "commonly accepted", whatever that means :-)

Well I'm sorry that I didn't equate "This is one of those commonly accepted things that is actually true" with "I've benchmarked this dozens, no wait, probably hundreds of times" - I guess this is because I have a problem you seem to get - MY crystal ball is in the shop right now!

I can't benchmark it, as I don't have the resources at the moment, but believe me, I would if I could. My gut feeling based on 4plus years of web development is that the connection/disconnection overhead is maybe one quarter second, maybe one half.

Now whether thats important to the users that their pages are that much slower is entirely subjective, but if a query is taking 12 seconds to come back, I don't think an extra half second is gonna piss the users too much.

I don't disagree with anything you write.

Hell, it isn't even an issue for me! I use 9iAs for everything I can - session management isn't even an issue, everything gets done for you! If your in a situation where your just dealing with Oracle, I wouldn't use anything else.

I guess I didn't express myself very well - I just want to say to the OP that if you've already gone down the road where your stuck with bad session management, it's maybe not worth your time trashing everything and starting again. But maybe I'm wrong.

As for all you "why" questions, I dont know. Now why does Microsoft release Office 97 / 2000 / XP / whatever, when MS Word 2 was 99.9% adequate for everything?

Oh, your comment "this is one of the commonly accepted things *that is actually true*" wasnt so obscure, just sometimes I like to see some EVIDENCE to back up claims like this - dont you?

Since I have queried v$session many times and seen that there would always be just 1 session for 1 web page, can I say this: the pool of connections is managed by the HTTP server(program apache.exe), which actually connects to Oracle and therefore is listed in v$session, and it handles the creation/killing of connections, as wells as passes input/output between the browser and database server. So 1 web page with any 1 user request would show up as 1 session in v$session, no matter how many users are actually visiting the page. But once you get out of that page, assuming you are the only user, the session would still be there till the configured connection time in HTTP server runs out?

Is there any pointer for us to read more about the configuration of pool of connections, e.g. how many depending on load, how long each would be, or these are totally transparent so they are not configurable?

Tom:

<In mod_plsql -- it is normal that a pool a connections is maintained and reused>.

1. I assume you mean the connection is the physical NET8 connection between app server and dbms and not browser/app server?

2. When you have a connection like sql*plus connection you create it using userid/password. What userid/password the app server use to create all those connections in the pool?

3. Is one connection (from pool) defined with whatever is defined in the DAD?

4. If we have blank DAD then we can't use connection pooling?

Tom:

OK I think I understand how it works now.

1. App server creates all connections in the pool based on how many DADs are created and assigns the appropriate connections to users based on their login. Correct?

2. If system was configured with blank DAD, i.e. let user enter userid/password first time he logs in, the app server will already have a pool of generic connections but then will switch userid/password info in connection to whatever he enters? Correct.

Thank you

Tom,

We are using Oracle 9.2, a web server using cold fusion, and have a web application that stores session data in the database, "session data" meaning state information for currently logged-in users. The session data includes various information such as the user's ID and security level, and occasional other chunks of information that have to follow the user around as she/he is using the application. The storage of session information in the
database is pretty common in web-based applications.(ColdFusion calls this feature "client variables" and WebSphere calls it "persistent sessions.") It's not universal - sessions can be stored in RAM when it's guaranteed that the userwill connect to only one application server during a given work session. But when there are multiple servers behind a "non-sticky" load balancer, the servers need a way to share users' states, and the database is the mechanism for doing this.
If the user state is being updated frequently, this can translate to a lot of database writes - often one write per webpage-hit. The impact on performance can be significant. During testing of the application, using load runner, the amount of redo generated was reduced by recoding parts of the application so that the session state was being changed much less often. But, not all applications will have the luxury of stable session state.
Now, in our application, the session state has no long-term meaning. If the database were to fail and then be restored, users reconnecting to the application would be expected to establish new sessions, that is, to log in
again (whether manually or via V-GO or true SSO, it would still be a new login and a new session), so there would be no need to restore the session data up to the point of failure.
Since logging, cannot be turned off for single inserts, do you have any suggestions on limiting redo from the frequent session updates?

Hi

Let's say I have a repot generation tool programmed using JSP, Servlets, Thin JDBC, connection pool etc. If I run a report which takes 10 minutes and my PC hangs before the repot finishes what happens to the database session? Does the report keep on running? Is there a way to kill/contol database session if this situation happens? Imagine my uses arent patient and keep on running reports thinking their previous executions hanged my database will be flooded!

What happens if the application server fails? Do the database sessions stop as well if there are long queries running?

Discussion is quite useful. I like to know how a Dead Connection Detection is done in Apache.
I am using Apache , mod_plsql. I want to detect inactive clients.

Pl elaborate.
Thanks.

Thank you. but, client in the sense a web browser for me. I like to know if there is any mechanism to find out idle web browsers.
Can Apache figure out such clients and close the connections and give a "Page Expired" message to the web browser when an attempt is made by the client (on web browser) after the expiry time?

Hi Tom,
Reading this article was very insightful, but I have a question for you. One of my clients is having a problem using Forms Oracle Application Server 10G. It seems that sometimes, a user will be working on the form, and either IE will crash (big surprise) or their internet connection will die. This will result in a session with locks, causing user errors. It takes between 15 and 20 minutes for this session to die. Would setting DCD up resolve this issue or do I have to investigate more about OAS?

Thanks,
Scott

Tom:

Is this correct?

I have a web app that uses mod_plsql with one userid/password defined in it. Users log in with using application table accounts.

The only way for me to tell what user A did is to track the session id in V$session and tie that to his userid. I can log that in a table and then query this table and V$session to track/audit what quaeries this user A did.

2. If i had one database account per user then I will not need to do this. correct? oracle will automatcially track the activity for each database account.

Tom:

I don't think the app pushes the true identity. You mean if john/oranges user logged in using one common master DB account, I have to push "john" with every SQL statement to the DB? What does dbms_session.set_identifier really do.

I do not umderstand why you can't get the info from V$session. Every user who logs in has a unique session id. All i need to to insert into a table (john/his_session_id) and query v$session to see the SQL that he ran. Am i wrong here.

I query "app_users_table" which has one record with userid "john" and password "oranges".

the query is 

select count(*) from app_users_table where userid='john' and password='oranges'

What you say makes sense is that the application only provides the app userid/password once on login. but as far as db it is one master account connection.


Where do you implement the "dbms_session"? in every powerbuilder application code statement or it is defined for the whole session. If i set this up how would i go to oracle to see who ran what. is it in V$sql.

Tom, in your followup from November 5, 2002 - 8am US/Eastern of this thread your wrote "The most expensive thing you can do on an OS is process(/thread) creation and process(/thread)
destruction. Connect/Disconnect generally equates to just that. If you say "well, we will just
use MTS (aka shared server)" you will find the code path to connect via that is many times longer
then dedicated hence you will not achieve anything going down that path either."
Would you like to explain why MTS Connect/Disconnect is not suitable for ordinary web applications like PHP/Apache/Oracle (one connect per web page)? Even any longer then dedicated you wrote. I thought in MTS Oracle creates n (10 for example) server processes which would not be destroyed and then created again so it would help to avoid this type of overhead. Where am I wrong an what is the REAL thing for it? Thanks, Andrei.

Hi Tom, Do you have an observation against this practice?
Because if in some way an open form having a session open, is like a persistent connection, or I'm wrong.

Thank :)

Tom,

I have RAC 2 Node database runing the ATG application. We have 30 instance of ATG talking to database at the same time. Each instance open 5 connection and go upto 20 connection. Whenever we take look at gv$session we find 90% of the session to be inactive.

Please suggest an session management techinque to effictivley manage session

Thanks,
Brijesh