| inactive sessions in v$session [message #174161] |
Fri, 26 May 2006 05:21  |
iannelis
Messages: 10
Registered: May 2006
|
Junior Member |
|
|
Hi,
I am working in an Oracle 9i/Weblogic/J2EE platform. And when i look for session info in v$session view, i see that there are many sessions that have a status "Inactive". I already figured it out what it means- the session is ACTIVE when it is doing an SQL query at the time and the session is INACTIVE when it is not doing an SQL query at that particular moment.
But i have questions:
1) If a client logs in to my webapplication and does a SQL query- then the sessions status is ACTIVE. After that, when the client just leaves (logs out or just closes the browser) then Oracle marks that connection as 'INACTIVE'- Oracle does not KILL that session.
Ok let that be, but can another client then log in to my webapplication (from different computer) and get that same INACTIVE connection and start to use it?? If not, then these "abandoned" connection are truly useless, because they still use ORACLE resources (memory).
2)Another thing is that there are many INACTIVE sessions in v$session that have a name "plsqldev.exe" in PROGRAM column. That is a database client that i use to connect directly to my DB. But basicly i have only one PL/SQL program with one SQL query window open (this session is marked ACTIVE in v$session). So are these other 10 INACTIVE "plsqldev.exe" sessions meant for new plsql clients that may start to use the database or can only that particular user for whom the session was created at first place use that session?
And finally- sessions that are INACTIVE and have "plsqldev.exe" as a PROGRAM in v$session - is there any chance that a client logs in to webapplication and then gets that INACTIVE session?
If not, then these 10 INACTIVE plsqldev sessions (allthough the user has maybe shut down the program) are wasteless for webapplication users and they just starve the database.
Also a screenshot for illustration.
Waiting for your comments,
Thanks!
[Updated on: Fri, 26 May 2006 06:20] Report message to a moderator |
|
|
|
| Re: inactive sessions in v$session [message #174192 is a reply to message #174161] |
Fri, 26 May 2006 07:09  |
aciolac
Messages: 242
Registered: February 2006
|
Senior Member |
|
|
|
Your main problem is that your application doesnt close session before exit. When You want to work with database, then you OPEN new connection to database(execute command that opens connection). And when you terminated work with database, you must to CLOSE your session(execute command that close connection). Oracle colse died session(session which have not programs working with them) only after some time - not immediately after exiting from program.
|
|
|
|
| Re: inactive sessions in v$session [message #174197 is a reply to message #174192] |
Fri, 26 May 2006 07:22 |
iannelis
Messages: 10
Registered: May 2006
|
Junior Member |
|
|
No the application closes the connections correctly! It means that connections are closed in FINALLY block with con.close() etc. When doing so, the connection is given back to Weblogic's connection pool. Pool is configured so :
InitialCapacity=15
MaximumCapacity=30.
So there is always at least 15 connections in pool that the client can quickly claim.
|
|
|
|
|
|
| Re: inactive sessions in v$session [message #174199 is a reply to message #174198] |
Fri, 26 May 2006 07:40 |
iannelis
Messages: 10
Registered: May 2006
|
Junior Member |
|
|
Yes, the application closes the connection. There is also
"Inactive Connection Timeout" setting enabled in weblogic, so inactive connections will forcibly be released back into the connectionpool after specified time.
|
|
|
|
| Re: inactive sessions in v$session [message #174202 is a reply to message #174199] |
Fri, 26 May 2006 08:03 |
aciolac
Messages: 242
Registered: February 2006
|
Senior Member |
|
|
Did you see from debugger that applciation close session? For example Whe I am test programs, and want to see if some part of code precisely executes, I put a one message before executing tested command, and one after - I I see in output of program both messages, I know that command was executed.
And yest one question -Is Your application connects directy to database, on connects by third program?
|
|
|
|
| Re: inactive sessions in v$session [message #174205 is a reply to message #174202] |
Fri, 26 May 2006 08:11 |
iannelis
Messages: 10
Registered: May 2006
|
Junior Member |
|
|
Application connects directly to database. I also connect to database using PL/SQL developer program.
Yes, i am sure that the application closes connections. The fact is (stated in Oracle manuals) than Oracle marks sessions as INACTIVE and there is no effective processes to remove these sessions in Oracle.
But still, the main questions were the ones asked in the first entry of this thread.
|
|
|
|
|
|
|
|
| Re: inactive sessions in v$session [message #174338 is a reply to message #174337] |
Sun, 28 May 2006 03:11 |
aciolac
Messages: 242
Registered: February 2006
|
Senior Member |
|
|
|
The goddest sourse is metalink.oracle.com, but you need a passwrod to connect here. Explain please more detalied your problem, because You are not said nothing - only informed that you have some problem.
|
|
|
|
|
|
|
|
| Re: inactive sessions in v$session [message #174341 is a reply to message #174161] |
Sun, 28 May 2006 03:34 |
iannelis
Messages: 10
Registered: May 2006
|
Junior Member |
|
|
|
I think you are mistaking now, you see- Oracle does not know who is the creater of the connection. There is basicly a tunnel between Oracle DB and Application server's ConnectionPool. For Oracle- the session is just created by one user (the application server). So when the session is created between Appservers ConnectionPool and Oracle- then another webapplications's user can log in to the application and get that inactive connection from the pool, which was abondoned before form another webapplication's user. Correct?
|
|
|
|
| Re: inactive sessions in v$session [message #174342 is a reply to message #174341] |
Sun, 28 May 2006 03:50 |
aciolac
Messages: 242
Registered: February 2006
|
Senior Member |
|
|
Oh, Your users not connect directly to Oracle database, but connects via some application server??? You are not precised this thing. In this case managment of sessions which was created between Oracle Database and Your applcation is strictly problem of your application. You can use connections in any manner You want - for Oracle there no difference. But in this context it is unclear some things for me:
1)You are asking at the start of this thread If You can use session crearted on Oracle by user, connected to Your Application. But later You are write that user close his session. My question - closes session between user and application or between applicaiton and database?
2)It is not good idea to open for erery user that connects to Your application new session in the database. Normally, when You start Your application(he servs as SERVER for users), he opens some number of connection to the Oracle database. When some user connects to the application server, he give the one of the connections that he have to the connected user. When user exits, he leave connection for application server, whic can give them to other connected user.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
When some application open a session and stops(crushes, disapears connection with database etc.), no one cannot use his session. The session become a fantom. This fantoms are clearing by Oracle background process called SMON.