« VARIOS : Buena matriz de las funciones en Oracle 11gr1¿¿RACK para terremotos?? »

BBDD : Como tracear una sesión que se conecta/procesa/desconecta en 2 segundos



0.-Situación
La situación es que hay una sesión que está hecha en un programa (C) , este programa hace una conexión a la base de datos y ejecuta ciertas rutinas en Pro*C , y se desconecta, el inconveniente es que se demora entre que se conecta, procesa y desconecta , entre 3 a 5 segundos.
Por ende hacerle un trace es bastante complicado, ya que no se alcanza a conocer el SID ,SPID o #SERIAL para incluirlo en el package
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESION(SID,#SERIAL,TRUE)

Tampoco podríamos utilizar el package DBMS_SUPPORT.START_TRACE_IN_SESSION() ya que también debiesemos conocer el SID y #SERIAL

Por ende se debe desarrollar algo alternativo para poder rescatar el proceso de esa sesión



1.-Solución
Crear un trigger on-logon para detectar cuando hace la conexión ese usuario a través de ese programa (Pro*C)

2.-Código
CREATE OR REPLACE TRIGGER
tracea_audit_trigger
AFTER LOGON ON DATABASE
DECLARE
var_session VARCHAR2(100);
var_module VARCHAR2(100);
BEGIN
SELECT sys_context('USERENV','SESSIONID')
INTO var_session
FROM dual
;


BEGIN
SELECT module
INTO var_module
FROM v$session
WHERE audsid = var_session;
EXCEPTION
WHEN OTHERS THEN
var_module := 'NO';
END;


IF UPPER(var_module) LIKE '%PROGRAMA%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
END IF;
END;
/


Hay que tener cuidado de dar los privilegios de select sobre la vista v$session, ya que un usuario que no posea los permisos de lectura no podrá realizar la conexión, por eso se recomienda que este código sea probado en ambientes de test, previo paso a producción

3.-Permisos necesarios

Grant select on v_$session to public;

¿Porque un privilegio a Public? Pues desde el momento en que se cree el trigger cualquier usuario que se conecte a la base de datos pasara por el trigger on-logon y si no tiene acceso a la vista v$session pues simplemente no se conectará y saldrá un error de privilegios.

Grant alter system to nombre_usuario;
Grant alter session to nombre_usuario;


Los permisos para alterar la sesión y el system debiesen ser revocados una vez finalizado el proceso de captura de estadísticas

Revoke alter system from ;
Revoke alter session from ;


4.-Si no esta activada la recolección de estadísticas
Si el parámetro TIMED_STATISTICS en la base de datos posee el valor FALSE, la traza no contendrá datos relevantes por ejemplo CPU, disk , etc.

Para ello se puede alterar el trigger y modificarlo de la siguiente forma

Código anterior
IF UPPER(var_module) LIKE '%PROGRAMA%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
END IF;


Código nuevo
IF UPPER(var_module) LIKE '%PROGRAMA%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
EXECUTE IMMEDIATE 'ALTER SYSTEM SET TIMED_STATISTICS=TRUE';
END IF;


5.-Si el archivo de trace generado es demasiado grande
Si el archivo de trace generado es demasiado grande, se puede disminuir su tamaño modificando el trigger

Código anterior
IF UPPER(var_module) LIKE '%PROGRAMA%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
END IF;


Código nuevo
IF UPPER(var_module) LIKE '%PROGRAMA%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
EXECUTE IMMEDIATE 'ALTER SYSTEM SET max_dump_file_size=5120';
END IF;


El tamaño asignado al parámetro max_dump_file_size está dado por Kb y el valor por defecto es UNLIMITED


6.- ¿Qué pasa si hay muchos usuarios, en la misma máquina, ejecutando el mismo Pro*C?
Pues una solución es realizar una copia de programa , por ejemplo si el programa se llama EjecutaProc, llamarlo de una forma que sea identificable , ejemplo , prueba_ejecucion y otorgar los permisos necesario para su ejecución

Con lo anterior , este programa va a ser inequivocamente ubicado en la vista v$session , bajo el campo module, y solamente a esta sesión le realizaremos un traceo

7.- Archivo de resultados
El archivo de resultados debe estar ubicado en la ruta que está predefinida en el parámetro de inicialización user_dump_dest

select value from v$parameter where name like 'user_dump_dest' , por defecto tiene el valor $ORACLE_HOME/rdbms/trace

El archivo de resultados del traceo , no es algo que sirva mucho a primera vista, a no ser que tengamos una memoria y ojos extraordinarios para hacer un formateo visual

Ejemplo del archivo :
PARSING IN CURSOR #6 len=67 dep=1 uid=0 oct=3 lid=0 tim=19191305310633 hv=2889900621 ad='f6f19668'
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
END OF STMT
PARSE #6:c=10000,e=9470,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=19191305310617
EXEC #6:c=0,e=918,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305311971
FETCH #6:c=0,e=408,p=1,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305312494
FETCH #6:c=0,e=24,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305312632
FETCH #5:c=0,e=94,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305312816
EXEC #6:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305313060
FETCH #6:c=0,e=42,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305313186
FETCH #6:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305313283
FETCH #5:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305313402
EXEC #6:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305313660
FETCH #6:c=0,e=46,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305313788
FETCH #6:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305313888
FETCH #5:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305314015
EXEC #6:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305314229
FETCH #6:c=0,e=39,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=19191305314347
FETCH #6:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=19191305314452


8.- Formateo de archivo, salida de trace
Como es algo complicado el análisis del archivo anterior, existe un utilitario llamado tkprof que realiza el formateo de este archivo, para utilizarlo se debe ejecutar lo siguiente

tkprof [explain=usuario/password] [sys=no] [insert=archivo]

Con el parámetro "explain=usuario/password" indicamos que nos muestre el plan de ejecución de todas las instrucciones, conectándose para ello al usuario/password indicados.

Con el parámetro "sys=no" indicamos que no nos muestre las instrucciones realizadas por el usuario SYS.

Con el parámetro "insert=archivo" , dejamos toda la información mostrada por tkprof en el archivo de salida,como sentencias insert en una tabla llamada tkprof_table, con lo cual podemos realizar consultas de forma más óptima

Después de haber formateado nuestro archivo en user_dump_dest , se visualizaría de la siguiente forma

select USER into :b0
from
DUAL


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1


Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 30

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL


by Ligarius
26.06.09. 18:15:21. 1151 words, 6704 views. Categories: Base de datos, Oracle 11g, Oracle 10g, Tuning / Performance ,