Cómo utilizar SQL Trace

El puente de Waterloo - Andre Derain

El puente de Waterloo - Andre Derain

Para utilizar el recurso SQL Trace, lo primero que hay que hacer es habilitarlo. SQL Trace puede ser habilitado a nivel de sesión o a nivel de instancia. Cuando se habilita SQL Trace a nivel de sesión, Oracle genera un archivo de trace que contiene información estadística de todas las sentencias SQL de dicha sesión. Cuando se habilita SQL Trace para la instancia, Oracle crea archivos de trace separados por cada proceso.

Para utilizar SQL Trace hay que:

  • Verificar los parámetros de inicialización TIMED_STATISTICS, MAX_DUMP_FILE_SIZE y USER_DUMP_DEST.
  • Determinar la forma en que se identificará el archivo de trace resultante.
  • Habilitar SQL Trace
  • Deshabilitar SQL Trace
Anuncios

Introducción a SQL Trace

SQL Trace es un recurso que provee Oracle para poder obtener información de performance de sentencias SQL en forma individual. Por cada sentencia SQL que se ejecuta, SQL Trace brinda la siguiente información estadística:

  • Cantidad de parseos, ejecuciones y fetches
  • Tiempos de CPU y tiempo total transcurrido durante la ejecución de la sentencia
  • Cantidad de lecturas lógicas y lecturas físicas
  • Cantidad de filas procesadas
  • Cantidad de veces que se perdió la sentencia en la library cache
  • Usuario bajo el cual se hizo el parseo de la sentencia
  • Registro de cada commit y rollback
  • Eventos de espera que se produjeron durante la ejecución de la sentencia SQL
  • Información Resumida

Si, además, el cursor creado para la sentencia SQL fue cerrado, SQL Trace ofrece la siguiente información adicional:

  • El plan de ejecución real de la sentencia SQL
  • Cantidad de filas, cantidad de lecturas consistentes, cantidad de lecturas físicas, cantidad de escrituras físicas y tiempo transcurrido por cada operación a nivel de fila.

Si bien es posible habilitar SQL Trace a nivel de sesión o instancia, Oracle recomienda utilizar los packages DBMS_SESSION o DBMS_MONITOR. Cuando SQL Trace se habilita para la sesión o instancia, Oracle comienza a crear archivos de trace (externos a la base) con la información estadística de las sentencias SQL ejecutadas por la sesión o en la instancia. Es importante tener en cuenta que la habilitación de SQL Trace puede tener un alto impacto en la performance general del sistema provocando excesivo consumo de CPU y espacio en disco (sobre todo si se lo habilita a nivel de instancia).

La buffer cache

La buffer cache es utilizada para almacenar en memoria los bloques recientemente accedidos de los datafiles. Un bloque es la unidad mínima de almacenamiento que lee o escribe Oracle en un momento determinado.

Es muy importante saber que antes de que un proceso de usuario pueda acceder a un bloque de datos, el bloque debe residir en la buffer cache. Esto significa que si un dato que va a ser accedido no reside en la buffer cache entonces el dato deberá ser recuperado desde el datafile en disco y colocado en la buffer cache. Cualquier modificación a los datos también se hace en los bloques almacenados en la buffer cache. Los datos nunca son modificados directamente sobre los datafiles en disco.

La shared pool

La shared pool puede ser dividida en dos grandes secciones: la library cache y la dictionary cache.

La library cache está diseñada para incrementar la eficiencia del código SQL permitiendo que se compartan entre los usuarios las sentencias tanto SQL como PL/SQL. La library cache almacena todas las sentencias SQL parseadas.

Cuando un usuario ejecuta una sentencia ocurren dos cosas. Primero, Oracle verifica si ya existe en la library cache una sentencia idéntica. Si no se encuentra una sentencia idéntica, la sentencia debe ser parseada y luego alojada en la library cache. El parseo requiere, a su vez, diversas subtareas que implican overhead para el sistema. Si, en cambio, existe una sentencia idéntica en la library cache, Oracle pobablemente la reutilice.

La otra parte importante de la shared pool es la dictionary cache. El objetivo de la dictionary cache es reducir los accesos a disco. La dictionary cache es similar a la library cache en el sentido de que ambas mantienen información reciente en memoria. El diccionario de datos contiene metadatos sobre la misma base de datos. La dictionary cache cachea esos metadatos. Cuando Oracle necesita información de esos metadatos, los busca primero en la dictionary cache. Si Oracle no encuentra en la dictionary cache los datos que necesita, entonces tendrá que efectuar operaciones de input-output para recuperar la información del diccionario.

La SGA

La persistencia de la memoria - Salvador Dali

La persistencia de la memoria - Salvador Dali

Cada instancia Oracle requiere su área de memoria propia. Esta área de memoria se llama System Global Area o SGA. Si bien esta área de memoria es dedicada para la instancia, es compartida por todos los usuarios que se conectan a la base. Es decir que, desde el punto de vista de la instancia, la SGA es una área de memoria dedicada pues no se comparte con otras instancias. Desde el punto de vista de los usuarios que se conectan a la instancia, la SGA es un área de memoria compartida.

La SGA está compueseta por tres estructuras principales:

  • la buffer cache
  • la shared pool
  • el redo log buffer

Estructuras de memoria

Primera Liz coloreada - Andy Warhol

Primera Liz coloreada - Andy Warhol

Oracle siempre trata de mantener la mayor cantidad posible de información cacheada en memoria. Mantener la información en memoria mejora el rendimiento general de la base de datos dado que los datos que se encuentran en cache son accedidos mucho más rápido que los que requieren acceso a disco. Al configurar una base de datos, el dimensionamiento de la memoria es muy importante. Si es demasiado chica, se obligará a Oracle a buscar datos en disco con demasiada frecuencia. Si se la dimensiona demasiado grande puede ocurrir que no haya suficiente memoria física provocando paginado a nivel de sistema operativo, afectando negativamente el rendimiento general de la base de datos.

Extents

Moon woman - Jackson Pollock

Moon woman - Jackson Pollock

Cada tanto, los segmentos de datos o índices que están contenidos en un tablespace necesitan espacio para crecer más allá del espacio que tienen alocado. El segmento necesita extenderse. El extent es una unidad de almacenamiento que contiene información del segmento en un conjunto de bloques contiguos. El segmento al crecer, aloca extents.