ORACLE SQL QUERIES : Sessions

SECCIÓN : Sesiones

SELECT	rpad(c.name||':',11)||
	rpad(' current logons='||(to_number(b.sessions_current)),20)||
	'cumulative logons='||rpad(substr(a.value,1,10),10)||
	'highwater mark='||b.sessions_highwater Information
FROM
   v$sysstat a,
   v$license b,
   v$database c
WHERE
   a.name = 'logons cumulative' ;
col ID format a15;
col USERNAME FORMAT A30 ;
col OSUSER FORMAT A40 ;
COL MACHINE FORMAT A20 WORD WRAPPED;
COL "SID,SERIAL" FORMAT A15;
SELECT USERNAME, 
        SID || ',' || SERIAL# "SID,SERIAL", 
        STATUS, 
        OSUSER, 
        MACHINE, 
        SQL_ID, 
        LAST_CALL_ET "Last Activity"
FROM v$session
WHERE username IS NOT NULL
ORDER BY STATUS DESC, last_call_et DESC
/
COLUMN USERNAME FORMAT A35 WORD_WRAPPED ;
COLUMN num_of_sessions FORMAT 99999999999 ;
SELECT ses.username username,
        COUNT(*) num_of_sessions
FROM V$SESSION SES, V$PROCESS P
WHERE SES.paddr = p.addr
AND ses.username IS NOT NULL
GROUP BY ses.username
ORDER BY num_of_sessions
/
COLUMN SID_SERIAL FORMAT A20 ;
COLUMN OSPID FORMAT A7;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A25 WORD_WRAPPED ;
COLUMN OSUSER FORMAT A45 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A33 WORD_WRAPPED ;
col client_info format a40 ;
COLUMN PROGRAM FORMAT A70 WORD_WRAPPED;
SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
      p.spid AS OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.OSUSER OSUSER,
      SES.MACHINE HOSTNAME,
      SES.PROGRAM PROGRAM,
      SES.CLIENT_INFO
FROM V$SESSION SES, V$PROCESS P
WHERE SES.paddr = p.addr
ORDER BY SES.MACHINE
/

Con usuario inicializado:

COLUMN SID_SERIAL FORMAT A20 ;
COLUMN OSPID FORMAT A7;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A25 WORD_WRAPPED ;
COLUMN OSUSER FORMAT A45 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A33 WORD_WRAPPED ;
COLUMN PROGRAM FORMAT A70 WORD_WRAPPED;
SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
      p.spid AS OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.OSUSER OSUSER,
      SES.MACHINE HOSTNAME,
      SES.PROGRAM PROGRAM,
      SES.CLIENT_INFO
FROM V$SESSION SES, V$PROCESS P
WHERE SES.paddr = p.addr
AND ses.username IS NOT NULL
ORDER BY SES.MACHINE
/

Más:

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a80
SELECT s.sid || ',' || s.serial# "SID/SERIAL", s.username, s.osuser, p.spid "OS PID", s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
ORDER BY to_number(p.spid)
/

Con status=ACTIVE y SIN sqltext

SET linesize 280;
COLUMN SID_SERIAL FORMAT A20 ;
COLUMN OSPID FORMAT A7;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A35 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A40 WORD_WRAPPED ;
COLUMN PROGRAM FORMAT A20 WORD_WRAPPED ;
COLUMN EVENT FORMAT A60 WORD_WRAPPED ;
col OSUSER format a30 ;
COLUMN WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 ;
COLUMN WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 ;
COLUMN QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED ;
SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
      p.spid OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.OSUSER OSUSER,
      SES.MACHINE HOSTNAME,
      lpad(SES.PROGRAM,15) PROGRAM,
      LPAD(WA.EVENT,30) EVENT,
      WA.WAIT_CLASS WAITCLASS,
      WA.SECONDS_IN_WAIT WAITINGSECS
FROM V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
WHERE SES.STATUS='ACTIVE'
AND SES.SQL_ID=SQL.SQL_ID
AND SES.SID=WA.SID
AND SES.paddr = p.addr
/
CLEAR COLUMNS ;

Con status=ACTIVE

SET linesize 280;
COLUMN SID FORMAT 99999 ;
COLUMN SERIAL FORMAT 999999 ;
COLUMN OSPID FORMAT A5;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A20 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A40 WORD_WRAPPED ;
COLUMN PROGRAM FORMAT A20 WORD_WRAPPED ;
COLUMN EVENT FORMAT A60 WORD_WRAPPED ;
COLUMN P1TEXT FORMAT A20 WORD_WRAPPED;
COLUMN WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 ;
COLUMN WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 ;
COLUMN QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED ;
SELECT SES.SID SID,
      SES.SERIAL# SERIAL,
      p.spid OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.MACHINE HOSTNAME,
      lpad(SES.PROGRAM,15) PROGRAM,
      LPAD(WA.EVENT,30) EVENT,
      WA.P1TEXT P1TEXT,
      WA.WAIT_CLASS WAITCLASS,
      WA.SECONDS_IN_WAIT WAITINGSECS,
      SQL.SQL_TEXT QUERY
FROM V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
WHERE SES.STATUS='ACTIVE'
AND SES.SQL_ID=SQL.SQL_ID
AND SES.SID=WA.SID
AND SES.paddr = p.addr
/
CLEAR COLUMNS ;

Mediante SQL_ID:

SET linesize 280;
COLUMN SID_SERIAL FORMAT A20 ;
COLUMN OSPID FORMAT A7;
COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
COLUMN USERNAME FORMAT A20 WORD_WRAPPED ;
COLUMN HOSTNAME FORMAT A40 WORD_WRAPPED ;
COLUMN PROGRAM FORMAT A20 WORD_WRAPPED ;
COLUMN EVENT FORMAT A60 WORD_WRAPPED ;
COLUMN P1TEXT FORMAT A20 WORD_WRAPPED;
COLUMN WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 ;
COLUMN WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 ;
COLUMN QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED ;
SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
      p.spid OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.MACHINE HOSTNAME,
      lpad(SES.PROGRAM,15) PROGRAM,
      LPAD(WA.EVENT,30) EVENT,
      WA.P1TEXT P1TEXT,
      WA.WAIT_CLASS WAITCLASS,
      WA.SECONDS_IN_WAIT WAITINGSECS,
      SQL.SQL_TEXT QUERY
FROM V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
WHERE SES.SQL_ID IS NOT NULL
AND SES.SQL_ID=SQL.SQL_ID
AND SES.SID=WA.SID
AND SES.paddr = p.addr
/
SELECT username, MACHINE, last_call_et seconds, STATUS
FROM v$session
WHERE username IS NOT NULL
ORDER BY last_call_et
/
SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='USUARIO';
ALTER system KILL SESSION 'SID,SERIAL' ;
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''' IMMEDIATE ; ' 
FROM V$SESSION 
WHERE USERNAME IN ('USERNAME1', 'USERNAMEn')

HARD LOCKS

  • v$lock based:

^ Human readable ^ plain mode ^

SELECT 'SID ' || l1.sid || ' is blocking ->' || l2.sid blocking
FROM v$lock l1, v$lock l2
WHERE l1.block =1 AND l2.request > 0
AND l1.id1=l2.id1
AND l1.id2=l2.id2
/
SELECT L1.SID, L2.SID
FROM V$LOCK L1, V$LOCK L2
WHERE 
   L1.BLOCK=1 
   AND L2.REQUEST > 0 
   AND L1.ID1=L2.ID1 
   AND L1.ID2=L2.ID2 ;
  • v$session based:
col wait_class format a40
col event format a60
 
SELECT
    sid || '.' || serial# sid_serial,
    username,
    osuser,
    wait_class_id,
    wait_class#,
    wait_class,
    event
FROM
    v$session
WHERE
        state = 'WAITING'
    AND
        wait_class = 'Concurrency';

Bloqueos de usuario (Soft locks)

Formateo:

COL USERNAME FORMAT A23
COL OSUser FORMAT A23
COL Machine FORMAT A33
COL mode_held FORMAT A23
COL mode_requested FORMAT A23
COL lock_type format A25
COL lock_id1 format A10
COL lock_id2 format a10
COL object_name format a33
col object_type format a20
  • Tx enqueue ready2kill:
COLUMN SID_SERIAL FORMAT A15 ;
SELECT
    lk.sid || ',' || se.serial# AS SID_SERIAL,
  se.username,
  se.OSUser,
  se.Machine,
  DECODE(lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
  DECODE(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.lmode)) mode_held,
  DECODE(lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.request)) mode_requested,
  TO_CHAR(lk.id1) lock_id1,
  TO_CHAR(lk.id2) lock_id2,
  DECODE(block, 0, 'No', 1, 'Yes', 2, 'Global') block,
  se.lockwait
FROM
  v$lock lk,
  v$session se
WHERE
  lk.type  = 'TX'
AND lk.SID = se.SID
;
  • Transaction enqueue (original):
SELECT
  lk.SID,
  se.username,
  se.OSUser,
  se.Machine,
  DECODE(lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
  DECODE(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.lmode)) mode_held,
  DECODE(lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.request)) mode_requested,
  TO_CHAR(lk.id1) lock_id1,
  TO_CHAR(lk.id2) lock_id2,
  DECODE(block, 0, 'No', 1, 'Yes', 2, 'Global') block,
  se.lockwait
FROM
  v$lock lk,
  v$session se
WHERE
  lk.type  = 'TX'
AND lk.SID = se.SID
;
  • DML enqueue & User supplied:
SELECT
  lk.SID,
  se.username,
  se.OSUser,
  se.Machine,
  DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
  DECODE (lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.lmode)) mode_held,
  DECODE (lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.request)) mode_requested,
  TO_CHAR (lk.id1) lock_id1,
  TO_CHAR (lk.id2) lock_id2,
  ob.owner,
  ob.object_type,
  ob.object_name,
  DECODE(lk.Block, 0, 'No', 1, 'Yes', 2, 'Global') block,
  se.lockwait
FROM
  v$lock lk,
  dba_objects ob,
  v$session se
WHERE
  lk.TYPE IN ('TM','UL')
AND lk.SID = se.SID
AND lk.id1 = ob.object_id
;

Sid,SERIAL# de los bloqueantes primarios

Sesiones que inician la cadena de bloqueos:

SELECT SID || ',' || SERIAL#
FROM V$SESSION
WHERE SID IN (
    SELECT l1.sid
    FROM v$lock l1, v$lock l2
    WHERE l1.block =1 AND l2.request > 0
    AND l1.id1=l2.id1
    AND l1.id2=l2.id2
    AND l1.id1 NOT IN (
        SELECT l2.sid
        FROM v$lock l1, v$lock l2
        WHERE l1.block =1 AND l2.request > 0
        AND l1.id1=l2.id1
        AND l1.id2=l2.id2
        )
    )
/

Bloqueadores

Extrictamente bloqueadores (sesiones que bloquean otras):

SELECT * FROM DBA_BLOCKERS ;

lock de DDLS

Mirar objetos que se están accediendo:

col object format a50 ;
col TYPE format a20 ;
col owner format a20 ;
SELECT * FROM V$ACCESS WHERE TYPE LIKE 'TYPE'  ;

Y el propio bloqueo en sí:

COL SESSION_ID FORMAT 999999;
COL OWNER FORMAT A20;
COL NAME FORMAT A30;
COL TYPE FORMAT A20;
SELECT * FROM DBA_DDL_LOCKS ;

lock de DML

Casi lo mismo que el anterior:

COL SESSION_ID FORMAT 999999;
COL OWNER FORMAT A20;
COL NAME FORMAT A30;
COL TYPE FORMAT A20;
SELECT * FROM DBA_DML_LOCKS ;
SELECT TO_CHAR(m.END_TIME,(''DD-MM-YYYY HH24:MI:SS'')) e_dtm, m.intsize_csec/100 ints, s.username usr, m.session_id sid, m.session_serial_num ssn,
	ROUND(m.cpu) cpu100, m.physical_reads prds, m.logical_reads lrds, m.pga_memory pga, m.hard_parses hp, m.soft_parses sp, m.physical_read_pct prp,
	m.logical_read_pct lrp
FROM v$sessmetric m, v$session s
WHERE (m.physical_reads > 100 OR m.cpu > 100 OR m.logical_reads > 100) AND m.session_id = s.sid  AND m.session_serial_num = s.serial#
ORDER BY m.physical_reads DESC, m.cpu DESC, m.logical_reads DESC;

A partir del *SID*, podemos saber qué _query_ están lanzando mediante:

SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
AND c.sid = &SID
ORDER BY  c.sid, d.piece;
SET feedback off
SET serveroutput ON SIZE 9999
COLUMN username format a20
COLUMN sql_text format a55 word_wrapped
BEGIN
  FOR x IN
   (SELECT username||'('||sid||','||serial#||') ospid = '|| process || ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') CURRENT_TIME,
    sql_address,
    sql_hash_value
   FROM v$session
   WHERE STATUS = 'ACTIVE'
   AND rawtohex(sql_address) <> '00'
   AND username IS NOT NULL ) loop
   FOR y IN (SELECT sql_text
   FROM v$sqlarea
   WHERE address = x.sql_address ) loop
   IF ( y.sql_text NOT LIKE '%listener.get_cmd%' AND
    y.sql_text NOT LIKE '%RAWTOHEX(SQL_ADDRESS)%' ) THEN
    dbms_output.put_line( '--------------------' );
    dbms_output.put_line( x.username );
    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
   END IF;
  END loop;
 END loop;
END;
/
  • oracle/oracle_sql_querys/sessions.txt
  • Last modified: 2018/11/14 12:14
  • by dodger