Parámetros de configuración que afectan a la optimización de consultas

Varios parámetros de configuración afectan al plan de acceso elegido por el compilador de SQL o XQuery. Muchas de ellos son apropiados para un entorno de base de datos de una sola partición y algunos solo son adecuados para un entorno de base de datos particionada. Suponiendo un entorno de base de datos particionada homogéneo, donde el hardware es el mismo, los valores utilizados para cada parámetro deberían ser los mismos en todas las particiones de la base de datos.

Nota: Cuando cambia un parámetro de configuración dinámicamente, es posible que el optimizador no lea los valores de parámetro cambiados inmediatamente debido a planes de acceso más antiguos en la memoria caché de paquetes. Para restablecer la memoria caché de paquete, ejecute el mandato FLUSH PACKAGE CACHE.

En un sistema federado, si la mayoría de las consultas acceden a apodos, evalúe los tipos de consultas que envía antes de cambiar el entorno. Por ejemplo, en una base de datos federada, la agrupación de almacenamiento intermedio no almacena en memoria caché las páginas de orígenes de datos, que son los DBMS y los datos dentro del sistema federado. Por esta razón, aumentar el tamaño del buffer no garantiza que el optimizador considere alternativas adicionales de plan de acceso cuando elige un plan de acceso para consultas que contengan nombres de usuario. Sin embargo, el optimizador puede decidir que la materialización local de las tablas de orígenes de datos es la ruta menos costosa o un paso necesario para una operación de ordenación. En ese caso, el aumento de los recursos disponibles podría mejorar el rendimiento.

Los siguientes parámetros de configuración o factores afectan al plan de acceso elegido por el compilador SQL o XQuery:

El tamaño de las agrupaciones de almacenamiento intermedio que ha especificado al crearlas o modificarlas.
Cuando el optimizador elige el plan de acceso, tiene en cuenta el coste de E/S de captar páginas del disco a la agrupación de almacenamiento intermedio y calcula el número de E/S necesarias para satisfacer una consulta. La estimación incluye una predicción del uso de la agrupación de almacenamiento intermedio, porque no se necesitan E/S físicas adicionales para leer filas de una página que ya está en la agrupación de almacenamiento intermedio.

El optimizador considera el valor de la columna npages en las tablas de catálogos del sistema SYSCAT.BUFFERPOOLS y, en entornos de bases de datos particionadas, las tablas de catálogos del sistema SYSCAT.BUFFERPOOLDBPARTITIONS.

Los costes de E/S de la lectura de las tablas pueden tener un impacto en la forma en que se unen las dos tablas y si se utiliza un índice no agrupado para leer los datos.
Grado predeterminado (dft_degree)
El parámetro de configuración dft_degree especifica el paralelismo proporcionando un valor predeterminado para el registro especial CURRENT DEGREE y la opción de vinculación DEGREE. Un valor de uno (1) significa que no hay paralelismo intrapartición. Un valor de menos uno (-1) significa que el optimizador determina el grado de paralelismo intrapartición basándose en el número de procesadores y el tipo de consulta.

Nota: El proceso en paralelo no se produce a menos que lo habilite estableciendo el parámetro de configuración del gestor de bases de datos intra_parallel .
Clase de optimización de consulta predeterminada (dft_queryopt)
Aunque puede especificar una clase de optimización de consulta cuando compila consultas SQL o XQuery, también puede establecer una clase de optimización de consulta predeterminada.
Número medio de aplicaciones activas (avg_appls)
El optimizador utiliza el parámetro avg_appls para ayudar a estimar qué cantidad de la agrupación de almacenamiento intermedio podría estar disponible en tiempo de ejecución para el plan de acceso elegido. Los valores superiores para este parámetro pueden influir en el optimizador para elegir planes de acceso que son más conservadores en el uso de la agrupación de almacenamiento intermedio. Si especifica un valor de uno (1), el optimizador considera que la agrupación de almacenamiento intermedio está disponible para la aplicación.
Tamaño de almacenamiento dinámico de ordenación (sortheap)
Si las filas a ordenar ocupan más espacio que el disponible en el almacenamiento dinámico de ordenación, se realizan varios pases de clasificación, en las que cada pase clasifica un subconjunto del conjunto completo de filas. Cada pase de clasificación se almacena en una tabla temporal del sistema en la agrupación de almacenamiento intermedio, que se puede grabar en el disco. Cuando todos los pases de clasificación están completas, estos subconjuntos ordenados se fusionan en un único conjunto de filas ordenadas. Una clasificación que no requiere una tabla temporal del sistema para almacenar la lista de datos siempre da como resultado un mejor rendimiento y, si es posible, se utiliza.

Al elegir un plan de acceso, el optimizador calcula el coste de las operaciones de ordenación, incluida la evaluación de si una ordenación se puede leer en un único acceso secuencial, estimando la cantidad de datos que se deben clasificar y analizando el parámetro sortheap para determinar si hay suficiente espacio para leer una clasificación en un único acceso secuencial.
Almacenamiento máximo para lista de bloqueos (locklist) y Porcentaje máximo de lista de bloqueos antes del escalamiento (maxlocks)
Cuando el nivel de aislamiento es de lectura repetible (RR), el optimizador tiene en cuenta los valores de los parámetros locklist y maxlocks para determinar si los bloqueos de nivel de fila pueden escalarse a un bloqueo de nivel de tabla. Si el optimizador estima que es posible que se produzca un escalamiento de bloqueo para un acceso de tabla, elige un bloqueo de nivel de tabla para el plan de acceso, en lugar de incurrir en la sobrecarga del escalamiento de bloqueo durante la ejecución de la consulta.
Velocidad de CPU (cpuspeed)
El optimizador utiliza la velocidad de CPU para estimar el coste de realizar determinadas operaciones. Las estimaciones de coste de CPU y las diversas estimaciones de coste de E/S ayudan a seleccionar el mejor plan de acceso para una consulta.

La velocidad de CPU de una máquina puede tener una influencia significativa en el plan de acceso elegido. Este parámetro de configuración se establece automáticamente en un valor adecuado cuando la base de datos está instalada o actualizada. No ajuste este parámetro a menos que esté modelando un entorno de producción en un sistema de prueba o que evalúe el impacto de un cambio de hardware. El uso de este parámetro para modelar un entorno de hardware diferente le permite conocer los planes de acceso que se pueden elegir para ese entorno. Para que el gestor de bases de datos vuelva a calcular el valor de este parámetro de configuración automática, establezca el valor de menos uno (-1).
Tamaño de almacenamiento dinámico de sentencia (stmtheap)
Aunque el tamaño del almacenamiento dinámico de sentencia no influye en el optimizador en la elección de vías de acceso diferentes, puede afectar a la cantidad de optimización realizada para sentencias de SQL o XQuery complejas.

Si el parámetro stmtheap no se establece en un valor suficiente, es posible que reciba un aviso que indique que no hay suficiente memoria disponible para procesar la sentencia. Por ejemplo, SQLCODE +437 (SQLSTATE 01602) puede indicar que la cantidad de optimización que se ha utilizado para compilar una sentencia es menor que la cantidad que ha solicitado.
Ancho de banda de comunicaciones (comm_bandwidth)
El optimizador utiliza el ancho de banda de comunicaciones para determinar las vías de acceso. El optimizador utiliza el valor de este parámetro para estimar el coste de realizar determinadas operaciones entre los servidores de particiones de base de datos en un entorno de base de datos particionada.
Tamaño de almacenamiento dinámico de aplicación (applheapsz)
Los esquemas grandes requieren espacio suficiente en el almacenamiento dinámico de la aplicación.