A continuación, comenzaré con una introducción de cómo hacer diagnóstico y tuning en Oracle 19c, incluyendo vistas clave, análisis de rendimiento, SQL Tuning, y estadísticas.

¿Qué es el Performance Tuning en Oracle 19c?

El tuning en Oracle 19c se divide en varias áreas:
✅ Tuning de la base de datos → Ajuste de parámetros, memoria y estructuras físicas.
✅ Tuning de SQL → Optimización de consultas y ejecución eficiente de SQL.
✅ Tuning de estadísticas → Uso de estadísticas para mejorar los planes de ejecución.
✅ Monitoreo del rendimiento → Identificación de cuellos de botella mediante vistas dinámicas (V$).

El objetivo es mejorar la velocidad y eficiencia de la base de datos minimizando el consumo de recursos.

Diagnóstico inicial del rendimiento

Antes de optimizar, debes identificar los cuellos de botella. Oracle ofrece herramientas para analizar el rendimiento en tiempo real.

Vistas dinámicas (V$) clave para diagnóstico

Consulta rápida para identificar sesiones con alto consumo de recursos:

SELECT SID, SERIAL#, STATUS, MACHINE, PROGRAM, SQL_ID 
FROM V$SESSION 
WHERE STATUS = 'ACTIVE' 
ORDER BY SQL_ID;

Ver qué SQL está consumiendo más recursos:

SELECT SQL_TEXT, EXECUTIONS, CPU_TIME, ELAPSED_TIME, DISK_READS 
FROM V$SQLAREA 
ORDER BY CPU_TIME DESC;

Ver eventos de espera que afectan el rendimiento:

SELECT EVENT, TOTAL_WAITS, TIME_WAITED
FROM V$SYSTEM_EVENT
ORDER BY TIME_WAITED DESC;

SQL Tuning en Oracle 19c

Una consulta mal optimizada puede afectar el rendimiento de toda la base de datos. Oracle tiene varias herramientas para mejorar la ejecución de SQL.

Ver el Plan de Ejecución de una consulta

Para ver cómo Oracle ejecuta una consulta, usa EXPLAIN PLAN:

EXPLAIN PLAN FOR
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Interpretación del plan de ejecución:

• FULL TABLE SCAN → La consulta está leyendo toda la tabla (malo si es grande).
• INDEX SCAN → Se usa un índice (bueno para consultas específicas).
• HASH JOIN / NESTED LOOPS → Método de combinación entre tablas.

Si ves FULL TABLE SCAN en una tabla grande, puedes:
Crear un índice en la columna usada en WHERE.
Reescribir la consulta para aprovechar índices existentes.

Ejemplo de índice para mejorar la consulta anterior:

CREATE INDEX IDX_EMP_DEPT ON EMPLOYEES (DEPARTMENT_ID);

Uso del SQL Tuning Advisor

Oracle 19c tiene el SQL Tuning Advisor, que analiza consultas y sugiere mejoras.

Ejecutar SQL Tuning Advisor en una consulta específica:

VARIABLE TUNING_TASK VARCHAR2(64);
BEGIN
:TUNING_TASK := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'SQL_ID_A_ANALIZAR',
scope => 'COMPREHENSIVE',
time_limit => 60);
END;
/

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:TUNING_TASK);

Ver recomendaciones del SQL Tuning Advisor:

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK) FROM DUAL;

Si el asesor sugiere un SQL Profile, puedes aplicarlo con:

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => :TUNING_TASK);

Estadísticas en Oracle 19c

Oracle usa estadísticas de objetos para optimizar los planes de ejecución de SQL.

Recolectar estadísticas manualmente

Si una consulta se ejecuta lento, es posible que las estadísticas estén desactualizadas. Para recolectarlas:

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

Para actualizar estadísticas de toda la base de datos:

EXEC DBMS_STATS.GATHER_DATABASE_STATS;

Ajustes avanzados para tuning

Identificar consultas que generan bloqueos

Si hay sesiones esperando, usa esta consulta para encontrar bloqueos:

SELECT BLOCKING_SESSION, SID, SERIAL#, WAIT_CLASS, EVENT
FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;

Para terminar una sesión bloqueante:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

juste de parámetros de memoria para mejor rendimiento

Si la base de datos consume demasiados recursos, ajusta la memoria:

Ver uso actual de memoria:

SELECT NAME, VALUE FROM V$MEMORY_TARGET_ADVICE;

Si necesitas más memoria, puedes aumentar SGA y PGA manualmente:

ALTER SYSTEM SET SGA_TARGET = 8G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 4G SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

Caso práctico de SQL Tuning en Oracle 19c

Problema: Una consulta en la tabla ORDERS es muy lenta:

SELECT * FROM ORDERS WHERE ORDER_DATE > SYSDATE - 30;

Diagnóstico

Ver el plan de ejecución:

EXPLAIN PLAN FOR
SELECT * FROM ORDERS WHERE ORDER_DATE > SYSDATE - 30;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Si muestra FULL TABLE SCAN, se necesita un índice.

Crear un índice en ORDER_DATE:

CREATE INDEX IDX_ORDERS_DATE ON ORDERS (ORDER_DATE);

Actualizar estadísticas:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'ORDERS');

Ejecutar de nuevo el plan de ejecución y verificar que ahora usa el índice.

Diagnóstico inicial: Usa V$SQLAREA, V$SESSION, V$SYSTEM_EVENT.
Optimización de consultas: Usa EXPLAIN PLAN y DBMS_SQLTUNE.
Mejora del rendimiento: Usa índices y actualiza estadísticas (DBMS_STATS).
Ajustes de memoria: Revisa MEMORY_TARGET y SGA_TARGET.

Con estas estrategias, puedes optimizar Oracle 19c y mejorar el rendimiento de la base de datos.