Hola de nuevo, hoy queremos compartir con vosotros una nueva entrada de Oracle. En este caso hemos probado la capacidad de ChatGPT para el análisis de rendimiento y optimización de consultas Oracle.
La versión actual es GPT-4 y ya se empiezan a ver mejoras notables con respecto a la versión 3, ¿pero sigue siendo suficiente para analizar eficientemente queries?
Para la prueba hemos creado dos tablas muy sencillas a partir del diccionario de datos de Oracle 19c, las dos tablas las conocéis de sobra, son dba_objects y dba_source. Hemos cargado los datos de los paquetes y luego vamos a hacer un join entre las dos para buscar los datos de un objeto en concreto:
SQL> create table tabla1 as select OWNER, OBJECT_NAME from dba_objects where OBJECT_TYPE='PACKAGE BODY';
Table created.
SQL> select count(1) from tabla1;
COUNT(1)
----------
1033
SQL> create table tabla2 as select OWNER, NAME OBJECT_NAME, LINE, TEXT from dba_source where TYPE='PACKAGE BODY';
Table created.
SQL> select count(1) from tabla2;
COUNT(1)
----------
45773
Hemos creado un índice en tabla1 que está basado en dba_objects de la siguiente forma:
SQL> ALTER TABLE tabla1 ADD CONSTRAINT tabla1_pk PRIMARY KEY (OWNER, OBJECT_NAME);
Table altered.
La query que vamos a probar es:
select t2.LINE, t2.TEXT
from tabla1 t1, tabla2 t2
where t1.OWNER = t2.OWNER and t1.OBJECT_NAME = t2.OBJECT_NAME and t2.OBJECT_NAME = 'DBMS_JAVA'
order by t2.LINE asc;
Poniendo a prueba a ChatGPT
Como no tenemos índices sobre tabla2 se realiza un full scan de la misma, por lo que vamos a preguntar a ChatGPT a ver que nos dice. La pregunta que le hemos hecho es:
Hola, tenemos un problema con una query en Oracle que está tardando, la query es:
select t2.LINE, t2.TEXT
from tabla1 t1, tabla2 t2
where t1.OWNER = t2.OWNER and t1.OBJECT_NAME = t2.OBJECT_NAME and t2.OBJECT_NAME = 'DBMS_JAVA'
order by t2.LINE asc;
y su plan de ejecución es:
Plan hash value: 123530362
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 708 | 1575K| | 1078 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 708 | 1575K| 1896K| 1078 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 708 | 1575K| | 738 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TABLA2 | 4958 | 10M| | 738 (1)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| TABLA1_PK | 1 | 132 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."OBJECT_NAME"='DBMS_JAVA')
4 - access("T1"."OWNER"="T2"."OWNER" AND "T1"."OBJECT_NAME"='DBMS_JAVA')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
21 rows selected.
Nos puedes dar alguna recomendación de mejora?
Lo que nos ha dicho es:
Para mejorar el rendimiento de la consulta en Oracle, se pueden considerar varias estrategias. Aquí te dejo algunas recomendaciones que podrían ayudar:
1. Uso de JOIN explícito
En lugar de usar la sintaxis de CROSS JOIN implícita, es mejor usar la sintaxis de INNER JOIN, ya que es más clara y permite que el optimizador de Oracle trabaje de manera más efectiva.
SELECT t2.LINE, t2.TEXT
FROM tabla2 t2
INNER JOIN tabla1 t1
ON t1.OWNER = t2.OWNER
AND t1.OBJECT_NAME = t2.OBJECT_NAME
WHERE t2.OBJECT_NAME = 'DBMS_JAVA'
ORDER BY t2.LINE ASC;
2. Índices apropiados
Asegúrate de que las columnas utilizadas en las condiciones de unión y en la cláusula WHERE tengan índices adecuados. Para esta consulta, puedes verificar y crear índices como:
CREATE INDEX idx_tabla2_owner_object_name ON tabla2 (OWNER, OBJECT_NAME);
CREATE INDEX idx_tabla1_owner_object_name ON tabla1 (OWNER, OBJECT_NAME);
3. Estadísticas actualizadas
Asegúrate de que las estadísticas de las tablas estén actualizadas. Puedes hacer esto con el siguiente comando:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLA1');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLA2');
4. Filtrado temprano
Asegúrate de filtrar los datos lo antes posible en la consulta. En este caso, ya estás filtrando en la cláusula WHERE, lo cual es correcto.
5. Revisión de índices
Revisa el uso de índices en el plan de ejecución. El acceso completo a la tabla (TABLE ACCESS FULL) en TABLA2 podría ser un punto de mejora. El índice TABLA1_PK es escaneado de manera única, lo cual está bien.
6. Analizar particionamiento
Si las tablas son muy grandes, podrías considerar el particionamiento de las mismas basándote en la columna OWNER o OBJECT_NAME.
7. Revisar el PLAN de ejecución actualizado
Después de aplicar las recomendaciones, revisa el nuevo plan de ejecución para verificar las mejoras.
8. Uso de Hints (sólo si es necesario)
Si después de todas estas recomendaciones, aún no ves una mejora significativa, podrías considerar el uso de "hints" en Oracle para guiar al optimizador. Por ejemplo:
SELECT /*+ USE_NL(t1 t2) */ t2.LINE, t2.TEXT
FROM tabla2 t2
INNER JOIN tabla1 t1
ON t1.OWNER = t2.OWNER
AND t1.OBJECT_NAME = t2.OBJECT_NAME
WHERE t2.OBJECT_NAME = 'DBMS_JAVA'
ORDER BY t2.LINE ASC;
Recuerda que el uso de hints debería ser la última opción, ya que estás forzando al optimizador a seguir un camino específico y esto podría no siempre ser lo mejor en todos los casos.
Aplicando estas recomendaciones deberías ver una mejora en el rendimiento de tu consulta.
Nos centramos en la recomendación de índices. Tabla1 ya tiene creada una PK y está haciendo un unique scan, por lo que no debería recomendarla, vamos a probar con el segundo índice:
CREATE INDEX idx_tabla2_owner_object_name ON tabla2 (OWNER, OBJECT_NAME);
El plan sigue siendo mejorable, ahora es:
Plan hash value: 538379878
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 708 | 1575K| | 348 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 708 | 1575K| 1896K| 348 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 708 | 1575K| | 8 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 708 | 1575K| | 8 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN | TABLA1_PK | 1 | 132 | | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_TABLA2_OWNER_OBJECT_NAME | 53 | | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| TABLA2 | 708 | 1484K| | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OBJECT_NAME"='DBMS_JAVA')
5 - access("T1"."OWNER"="T2"."OWNER" AND "T2"."OBJECT_NAME"='DBMS_JAVA')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
24 rows selected.
El coste ha bajado de 1078 a 348 pero parece mejorable. Vamos a preguntar de nuevo:
Tabla1 ya tiene creado el siguiente índice:
ALTER TABLE tabla1 ADD CONSTRAINT tabla1_pk PRIMARY KEY (OWNER, OBJECT_NAME);
Hemos creado el índice recomendado en tabla2: CREATE INDEX idx_tabla2_owner_object_name ON tabla2 (OWNER, OBJECT_NAME);
El plan sigue siendo mejorable, ahora es:
Plan hash value: 538379878
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 708 | 1575K| | 348 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 708 | 1575K| 1896K| 348 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 708 | 1575K| | 8 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 708 | 1575K| | 8 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN | TABLA1_PK | 1 | 132 | | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_TABLA2_OWNER_OBJECT_NAME | 53 | | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| TABLA2 | 708 | 1484K| | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Alguna recomendación nueva de índices?
Lo que nos comenta es:
El nuevo plan de ejecución muestra mejoras notables, especialmente en la reducción del costo total y el uso efectivo de los índices. A continuación, te ofrezco algunas recomendaciones adicionales para seguir optimizando:
Revisión y Optimización de Índices Existentes
Índice en TABLA2 para LINE:
Si la columna LINE se utiliza frecuentemente en ordenaciones, podrías considerar crear un índice que incluya esta columna. Sin embargo, ten en cuenta que crear demasiados índices puede ralentizar las operaciones INSERT y UPDATE.
CREATE INDEX idx_tabla2_owner_object_name_line ON tabla2 (OWNER, OBJECT_NAME, LINE);
Considerar Índices Compuestos:
Los índices compuestos que incluyan todas las columnas involucradas en la condición de unión pueden ayudar. Ya creaste un índice en TABLA2 con OWNER y OBJECT_NAME. Si la columna LINE se usa mucho en las consultas, podrías incluirla en un índice compuesto.
Verificar el Índice Único:
Asegúrate de que el índice único en TABLA1 esté optimizado para la consulta. Dado que TABLA1_PK ya es un índice único, revisa que su utilización sea efectiva.
Optimización del Plan de Ejecución
Hints para Optimización:
Si ves que los índices están bien pero el plan de ejecución aún no es óptimo, puedes usar "hints" para guiar al optimizador. Ya usaste USE_NL (Nested Loops) en la consulta anterior. Aquí
SELECT /*+ INDEX(t2 idx_tabla2_owner_object_name_line) */ t2.LINE, t2.TEXT
FROM tabla2 t2
INNER JOIN tabla1 t1
ON t1.OWNER = t2.OWNER
AND t1.OBJECT_NAME = t2.OBJECT_NAME
WHERE t2.OBJECT_NAME = 'DBMS_JAVA'
ORDER BY t2.LINE ASC;
Verificar Estadísticas de Índices:
Asegúrate de que las estadísticas de los índices estén actualizadas para que el optimizador pueda tomar decisiones informadas.
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCHEMA_NAME', 'IDX_TABLA2_OWNER_OBJECT_NAME');
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCHEMA_NAME', 'TABLA1_PK');
Verificación del Plan de Ejecución Actualizado
Después de hacer estos ajustes, revisa nuevamente el plan de ejecución para asegurarte de que las operaciones de escaneo de índices y los costos se han optimizado.
Evaluación del Desempeño
Finalmente, realiza pruebas de rendimiento para asegurarte de que los cambios han tenido el efecto deseado en términos de tiempos de respuesta y carga del sistema. Si el rendimiento sigue sin ser adecuado, podrías considerar analizar las tablas involucradas para detectar posibles problemas de fragmentación o evaluar la posibilidad de particionarlas si son muy grandes.
¿Es mejor que nosotros optimizando consultas?
La nueva recomendación no tiene mucho sentido, vamos a ver si todavía podemos ganar nosotros a la IA:
Como estamos preguntando por OBJECT_NAME y también necesitamos el campo OWNER para el join vamos a probar a cambiar el orden del índice:
drop index IDX_TABLA2_OWNER_OBJECT_NAME;
create index IDX_TABLA2_OWNER_OBJECT_NAME on tabla2(OBJECT_NAME, OWNER);
Probamos el plan nuevo y nos sale:
Plan hash value: 538379878
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 115 | 255K| | 67 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 115 | 255K| 320K| 67 (2)| 00:00:01 |
| 2 | NESTED LOOPS | | 115 | 255K| | 8 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 115 | 255K| | 8 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN | TABLA1_PK | 1 | 132 | | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_TABLA2_OWNER_OBJECT_NAME | 53 | | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| TABLA2 | 115 | 241K| | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Hemos conseguido bajar el coste a 67 de 348.
Parece que el mundo de la IA está mejorando a pasos agigantados, pero todavía hay tareas en las que puede mejorar bastante. Iremos probando en versiones superiores a ver si nos sorprende, aunque ya empieza a tener funcionalidades muy interesantes en la optimización de consultas.
Si no te fías de lo que te dice ChatGPT o no sabes como implementarlo, confía en nuestro servicio de soporte y mantenimiento Oracle. Esperamos que os haya gustado, nos vemos en la siguiente.
¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.
Sígue a GPS en LinkedIn