TOP SQL

Saludos,

En ocasiones no basta solo con realizar una buena (me refiero a que use buenas practicas SQL o que use los índices correctos) consulta SQL eficiente y cuyo costo no se alto, puede ser que durante una arranque inicial de un sistema , las consultas SQL no se comportan nada anormal ya que el rendimiento y tiempo de respuesta es eficiente, pero que pasará luego de 1, 2, 3 o 12 meses? Nos reportan los usuarios que cada mes el reporte o sistema se pone más lento?

Eso pasa en todo sistema que arranca desde cero o que se implementa nuevos módulos o funcionalidades que requieren crea tablas, agregar columnas o crear nuevas consultas. Es por ello que se necesita siempre de un constante monitoreo de dichas consultas SQL que se están ejecutando o ejecutaron en la base de datos para posterior a esto realizar un afinamiento que mejore el desempeño.

 ¿Cómo podemos monitorear los SQL pesados?
Podemos ayudarnos de herramientas o scripts para obtener esta información:

• Oracle Enterprise Manager
• TOAD SGA Trace
• Oracle SQL Developer
• Scripts SQL

Oracle Enterprise Manager:
El Enterprise manager proporciona una utilidad para el análisis de los TOP SQL que se han ejecutado y analizar la sentencia.

Aquí nos proporciona una lista de los SQL que se han ejecutado que tienen mayor consumo.
Esta utilidad se encuentra dentro del Enterprise Manager de Oracle Data Base.

 TOAD SGA Trace
Es una utilidad integrada al TOAD que nos permite visualizar los SQL almacenados en el área SQL del Oracle. Aquí se muestra información estadística sobre los SQL ejecutados, pero ¿cómo podemos determinar los SQL pesados? yo siempre me baso en tres columnas para encontrarlos:

• Hit Rate.- Es un valor porcentual que va desde 0 hasta 100. Este valor representa una relación de lecturas de bloques consistentes (Lecturas/Escrituras en la SGA, para ser mas exacto en la Buffer Cache) vs las lecturas físicas (Lecturas/Escrituras de bloques de datos en disco) que se realizan al ejecutar un SQL. Lo óptimo es que este valor este entre 85% y 100%, ya que eso nos indicaría que la sentencia trabaja más sobre los bloques de datos de la Buffer Cache que sobre los bloques de datos de los datafile almacenados en disco, si el valor retornado es menor a 85 es necesario analizar la sentencia para ver que se puede optimizar.
• Buffer Gets.- Es la cantidad de bloques de datos recuperados del área Buffer Cache de la SGA para que la sentencia SQL necesitó para poder elaborarse.
• Disk Reads.- Es la cantidad de bloques de datos recuperados de los datafiles para que la sentencia SQL necesitó para poder elaborarse.

Toad es una herramienta pagada muy buena herramienta, pero puedes usar la versión de prueba que puedes descargar del siguiente link Toad 10

 Oracle SQL Developer

Esta es una herramienta de Oracle para desarrolladores SQL y PL/SQL y puedes descargarla de manera gratuita desde la siguiente link OracleSqlDeveloper

Esta herramienta ofrece una opción de informes relacionados con la base de datos y que incluye informes de rendimientos SQL que los listará por:

• SQL Principal por CPU.- Por el consumo de CPU que realice la ejecución de esta sentencia.
• SQL Principal por Ejecuciones.- Por la cantidad de veces que se ha ejecutado.
• SQL Principal por Esperas.- Por el tiempo que ha demorado en ejecutarse el SQL.
• SQL Principal por Lecturas de Disco.- Por la cantidad de lecturas de bloques a disco que se realiza.
• SQL Principal por Obtenciones en Buffer.- Por la cantidad de lecturas de bloques a la Buffer Cache
• SQL Principal por Obtenciones en Buffer/Procesos.- Por la cantidad de lecturas de bloques a la Buffer Cache con relación al procesamiento

Scripts
La manera más artesanal de verificarlo es por medio de los scripts. A continuación script que les servirá para obtener los SQL con alto consumo.

SQL con Hit Rate inferior al 85% y con mas de 1000 bloques leídos en disco, ordenados de manera ascendente por el Hit Rate

SELECT sql_text, disk_reads, buffer_gets, module, (buffer_gets - disk_reads) / buffer_gets hit_rate FROM v$sqlarea WHERE buffer_gets != 0 AND (buffer_gets - disk_reads) / buffer_gets < .85 AND disk_reads > 1000 ORDER BY 4;

SQL con mas de 1000 bloques leídos en disco, ordenados de manera descendente por el campo Disk_Reads

SELECT sql_text, disk_reads, buffer_gets, module, (buffer_gets - disk_reads) / buffer_gets hit_rate FROM v$sqlarea WHERE buffer_gets != 0 AND disk_reads > 1000 ORDER BY 4;