Oracle11g. Identificación de SQL Cargado

Identificar el SQL Cargado es el primer paso en el proceso de ajuste de SQL. En este capítulo se verán las distintas herramientas disponibles para identificarlo con:

• ADDM
• Top SQL
• Dynamic performance views
• Statspack

Proceso de Ajuste de SQL

Existen tres pasos en el ajuste de SQL:

• Identificar las sentencias SQL que son las responsables de la carga de la aplicación y consumo de recursos del sistema, buscando en  la historia de las ejecuciones pasadas de SQL en el sistema( con el informe de ADDM o la página de Top SQL en Enterprise Manager, o las estadísticas en V$SQL).
• Analizar el SQL para verificar que la ejecución de los planes producidos por el optimizador de consultas ejecutan razonablemente bien.
• Realizar acciones correctivas para generar el mejor plan de ejecución para la mejora de rendimiento de las sentencias.

Estos pasos se deben aplicar hasta que el rendimiento del sistema mejora o hasta que no  hay más sentencias que ajustar.

Identificar el SQL Cargado

Este es el primer paso en el proceso de ajuste. Las sentencias que tienen un tiempo de respuesta bajo o bien un alto consumo de recursos como (CPU, E/S, y espacio temporal) se consideran SQL cargado.

Con Oracle Database 11g, la tarea para identificarlo ha sido automatizada por Automatic Database Diagnostic Monitor (ADDM). ADDM es la herramienta proactiva de diagnósticos de rendimiento que automáticamente identifica cuellos de botella dentro de Oracle Database, incluyendo bajo rendimiento o SQL cargado, y hace recomendaciones acerca de las opciones disponibles para fijar estos cuellos.

Otras formas para identificarlo en Oracle Database 11g es con la página de Top SQL en Enterprise Manager (EM) que contiene información de los SQL que más recursos consumen en el sistema. Hay información para dos tipos de SQL:

• Spot SQL: SQL actual para el cursor caché y  sesiones activas
• Period SQL: SQL histórico almacenado en Automatic Workload Repository (AWR) based en el período de retención especificado

Se puede obtener información de SQL cargado en V$SQL_AREA y V$ACTIVE_SESSION_HISTORY y en vistas de repositorio de carga como DBA_HIST_ACTIVE_SESS_HISTORY y DBA_HIST_SQL_PLAN.

Automatic Database Diagnostic Monitor( ADDM)

ADDM es usado para tareas de ajuste proactivas y efectivas en la base de datos. ADDM ejecuta de manera automática a intervalos predefinidos y analiza el rendimiento del sistema. Identifica todos los problemas potenciales de rendimiento incluyendo SQL. Asimismo la tarea de identificación de SQL cargado es automáticamente ejecutado por ADDM y sólo hay que mirar en el informe de ADDM para ver las recomendaciones.

Por defecto la base de datos captura estadísticas cada 60 minutos de la SGA y lo almacena en AWR en forma de snapshots. Estos snapshots se almacenan en disco. Adicionalmente,  ADDM se planifica para ejecutar automáticamente por MMON, un nuevo proceso background introducido en Oracle Database 11g para detectar problemas proactivamente. Cada vez que se toma un snapshot, ADDM se dispara para hacer el análisis del período correspondiente a los dos últimos snapshots. Como capacidad de los monitores proactivos de la instancia detecta todos los tipos de cuellos de botella de rendimiento.

El resultado de cada análisis de ADDM se almacena dentro de AWR y está accesible desde la consola de EM y en línea de comando.

Salida de ADDM

La mejor manera de ver ADDM es con la consola de EM. Después de cada análisis, ADDM saca información correspondiente al impacto de base de datos. El impacto se mide por el porcentaje de tiempo de espera en la base de datos. ADDM encuentra síntomas y causas de raíz. ADDM también produce un conjunto de recomendaciones con beneficios asociados y razones. Las recomendaciones pueden incluir información para detallar más el análisis de un problema particular.

La salida de ADDM es también almacenada en AWR para propósitos de histórico.

Top SQL. Identificación Manual

Otra forma para identificar el SQL cargado es listar desde EM las sentencias ‘Top SQL’. Estas son capturadas por AWR basado en sus estadísticas acumulativas dentro de la ventana seleccionada. Las sentencias ‘Top SQL’ pueden ser ordenadas en base a consumos de CPU, tiempos de retardo, buffers conseguidos, etc.. El usuario puede seleccionar o más sentencias identificadas por su SQL ID y enlazarlo con SQL Tuning Advisor para ajustarlas.

Conjuntos de Ajuste de SQL . SQL Tuning Set (STS)

Un conjunto de ajuste de SQL es un nuevo objeto para ajustar múltiples sentencias SQL y manejar cargas de SQL. Un STS es un objeto de base de datos que almacena una o más sentencias a través de sus contexto y estadísticas de ejecución y  posiblemente con ranking de prioridad de usuarios. Las sentencias SQL pueden cargarse dentro de un STS de diferentes fuentes SQL. Las fuentes SQL incluyen una lista de top SQL, Automatic Workload Repository (AWR), cursor caché, y SQL que necesita analizarse.  Se pueden seleccionar las sentencias SQL de interés desde la página de ‘Top SQL’ y cada ajuste luego crearlo en STS que almacena las sentencias pertenecientes  con su contexto de ejecución para luego ajustar. EM permite que se busquen en varios STSs creados por diferentes usuarios. STSs se usan como fuente para ajuste personalizado de SQL por SQL Tuning y Access Advisors.

Spot SQL

Muestra como las sentencias SQL pueden seleccionarse desde la página de ‘Top SQL’ para darlas como entrada a SQL Tuning Advisor. La página de Top SQL tiene dos pestañas.

• Spot SQL
• Period SQL

Spot SQL muestra todas las sentencias SQL que han estado activas en un intervalo reciente de 5 minutos. Esta ventana se usa para identificar sentencias actuales o ejecutadas recientemente.

Si es más concerniente a CPU, examinar después ‘top SQL’ que tengan el valor más alto de CPU_TIME durante ese intervalo. Por otro lado se puede empezar por la sentencia que más DISK_READS tenga.

Para acceder a estas páginas, seleecionar EM àPágina de Performanceà  Additional Monitoring Links (TOP SQL)à Period SQL ó SPOT SQL.

Period SQL

La información mostrada se almacena en AWR.  Se usa para identificar el SQL cargado histórico. Se puede usar para seleccionar un intervalo de histórico de 24 horas para los cuáles se puede ver datos en gráficos. Se puede usar los datos de sesiones activas para ayudarte en la selección.

La información en AWR se retiene por defecto 7 días. Esto significa que se puede ver el SQL de 7 días e identificar el SQL cargado desde este período.

Statspack

Statspack es un conjunto de SQL, PL/SQL, y scripts SQL*Plus que permiten coleccionar, almacenar y visualizar datos de rendimiento y SQL Cargado. Un usuario se crea de manera automática con el script de instalación (PERFSTAT). Este es propietario de todos los objetos necesarios por el paquete. El usuario tiene permisos de consulta sobre las vistas de la capa v$ requeridas para realizar el ajuste. Los usuarios de Statspack pueden familiarizarse con el concepto de snapshot, como una sola colección de datos de rendimiento. Cada snapshot está identificado por un ID, que se genera cuando se crea. Cada vez que se genera una colección nueva un nuevo SNAP_ID se genera. SNAP_ID,  junto con (DBID) e (INSTANCE_NUMBER), permite identificar a múltiples instancias en un entorno de Oracle Real Application Clusters. Una vez recogido el snapshot, es posible ejecutar el informe de rendimiento. El informe a generar permite especificar dos ID entre los cuales calcular la actividad de la instancia entre dos períodos para dos snapshot.

Statspack puede usarse con Standard Edition de Oracle Database si ADDM no está disponible.

Entre las características de Statspack están:

• Statspack collecciona datos incluyendo SQL cargado.
•  Statspack precalcula muchos ratios usados en el ajuste de rendimiento, como ratios de hits de cache, estadísticas por transacciones y por segundo.
• Las tablas permanentes propiedad de PERFSTAT almacenan estadísticas de rendimiento. En vez de crearlas cada vez y analizar se puede mantener un histórico más permitir comparaciones de manera más sencilla.
• Statspack separa la colección de datos de la generación del informe. Los datos se colleccionan como un snapshot; la colección de datos es más fácil automatizarla con el paquete DBMS_JOB.

Vistas de Rendimiento Dinámico

También pueden utilizarse para ver el SQL cargado y las sentencias más frecuentes y responsables de la carga de la aplicación y recursos del sistema. Se hace revisando la historia del pasado de la ejecución de SQL disponible en el sistema.

Los pasos a seguir son:

• Determinar que período en el día se debería examinar; típicamente se hace en los picos en los que hay una bajada de rendimiento.
• Recopilar estadísticas de Oracle y del sistema al finalizar dicho período. Lo mínimo a recoger sería:
  • Estadísticas de sistema en V$SYSSTAT
  • Estadísticas de SQL en V$SQLAREA
• Usar los datos collecionados en el apartado anterior, identificar las sentencias SQL que usa más recursos. Una buena forma para identificar sentencias candidatas es en V$SQLAREA. V$SQLAREA contiene información de el uso de recursos para todas las sentencias SQL en shared pool. Los datos en V$SQLAREA deberían ordenarse por uso de recursos.

V$SQLAREA

Se puede unir PARSING_USER_ID con USER_ID dela vista DBA_USERS. Adicionalemente se puede consultar para valores grandes para DISK_READS (excedan de 111.000).

Una salida sería:

Investigar operaciones de Full Table Scan

Un full table scans (FTS) en una tabla grande debería ser un recurso a minimizar. Para encontrar esta operación se puede mirar en V$SYSSTAT.

Se considera una tabla grande cuando ocupa más de 25 buffer cache; una pequeña cuando tiene menos de 25 buffer cache.

Si los recorridos de tablas grandes es alto, se debería pensar en añadir índices. Con esto deberían decrecer los FTS. Si no es así es que no se han diseñado de manera correcta los índices.