Recolectando trazas extendidas en oracle, evento 10046

Esto ya lo habíamos visto un poco por encima pero un poco de detalle nunca está mal

10046 Niveles de traza

0 – sin trazas

1 – trazas básicas CURSOR, PARSE, EXEC, FETCH ERROR, SORT UMAP, ERROR, UMAP, STATS y XCTEND. Lo mismo que poner sql_trace=true.

2 – Lo mismo que el nivel 1

4 – Lo mismo que el nivel 1 pero con BIND

8 – Lo mismo que el nivel 1 pero con eventos de espera.

12 – Combina los niveles 4 y 8.

Se tiene que tener el parámetro  timed_statistics=true. (comprobar con un show parameter timed_statistics) . Se puede cambiar a nivel de sistema o de sesión. También es conveniente tener el max_dump_file_size=unlimited para que quepa toda la traza. Si esta limitado el tamaño de traza podemos ver este error

*** DUMP FILE SIZE IS LIMITED TO 10000000 BYTES ***

Para tracear la sesión de uno mismo

SQL> alter session set timed_statistics=true — turn timing on.

SQL> alter session set max_dump_file_size=unlimited

SQL> alter session set tracefile_identifier=’logsesion’ – sirve para identificar el archive de trazas

SQL> alter session set events ‘10046 trace name context forever, level 8’

Ejecutas lo que se quiere tracear

******* run all of your processing here *******

SQL> alter session set events ‘10046 trace name context off’ – se deshabilitan las trazas.

Para tracear la sesión de otro

Es facil si sabemos el sid y el serial#  de la session. Es un poco más dificil con MTS porque con la utilización de pules de conexión una conexión puede ser reutilizada por varios usuarios.

Encontrar la sesión:

set linesize 150
column Name format a14
column SID format 9999
column PID format 99999
column TERM format a15
column OSUSER format a15
column Program format a15
column Stats format a10
column Logon_time format a20
select a.username Name,
        a.sid SID,
        a.serial#, b.spid PID,
        SUBSTR(A.TERMINAL,1,9) TERM,
        SUBSTR(A.OSUSER,1,9) OSUSER,
        substr(a.program,1,10) Program,
        a.status Status,
        to_char(a.logon_time,'MM/DD/YYYY hh24:mi') Logon_time
from v$session a,
     v$process b
where a.paddr = b.addr
and a.serial# <> '1'
and a.status = 'ACTIVE'
and a.username like upper('%&user%') – si se quiere filtrar por un usuario concreto
order by a.logon_time
/

Una vez que tenemos el SID y el  SERIAL#  de la sesión. Podemos utilizar los siguientes paquetes

DBMS_SYSTEM:

SQL> execute sys.dbms_system.set_bool_param_in_session(&&SID, &&SERIAL,’timed_statistics’,true);

— para activar el timed statistics a nivel de sesión de usuario si no está activado a nivel de sistema.

 

SQL> execute sys.dbms_system.set_ev(&&SID, &&SERIAL, 10046, 8, ‘ ‘);

— poner a nivel 8 el evento de traceo.

SQL> execute sys.dbms_system.set_ev(&&SID, &&SERIAL, 10046, 0, ‘ ‘);

–desactivar las trazas

Logon trigger:

En algunos sistemas puede ser interesante incluir triger de logon para activar las estadísticas.

Más información en http://www.databasejournal.com/features/oracle/article.php/3469891/Collecting-Oracle-Extended-Trace-10046-event.htm

Anuncios

Cómo tracear que sesión bloquea y que sql está ejecutando

Con esta select obtenemos la sesión que bloquea y las que están esperando.

SELECT inst_id, DECODE(request, 0,'Bloquea: ','Espera: ')||sid SID, id1, id2, lmode, request, type, ctime/60 "minutos"
 FROM GV$LOCK
 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request > 0)
 ORDER BY id1, request;

Con esto colocando el sid de la select anterior obtenemos características de la sesión.

select sid,serial#, username, status, sql_hash_value,PREV_HASH_VALUE,machine from v$session where sid in ();

Y por último con está el código que está ejecutando.

select sql_text from v$sqltext where HASH_VALUE= '2622419231' order by piece;

Trazas de una sesión sql

  1. Opción oradebug:

Obtener el sid

select sid, serial#, username from v$session where USERNAME =

Obtener el pid de ese sid

&lt;table class=”msgText”&gt; &lt;tr&gt; &lt;td class=”errorAlertIco”&gt;&lt;img src=”res://instant-message.dll/#10/#2415″ /&gt;&lt;/td&gt; &lt;td class=”boxedMessageText”&gt;It appears that you do not have JavaScript enabled, please contact your adminitrator.&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt;

select pid, spid, machine, vs.program,vs.sid,vs.serial# from v$process p,v$session vs where  p.addr = vs.paddr and vs.sid=
Activar las trazas
oradebug setorapid <PID>

El probelama ahora es encontrar la salida, pero en el caso de MTS (shared server) lo podemos encontrar en

$diagnostic_dest/diag/rdbms/<db_name>/<instance>/trace

Sqlplus ‘/ as sysdba’


Show parameter diagnostic_dest

2. sys.dbms_system.set_ev

Uutilizar está vista: v$session
Activar la traza en la sesión:

EXECUTE sys.dbms_system.set_ev ('SID','SERIAL#',10046,12,'');
 desactivo traza:
 EXECUTE sys.dbms_system.set_ev ('SID','SERIAL#',10046,0,'');

Dónde deja las tazas:

 

sqlplus ‘/ as sysdba’

SELECT s.sid,
 s.serial#,
 pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) || 
 '_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
 v$process p,
 v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = <audsid de v$session>

Para formatear el fichero tkprof

3. SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION

Obtener SID y #SERIAL de v$session

Activar

execute SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID, #SERIAL,TRUE);

Desactivar

execute SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID, #SERIAL,FALSE);

Como verificar que sesión bloquea un objeto

set linesize 150;
set head on;
col sid_serial form a13
col ora_user for a15;
col os_user for a15;
col object_name for a35;
col object_type for a10;
col lock_mode for a15;
col last_ddl for a8;
col status for a10;
break on sid_serial;

SELECT l.session_id||’,’||v.serial# sid_serial,
l.ORACLE_USERNAME ora_user,
v.OSUSER os_user,
o.object_name,
o.object_type,
DECODE(l.locked_mode,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’,
TO_CHAR(l.locked_mode)
) lock_mode,
o.status,
to_char(o.last_ddl_time,’dd.mm.yy’) last_ddl
FROM dba_objects o, v$locked_object l, v$session v
WHERE o.object_id = l.object_id
and l.SESSION_ID=v.sid
order by 2,3;

Información sobre usuarios:

Para ver el usuario conectado en esa sesión:

Show user

Para obtener información básica del usuario conectado consulta la vista USER_USERS. Con el siguiente query se obtiene el nombre del usuario y sus tablespaces por defecto.
Para obtener información de todos los usuarios de la base de datos, utiliza la vista DBA_USERS.

select username, account_status, created
from dba_users
 order by 1;

Además de la tabla dba_user existe la función sys_contrext que te permite obtener información similar:

select sys_context('USERENV', 'CURRENT_USER') USUSARIO
    , sys_context ('USERENV', 'AUTHENTICATION_METHOD') AUTH_MTH
    , sys_context ('USERENV','HOST') HOSTS
    , sys_context ('USERENV','INSTANCE_NAME') INST
  FROM DUAL

Para obtener los usuarios conectados a la base de datos ese momento:

select count(*), username from v$session group by username;

En caso de un cluster:

select count(*), username from gv$session group by username;

Obtener las select que se están ejecutando en ese momento en la base de datos:

select  a.sid, a.username, b.sql_text
    from v$session a
     , v$sqltext_with_newlines b
     where a.sql_id = b.sql_id
     order by a.username, a.sid, b.piece;

La misma select que la anterior pero para Oracle 9 o versiones inferiores:

select  a.sid, a.username, b.sql_text
 from v$session a
  , v$sqltext_with_newlines b
   where a.sql_address= b.address
And  a.sql_hash_value = b.hash_value
  order by a.username, a.sid, b.piece;

Matar un sesión en Oracle

Existen diferentes maneras de matar una sesión de oracle tanto dentro de oracle como fuera:

El primer paso es identificar la sesión que queremos matar:

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

   INST_ID        SID
   SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
         1         30         15 3859       TEST       sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         23        287 3834       SYS        sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         40        387 4663                  oracle@oel5-11gr2.localdomain (J000)
         1         38        125 4665                  oracle@oel5-11gr2.localdomain (J001)

 

Los valores SID y SERIAL# son los que se utilizaran en los comandos que explicamos más delante.

 ALTER SYSTEM KILL SESSION

La sintaxis básica para matar una sesión es la siguiente:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

En un ambiente RAC, opcionalmente se puede añadir  el identificador de la instancia INST_ID, lo obtenemos de la vista GV$SESSION view. Este permite matar una sesión desde otro de los nodos de RAC.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial,#@inst_id';

El comando KILL SESSION no mata la sesión, simplemente le indica a la sesión que debe matarse ella misma. En algunas situaciones como en la espera de una respuesta de una base de datos remota o cuando se esta haciendo un roll back a una transacción, la sesión no se mata a si misma inmediatamente, espera a que termine la operación que está realizando. En estos casos la sesión adquiere el status de “marked for kill”, y se mata lo antes posible

Además de la syntaxix  descrita anteriormente, se puede añadir la clausula IMMEDIATE.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Esto no afecta al trabajo que hace el comando, pero devuelve el control a la sesión inmediatamente, en vez de esperar la confirmación de que se ha matado la sesión.

Si la sesión marcada persiste demasiado en el tiempo se puede intentar matar el proceso a nivel de sistema operativo, antes de hacer esto es conveniente comprobar que la sesión no está haciendo rollback. Podemos comprobar esto con el script (session_undo.sql), lo tenéis en la página de scripts. Si el valor de USED_UREC decrece para la sesión en cuestión es mejor dejar que termine el rollback antes de matar la sesión a nivel de sistema operativo.

ALTER SYSTEM DISCONNECT SESSION

Oracle 11g introduce la sintaxis ALTER SYSTEM DISCONNECT SESSION  un Nuevo método de matar una sesion Oracle.  Lo que hace este comando es matar el proceso de servidor dedicado ( o circuito virtual cuando se utilizan los Shared Sever), lo que es equivalente a matar el proceso desde el sistema operativo. La sintaxis básica es similar a la del comando KILL SESSION más la clausula POST_TRANSACTION

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

La clausula POST_TRANSACTION espera a que la transacción se complete antes de desconectar la sesión.

Matar una sesión en windows

C:> orakill ORACLE_SID spid

Matar una sesión en Unix

% kill spid

Si después de unos minutos no ha muerto utilizar

% kill -9 spid

Para verificar que el spid coincide con el proceso del sistema operativo:

% ps -ef | grep ora