Oracle - Traza 10046 (SQL_TRACE)

Cómo obtener una traza 10046 (SQL_TRACE)

Hay varias formas de obtener una traza 10046 para el diagnostico de rendimiento en consultas.

1º Evento 10046

El código de evento especial es equivalente a establecer el parámetro de inicialización SQL_TRACE=true

La ventaja de utilizar este evento es que posibilita detalles extra en el fichero de traza generado, dependiendo del nivel especificado en el evento.

10046 EVENT levels:

        1  - Enable standard SQL_TRACE functionality (Default)

        4  - As Level 1 PLUS trace bind values

        8  - As Level 1 PLUS trace waits

             This is especially useful for spotting latch wait etc.

             but can also be used to spot full table scans and index scans.

        12 - As Level 1 PLUS both trace bind values and waits

        16 - Added in 11g to generate STAT line dumps for each execution

Sintaxis de ejemplo:

    

• A nivel de sesión:

            alter session set events '10046 trace name context forever';

            alter session set events '10046 trace name context forever, level 8';

            Para deshabilitarla:

            alter session set events '10046 trace name context off';

•  Init.ora (a nivel de instancia):

            alter system set events '10046 trace name context forever,level 12';

            Para deshabilitarla:

            alter system set events '10046 trace name context off';

           

            O directamente como parámetro de inicialización:

event="10046 trace name context forever,level 4"

            Para deshabilitarla:

alter system set events '10046 trace name context off';

2º ALTER SESSION

  

·         Localizamos la ubicación del destino de las trazas de usuario:

show parameter user_dump_dest (antes de la 11gR1)

show parameter diagnostic_dest (a partir de la 11gR1)

·         Si la sesión está accesible al usuario antes de ejecutar la sentencia:

alter session set tracefile_identifier='10046';

  alter session set timed_statistics = true;
  alter session set statistics_level=all;
  alter session set max_dump_file_size = unlimited;

  alter session set events '10046 trace name context forever,level 12';

  -- Execute the queries or operations to be traced here --

  select * from dual;
  exit;

·         Si no se sale de la sesión, la traza se puede deshabilitar así:

alter session set events '10046 trace name context off';

3º oradebug

Si el proceso ya está corriendo podemos usar oradebug para atacharnos a la sesión e iniciar la traza 10046.

·         Primero identificamos la sesión a tracear:

select p.PID,p.SPID,s.SID
  from v$process p,v$session s
  where s.paddr = p.addr
  and s.sid = &SESSION_ID
  /

SPID is the operating system Process identifier (os pid)
PID is the Oracle Process identifier (ora pid)

·         A continuación, podemos utilizar el ospid o el orapid obtenido anteriormente:

connect / as sysdba

  oradebug setospid <spid>

  oradebug unlimit

  oradebug event 10046 trace name context forever,level 12

connect / as sysdba

  oradebug setorapid 

  oradebug unlimit

  oradebug event 10046 trace name context forever,level 12

·         Para deshabilitar oradebug tras finalizar la traza:

oradebug event 10046 trace name context off 

4º Mediate un trigger de tipo LOGON

Puede haber situaciones donde es necesario trazar la actividad de un usuario específico.

En ese caso un trigger nos puede servir:

Por ejemplo:

  CREATE OR REPLACE TRIGGER SYS.set_trace
  AFTER LOGON ON DATABASE
  WHEN (USER like  '&USERNAME')
  DECLARE
      lcommand varchar(200);
  BEGIN
      EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
      EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
      EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
  END set_trace;
  /

El usuario que ejecute el trigger require privilegios explícitos de ‘alter session’.

5º SQLT (SQLTXPLAIN)

Utilizando SQLTXPLAIN con la opción “Xecute” produce una traza 10046 como parte de la salida SQLT.

SQLT ejecutará la sentencia SQL a analizar, y producirá una serie de ficheros de diagnostico, entre ellos la traza 10046.

6º DMBS_MONITOR

·         Para consultar las trazas habilitadas:

select trace_type, primary_id, QUALIFIER_ID1, waits, binds
from DBA_ENABLED_TRACES;

·         Podemos usar el procedimiento SESSION_TRACE_ENABLE para habilitar la traza para una sesión en concreto.

Sintaxis
Para habilitar la traza:
dbms_monitor.session_trace_enable(session_id => x, serial_num => y,
waits=>(TRUE|FALSE),binds=>(TRUE|FALSE) );
Para deshabilitarla:
dbms_monitor.session_trace_disable(session_id => x, serial_num => y);