While goggling I got a good notes on different state and status of a oracle session .So I thought of sharing it here .
First, we have V$SESSION.STATUS, which can be ACTIVE or INACTIVE. A session is ACTIVE if it's in a database call. So, think of this from the client side point of view. A session does a parse call, or an execute call, or a fetch call, etc. For the duration of that call, till control returns back to the client, that session is said to be ACTIVE.From the time the calls returns, till the time of the next call, the session is INACTIVE.
In other words: what is an inactive session? It depends of the architecture of your applications, but just some examples:
- interactive sqlplus session of a user: the user sends statements, receives results, thinks about next statement, ... The session is INACTIVE most of the time (active only during the execution of the statements)
- batch job: statements are followed by each other quickly, the session is often "active", except if there is an interaction with the client.
- usual 3-tier application with or without session pooling: most of the time the sessions are "idle", waiting the next instruction from the client (who for example is filling a form on the screen before clicking on the "submit" button)
Consider an batch job fetching large amounts of data from the database in 1000 row chunks, and then processing those rows on a middle tier before fetching the next 1000 rows. If the middle tier takes any length of time to process those rows, then the database will show the session as INACTIVE ie waiting on SQL*NET message from client. If you kill "INACTIVE" processes then you would likely kill this and have to rerun the batch job.
So simply being connected to the database DO NOT make your session active. You can check it by making a simple connection to a database and by checking it's status . It should be in INACTIVE status .
A session is active ONLY when it executes a STATEMENT in the database,and becomes inactive again when that statement is finished. INACTIVE session usually DOES NOT consume resources like CPU, IO. It may consume some memory, for example for session level package data, but this is usually not significant. The biggest problem with INACTIVE sessions might be that such a session can hold data locks that block other sessions and prevent them from proceeding.
A session with STATUS of INACTIVE, will always be in STATE of WAITING, waiting on the 'SQL*NET message from client' wait. So, in that case, it means the server process is waiting around for work to do. It's in between calls, so, STATUS is INACTIVE, and it's waiting on that network port, to receive the next call from the client.
Inactive sessions are waiting, Most likely they are waiting on a user who may be in the middle of a multi step transaction, between transactions, or idle.So its not always a good idea to kill INACTIVE session , as it may contains session that are not yet committed. So Forget about the idea of killing any inactive session in the database if you don't exactly know WHY you need to kill it.
Next, we have V$SESSION.STATE. This is probably more useful to think of from the server process point of view. This refers to whether the server process is currently running, i.e. on the CPU, or WAITING, i.e., waiting on a resource. Possible values for this column are WAITING, WAITED KNOWN TIME, WAITED SHORT TIME, and WAITED UNKNOWN TIME. Of those possibilities, a session is only actually waiting if STATE is WAITING. All the other values mean that it's no longer waiting, but is running on CPU.
You can establish a policy to automatically disconnect sessions that were IDLE for more than a given time ( by using PROFILES ), but I am not sure that the users will thank you for doing this .An Oracle session status will show as SNIPED when you set the idle_time parameter to disconnect inactive sessions.
An example of a session that's ACTIVE and has STATE of WAITING, would be a session that's, for example, doing a full table scan , gc cr multi block request etc . So for full table scan, it's got lots of data to read from disk. While the session waits for the read from disk to complete, the session waits on 'db file scattered read'.
Finally, for completeness, the difference between the different possible values of the STATE column. I already covered WAITING. If a session is not waiting, it's now on CPU, and it previously waited. If so, it either waited more than 10 ms, in which case it will report WAITED KNOWN TIME, or less than 10 ms, in which case it reports WAITED SHORT TIME, or timed_statistics is false, in which case this column will always be WAITED UNKNOWN TIME. Also, it's important to pay attention to this column, when trying to interpret the WAIT_TIME and SECONDS_IN_WAIT columns.
First, we have V$SESSION.STATUS, which can be ACTIVE or INACTIVE. A session is ACTIVE if it's in a database call. So, think of this from the client side point of view. A session does a parse call, or an execute call, or a fetch call, etc. For the duration of that call, till control returns back to the client, that session is said to be ACTIVE.From the time the calls returns, till the time of the next call, the session is INACTIVE.
In other words: what is an inactive session? It depends of the architecture of your applications, but just some examples:
- interactive sqlplus session of a user: the user sends statements, receives results, thinks about next statement, ... The session is INACTIVE most of the time (active only during the execution of the statements)
- batch job: statements are followed by each other quickly, the session is often "active", except if there is an interaction with the client.
- usual 3-tier application with or without session pooling: most of the time the sessions are "idle", waiting the next instruction from the client (who for example is filling a form on the screen before clicking on the "submit" button)
Consider an batch job fetching large amounts of data from the database in 1000 row chunks, and then processing those rows on a middle tier before fetching the next 1000 rows. If the middle tier takes any length of time to process those rows, then the database will show the session as INACTIVE ie waiting on SQL*NET message from client. If you kill "INACTIVE" processes then you would likely kill this and have to rerun the batch job.
So simply being connected to the database DO NOT make your session active. You can check it by making a simple connection to a database and by checking it's status . It should be in INACTIVE status .
A session is active ONLY when it executes a STATEMENT in the database,and becomes inactive again when that statement is finished. INACTIVE session usually DOES NOT consume resources like CPU, IO. It may consume some memory, for example for session level package data, but this is usually not significant. The biggest problem with INACTIVE sessions might be that such a session can hold data locks that block other sessions and prevent them from proceeding.
A session with STATUS of INACTIVE, will always be in STATE of WAITING, waiting on the 'SQL*NET message from client' wait. So, in that case, it means the server process is waiting around for work to do. It's in between calls, so, STATUS is INACTIVE, and it's waiting on that network port, to receive the next call from the client.
Inactive sessions are waiting, Most likely they are waiting on a user who may be in the middle of a multi step transaction, between transactions, or idle.So its not always a good idea to kill INACTIVE session , as it may contains session that are not yet committed. So Forget about the idea of killing any inactive session in the database if you don't exactly know WHY you need to kill it.
Next, we have V$SESSION.STATE. This is probably more useful to think of from the server process point of view. This refers to whether the server process is currently running, i.e. on the CPU, or WAITING, i.e., waiting on a resource. Possible values for this column are WAITING, WAITED KNOWN TIME, WAITED SHORT TIME, and WAITED UNKNOWN TIME. Of those possibilities, a session is only actually waiting if STATE is WAITING. All the other values mean that it's no longer waiting, but is running on CPU.
You can establish a policy to automatically disconnect sessions that were IDLE for more than a given time ( by using PROFILES ), but I am not sure that the users will thank you for doing this .An Oracle session status will show as SNIPED when you set the idle_time parameter to disconnect inactive sessions.
An example of a session that's ACTIVE and has STATE of WAITING, would be a session that's, for example, doing a full table scan , gc cr multi block request etc . So for full table scan, it's got lots of data to read from disk. While the session waits for the read from disk to complete, the session waits on 'db file scattered read'.
Finally, for completeness, the difference between the different possible values of the STATE column. I already covered WAITING. If a session is not waiting, it's now on CPU, and it previously waited. If so, it either waited more than 10 ms, in which case it will report WAITED KNOWN TIME, or less than 10 ms, in which case it reports WAITED SHORT TIME, or timed_statistics is false, in which case this column will always be WAITED UNKNOWN TIME. Also, it's important to pay attention to this column, when trying to interpret the WAIT_TIME and SECONDS_IN_WAIT columns.
No comments:
Post a Comment