viernes, 30 de enero de 2009

Oracle SQL Developer Data Modelling

Los días de Oracle Designer 10g están acabando. El cásico paquete de diseño, versionamiento e implementación de bases de datos, tiene un jóven sustituyo acorde a los tiempos que corren: Oracle SQL Developer Data Modelling.

Esta herramienta de nombre largo es totalmente gratuita, profesional y actualizada, y su propósito es quitarle el mercado que otros diseñadores compactos como Erwin o Toad Data Modeller han ganado.

Es que la comunidad Oracle reclamaba hace tiempo las limitaciones de la interfase, que no había cambiado mucho desde la versión Designer/2000 de 1995, y prácticamente nada desde 9i para 10g. También, la ubicación geográfica de los desarrolladores era un problema cada vez que debían movilizarse lejos del repositorio que Designer require para funcionar.

Oracle SQL Developer Data Modelling simplemente se copia en una carpeta y no require de instalador. Es un producto separado disponible para Windows, Linux y Mac.
Se conecta con Oracle a partir de 9.2.0.1, no necesita un repositorio (aunque opcionalmente puede usarlo), y tiene una abanico de funcionalidades muy atractivas: modelos lógico, relacional y físico, ingeniería reversa desde otras bases de datos, diseño multidimensional de tablas para DW, data flow diagrams, modelado gráfico de Oracle types, importación desde otros modeladores como Erwin, importación por ODBC, exportación a ddl, csv, xml, Oracle AW.

Mi impresión sobre la herramienta es muy buena. Luego de un buen tiempo cuesta dejar algunos condicionamientos que el arcaico Designer impone para sobrellevar los problemas de su propia interfase, pero parece que con Oracle Data Modelling no son más necesarios.
Uno típico era el nombrado automático de objetos, Designer tenía una forma particular de autogenerar nombres de FKs e índices, y cada vez que se generaban había que arreglarlos. Oracle Data Modeller permite personalizar todos esos prefijos que usa para ingeniería reversa, o pasaje de modelo lógico para relacional, facilitando el cumplimiento de estándares. Por ejemplo para la generación de índices puedo definir los nombres como: IX_{table}_{seq nr}.
El Navigator (pequeño cuadro que muestra un mapa de todo el modelo) es otra buena innovación que aporta navegabilidad en diagramas grandes.
Es posible especificar una vasta variedad de propiedades sobre cada objeto, para mejorar la generación de código final y evitar tener que repasar los ddls generados.
También cuenta con un analizador de impacto, para anticipar la propagación de cambios en el modelo existente.
La integración por ODBC permite conectarse con prácticamente cualquier base de datos existente.
La mayor ventaja de todas es, como dije anteriormente, que es gratuito.

Oracle SQL Developer Data Modeller está proyectado para ser lanzado sobre el correr de 2009, pero una serie de versiones beta están siendo publicadas por Oracle para evaluación.
La versión Early Adopter Release 2 (Nov 2008) está disponible para descargar en OTN.

ACTUALIZACIÓN (01-mar-2010): Este articulo comenta sobre la versión Early Adopter R2, la cual no está más disponible. Las versiones comerciales actuales no son gratuitas. Ver comentarios abajo.

Ver también:
OTN SQL Developer

viernes, 23 de enero de 2009

Combinando Microsoft Access y Oracle

En este artículo vamos a ver cómo implementar una sencilla aplicación que trabaje sobre una base de datos Oracle para actualizar datos y generar reportes, usando una herramienta amigable y disponible en la mayoría de los ordenadores como lo es Microsoft Access.

La idea es, en 5 pasos crear una interfase gráfica con poco esfuerzo y en tiempo récord, que facilite la vida de muchos usuarios no IT, como por ejemplo la del Gerente del Area de Recursos Humanos que necesita ingresar y consultar datos en su base de datos corporativa. No hay necesidad de comprar un caro producto comercial para esa sencilla tarea.

Qué software necesitamos para desarrollar y ejecutar nuestra aplicación?

- Microsoft Access, sino nuestro archivo .mdb no será reconocido por Windows.
- Un cliente Oracle, para que podamos conectarnos con la base de datos de destino.

(Este software debe estar presente ya sea en la máquina que usemos para desarrollar como en la máquina destino donde va a correr el programa. No está en el alcance de este artículo explicar cómo instalar estos dos productos)

Los 5 pasos para crear la aplicación

1) Crear una conexión ODBC
2) Vincular en Access las tablas de Oracle
3) Crear las relaciones existentes
4) Crear formularios
5) Crear un reporte

Nuestra aplicación va a conectarse y trabajar con el usuario Oracle SCOTT. Uso Windows Vista, Access 2003 y un cliente Oracle 10g.

Disculpas por las pantallas de Access en portugués y Windows en inglés, pero es todo lo que tengo en la vuelta :)

Paso 1. Crear una conexión ODBC

- En Windows, vamos al menú Start, Control Panel, Administrative Tools y seleccionamos Data Sources (ODBC)

- En la lengueta User DNS, presionar sobre el botón Add. Una lista de drivers disponibles aparecerá en un diálogo. Tenemos dos posiblidades para seleccionar, una es elegir el driver de Microsoft para Oracle (Microsoft ODBC for Oracle), y la otra es elegir el driver de Oracle que fue instalado con nuestro cliente (en mi caso Oracle em OraClient10g). Recomiendo usar el driver nativo de Oracle, ya que está optimizado y se corresponde exactamente con la versión de Oracle que acabamos de instalar.

Nota: En caso que se nos presente un mensaje de error con el título SQORAS32 y el mensaje An unsupported operation was attemped, se trata de un bug conocido cuya solución está en Metalink nota NR.5699495. Será necesario descargar un patch y reemplazar algunos archivos en el cliente Oracle. Es aplicable a los clientes 10.2.0.3.0 a 10.2.0.3.7 y las instrucciones vienen con el propio patch.

- En el diálogo siguiente, ingresar los datos de la conexión:

Data Source Name: Un nombre cualquiera describiendo nuestra conexión
Description: Opcional, puede ser nada
TNS Service Name: El mismo nombre de servicio en nuestro tnsnames.ora
User ID: Opcional, puede ser nada

- Pulsar Test Connection para ver si nuestra conexión funciona. Aparecerá un diálogo solicitando usuario y password. En mi caso colocaré scott/tiger. El mensaje Connection successfull deberá aparecer para poder continuar. De lo contrario, revisar el tnsnames.ora que está siendo usado, para ver si tiene la entrada correcta.



Luego de sele
ccionar OK en el diálogo principal, nuestra conexión ODBC está lista para ser usada.

Paso 2. Vincular en Access las tablas de Oracle

- Ingresar a Access y comenzar un nuevo proyecto en blanco.

- En el menú Insertar, elegir Tabla y Vinculación de tabla.

Aparecerá un diálogo como los de seleccionar archivos, pero tenemos primero que elegir en el combo Tipos de archivos, la opción Bases de datos (ODBC).





Luego se muestra el diálogo Select Data Source. Ir a la lengueta Machine Data Source y seleccionar la conexión ODBC creada en el paso anterior.

Somos solicitados para informar el usuario y password de la base de datos Oracle (scott/tiger).





Observar que se nos presentan todas las tablas y vistas a las que el usuario scott tiene acceso. Buscar aquellas que comienzan con el prefijo SCOTT y marcarlas: BONUS, DEPT, EMP y SALGRADE. Seleccionar OK.

- Access solicitará indicar para algunas tablas las columnas identificadoras (clave primaria). Eso dependerá si la clave está definida en la tabla original o no. En este caso vamos a ignorar el aviso simplemente seleccionando OK.

Listo! Las tablas ya están vinculadas en nuestro proyecto. Notar que el nombre de cada tabla asignado automáticamente por Access es [ESQUEMA]_[TABLA].

Paso 3. Crear las relaciones

Cuando se vinculan las tablas del esquema Oracle en Access, no se crean los relacionamientos entre ellas. Es necesario hacerlo manualmente, si queremos mantener el esquema de integridad de datos.

- En el menú Herramientas, ingresar a Relaciones.

- En el diálogo Mostrar Tabla, en la lengueta Tablas, seleccionar (presionando Control) las tablas SCOTT_EMP y SCOTT_DEPT, y pulsar Agregar. Las tablas se mostrarán en la ventana de relacionamientos, y podremos cerrar el diálogo Mostrar Tabla.

- Si la tabla SCOTT_EMP no muestra todas las columnas, agrandarla desde el vértice inferior derecho para permitir ver la columna DEPT.

- Hacer click (izquierdo) sobre la columna DEPT de SCOTT_EMP, y sin soltar, arrastrar la columna hacia la tabla SCOTT_EMP, soltándola encima de la columna DEPTNO (clave primaria de SCOTT_DEPT). Un nuevo diálogo se abrirá con la definición de la relación.



- Podemos elegir las propiedades de esta relación, en nuestro caso vamos a dejar todo por defecto, que corresponde a un INNER JOIN entre ambas tablas. Luego de seleccionar Crear, la relación entre las tablas será visible gráficamente en pantalla.

- Cerrar la ventana de relacionamientos y elegir Sí para guardar los cambios.


Paso 4. Crear formularios

Tenemos las tablas vinculadas en Access con sus relaciones, y de aquí para adelante depende exclusivamente de nuestros conocimientos en Access. Da lo mismo que sean tablas propias de Access o de Oracle con las que estemos trabajando. Podemos inclusive usar este mismo esquema para importar datos desde una tabla Oracle a una local.

Crearemos un formulario para cada una de las tablas importadas, para facilitar el mantenimiento de los datos a través de la aplicación. Explicaré cómo hacer los formularios de DEPT y EMP; para el resto es el mismo procedimiento.

- En la ventana principal de Access, ir a Formularios, y elegir la opción Crear formulario usando el asistente.

- Seleccionar la tabla SCOTT_DEPT en el combo de tablas, y seleccionar todos los campos disponibles (DEPTNO, DNAME, LOC) con el botón >>.

- Presionar Avanzar, elegir el layout 'Columna', el estilo 'Standard', y escribir el título 'Departamentos'. Seleccionar Concluir.

Ya está listo nuestro primer formulario donde daremos mantenimiento a los departamentos. Por defecto, todas las operaciones sobre los datos están permitidas. Si se desea, se pueden alterar las propiedades del formulario activando la caja Propiedades con Alt-Enter.


Ahora vamos al formulario de empleados.

- En la ventana Formularios, elegir la opción Crear formulario usando el asistente.

- Seleccionar la tabla SCOTT_EMP en el combo de tablas, y seleccionar todos los campos disponibles con el botón >>.

- Elegir las mismas opciones que en la tabla anterior, excepto por el título 'Empleados'.

Nuestro formulario de empleados está listo, pero vamos a introducir una pequeña mejora: que el campo DEPTNO en lugar de un texto numérico sea un combo list con los nombres de los departamentos, para evitar tener que recordar los números.

- Seleccionar en el menú Exibir, el modo Diseño.

- Marcar el campo DEPTNO en el formulario y borrarlo (pulsando la tecla DELETE)

- En la caja de herramientas (lateral izquierda) seleccionar la herramienta Caja de Combinación.



- Crear el combo en el lugar que eliminamos el campo numérico DEPTNO. Se abrirá automáticamente el asistente para caja de combinación.

- Seleccionar la primera opción para que el combo busque los datos a partir de una consulta y Avanzar.

- Elegir la tabla SCOTT_DEPT y Avanzar.

- Seleccionar los campos DEPTNO y DNAME y Avanzar

- Definir el orden de ordenamiento por DNAME (creciente) y Avanzar.

- Dejar seleccionado el checkbox Ocultar llave primaria y Avanzar.

- Dejar la opción Recordar valor para uso posterior y Avanzar.

- Si se desea modificar el rótulo, puede alterarse para 'DEPTO'.

- Concluir la operación.

Terminamos nuestro formulario para dar mantenimiento a los empleados.
Ahora nuestro campo de departamentos se actualiza dinámicamente a partir de la tabla SCOTT_DEPT.




Repetir el proceso de creación de formularios para el resto de las tablas, y estaremos aptos para insertar, borrar y alterar registros completamente a través de nuestra interface gráfica.

Paso 5. Crear un reporte

Crearemos un reporte para mostrar un ejemplo más de funcionalidad.

- En la ventana principal de Access, ir a Consultas, y elegir la opción Crear consulta en modo diseño.

- Cerrar la ventana Mostrar tabla y en el menú Exibir seleccionar SQL.

- Escribir la query:
SELECT e.empno, e.ename, e.job, e.sal, d.dname, s.grade
FROM scott_emp e, scott_dept d, scott_salgrade s
WHERE e.deptno=d.deptno
AND e.sal BETWEEN s.losal AND s.hisal
- Cerrar la ventana query y elegir Sí para grabar los cambios, asignando un nombre cualquiera para la consulta, por ejemplo 'Salarios de empleados por departamento'.

- En la ventana principal de Access, ir a Reportes, y elegir la opción Crear reporte usando el asistente.
- En el combo Tablas/Consultas elegir la consulta creada anteriormente, junto con todas las columnas disponibles en la consulta, y avanzar.




- En el nivel de agrupamiento, elegir DNAME (el departamento) y avanzar.





- Elegir el ordenamiento por orden de ENAME creciente.

- Dejar las opciones de diseño por defecto y avanzar hasta visualizar el reporte.
Nuestro reporte está listo luego de 10 minutos con cero esfuerzo de programación, simplemente usando una sencilla consulta SQL.







Combinamos la facilidad de uso y versatilidad de una herramienta como Access con el potencial de una base de datos Oracle. Las posibilidades dependen sólo de nuestra voluntad, pudiendo integrar planillas de Excel, gráficos, multimedia y mejorar la lógica de nuestra aplicacion con VBA (Visual Basic for Applications).

martes, 13 de enero de 2009

Aumento en esperas log file sync

Se puede encontrar mucha "blografía" acerca de este clásico problema de tunning. El parámetro log file sync está directamente ligado al proceso interno LGWR que sincroniza el log buffer (área de memoria interna de la SGA) y redo logs en disco (archivos donde son registradas las transacciones), y puede revelar uno de dos problemas (sino ambos): un sub dimensionamiento de la base de datos, o un uso inadecuado de transacciones en las aplicaciones que la usan. En ambos casos, el tiempo de respuesta puede experimentar picos, deteriorando la performance.

Si bien alguien puede pensar en alterar la configuración de Oracle, es mejor comenzar a buscar posibles sesiones ofensoras. Es sabido que el 98% de las veces las aplicaciones son las principales reponsables de los problemas de performance que los usuarios reclaman, y no la base de datos.

Recientemente tuve la oportunidad de ver un problema de performance en una base de datos de producción que comenzó a paralizarse sin explicación aparente. He notado una tendencia de los gerentes a pensar los problemas de performance como problemas de hardware, y que adquiriendo nuevos recursos el problema se soluciona. Hay veces que puede ser cierto -no descartemos posibilidades- pero antes de cualquier conjetura y gasto innecesario, un poco de investigación es primordial. Para eso, Oracle nos provee de algunas herramientas de gran utilidad para identificar comportamientos anómalos, y causas que los producen.

En este caso, fue la herramienta de diagnóstico Statspack quien reveló la verdadera causa del problema.

El primer indicio en el reporte fue en los Top 5 wait events: log file sync al tope de la lista.
Eso significaba que Oracle estaba generando mucho más REDO, y tan agresivamente que la instancia dificilmente podía procesarlo. Existen errores típicos de programación PL/SQL que generan más REDO del necesario, uno de ellos es el clásico commit dentro del loop:
FOR rec IN (SELECT * from TABLA_GRANDE)
-- ..
-- procesamiento del registro rec
COMMIT;
END LOOP;
-- no es aquí donde debería hacerse el commit?
Analisando los resultados del reporte de Statspack, se pudo percibir que no había habido un aumento de COMMITs sino de ROLLBACKs.

Extracto de Statspack:
Load Profile
-----------



% Blocks changed per Read: 4.09 Recursive Call %: 92.21
Rollback per transaction %: 64.85 Rows per Sort: 14.35

....
user commits 96,732 161.2 0.4
user rollbacks 175,390 292.3 0.6
...
El valor de Rollback per transaction indicaba que un 65% de las transacciones terminaban con rollback, y más adelante en el reporte se pudo verificar que efectivamente estaban ocurriendo el doble de rollbacks que de commits. Esto no es normal prácticamente en ninguna base de datos, al menos las que yo he visto. Leyendo reportes anteriores con actividad normal, se comprobó que efectivamente estos números había cambiado (anteriormente el índice era 4%). Sirvió para enfocarse y analisar de lleno las aplicaciones que se estaban ejecutando, y descubrir a través de las sesiones activas que una de ellas generaba mucho rollback. Sucede que el código que esta ejecutaba, había sido levemente modificado (3 líneas) y estaba generando errores inesperados con rollbacks de forma indiscriminada.

Las bases de datos deben servir a las aplicaciones y usuarios de la mejor forma posible. Para eso, no basta simplemente con ampliar recursos de memoria ni ajustar parámetros. Las aplicaciones que afectan la base de datos deben ser las primeras en ser tuneadas, y ni que hablar testeadas antes de colocarlas en producción. Ya sea en proyectos de desarrollo o mantenimiento, los tests de carga son necesarios. Un test de funcionalidad con una única sesión no revela este tipo de defectos, los cuales sumados en un ambiente real se convierten en un gran problema.

Haciendo las veces de investigador, el DBA debe recurrir a las herramientas de diagnóstico como Statspack, tkprof y vistas dinámicas. En caso de tratarse de código malicioso, será fundamental trabajar en equipo junto a los desarrolladores para hallar la fuente del problema.

viernes, 9 de enero de 2009