Shulou(Shulou.com)06/01 Report--

STATUS

ACTIVE-Session currently executing SQL

INACTIVE

KILLED-Session marked to be killed

CACHED-Session temporarily cached for use by Oracle*XA

SNIPED-Session inactive, waiting on the client

(1) A session in which active is in this state indicates that it is executing and is active.

Official documentation: Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session.

(2) the session in which killed is in this state is marked as deleted, indicating that an error has occurred and is rolling back.

Of course, it also takes up system resources. Another point is that the state of killed usually lasts for a long time, and it doesn't work to kill it with the tool pl/sql developer under windows. Use the command: alter system kill session 'sid,serial#'.

(3) A session in which inactive is in this state indicates that it is not executing

This state is in a wait operation (that is, waiting for the SQL statement to be executed), usually when the DML statement has been completed. But the connection is not released, this may not be released in the program, if it is using middleware to connect, it may also be caused by the configuration of middleware or bug. Inactive has no effect on the database itself, but if the program does not commit in time, it will take up too many sessions. It is easy for the session of DB to reach the limit.

Solution:

1 SQLNET.EXPIRE_TIME (set size appropriately)

2 alter profile default limit idle_time 5

The number of connections to the application can be determined according to the following SQL and the hostname of the application server:

SELECT b.MACHINE, b.PROGRAM, COUNT (*) FROM v$process a, v$session b

WHERE a.ADDR = b.PADDR AND b.USERNAME IS NOT NULL

ADN A.TYPE! = 'BACKGROUND'

And a.status in ('ACTIVE','INACTIVE')

GROUP BY b.MACHINE, b.PROGRAM

ORDER BY COUNT (*) DESC

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.