Herramienta para diagnosticar problemas de performance (Oracle Performance Viewer Freeware)

(Ahora podes bajar la ultima versión del utilitario desde http://www.oramdq.com/oracle-performance-viewer/)

Hace ya varios años que trabajo con bases de datos Oracle y en todo ese tiempo fui acumulando scripts que me facilitaron diversas tareas de administración, mantenimiento, monitoreo, etc. Ya que ultimamente me vengo dedicando a temas de performance, me di cuenta que en ese tipo de actividad es importante analizar la mayor cantidad de información en el menor tiempo posible, recordemos que Oracle es una base altamente instrumentada y que versión tras versión se van agregando mas métricas (Oracle Wait Interface). A partir de 10g tenemos el repositorio AWR que almacena historial detallado de la actividad de la base.

El AWR resulta de mucha utilidad como baseline o punto de comparación cuando nos encontramos con una base que esta con problemas de rendimiento, detectados o bien por la activación de alarmas o peor aún, cuando el usuario final persive la demora y realiza el reclamo. Cualquiera que trabaje como DBA de bases productivas, ni hablar si son muy criticas, tendrá que responder rapidamente a su superior evaluando el escenario, diagnosticando y proponiendo o activando cursos de acción en forma inmediata. A veces los problemas no se detectan con facilidad y empezar a correr scripts por separado puede resultar un tanto lento.

Para facilitarme la tarea, programé un pequeño utilitario en C# donde agregué varias de las consultas que utilizo diariamente, sumado a todo el poder gráfico que me permite entre otras cosas analizar la historia, filtrar convenientemente, exportar a excel la grilla, explotar la información con doble click sobre la celda, etc. Abajo copié algunas pantallas para mostrarles como esta pensada la aplicación y obviamente me interesa compartirla en forma gratuita con quien le interesa y asi poder mejorarla.


La pantalla principal es una MDI con 4 paneles, el de arriba a la izquierda tiene una estructura de árbol con todos los reportes disponibles hasta el momento, clasificados según cierto criterio. el panel de abajo a la izquierda tiene un resumen de la base de datos donde esta conectada la app. el panel de arriba a la derecha tiene la grilla con los resultados, y el panel de abajo a la derecha tiene la consulta que se ejecutó para llenar la grilla.



La pantalla 2 muestra los criterios de filtrado para llenar la grilla de acuerdo al tipo de reporte que se este ejecutando:



La pantalla 3 muestra la grilla resultado. En el ejemplo se ejecutó el reporte de historial de DB Time:



La pantalla 4 muestra la salida del reporte de historial de los 5 eventos de espera mas importantes:



La pantalla 5 muestra las sesiones actuales de la base (información actual)



Con doble click sobre una fila de la grilla se abre otro form con detalle de la sesion seleccionada. En el panel donde aparece el texto de la sentencia que esta ejecutando la sesión se puede clickear el boton derecho y ver y seleccionar "Detalle.." para ver el historial de ejecución de dicha sentencia.



La siguiente pantalla muestra todos los forms abiertos puestos en mosaico horizontal (menu Ventanas)



La pantalla 8 es el resultado de la ejecucion del reporte de Tablespaces



Con doble click sobre la fila de la grilla se abre un nuevo form con detalle del tablespace:



La pantalla 10 muestra los posibles filtros que se pueden realizar para analizar el historial de sentencias TOP.



La pantalla 11 muesta las sentencias TOP que cumplen los filtros definidos anteriormente:



Con un click sobre la celda que tiene el SQLID se copia el valor y luego presionando el boton de REFRESCAR se "pastea" el valor copiado para ver historial de la sentencia:



Todas las grillas de resultado se pueden exportar a excel, se pueden filtrar y todas las consultas se puede copiar (botón derecho sobre el panel donde esta el texto de la consulta y seleccionar COPIAR).

Mi idea fue mostrarles algunas pantallas para que vean la funcionalidad que intenté darle a mi aplicación. Obviamente hay varios reportes mas que no estan detallados en el blog, pero invito a quien quiera evaluar mi programita (se llama OraPerfViewer.exe y pesa alrededor de 150kb) que solo me escriba a: rovedop@gmail.com y les enviaré el ejecutable que solo requiere windows y un framework .NET instalado (creo que la mayoria de las windows lo traen instalado por default). Por el momento no es RAC-Aware, pero en el futuro lo será y corre sobre versiones 10g en adelante.

Es una primera versión, y seguramente tenga varios bugs y cosas que mejorar, pero estoy seguro que compartiendo y discutiendo ideas se mejoran las cosas.

Hard Parse vs Soft Parse vs Non Parse

El impacto del parsing en una base de datos puede ser muy variable. En ciertos casos, afortunadamente la mayoria, no es notorio. En otros casos mas especificos puede ocasionar problemas mayores de rendimiento de la base de datos. En general los problemas de excesivo parsing se deben a una mala programación, es decir se deben analizar y solucionar desde el código de las aplicaciones que interactuan con la base. Es por eso que los desarrolladores deben estar concientes del impacto negativo que puede provocar una mala programación y considerar este tema como punto prioritario desde el inicio de la confección del código.

El parsing es el primer paso que se lleva a cabo para procesar una sentencia. En esta etapa se debe conocer de que tipo de sentencia se trata (DML, DDL o un select) para asi poder realizar los correspondientes chequeos. Los principales actividades son el chequeo sintáctico y el análisis semántico

Chequeo Sintáctico

Este chequeo verifica si la sentencia cumple con la gramática de la sentencia definida para la versión de la base.

Análisis Semántico

Se analiza si los objetos referenciados en la sentencia existen, si las columnas existen, si se tiene acceso a los segmentos y a las columnas (privilegios), etc.

Una vez que se pasan con éxito las dos etapas antes mencionadas, Oracle busca en la memoria (shared pool) para ver si ya fue ejecutada la misma sentencia por otra sesión. Si la encuentra, entonces se dice que se realizó un SOFT PARSE. Por otro lado, si no la encuentra, se realizan dos pasos adicionales, que son la optimización de la sentencia y la generación y carga del plan en la memoria (row source generation). La ejecución de todos los pasos se llama HARD PARSE. El hard parsing es cpu intensivo, y en el caso de que sea elevado puede compromenter seriamente la performance general dada la alta contención que se provoca. Para evitar el hard parsing hay que usar variables BIND en los statements (ej: usar preparedStatement). Si se trata de un código "enlatado" donde no se utilizan binds y no puede modificarse se puede utilizar en la base CURSOR_SHARING, cuyo default es EXACT y habría que cambiarlo a SIMILAR (existe a partir de 9i) o FORCE, aunque siempre recomiendo usar SIMILAR, porque es menos riesgoso.

El soft parse puede ser aún mas soft si se cachea el cursor en la sesión (session_cached_cursor) y asi se evita ir a la shared pool a buscarlo. Desde el código de la app se puede habilitar y definir el tamaño de cache mas adecuado (ej: ((oracle.jdbc.OracleConnection)connection).setStatementCacheSize(40)). Esto esta disponible en casi todas las interfaces (jdbc,.NET,PL/SQL,oci,etc).

Para evitar el reparseo en una sesión se debe mantener abierto el cursor. Algunas interfaces tales como PL/SQL, jdbc y la oci permiten realizar esto. La interfaz OLE DB, SQLJ u ODP, al menos hasta la ultima version que conozco, no lo permiten. A continuación voy a copiar 3 fragmentos de código Java para mostrar la diferencia entre parseo hard, soft y no parsear.

El primer fragmento de abajo muestra la NO utilización de binding, ya que se concatenan los literales y no se usa PreparedStatement:

TEST 1
-------

      sql = "SELECT X FROM t WHERE Y = ";
      for (int i=0 ; i<10000; i++)
      {
        statement = connection.createStatement();
        resultset = statement.executeQuery(sql + Integer.toString(i));
        if (resultset.next())
        {
          val = resultset.getString("X");       
        }
        resultset.close();
        statement.close();
      }

Este código, además de se muy pobre en performance, propicia el hacking por sql injection.

El segundo fragmento utiliza binding pero abre y cierra el cursor en cada ejecución por lo cual genera soft parse.

TEST 2
-------

      sql = "SELECT X FROM t WHERE Y = ?";
      for (int i=0 ; i<10000; i++)
      {
        statement = connection.prepareStatement(sql);
        statement.setInt(1, i);
        resultset = statement.executeQuery();
        if (resultset.next())
        {
          val = resultset.getString("X");          
        }
        resultset.close();
        statement.close();
      } 

El último fragmento, que es el óptimo, reduce el parsing al mínimo (solo un soft parsing):

TEST 3
-------

      sql = "SELECT X FROM t WHERE Y = ?";
      statement = connection.prepareStatement(sql);
      for (int i=0 ; i<10000; i++)
      {
        statement.setInt(1, i);
        resultset = statement.executeQuery();
        if (resultset.next())
        {
          val = resultset.getString("X");          
        }
        resultset.close();
      } 
      statement.close();

En una prueba que realicé los tiempos de respuesta de cada test fueron los siguientes:

TEST1 --> 12.2"
TEST2 --> 6.4"
TEST2 (caching) --> 3.9"
TEST3 --> 3.7"
TEST3 (caching) --> 3.7"

Como se ve arriba, el TEST2, se puede mejorar usando caching, pero al usar caching en el TEST3 no se ven diferencias.

El parseo se puede ver como una mini compilación, podriamos comparar un código que ejecuta en un bucle un prepareStatement para cada sentencia con un código interpretado. Cualquier programador sabrá que la ejecución de un código compilado es mucho mas rápida que ejecutar un código que necesita interpretarse linea por línea.