Buenas prácticas de programación con SQL y PL/SQL

Extraído de una presentación de Steven Feurestein https://community.oracle.com/servlet/JiveServlet/download/915011-2-163991/Make%20No%20Small%20Plans%20PDF.pdf

Reutilización
- Usa bind variables (con JDBC usar parámetros https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html#create_ps).
- Usa código almacenado en la BD (paquetes, procedimientos, funciones, etc).
- Usa vistas materializadas en lugar de vistas con DBlinks
Acceso a Datos
- Calcula estadísticas de tablas, columnas e índices
- El acceso más rápido a un datos es obteniendo directamente la fila (ROWID).
- Revisa el plan de ejecución (explain plan)
Seguridad
- Usa bind variables.
- El usuario que ejecuta la aplicación NO puede ser el propietario de las tablas.
- Construye una API con PL/SQL para consultar y actualizar los datos, de forma que el usuario que ejecuta la aplicación NO tenga acceso directo a las tablas.
Con PL/SQL solo se comparten en SGA las constantes estáticas de paquete (p.e. valor fijo de nº o string, pero NO un valor de secuencia).
Minimizar los cambios de contexto entre motor PL/SQL y SQL, que ocurren cada vez que se ejecuta una sentencia SQL dentro de PL/SQL.
- No repetir sentencias (usar funciones/procedures)
- Usar BULK COLLECT y FORALL
- Una función en una sentencia SQL, a su vez, NO puede contener ninguna sentencia SQL (debe ser determinística).
Hacer GTs para estandarizar la escritura de SQL:
- Estar al día sobre las novedades en SQL
- ¿cómo, cuándo y dónde escribir SQL?
Novedades SQL
- Funciones analíticas, como LAG y LEAD q permiten trabajar con las filas anterior y siguiente para calcular diferencias.
- WITH, para definir “vistas” reutilizables en una sentencia compleja.
- Flasback query.
- ANSI JOIN: cambiar “(+)” por sintaxis completa.
- Usar subconsultas como si fuesen funciones.
- Novedades 12.1
  - Patrones (patter matching) y limit (row limiting)
  - Columnas en memoria (in-memory column store)
  - JSON nativo
Simplificar SQL (fuera sentencias complejas)
- NUNCA repitas una sentencia SQL.
- Oculta las sentencias SQL en una capa de acceso a datos.
- SQL como servicio = vistas + paquetes
- Gestión de excepciones
- NO consultes tablas directamente, hazlo mediante vistas.
- Separa datos y código en esquemas diferentes.
- Rutas absolutas para todas las columnas y variables q referencias en una consulta.
Gestión de excepciones (ejemplo Sistema de Error Logger https://github.com/OraOpenSource/Logger)
- Pasarle al Sistema de Log las variables locales y el estado de la app.
- Usar transacciones autónomas para el Sistema de Log.
- Usar EXCEPTION_INIT para asignarle nombre a los errores, NO usar directamente nºs y/o mensajes de error.
Trazas (solo en el entorno de producción, por defecto desactivada).
- Usar Oracle Trace, NUNCA dbms_output.put_line()
- Usar DBMS_APPLICATION_INFO
- Tb puedes usar Logger (visto antes) https://github.com/OraOpenSource/Logger
Testing
- SQLdeveloper Unit Testing
- Test unitarios del código PL/SQL con utPLSQL
Escribe Código Mantenible
- Regression test
- NO repetir nada (no solo constantes, tampoco varchar2 y lógica de negocio).
- Usar %TYPE y %ROWTYPE (en lugar de varchar2)
- Usar funciones para encapsular fórmulas y lógica de negocio
- Minimizar la parte ejecutable de los bloques, NO más de 50 líneas.
- Ocultar expresiones complejas en constantes o funciones.
Optimizar el código
- Cachear (function result cache, q solo está en versión enterprise de Oracle)
- IMPORTANTE: la optimización de BD (DBA) no llega a más del 20% del efecto, el 80% se consigue escribiendo código mantenible.
- Identificar cuellos de botella haciendo tuning más granular, usando DBMS_PROFILER y DBMS_HPROF.
- Usar variables de paquete (colecciones) para cachear tablas pequeñas de códigos, estáticas, teniendo en cuenta q SOLO sirve para mantener los datos durante la MISMA SESION (mejor en procesos batch, OJO con apps web q usarn datasource donde la sesión web NO ES IGUAL a la sesión de BD). Consumen PGA.
- Function RESULT CACHE.
  - No se cachean tipos complejos como registros (record) con CLOBS, colecciones, etc.
  - Solo se cachean los parámetros de entrada y el valor de salida
  - Si usar INVOKER RIGHTS necesitas v12.1 para q use result cache
  - OJO si usas VPD (virtual private database) y/o contextos (SYS_CONTEXT)
  - Sobre todo cachear tablas cuyos datos no cambian durante una sesión
- BULK COLLECT. Se usa en SELECTs para cargar todas las filas en una colección, de un golpe.
  - Ejemplos sencillos
```
                    SELECT * BULK COLLECT INTO collection(s) FROM TABLE;
                    FETCH cur BULK COLLECT INTO collection(s);
                    EXECUTE IMMEDIATE query BULK COLLECT INTO collection(s); 
```
  - No sirve NO_DATA_FOUND, hay q controlar cuando se vacía la colección.
  - Se puede usar LIMIT para limitar el nº de filas a procesar
```
                OPEN emps_in_dept_cur; 
                LOOP
                  FETCH emps_in_dept_cur
                    BULK COLLECT INTO emps LIMIT 1000;
                  EXIT WHEN emps.COUNT = 0;
                  process_emps (emps);
                END LOOP;
```
  - NO USES Bulk Collect si tu bucle funciona bien, o si NO contiene sentencias DMLs.
- FORALL. Se usa en UPDATE/INSERT/DELETE, para actualizar todas las filas de un golpe, desde una colección.
  - Ejemplo
```
               FORALL indx IN low_value .. high_value
                  UPDATE employee
                     SET salary = newsal_in
                   WHERE employee_id = list_of_emps (indx);
```
  - SQL%ROWCOUNT cuento el total de filas modificadas por todo el FORALL
  - SQL%BULK_ROWCOUNT cuenta el nº de filas modificadas en cada ciclo del bucle FORALL
- Si tienes bucles q contienen sentencias DML, cambia el proceso por otro en 3 fases:
  - Primero, usa BULK COLLECT para cargar los datos en una colección.
  - Segundo, haz los cambios en la colección.
  - Tercero, usa FORALL para aplicar los cambios en la BD desde la colección.
- Mejoras en 12c de WITH https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1

Este contenido es una copia de https://wiki.um.es/wikis/programador/doku.php?id=plsql:optimizacion

— JUAN LUIS SERRADILLA AMARILLA 15/09/2022