Differences

This shows you the differences between two versions of the page.

Link to this comparison view

oracle:oracle_sql_querys:sessions [2018/11/14 12:14] (current)
dodger created
Line 1: Line 1:
 +====== ORACLE SQL QUERIES : Sessions ======
  
 +======SECCIÓN : Sesiones======
 +=====Listar numero de sesiones=====
 +<code sql>​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'​ ;
 +</​code>​
 +
 +===== Usuarios conectados =====
 +<code sql>
 +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
 +/
 +</​code>​
 +
 +
 +
 +===== Numero de sesiones por usuario =====
 +<code sql>
 +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
 +/
 +</​code>​
 +
 +
 +
 +
 +
 +
 +
 +
 +=====Listar sesiones=====
 +
 +<code sql>
 +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
 +/
 +</​code>​
 +
 +Con usuario inicializado:​
 +<code sql>
 +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
 +/
 +</​code>​
 +
 +
 +Más:
 +<code sql>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)
 +/
 +</​code>​
 +=====Listar sesiones ACTIVAS=====
 +Con status=ACTIVE y SIN sqltext
 +<code sql>
 +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 ;
 +</​code>​
 +
 +
 +Con status=ACTIVE
 +<code sql>
 +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 ;
 +</​code>​
 +
 +Mediante SQL_ID:
 +<code sql>
 +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
 +/
 +</​code>​
 +
 +===== Tiempo desde la última actividad =====
 +<code sql>
 +SELECT username, MACHINE, last_call_et seconds, STATUS
 +FROM v$session
 +WHERE username IS NOT NULL
 +ORDER BY last_call_et
 +/
 +</​code>​
 +
 +
 +=====Listar sesiones de usuario=====
 +<code sql>​select SID,SERIAL# from V$SESSION where USERNAME='​USUARIO';</​code>​
 +=====Matar sesion=====
 +<code sql>​alter system kill session '​SID,​SERIAL'​ ;
 +</​code>​
 +=====Matar todas las sesiones de un usuario=====
 +<code sql>
 +select 'ALTER SYSTEM KILL SESSION '''​ || SID || ','​ || SERIAL# || '''​ IMMEDIATE ; ' ​
 +FROM V$SESSION ​
 +WHERE USERNAME IN ('​USERNAME1',​ '​USERNAMEn'​)
 +</​code>​
 +
 +
 +=====Bloqueos / locks=====
 +==== HARD LOCKS ====
 +
 +  * **v$lock** based:
 +^ Human readable ^ plain mode ^
 +| <code sql>
 +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
 +/
 +</​code>​ | <code sql>​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 ;
 +</​code>​ | 
 +
 +  * **v$session** based:
 +<code sql>
 +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';​
 +</​code>​
 +
 +
 +
 +==== Bloqueos de usuario (Soft locks) ====
 +
 +Formateo:
 +<code sql>
 +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
 +</​code>​
 +
 +  * Tx enqueue ready2kill:
 +<code sql>
 +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
 +;
 +</​code>​
 +
 +  * Transaction enqueue (original):
 +<code sql>
 +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
 +;
 +</​code>​
 +
 +
 +
 +  * DML enqueue & User supplied:
 +<code sql>
 +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
 +;
 +</​code>​
 +
 +==== Sid,SERIAL# de los bloqueantes primarios ====
 +Sesiones que inician la cadena de bloqueos:
 +<code sql>
 +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
 +        )
 +    )
 +/
 +</​code>​
 +
 +==== Bloqueadores ====
 +Extrictamente bloqueadores (sesiones que bloquean otras):
 +<code sql>​SELECT * from DBA_BLOCKERS ;
 +</​code>​
 +==== lock de DDLS ====
 +Mirar objetos que se están accediendo:
 +<code sql>
 +col object format a50 ;
 +col type format a20 ;
 +col owner format a20 ;
 +select * from V$ACCESS where type LIKE '​TYPE' ​ ;
 +</​code>​
 +
 +Y el propio bloqueo en sí:
 +<code sql>
 +COL SESSION_ID FORMAT 999999;
 +COL OWNER FORMAT A20;
 +COL NAME FORMAT A30;
 +COL TYPE FORMAT A20;
 +SELECT * FROM DBA_DDL_LOCKS ;
 +</​code>​
 +
 +==== lock de DML ====
 +Casi lo mismo que el anterior:
 +<code sql>
 +COL SESSION_ID FORMAT 999999;
 +COL OWNER FORMAT A20;
 +COL NAME FORMAT A30;
 +COL TYPE FORMAT A20;
 +SELECT * FROM DBA_DML_LOCKS ;
 +</​code>​
 +
 +==== Links bloqueos====
 +  * http://​dbamohsin.wordpress.com/​2011/​07/​19/​t-sql-session-locks-blocks-waits/​
 +
 +=====Consumo por sesiones=====
 +<code sql>
 +
 +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;
 +</​code>​
 +
 +A partir del *SID*, podemos saber qué _query_ están lanzando mediante:
 +<code sql>
 +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;
 +</​code>​
 +
 +===== QUERYS activas =====
 +<code sql>
 +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;
 +/
 +</​code>​
  • oracle/oracle_sql_querys/sessions.txt
  • Last modified: 2018/11/14 12:14
  • by dodger