Tema 4 - Tuning 565656565 PDF

Preview

Summary

pdddd podddd dsfsdasd asda asd asdasd asd...

Description

IV - AJUSTE DEL RENDIMIENTO (TUNING)

9.

Mejoras en las Operaciones de Ordenación

10.

Ajustes del Segmento Deshacer (Rollback)

11.

Mejoras de las Consultas SQL

12.

Gestión de las Estadísticas

Instituto Superior de Formación Docente y Técnica Nº 5 José Eugenio Tello

IV – AJUSTE DEL RENDIMIENTO (Tuning)

TECNICATURA SUPERIOR EN INFORMÁTICA Taller de Ajuste y Optimización de Bases de Datos 2021

Los problemas relacionados con el rendimiento de la aplicación se relacionan frecuentemente a consultas mal estructuradas ó a un diseño de la base de datos ineficiente. Debido a esto, Oracle provee al DBA una serie de herramientas y técnicas para identificar y solucionar este tipo de problemas y, por lo tanto, solucionar los problemas de rendimiento de la aplicación. Estas herramientas ó técnicas pueden ser utilizadas tanto en ambientes de desarrollo, donde el costo de implementación de las mejoras es mínimo, como en ambientes productivos, en donde se hace más costoso este tipo de ajustes. En este capítulo veremos cómo detectar y mejorar problemas de rendimiento relacionados con las operaciones de ordenación de las consultas que se realizan a la base de datos, como es el caso del ajuste del segmento de deshacer, para el trabajo con las transacciones. Este capítulo finaliza con el recorrido por las herramientas más importantes para la recolección y análisis de estadísticas relacionadas con el rendimiento tanto de las consultas de las aplicaciones como de los procesos que impactan en el almacenamiento y obtención de los datos de la base de datos.

Profesor: Mg. Ing. Héctor Pedro Liberatori

Pág. 95

Instituto Superior de Formación Docente y Técnica Nº 5 José Eugenio Tello

IV – AJUSTE DEL RENDIMIENTO (Tuning)

TECNICATURA SUPERIOR EN INFORMÁTICA Taller de Ajuste y Optimización de Bases de Datos 2021

UNIDAD 9 MEJORAS EN LAS OPERACIONES DE ORDENACION

9.1. OBJETIVOS En esta unidad el alumno deberá adquirir conocimientos que le permitan saber listar las operaciones que necesitan espacio temporal y saber crear y controlar Tablespaces Temporales. También deberá poder describir el uso de disco y memoria en operaciones de ordenación, y poder establecer la diferencia entre las operaciones de ordenación de disco y las de memoria. Por último, el alumno deberá poder llegar a conocer tanto las formas de reducir las ordenaciones en disco, como los parámetros de inicialización para las ordenaciones.

9.2. CUESTIONARIO DE INICIACION

9.3. ADMINISTRACION AUTOMATICA DEL AREA DE ORDENAMIENTO Las ordenaciones ocurren cuando los datos resultantes de una consulta SQL a la base de datos requieren ser representados con un orden específico. Cuando una ordenación ocurre en una instancia Oracle, ésta puede ocurrir en dos ubicaciones diferentes: en la memoria ó en el disco. Las ordenaciones en memoria son menos costosas en términos de rendimiento y las ordenaciones en disco son más costosas, ya que requieren de un procesamiento adicional de acceso a disco de E/S. Por lo general, la primer meta, en cuanto a objetivos de rendimiento, es minimizar la actividad de ordenaciones, cuando esto sea posible. De lo contrario, la meta es tratar de realizar, lo más que se pueda, estas operaciones en memoria.

Profesor: Mg. Ing. Héctor Pedro Liberatori

Pág. 96

Instituto Superior de Formación Docente y Técnica Nº 5 José Eugenio Tello

IV – AJUSTE DEL RENDIMIENTO (Tuning)

TECNICATURA SUPERIOR EN INFORMÁTICA Taller de Ajuste y Optimización de Bases de Datos 2021

Figura 9.1

9.4.

PROGRAM GLOBAL AREA (PGA)

La cantidad de memoria que se define para las tareas de ordenación, para cada Proceso de Servidor de cada Proceso Usuario, se determina por los siguientes parámetros de ordenación:  SORT_AREA_SIZE  SORT_AREA_RETAINED_SIZE  PGA_AGGREGRATE_TARGET  WORKAREA_SIZE_POLICY Cada uno de estos parámetros tiene sus propios efectos al momento de realizar ordenaciones: SORT_AREA_SIZE: este parámetro determina qué cantidad de memoria dispondrá cada Proceso de Servidor para realizar tareas de ordenaciones. El valor por defecto de este parámetro dependerá de la configuración del sistema operativo, el valor mínimo se determinará por el equivalente al tamaño de seis bloques de Oracle, y el valor máximo, como se ha comentado, dependerá de la capacidad del hardware del servidor. Cabe destacar que cuando se está utilizando una arquitectura de Servidor Compartido, la memoria utilizada para las ordenaciones surge de la UGA, no de la memoria principal del servidor. SORT_AREA_RETAINED_SIZE: una vez que la operación de ordenación es completada, si el área de memoria de ordenación aún contiene registros ordenados para devolver al usuario, el Proceso de Servidor del usuario reduce el tamaño de memoria para ordenaciones, al valor definido en este parámetro. De esta manera se puede ir agrandando ó reduciendo la cantidad de memoria para las ordenaciones en forma automática y sin intervención del DBA. Por defecto, su tamaño es equivalente al valor del parámetro SORT_AREA_SIZE. El valor mínimo es el equivalente a dos veces el tamaño del bloque de Oracle y el tamaño máximo está limitado por el valor del parámetro SORT_AREA_SIZE. PGA_AGGREGATE_TARGET: Luego de utilizar el parámetro SORT_AREA_SIZE para determinar qué cantidad de memoria se utilizará para los procesos de ordenación de cada usuario, el parámetro PGA_AGGREGATE_TARGET puede ser utilizado para establecer un límite máximo de consumo de memoria para las actividades sobre la base de datos de los Procesos de Usuario, incluyendo operaciones de ordenación. El valor por defecto es cero y los posibles valores que puede tomar van de 10MB a 4GB. WORKAREA_SIZE_POLICY: Este parámetro es utilizado para determinar cuándo la cantidad total de memoria designada para todos los Procesos de Usuario está gestionada explícita ó implícitamente. Cuando este parámetro se define con el valor MANUAL, el tamaño de ordenación de cada usuario será equivalente al valor de SORT_AREA_SIZE. Si de lo contrario se lo define en AUTO, Oracle gestionará automáticamente el tamaño de asignación de memoria para que no exceda el tamaño definido en PGA_AGGREGATE_TARGET. De esta manera, si se decide utilizar el parámetro PGA_AGGREGATE_TARGET, se debe definir este parámetro en AUTO. Cabe destacar que tanto este parámetro como PGA_AGGREGATE_TARGET son nuevos a partir de Oracle 9i.

Profesor: Mg. Ing. Héctor Pedro Liberatori

Pág. 97

Instituto Superior de Formación Docente y Técnica Nº 5 José Eugenio Tello

IV – AJUSTE DEL RENDIMIENTO (Tuning)

TECNICATURA SUPERIOR EN INFORMÁTICA Taller de Ajuste y Optimización de Bases de Datos 2021

Figura 9.2

9.5. VISTAS DEL DICCIONARIO DE DATOS Las actividades de ordenación pueden ser monitoreadas utilizando las vistas dinámicas de rendimiento V$SYSSTAT y V$SORT_SEGMENT. La vista V$SYSSTAT tiene dos estadísticas: “sorts (memory)” y “sorts (disk)” que pueden ser utilizadas parar monitorear la actividad de ordenación. Utilizando estas dos estadísticas se puede ver cómo se calcula el indicador de ordenaciones en memoria, en el ensayo de la Figura 9.3. De esta manera, según el ejemplo de la figura, un 99,23% de las ordenaciones que ocurrieron desde que se inició la instancia, se realizaron en memoria y no en disco. La recomendación de Oracle es que al menos el 95% de las actividades de ordenación ocurran en memoria.

Figura 9.3

Profesor: Mg. Ing. Héctor Pedro Liberatori

Pág. 98

Instituto Superior de Formación Docente y Técnica Nº 5 José Eugenio Tello

IV – AJUSTE DEL RENDIMIENTO (Tuning)

TECNICATURA SUPERIOR EN INFORMÁTICA Taller de Ajuste y Optimización de Bases de Datos 2021

9.6. EL PROCESO DE ORDENACION Los siguientes tipos de consultas SQL son los que pueden causar una ordenación:  ORDER BY  GROUP BY  SELECT DISTINCT  UNION  INTERSECT  MINUS  ANALYZE  CREATE INDEX  Uniones de tablas por columnas que no están indexadas. Oracle permite a cada Proceso de Usuario utilizar una cantidad de memoria definida para cada operación de ordenación. Si una operación de ordenación es menor a este tamaño, el proceso de ordenación se realizará en memoria Figura 9.4), de lo contrario, la ordenación se dividirá en pedazos llamados corridas de ordenación ó pasadas (Figura 9.5). Cada una de estas pasadas se ordenará en forma individual, utilizando el tablespace temporal de cada usuario para realizar esta operación (que no puede ser realizada en memoria). Una vez que todos los pedazos de la ordenación han sido ordenados, el resultado final es devuelto al usuario.

Figura 9.4

Figura 9.5

Profesor: Mg. Ing. Héctor Pedro Liberatori

Pág. 99

Instituto Superior de Formación Docente y Técnica Nº 5 José Eugenio Tello

IV – AJUSTE DEL RENDIMIENTO (Tuning)

TECNICATURA SUPERIOR EN INFORMÁTICA Taller de Ajuste y Optimización de Bases de Datos 2021

9.7. MEJORAS EN LAS OPERACIONES DE ORDENACION Las actividades de ordenación pueden causar excesivas operaciones de Entrada/Salida en disco, a pesar de la memoria, con lo que el DBA debería tratar de ajustar esta actividad para minimizar este impacto negativo. Esta tarea se puede realizar aplicando distintos métodos, como ser:  Evitando sentencias SQL que causen ordenaciones.  Ajustando los parámetros de inicio relacionados con las ordenaciones.  Realizando un uso correcto del tablespace temporal.  Mejorando las lecturas relacionadas con las Entradas/Salidas de las ordenaciones. Evitando Ordenaciones Una forma de mejorar el rendimiento de las ordenaciones es minimizando la cantidad de ordenaciones que realiza el código de la aplicación, como ser sentencias recursivas ó actividades DML. Para esto se puede hacer:  Mejora de las sentencias SQL: si el resultado esperado no es afectado negativamente, utilizar el operador UNION ALL en lugar del operador UNION; de esta manera se evitará la ordenación para eliminar los registros duplicados. De la misma forma, al evitar los operadores INTERSECT, MINUS y DISTINCT se obtendrá el mismo resultado.  Uso apropiado de los índices: indexar las columnas que formarán parte de cláusulas ORDER BY y GROUP BY en las aplicaciones SQL puede minimizar ordenaciones innecesarias. Asegurándose que todas las claves foráneas se encuentran indexadas ayudará a reducir las ordenaciones cuando se realizan uniones de tablas ya que el optimizador utilizará mejor el plan de ejecución.  Sobrecarga del uso de índices: si una tabla a ser indexada ya cuenta con un índice con cierto orden, es probable que elimine la ordenación que normalmente se utiliza durante la creación del nuevo índice. Para evitar esto, debe utilizar la opción NOSORT del comando CREATE INDEX.  Demasiados cálculos de estadísticas: las ordenaciones también ocurren cuando se toman estadísticas de una tabla ó índice. Hay que considerar la opción ESTIMATE en lugar de COMPUTE cuando se toman estadísticas para minimizar el sobre- procesamiento de esta actividad.

Figura 9.6

9.8. ESPACIO TEMPORAL Cuando un Proceso de Servidor de un usuario escribe una parte de una ordenación grande a disco, lo hace directamente al tablespace temporal de ese usuario. Este tablespace además de estar definido como el tablespace temporal del usuario, puede crearse con el atributo de ser permanente ó temporal. La tabla de la Figura 9.7 muestra una comparación entre los tablespaces temporales y permanentes. Es posible consultar la vista del diccionario de datos DBA_TABLESPACES para determinar si un tablespace está definido como permanente ó temporal, como se observa en la Figura 9.8.

Profesor: Mg. Ing. Héctor Pedro Liberatori

Pág. 100

Instituto Superior de Formación Docente y Técnica Nº 5 José Eugenio Tello

IV – AJUSTE DEL RENDIMIENTO (Tuning)

TECNICATURA SUPERIOR EN INFORMÁTICA Taller de Ajuste y Optimización de Bases de Datos 2021

Junto al concepto de Tablespace Temporal está asociado el concepto de Archivo Temporal ó TempFile. Los Archivos Temporales son similares a los Archivos de Datos ó DataFiles excepto que son utilizados exclusivamente para almacenar segmentos de ordenación escritos en disco. La Figura 9.9 muestra un ensayo de una consulta SQL para crear un tablespace temporal, gestionado localmente, utilizando un archivo temporal. Como se observa en la sentencia, el valor especificado en la opción UNIFORM SIZE debería ser equivalente al valor de SORT_AREA_SIZE + 1 bloque (ó un múltiplo de éste). De esta manera se asegura que los pedazos completos de la división de una ordenación se escriban completos al tablespace temporal. Cabe recordar que definir los Archivos Temporales en múltiples dispositivos puede proporcionar mejoras de performance excelentes. Una vez creado el tablespace temporal, se pueden monitorear los archivos temporales utilizando la vista dinámica de rendimiento V$TEMPFILE y la vista del diccionario de datos DBA_TEMP_FILES. El contenido de estas vistas es similar. Oracle 9i también permite al DBA especificar un tablespace temporal como el tablespace temporal por defecto para todos los usuarios de la base de datos, utilizando el comando ALTER DATABASE: SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp: De esta manera, se define que el tablespace temporal por defecto del sistema es el tablespace TEMP en lugar del tablespace SYSTEM. Designar un tablespace que no sea el tablespace SYSTEM como el tablespace temporal por defecto tiene ciertas implicaciones para la administración de la base de datos. El tablespace temporal por defecto debe ser siempre un tablespace del tipo temporal, no puede ser un tablespace permanente y no es posible convertir un tablespace temporal a permanente una vez que fue designado como tablespace temporal por defecto. Adicionalmente, no puede eliminarse el tablespace temporal por defecto hasta que otro tablespace temporal haya tomado su lugar. Finalmente, un tablespace temporal por defecto no puede ponerse en estado OFFLINE. Como resultado de este último aspecto, surge la idea de que este tipo de tablespaces no pueden formar parte de un backup en caliente. La realidad indica que tampoco es necesario debido a que este tipo de tablespaces no pueden contener segmentos permanentes.

Figura 9.7

Figura 9.8

Profesor: Mg. Ing. Héctor Pedro Liberatori

Pág. 101

Instituto Superior de Formación Docente y Técnica Nº 5 José Eugenio Tello

IV – AJUSTE DEL RENDIMIENTO (Tuning)

TECNICATURA SUPERIOR EN INFORMÁTICA Taller de Ajuste y Optimización de Bases de Datos 2021

Figura 9.9

9.9. OPERACIONES DE ORDENACION Otra técnica para mejorar el acceso a disco relacionado a las operaciones de ordenación, es minimizando la cantidad de ordenaciones hechas en disco, incrementando el valor del parámetro de inicio SORT_AREA_SIZE. Hay que tener especial cuidado al realizar esta operación. Debido a que la cantidad de memoria definida en este parámetro no se asigna a la sesión del usuario hasta que no inicie un proceso de ordenación, la memoria disponible del servidor debe ser la suficiente para poder gestionar este espacio de memoria sobrante. En particular, si el valor de SORT_AREA_SIZE se cambia a un valor mayor y una gran cantidad de usuarios proceden a ordenar simultáneamente las demandas ubicadas en la memoria del servidor, éstas podrían impactar negativamente al rendimiento, hasta que la memoria asignada a esas ordenaciones se liberara a medida que van concluyendo. Por otro lado, disminuir el tamaño de SORT_AREA_RETAINED_SIZE cuando se incrementa SORT_AREA_SIZE, ayudará a minimizar el problema del uso excesivo de memoria para ordenaciones. Cabe destacar que se debe tener un particular cuidado al decidir incrementar SORT_AREA_SIZE si se va a utilizar la característica de consulta paralelizada (Parallel Query). En este tipo de sentencias, una simple consulta puede llegar a requerir un especio de memoria equivalente a SORT_AREA_SIZE x 2 x cantidad de paralelismo definido. La recomendación de Oracle dice que el valor por defecto que suele asignarse a SORT_AREA_SIZE suele ser adecuado para la mayoría de los ambientes OLTP, como así también recomienda que un valor de 1MB es generalmente óptimo para sistemas que utilizan la opción de consultas paralelizables.

Figura 9.10

Profesor: Mg. Ing. Héctor Pedro Liberatori

Pág. 102

Instituto Superior de Formación Docente y Técnica Nº 5 José Eugenio Tello

9.10.

IV – AJUSTE DEL RENDIMIENTO (Tuning)

TECNICATURA SUPERIOR EN INFORMÁTICA Taller de Ajuste y Optimización de Bases de Datos 2021

ADMINISTRADOR DE PERFOMANCE

El componente Performance Manager del paquete de diagnósticos de Oracle Enterprise Manager (OEM) incluye varias representaciones gráficas sobre el rendimiento de las ordenaciones. La Figura 9.11 muestra un ejemplo de la pantalla del Performance Manager que muestra la actividad de ordenación de la memoria. En esta pantalla se puede observar que la mayoría de la actividad de ordenación ocurre en memoria. De todas maneras, se ve que en dos ocasiones el rendimiento ha disminuido al 90% aproximadamente.

Figura 9.11

9.12.

MONITOREO DE TABLESPACES TEMPORALES

Si la base de datos no está utilizando tablespaces temporales para las operaciones de ordenación, cada usuario que realice esta tarea deberá construir y luego eliminar un segmento de ordenación en su tablespace. Esta gestión individual se traduce en un alto costo de acceso a disco. A diferencia de un segmento de ordenación almacenado en un tablespace permanente, los segmentos de ordenación almacenados en los tablespaces temporales no se eliminan cuando concluye la ordenación. De esta forma, estos segmentos quedan disponibles para posibles ordenaciones futuras y permanecerán en el tablespace hasta que se detenga la instancia. Es posible monitorear el tamaño y el uso de estos segmentos de ordenación utilizando las vistas dinámicas de rendimiento V$SORT_SEGMENT y V$SORT_USAGE. V$SORT_SEGMENT se utiliza para monitorear el tamaño y el crecimiento de los segmentos de ordenación que residen en el tablespace temporal. Estos segmentos crecerán dinámicamente en función de la demanda que tengan. La Figura 9.12 muestra un ensayo de esta vista donde se puede observar que hay 11 usuarios concurrentes utilizando el segmento de ordenación almacenado en el tablespace temporal TEMP. Adicionalmente, la mayor ordenación realizada por un usuario consumió 300 bloques de Oracle. Esta última estadística en particular (MAX_SORT_BLOCKS) puede resultar útil cuando se está tratando de determinar el tamaño óptimo del parámetro SORT_AREA_SIZE y SORT_AREA_RETAINED_SIZE. El resultado sería multiplicando MAX_SORT_BLOCKS * DB_BLOCK_SIZE. Mientras que V$SORT_SEGMENT muestra estadísticas asociadas a las operaciones de ordenación en general, V$SORT_USAGE incluye una columna llamada USER la cual puede ser utilizada para monitorear la actividad de ordenación por usuario, como se muestra en el ensayo de esta vista en la Figura 9.13 donde se ve que la columna TABLESPACE indica el tablespace temporal donde se están almacenando los segmentos de ordenación. La columna BLOCKS indica el tamaño en bloques de esta actividad. Cabe destacar que la vista V$SORT_USAGE sólo contiene entradas cuando al menos una ordenación se encuentra ejecutándose. Si se consulta esta vista cuando no hay usuarios conectados ó consultando, la consulta no retornará registros.

Profesor: Mg. Ing. Héctor Pedro Liberatori

Pág. 103

Instituto Superior de Formación Docente y Técnica Nº 5 José Eugenio Tello

IV – AJUSTE DEL RENDIMIENTO (Tuning)

TECNICATURA SUPERIOR EN INFORMÁTICA Taller de Ajuste y Optimización de Bases de...