When using the Top Session Finder, often when using the Overall stored profile, sessions that are in the database in an inactive state are showing up as the top sessions. How can an inactive session rise to the top and be highlighted as top sessions when there might be 50 or so currently active session running at the time?
Also, usually one might use the tool to find out what sessions are bogging down the database. Why sometimes killing those inactive sessions acts like a flush and the database often times will appear to run much better (referring to sessions that have been in an inactive state for a long time). In the morning, sessions show up as top sessions but also have been inactive for hours. Could it be that resources are still being held by those sessions?
The data in the grid and pie chart do not indicate which session is working "hardest" right now - they indicate which sessions have done the most work since they connected. That is why an inactive session might rank higher than an active session. To find out which sessions have been active (working on a single query) for the longest time - to determine which session is working hardest right now, but that's not what the Top Session Finder does. One can find that information with this query:
select *
from v$session
where status = 'ACTIVE'
and type = 'USER'
and upper(program) not like 'ORACLE.EXE%' -- you would need to change this line if your database does not run on windows
order by last_call_et desc
Changing the option to exclude inactive sessions, the Top Session Finder will remember your preference.
==========
It's hard to say without being there, but yes, resources can be held by those sessions. It could be those sessions have some uncommitted transactions and are tying up undo space and holding locks. You might be able to get some additional information about those sessions by looking at them in the Session Browser before killing them.
The Session Browser can show you more than the Current Statement. Just look at the RHS/Bottom tabs. IO, Waits, Current Statement, Open Cursors, Access (Objects being accessed), Locks, RBS Usage, Long Ops, Statistics. There is a wealth of information there.
The inactive sessions are showing as top sessions because those sessions have done more work than any currently connected session. You can change the option if you would like so that only the active sessions are considered.
There is an algorithm that the Top Session Finder uses. The "overall" profile checks 7 parameters. The parameters have different weights (In this case, "CPU used by this session" has a weight of 1000, "redo size" has a weight of 100, etc). So, the algorithm is:
Find the values for each of these statistics in V$SESSTAT. Multiply each value by its associated weight. Each of those weighted values are shown in the columns of the grid. The sum of all the weights is the value that you see in the "WTDSUM" column in the grid. Sort the grid by that value (descending) and you have the sessions that have done the most work according to the weighted statistics. If you would like to change those weights or statistics, you can change them in the current profile or create a profile of your own.
