v$session Sistem Tablosu

Giriş
TCP bağlantısı eşittir session gibi düşünülüyor. Ancak böyle olmak zorunda değil. Açıklaması şöyle

Connections vs. Sessions

It surprises many people to discover that a connection is not synonymous with a session. In most people’s eyes they are the same, but the reality is they do not have to be. A connection may have zero, one, or more sessions established on it. Each session is separate and independent, even though they all share the same physical connection to the database. A commit in one session does not affect any other session on that connection. In fact, each session using that connection could use different user identities! In Oracle, a connection is simply a physical circuit between your client process and the database instance — a network connection, most commonly. The connection may be to a dedicated server process or to a dispatcher. As previously stated, a connection may have zero or more sessions, meaning that a connection may exist with no corresponding sessions.

v$session Tablosu Nedir

Açıklaması şöyle

The standard views to check for database performance are v$session, v$sql, and others. The v$ views work well if the database is hosted on a single node. However, if the database is split into multiple nodes, we need to look at the gv$ views. The INST_ID column will tell us which exact node is fulfilling the request.

Örnek - All Established Connections

Şöyle yaparız

elect status, count(1) 
from gv$session
where module = '<module-name>' 
and type = 'USER' 
group by status;

Örnek - Active Connections

Şöyle yaparız

select last_call_et, module, sql_id, event, blocking_instance, blocking_session,
  seconds_in_wait 
from gv$session
where module = '<module-name>' 
and type = 'USER' 
and status = 'ACTIVE' 
order by last_call_et desc, module, sql_id, event, blocking_instance, blocking_session,
  seconds_in_wait;

select * from gv$sql where sql_id = '<above-sql-id>';

Örnek - Inactive Connections

Şöyle yaparız

select last_call_et, module, prev_sql_id   from gv$session 
  where module = '<module-name>' 
  and type = 'USER' 
  and status = 'INACTIVE';

select * from gv$sql where sql_id = '<above-prev-sql-id>';  

Örnek
Şöyle yaparız.

SQL> select sid, process, port, paddr from v$session where username = 'BP';

       SID PROCESS                        PORT PADDR
---------- ------------------------ ---------- ----------------
       395 31251                         35298 0000000066E75338
       399 31251                         35298 0000000066E75338