miércoles, 26 de septiembre de 2007

tic... toc

¿Cómo medir la performance de un código PL/SQL?

Muchas veces nos encontramos con procedimientos lentos de muchos pasos, con llamadas a funciones, DML, loops con cursores, etc, que queremos revisar. Como SYSDATE nos da la cantidad de segundos como mínima fracción de tiempo, no nos es de mucha ayuda consultarlo entre líneas.

Afortunadamente, contamos con la función get_time del paquete dbms_utility para obtener la cantidad de centésimas de segundo que transcurren entre dos instantes de tiempo.
A sabiendas de eso, debemos incluir los statements que invoquen a dichas funciones y desplieguen los tiempos por la salida, en un archivo o en una tabla de logs.

Con el fin de evitar duplicar código y además aprovechar las bondades del cacheado de paquetes en el área de memoria compartida, creé un pequeño paquete exclusivo para ese fin. El mismo tiene dos procedimientos: tic y toc. Cuando quiero comenzar a medir tiempo, invoco a tic (con un mensaje para registro). Cuando quiero medir el tiempo transcurrido, uso toc. Así de simple.

CREATE OR REPLACE PACKAGE crono AS
  v_tic PLS_INTEGER;
  evento VARCHAR2(100);
  PROCEDURE tic(p_evento IN VARCHAR2);
  PROCEDURE toc;
END crono;
/


CREATE OR REPLACE PACKAGE BODY crono AS
 PROCEDURE tic(p_evento IN VARCHAR2) IS
 BEGIN
   v_tic := DBMS_UTILITY.GET_TIME;
   evento := p_evento;
   DBMS_OUTPUT.PUT_LINE('TIC: '||p_evento);
 END tic;

 PROCEDURE toc IS
   df pls_integer := CAST(DBMS_UTILITY.GET_TIME AS PLS_INTEGER)-v_tic;
 BEGIN
   DBMS_OUTPUT.PUT_LINE('TOC: '||evento||', T(1/100s): '||df);
 END toc;
END crono;
/


El uso del paquete en un procedimiento cualquiera puede ser:

SQL> begin
2     crono.tic('creo tabla');
3     execute immediate 'CREATE TABLE testtable as select * from user_tables';
4     crono.toc;
5     crono.tic('elimino registros');
6     execute immediate 'delete from testtable';
7     crono.toc;
8     crono.tic('hago commit');
9     commit;
10     crono.toc;
11     crono.tic('borro tabla');
12     execute immediate 'drop table testtable';
13     crono.toc;
14 end;
15 /
TIC: creo tabla
TOC: creo tabla, T(1/100s): 34
TIC: elimino registros
TOC: elimino registros, T(1/100s): 5
TIC: hago commit
TOC: hago commit, T(1/100s): 0
TIC: borro tabla
TOC: borro tabla, T(1/100s): 50

Procedimiento PL/SQL terminado correctamente.

Aunque esta experiencia es meramente demostrativa, notar que no pasó un segundo en toda la operación, y sin embargo estamos sacando medidas ilustrativas en cada paso.

Es una solución práctica y disponible para todos los usuarios de la base de datos, solo tengo que dar permisos de ejecución a los desarrolladores o a public. Un sinónimo puede simplificar aún más las llamadas a tic y a toc.

Pese al mínimo costo de salida por pantalla, una desventaja es que el procedimiento tiene que terminar exitosamente para obtener las medidas (dbms_output despliega su salida al final del proceso). Una alternativa para lograr la medición "on the run", es implementar la salida a archivo (utilizando el paquete utl_file), o insertando en una tabla (con una transacción autónoma para no hacer commit sobre la actual).
Estos cambios pueden ser transparentes al usuario, ya que solo hará falta recompilar el body del paquete. Las ventajas están a la vista.

Ver también:
Uso del paquete utl_file

sábado, 22 de septiembre de 2007

El usuario Scott no existe

SQL> conn scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied


Dependiendo de las opciones de instalación o de los schemas que vengan instalados en algunas versiones del RDBMS (como Express por ejemplo), el usuario más famoso de Oracle, Scott, puede no estar presente. Citando el caso de Oracle XE, el único schema de ejemplo que viene instalado por defecto es HR.

Pero no hay problema, lo único que tenemos que hacer para instalar el schema Scott con sus clásicas tablas EMP, DEPT, BONUS y SALGRADE, es correr (en SQL*Plus) el script utlsampl.sql que está en el directorio del servidor $ORACLE_HOME/RDBMS/ADMIN.

Como se requiere de ciertos permisos especiales que un usuario corriente no tiene, deberá hacerlo el DBA.

lunes, 10 de septiembre de 2007

Limpiando código DDL

A propósito de Un extractor de DDL en archivos separados, me preguntaron como evitaba exportar las cláusulas de storage en Oracle 9i, únicamente dejando las que especifican los tablespaces.

SQL> create table t (a int);

Tabla creada.

SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

CREATE TABLE "LFERNANDEZ"."T"
( "A" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"


En la práctica, las cláusulas PCTFREE, PCTUSED, STORAGE, etc, no son útiles durante el desarrollo, sino cuando los objetos son puestos en producción. Es allí donde el detalle en la especificación de storage de los objetos tiene un impacto significativo sobre la performance de los sistemas.

Repasando las opciones disponibles en el paquete dbms_metadata y en particular la función set_transform_param, encontramos que podemos generar el código de asignación de tablespace pasando el valor 'TABLESPACE' como segundo parámetro y TRUE como tercero:
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', TRUE);

Desafortunadamente, esta opción es ignorada cuando deshabilitamos todo el STORAGE usando:
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', FALSE);

No tenemos alternativa entre: o generar todo el storage, o no generarlo en absoluto.

El camino que termina siendo elegido es el de generar todo el código DDL y luego procesar el archivo de salida con algun lenguaje con buenas cualidades para procesamiento de texto, como por ejemplo Perl o AWK.

No se requiere ser experto en AWK para programar un sencillo script que elimine algunas líneas de archivos:

BEGIN {flag=2}
/^ *) PCTFREE/{print ")"; flag=0}
/^ *PCTFREE/{flag=0}
flag==0 && $0~/)$/{flag=1}
flag==2
flag==1{flag=2}


Esta es la idea: uso flags para saber cuándo imprimir y cuándo no, luego tengo dos posibles comienzos con PCTFREE, el primero para tablas comunes y el segundo para el caso de tablas particionadas (las cuales no son antecedidas por un paréntesis).

Cualquier comentario para mejorar la performance de este script será bienvenido!

Ver también
Un extractor de DDL en archivos separados