oracle session - ghdrako/doc_snipets GitHub Wiki

 SQL> select username,
            v$lock.sid,
            trunc(id1/power(2,16)) rbs,
            bitand(id1,to_number('ffff','xxxx'))+0 slot,
            id2 seq,
            lmode,
            request,
            block
     from v$lock, v$session
     where v$lock.type = 'TX'
    and v$lock.sid = v$session.sid
    and v$session.username = USER;
 USERNAME       SID    RBS   SLOT       SEQ      LMODE    REQUEST      BLOCK
----------- ------ ------ ------ --------- ---------- ---------- ---------
YODA             6     16     31       571          6          0          0
YODA             6     19      8       567          0          6          0
YODA            36     19      8       567          6          0          1
 SQL> select XIDUSN, XIDSLOT, XIDSQN from v$transaction;
    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ---------
        16         31        571
        19          8        567


 SQL> select
 (select username from v$session where sid=a.sid) blocker,
 a.sid,
 ' is blocking ',
 (select username from v$session where sid=b.sid) blockee,
 b.sid
 from v$lock a, v$lock b
 where a.block = 1
 and b.request > 0
 and a.id1 = b.id1
 and a.id2 = b.id2;
 BLOCKER        SID   'ISBLOCKING'   BLOCKEE        SID    
--------------- ------ ------------- --------------- -----
YODA                36  is blocking  YODA            6

DBA_WAITERS view

DBA_WAITERS view shows all sessions waiting for a lock, for example:

SQL> select holding_session, waiting_session, lock_type, mode_held,  mode_requested from dba_waiters;
 HOLDING_SESSION   WAITING_SESSION   LOCK_TYPE     MODE_HELD   MODE_REQUE
----------------- ----------------- ------------- ----------- ---------
36                6                 Transaction   Exclusive   Exclusive

Sesja 6 czeka na sesje 36.

Informacja na co sesje czekaja - na jaki event

$cat su.sql
su.sql
prompt
set pagesize 999 linesize 80
col l0  for a80
col l1  for a80
col l2  for a80
col l3  for a80
col l4  for a80
col l5  for a80
col l6  for a80
col l7  for a80
col l8  for a80
col l9  for a80
col l10 for a80
col l11 for a80
col tl for a80
set head off
set verify off
prompt '--------------------------------------------------------------------------------'
select
    'SID, serial: ' || s.sid || ', ' || s.serial# l0
  , 'OS pid:      ' || p.spid l1
  , 'Oracle USER: ' || s.username l2
  , 'Logon Time:  ' || to_char(s.logon_time,'Mon dd hh24:mi:ss') l3
  , 'Client USER: ' || s.osuser || '@' || s.machine l4
  , 'Program:     ' || s.program l5
  , 'Module:      ' || s.module l6
  , 'Action:      ' || s.action l7
  , 'Client ID:   ' || s.client_identifier l8
  , 'Status:      ' || s.status l9
  , 'State:       ' || decode(state,'WAITING',
                 'WAITING '||decode(round(wait_time_micro/1000000,0),
                                   0,round(wait_time_micro/1000,1)||' ms: ',
                                     round(wait_time_micro/1000000,3)||' sec ')||' seq#: '||s.seq#,
                 'ON CPU'||decode(round(wait_time_micro/1000,0),
                                   0,' ',
                                     ': '||round(wait_time_micro/1000,0)||' ms') ) l10
  , 'Wait event:  ' || decode(state,'WAITING', event||' ('||s.wait_class||')', ' ') l11
  , rpad('-',80,'-') tl
from
    v$session s
  , v$process p
  , v$sql q
where
    s.paddr = p.addr
and s.sql_address = q.address(+)
and s.sql_hash_value = q.hash_value(+)
and lower(s.osuser) like '%'||'&&1'||'%'
order by s.sid
/
set head on

Monitoring sesji

select
    px.qcsid qcs
  , decode(px.qcinst_id,NULL, ' QC', '(Slave)'||lpad(to_char(px.server#),4)) qcsl
  , s.sid
  , decode(px.qcinst_id,NULL,s.username, ' - '||lower(substr(s.program,length(s.program)-4,4) ) ) uname
  , s.status
  , decode(s.state,'WAITING',
                 'WAITING '||decode(round(s.wait_time_micro/1000000,0),
                                   0,round(s.wait_time_micro/1000,1)||' ms: ',
                                     round(s.wait_time_micro/1000000,3)||' s: ')||event,
                 'ON CPU'||decode(round(s.wait_time_micro/1000,0),
                                   0,' ',
                                     ': '||round(s.wait_time_micro/1000,0)||' ms') ) ||
                 decode(s.sid, sys_context('userenv','sid'), ' (My session)','') state
  , decode(s.final_blocking_session_status,'VALID',to_char(s.final_blocking_session),' ') bl_sid
  , s.sql_id
from
   v$session s,
   v$px_session px
where
     px.sid=s.sid (+) and px.serial#=s.serial# (+)
 and
     s.type = 'USER'
order by
     1,2

opis:

qcs - Identyfikatory sesji równoległych - numer „Query Coordinator” (QC) lub jego slave’a.
qcsl - Oznaczenie sesji równoległej vs. coordinator - jeżeli px.qcinst_id jest NULL, to sesja jest koordynatorem (QC), w przeciwnym razie to slave i wypisujemy jego numer (server#).
sid
uname - dla sesji QC pokazujemy username, dla slave’a fragment nazwy programu z Oracle (np. „(solap)”).
status – zwykle ACTIVE lub INACTIVE.
state - Stan i czas oczekiwania jesli WAITING', to dopisuje nazwe eventu i czas oczekiwania inaczej ON CPU i czas CPU w ms
bl_sid - jeżeli final_blocking_session_status = 'VALID', to wypisuje SID sesji, która blokowała tę
SQL_ID - identyfikator ostatniego lub aktualnie wykonującego się zapytania.

prompt
set pagesize 999
set linesize 180 trimout on
set head on
col sid for 9999
col username for a12
col bl_sid for a6
col process for a16
col state for a50

prompt Active BACKGROUND sessions:
select
    s.sid
  , p.pname||' '|| p.spid process
  , decode(s.state,'WAITING',
                   'WAITING '||round(s.wait_time_micro/1000,0)||' ms: '||s.event,
                   'ON CPU: '||round(s.wait_time_micro/1000,3)||' ms') state
  , decode(s.final_blocking_session_status,'VALID',to_char(s.final_blocking_session),' ') bl_sid
from
    v$session s
  , v$process p
where
    s.paddr = p.addr
and s.type = 'BACKGROUND'
and s.status = 'ACTIVE'
and s.wait_class <> 'Idle'
order by sid
/

col state for a50
col sql_id for a13
prompt Active USER sessions:
select
    sid
  , username
  , decode(state,'WAITING',
                 'WAITING '||decode(round(wait_time_micro/1000000,0),
                                   0,round(wait_time_micro/1000,1)||' ms: ',
                                     round(wait_time_micro/1000000,3)||' s: ')||event,
                 'ON CPU'||decode(round(wait_time_micro/1000,0),
                                   0,' ',
                                     ': '||round(wait_time_micro/1000,0)||' ms') ) ||
                 decode(sid, sys_context('userenv','sid'), ' (My session)','') state
  , decode(final_blocking_session_status,'VALID',to_char(final_blocking_session),' ') bl_sid
  , sql_id
from
   v$session
where
   type = 'USER' and status = 'ACTIVE'
/

Active Background Sessions:

Purpose: Identifies background sessions that are currently active and not idle.
Key Columns:
- SID: Session Identifier.
- PROCESS: Concatenation of process name and OS process ID.
- STATE: Indicates if the session is waiting or on CPU, along with wait time and event.
- BL_SID: Final blocking session ID if applicable.
Filters Applied:
- s.type = 'BACKGROUND': Selects background sessions.
- s.status = 'ACTIVE': Ensures the session is active.
- s.wait_class <> 'Idle': Excludes idle wait classes.

Active User Sessions:

Purpose: Lists user sessions that are active, detailing their current state and any blocking sessions.
Key Columns:
- SID: Session Identifier.
- USERNAME: Name of the user.
- STATE: Describes if the session is waiting or on CPU, including wait time and event.
- BL_SID: Final blocking session ID if applicable.
- SQL_ID: Identifier of the SQL statement being executed. Filters Applied:
- type = 'USER': Selects user sessions.
- status = 'ACTIVE': Ensures the session is active.

Wylistowanie sesji

SQL> SELECT b.spid, a.sid, a.serial#, a.username, a.program
     FROM v$session a, v$process b
     WHERE a.paddr = b.addr
     AND a.type = 'USER';


SELECT SID, SERIAL#, USERNAME, MACHINE, STATUS, SCHEMANAME, PROGRAM 
FROM V$SESSION
ORDER BY STATUS DESC;

SID – identyfikator sesji.
SERIAL# – numer seryjny sesji (potrzebny do jej zakończenia).
USERNAME – nazwa użytkownika.
MACHINE – nazwa komputera użytkownika.
STATUS – aktywny (ACTIVE) lub nieaktywny (INACTIVE).
SCHEMANAME – schemat, do którego sesja jest przypisana.
PROGRAM – aplikacja, która używa sesji.

Zakonczenie sesji

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
ALTER SYSTEM KILL SESSION '105,3215' IMMEDIATE;

Polecenie nie kończy sesji w gwałtowny sposób, lecz prosi ją o zakończenie jej samodzielnie. Bardzo często oznacza to, że sesja próbuje dokończyć bieżącą transakcję. Następnie otrzymuje status MARKEDFORKILL. Opcja IMMEDIATE przywraca kontrolę bezpośrednio do sesji. Opcja ta nie zmienia jednak sposobu zakończenia sesji.

Aby zakończyć sesję w bazie danych Real Application Clusters, konieczne jest również podanie numeru instancji. Polecenie wygląda następująco:

Alter System KILL Session '<SID>,<SERIAL#>,@<N>'

, gdzie to numer instancji.

Z poziomu systemu operacyjnego

SELECT spid FROM V$PROCESS WHERE addr = (SELECT paddr FROM V$SESSION WHERE SID = 105);

kill -9 <PID>

Ustawianie limitu czasu dla nieaktywnych sesji

Można ograniczyć czas bezczynności użytkowników, aby zwalniać zasoby.

1️. Ustaw limit czasu w profilu użytkownika:

ALTER PROFILE DEFAULT LIMIT IDLE_TIME 30; -- 30 minut bezczynności

2️. Sprawdź aktualne ustawienia profilu:

SELECT PROFILE, RESOURCE_NAME, LIMIT 
FROM DBA_PROFILES WHERE RESOURCE_NAME = 'IDLE_TIME';

Jeśli użytkownik przekroczy czas IDLE_TIME, jego sesja zostanie automatycznie zamknięta.

Monitorowanie aktywności sesji

Oracle umożliwia śledzenie aktywnych sesji oraz ich wykorzystania CPU, I/O i pamięci.

🔹 Sesje aktywne w danej chwili:

SELECT SID, SERIAL#, USERNAME, STATUS, SCHEMANAME, OSUSER, MACHINE FROM V$SESSION WHERE STATUS = 'ACTIVE';

🔹 Sesje zużywające najwięcej CPU:

SELECT s.SID, s.SERIAL#, s.USERNAME, t.SQL_ID, t.PHYSICAL_READS, t.CPU_TIME FROM V$SESSION s, V$SQLAREA t WHERE s.SQL_ID = t.SQL_ID ORDER BY t.CPU_TIME DESC;

🔹 Sesje powodujące blokady:

SELECT blocking_session, sid, serial#, wait_class, event FROM V$SESSION WHERE blocking_session IS NOT NULL;

Zarządzanie połączeniami użytkowników a) Ograniczenie liczby sesji dla użytkownika

Można kontrolować, ile sesji może otworzyć pojedynczy użytkownik:

ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 3;

📌 Maksymalnie 3 aktywne sesje na użytkownika. 📌 Aby zobaczyć limity:

SELECT PROFILE, RESOURCE_NAME, LIMIT 
FROM DBA_PROFILES WHERE RESOURCE_NAME = 'SESSIONS_PER_USER';

b) Zamykanie wszystkich sesji użytkownika

Jeśli chcesz rozłączyć wszystkie sesje danego użytkownika:

BEGIN
    FOR r IN (SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'NAZWA_UŻYTKOWNIKA') LOOP
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.SID || ',' || r.SERIAL# || ''' IMMEDIATE';
    END LOOP;
END;

Która sesja powoduje obciazenie CPU

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.osuser,
       s.program,
       p.spid AS os_pid,
       s.sql_id,
       s.module,
       s.machine,
       se.value AS cpu_time
FROM v$session s
JOIN v$sesstat se ON s.sid = se.sid
JOIN v$statname sn ON se.statistic# = sn.statistic#
JOIN v$process p ON s.paddr = p.addr
WHERE sn.name = 'CPU used by this session'
ORDER BY se.value DESC FETCH FIRST 10 ROWS ONLY;

Opis:

s.sid, serial# – identyfikatory sesji
sql_id – zapytanie, które aktualnie się wykonuje
cpu_time – ilość czasu CPU użyta przez sesję
spid – identyfikator procesu na systemie operacyjnym (do użycia np. z top/ps)

Sprawdzenie treści zapytania (SQL_ID)

Po zidentyfikowaniu SQL_ID z powyższego zapytania, możesz zobaczyć treść zapytania:

SELECT sql_id, sql_text
FROM v$sql
WHERE sql_id = 'twoj_sql_id';

Aktywnie używające CPU teraz

Możesz też podejrzeć tylko aktywne sesje, które właśnie zużywają CPU:

SELECT sid, serial#, username, status, sql_id, wait_class
FROM v$session
WHERE state = 'ON CPU' AND type = 'USER';