Masterclass: Efectos del uso de funciones en el CBO Oracle

Name: Masterclass: Efectos del uso de funciones en el CBO Oracle
Rating: 4.4 (74 reviews)

Dos masterclass - laboratorio para estudiar el efecto de las funciones en la optimización de SQL de Oracle

Created byJavier Morales Carreras

Last updated 5/2019

Spanish

What you'll learn

Ejemplos prácticos sobre el efecto del uso de funciones en la evaluación de literales y su efecto en la optimización SQL.
Nociones básicas de partition pruning con Oracle
Un caso práctico de comparativa de rendimiento al usar funciones para evaluar filtros
Comprensión sobre el uso de índices basados en funciones y cómo le afectan las funciones internas
Funcionamiento e impacto en la evaluación de cardinalidad de las funciones internas

Course content

2 sections • 5 lectures • 52m total length

TO_DATE or not TO_DATE - ¿Es la función la causante de una mala optimización?13:23
La documentación de Oracle ofrece poca información sobre "best practices" relativas al partition pruning.
Es importante indicar en los filtros información que ayude al optimizador a determinar a qué particiones acceder. Por ejemplo, no es posible seleccionar particiones de tabla si se accede a través de un índice global, aunque todas las filas seleccionadas pertenezcan a una misma partición.
Además, existen dos tipos de pruning:
el estático, que se conoce directamente en el parse. Sucede, por ejemplo, cuando se filtra por una fecha concreta.
el dinámico, que es el que se resuelve en tiempo de ejecución. Un ejemplo podría ser cuando se utilizan operadores o funciones.
En base a esta teoría, un filtro sobre una columna clave de particionamiento (en este caso, FECHA), utilizando una función TO_DATE, debería ser suficiente para realizar partition pruning estático, pero no es así.
En un primer entorno Oracle11g, dos sentencias SELECT idénticas salvo el formato de fecha en el filtro de WHERE determinaba cardinalidades diferentes en planes de ejecución idénticos y, en tiempo de ejecución, se resolvían "row sources" diferentes.
Estudio del laboratorio y las trazas de ejecución.12:34
Para estudiar este comportamiento, preparamos un laboratorio en Oracle12c para, además, determinar si se trataba de algo específico de la versión Oracle11g, o podía reproducirse en otras versiones.
Para preparar este laboratorio he usado la máquina virtual de Oracle Developers Day en versión 12c, que puedes descargarte en el enlace siguiente, y bajo un usuario nuevo llamado LABORATORIO creé una tabla particionada por fecha, cargué 31,5 millones de filas y recopilé estadísticas para tener un entorno lo más parecido al caso original.
Crea tu entorno de laboratorio

Funciones internas TO_DATE en conversiones implícitas15:59
No hay mucha información sobre las conversiones implícitas que el optimizador de Oracle realiza para evaluar filtros.
Aunque se recomienda usar los mismos formatos y restringir el uso de funciones sobre los valores de los campos, no hay una base clara de buenas prácticas sobre la implementación de filtros en las cláusulas WHERE.
Por un lado, usar una función en el campo que se evalúa invalida el uso de los índices sobre esa columna, pues estas funciones están alterando los valores y ya no pueden compararse con los usados en la ordenación del índice.
No obstante, incluso cuando los campos se dejan inalterados, es posible que Oracle realice conversiones internas que alteren los resultados de la fase de compilación y optimización del SQL.
¿Qué ocurre, por ejemplo, si se compara una fecha con otro literal de tipo timestamp (fecha con precisión de fracción de segundos y soporte de hora local) aún cuando el valor no tiene precisión de fracciones de segundo?
Funciones internas en conversiones con TO_TIMESTAMP4:21
En el siguiente test hacemos la prueba con una tabla más grande, cargando fechas a partir de DBA_OBJECTS para tener un registro cada 10 segundos.
Así hacemos la comparativa de la conversión explícita TO_CHAR y TO_TIMESTAMP para evaluar cardinalidades.
Funciones internas en evaluaciones de índices basados en función.6:22
El caso aplicado a índices basados en funciones es similar, con la complejidad que aporta el necesitar, en muchos casos, conocer la expresión exacta con la que se ha creado el índice basado en función.
Si la función expresada en la query no coincide exactamente con la expresión de función del índice, el optimizador no considerará su uso.
Crea tu entorno de laboratorio

Requirements

Nociones básicas de optimización SQL en Oracle
Conocimiento del funcionamiento del CBO de Oracle
Nociones de planes de ejecución y trazas de servidor Oracle
Conocimientos básicos de administración Oracle

Description

¿Conoces los efectos de utilizar máscaras de fecha en dos o cuatro dígitos de año? Según la teoría, debería ser transparente, sobre todo cuando las funciones se usan en la evaluación de literales, pero en la práctica no es así. Estos dos estudios analizan el comportamiento del optimizador de Oracle y desvelan trucos y posibles problemas de performance que tú puedes tener en tu entorno productivo.

Who this course is for:

Administradores de bases de datos Oracle
Desarrolladores Oracle con un nivel avanzado de SQL y nociones de optimización

Masterclass: Efectos del uso de funciones en el CBO Oracle

What you'll learn

Explore related topics

Course content

Masterclass 1 - Impacto de diferentes formatos de fecha para la optimización SQL2 lectures • 26min

Masterclass 2 - Funciones internas y su efecto en el optimizador3 lectures • 27min

Requirements

Description

Who this course is for: