Recientemente tuve la necesidad de realizar optimización, aquí intentaré explicar y dar algunos pequeños consejos a la hora de empezar. Si bien ya tenía en mente el uso de algunas herramientas y los clásicos índex para optimización. Esto realmente no lo es todo, hay muchos factores, así como describiré a continuación:
Antes de empezar para entender los siguientes conceptos que mencionare recomiendo revisar esta serie de videos cortos de Maria Colgan Blog
SQL Developer
Si es que aun pensamos que el tiempo de ejecución que retorna SQL developer es lo que debería cargar nuestro reporte, estamos muy equivocados, este solo muestra 50 registros, pero si queremos un costo aproximado al real, deberíamos aumentar la cantidad de registros, para esto es simple una vez ejecutado la consulta, en el área de resultado presionamos crtl + a, para seleccionar todos los registros, esto hará que la consulta vuelva a ejecutarse, pero retornando todos los resultados.
Planes de ejecución / explain plan
¿Costo de ejecuciones?
Quizá muchas veces hemos escuchado que para bajar la ejecución de un SQL debemos tener un costo bajo, esto es cierto hasta cierto punto trabajando en una consulta independiente, pero cuando intentamos comparar contra otro esto es totalmente irrelevante, dejo estos links con más detalle:
DBMS_XPLAN, v$sql, V$SQL_SHARED_CURSOR
Entendiendo como crear y leer un plan de ejecución, necesitaremos la forma de cómo obtener esta información, estos links y consultas que dejo a continuación deberían ser útiles.
- Dbms xplan, display cursor
- How to see last sql statement executed
- Execution plans shared
- $Sql
- $Sql Shared Cursor
Hints?
No usar “materialize” hint al menos que este 100% seguro que sirve. Particularmente cuando lo use, elevaba el costo y lo hacía más lento. Tampoco se recomienda el uso de aquellos no documentados.
Videos recomendados para entender más los Hints. Video 1, Video 2
Existen muchos otros Hints, como Dynamic Sampling, usarlos con cuidado y entendiendo el concepto de estos.
Índices
Si quizá es lo primero que pensamos, agrégalos de la misma si entiendes el concepto de ellos, si aplicas índices por todo lado sin analizar las relaciones de tabla harás la consulta mucho más lenta.
Oracle Apex
Para Oracle APEX dejaré unos pequeños tips no muy obvios pero muy comunes que hacen lento nuestra página o reporte.
Funciones dentro de la consulta SQL
Cuando tenemos nuestra consulta SQL totalmente optimizada con índices, sin funciones etc. ¡Llevaremos nuestro código a SQL, ejecutamos la página y bum! Esta tarda en cargar demasiado tiempo. Bueno para esto deberíamos empezar por poner la página en debug LEVEL9 e identificar que otras cosas existen al cargar la página.
APEX procedimientos, funciones al cargar la página
Revisar procesos, funciones, esquemas de autorización, etc. cualquier código que cargue al inicio de página. Dependiendo que tan complejo sea esto también hará que tu página cargue más lento.
APEX Ordenamiento en el reporte
Si tenemos nuestro reporte primario guardado con ordenamiento esto incrementaría también la carga de la página. Así como cualquier otro calculo / modificación adicional que se le este dando al reporte, afectara en el tiempo de carga.
APEX Lovs en columnas
Quizá ya lo saben, pero nunca usen LOVs en reportes, el costo de hacer esto es demasiado, lo cual hará muy lento tu reporte.
APEX referencias adicionales
¿Aun lento?
Revisa este post de Jorge R. en el cual explica otros factores de por qué nuestro código podría estar mal escrito y generando muchos planes de ejecución y no ser optimizado por la base de datos.
Tanel Poder de igual forma tiene excelente material para continuar revisando y explorando performance en consultas.
https://github.com/tanelpoder/tpt-oracle
Otros.https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:6407993912330
A little knowledge to share (Oracle APEX) 