viernes, 25 de abril de 2025

Cómo influir en el optimizador de Oracle por medio de los Hints Más Comunes en Oracle

En Oracle, los hints (sugerencias) son instrucciones opcionales que puedes incluir en tus consultas SQL para influir en el optimizador de consultas. Aunque Oracle suele elegir buenos planes de ejecución automáticamente, los hints pueden ayudarte a forzar comportamientos específicos cuando conoces bien tus datos y patrones de uso.

En este artículo veremos los hints más comunes con ejemplos para que puedas usarlos de manera efectiva.

¿Cómo se usan los Hints?

Los hints se colocan en el bloque SELECT inmediatamente después de la palabra clave SELECT, encerrados entre /*+ ... */.

SQL

SELECT /*+ HINT */ columna1, columna2
FROM tabla;

1. FULL – Forzar un Full Table Scan

Este hint le indica a Oracle que debe escanear toda la tabla, ignorando índices disponibles.

Ejemplo:

SQL
SELECT /*+ FULL(emp) */ *
FROM emp
WHERE deptno = 10;

Cuándo usarlo: Si el índice no es selectivo y escanear la tabla completa es más eficiente.

2. INDEX – Forzar el uso de un índice

Este hint le dice a Oracle que use un índice específico.

Ejemplo:

SQL
SELECT /*+ INDEX(emp emp_idx1) */ *
FROM emp
WHERE ename = 'SMITH';

Cuándo usarlo: Cuando Oracle no elige un índice útil por sí solo, y sabes que usarlo es más eficiente.

3. USE_NL – Forzar Nested Loops Join

Este hint fuerza el uso del algoritmo de nested loops para una combinación (JOIN).

Ejemplo:

SQL
SELECT /*+ USE_NL(e d) */ *
FROM emp e, dept d
WHERE e.deptno = d.deptno;

Cuándo usarlo: Cuando esperas pocos resultados del lado interno de la combinación.

4. USE_HASH – Forzar Hash Join

Este hint obliga a Oracle a usar hash joins en lugar de otras estrategias.

Ejemplo:

SQL
SELECT /*+ USE_HASH(e d) */ *
FROM emp e, dept d
WHERE e.deptno = d.deptno;

Cuándo usarlo: Para combinar grandes volúmenes de datos eficientemente.

5. LEADING – Controlar el orden de las tablas

Permite especificar qué tabla debe ser procesada primero en una combinación.

Ejemplo:

SQL
SELECT /*+ LEADING(e d) */ *
FROM emp e, dept d
WHERE e.deptno = d.deptno;

Cuándo usarlo: Cuando el orden de acceso puede mejorar el rendimiento.

6. PARALLEL – Ejecutar en paralelo

Solicita a Oracle que ejecute la consulta en paralelo.

Ejemplo:

SQL
SELECT /*+ PARALLEL(emp, 4) */ *
FROM emp;

Cuándo usarlo: En consultas sobre grandes volúmenes de datos para mejorar el tiempo de respuesta.

7. MERGE – Forzar Merge Join

Fuerza el uso de merge join como estrategia de combinación.

Ejemplo:

SQL
SELECT /*+ MERGE(e d) */ *
FROM emp e, dept d
WHERE e.deptno = d.deptno;

Cuándo usarlo: Cuando las tablas ya están ordenadas por las columnas de combinación.

Consejos para el uso de Hints

No abuses de los hints. Deja que Oracle optimice por su cuenta si no estás seguro.
Prueba y mide. Usa EXPLAIN PLAN o AUTOTRACE para verificar el efecto de tus hints.
Documenta bien tu código. Otros desarrolladores deben entender por qué usaste un hint.

17 Optimizer Hints

Using Optimizer Hints

Comportamiento de los Algoritmos de Join en Oracle

Introducción

Como ya sabemos los JOINS (Inner, left, right, full, cross, etc) son parte de la elaboración de los SQL para generar consultas en las base de datos segun nuestro criterio para combinar las direntes entidades de datos.

Ahora viendo a detalle como el motor de la base con su optimizador hace este trabajo? el optimizador usa algoritmos bajo su criterio para hacer esta tarea.

En Oracle, los algoritmos de join son fundamentales para combinar datos de múltiples tablas. El optimizador de Oracle utiliza varios métodos para realizar estas uniones de manera eficiente:

Nested Loops Join,
Hash Join y
Sort-Merge Join.

En este artículo, exploraremos cómo funcionan estos métodos, cómo se representan en los planes de ejecución y proporcionaremos ejemplos prácticos con imágenes para ilustrar su funcionamiento.

Métodos de Join en Oracle

Nested Loops Join

El método Nested Loops Join es uno de los más comunes y se utiliza cuando una tabla tiene un índice que puede ser aprovechado para realizar la unión. Este método es eficiente para conjuntos de datos pequeños o cuando el índice es altamente selectivo.


SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Plan de Ejecución:

--------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   107 |  8560 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS       |             |   107 |  8560 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | EMPLOYEES   |   107 |  7490 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL | DEPARTMENTS |    27 |   810 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Hash Join

El Hash Join es ideal para grandes conjuntos de datos y se utiliza principalmente para equi-joins. Oracle crea una tabla hash en memoria para una de las tablas y luego escanea la otra tabla para encontrar coincidencias.

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Plan de Ejecución:

--------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   107 |  8560 |     3   (0)| 00:00:01 |
|   1 |  HASH JOIN          |             |   107 |  8560 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | EMPLOYEES   |   107 |  7490 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL | DEPARTMENTS |    27 |   810 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Sort-Merge Join

El Sort-Merge Join se utiliza cuando ambas tablas están ordenadas por las columnas de la condición de join. Este método es eficiente para grandes conjuntos de datos que ya están ordenados.


SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Plan de Ejecución:

--------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   107 |  8560 |     3   (0)| 00:00:01 |
|   1 |  SORT MERGE JOIN    |             |   107 |  8560 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | EMPLOYEES   |   107 |  7490 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL | DEPARTMENTS |    27 |   810 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Conclusión

Los métodos de join en Oracle son esenciales para la eficiencia de las consultas en bases de datos relacionales. Comprender cómo funcionan y cómo se representan en los planes de ejecución puede ayudarte a optimizar tus consultas y mejorar el rendimiento de tu base de datos.

miércoles, 23 de abril de 2025

Cómo cambiar el SID de una base de datos Oracle

En Oracle, el SID (System Identifier) identifica de forma única una instancia de base de datos dentro del sistema operativo. Aunque es común confundirlo con el nombre de la base de datos (DB_NAME), el SID se utiliza para iniciar y conectar la instancia localmente y remotamente. Este procedimiento NO CAMBIA el nombre de la base.

En este artículo te mostramos cómo cambiar el SID de una base de datos Oracle de forma controlada y segura.

¿Se puede cambiar directamente el SID?

No. El SID no se cambia dentro de Oracle como el DB_NAME. En cambio, se crea una nueva instancia (nuevo SID) que apunta a la misma base de datos física.

Pasos para cambiar el SID

Vamos a cambiar el nombre de la base de datos orcl a orclnew , primero identifiquemos que vamos a tener que modificar

Indetificar el nombre actual: select instance_name from v$instance;

Nombre de la instancia en el archivo pfile o spfile $ORACLE_HOME/dbs

Archivo listener, para indicar el nuevo nombre de la instancia

Archivo de ambiente oraenv, por lo general esta en la ruta /home/oracle/scripts, cambiamos el ORACLE_SID

1. Detener la instancia actual

Desde SQL*Plus:

SHUTDOWN IMMEDIATE;

2. Crear una nueva estructura de entorno con el nuevo SID

Define el nuevo ORACLE_SID en tu entorno o editar el archivo setEnv.sh:

export ORACLE_SID=ORCLNEW

Este valor debe ser configurado en tu entorno de usuario o en el script de inicio del sistema operativo.

nano /home/oracle/scripts/setEnv.sh

Aplicamos el las variables de ambiente

3. Crear el nuevo archivo de parámetros para el nuevo SID

Copia el archivo de parámetros existente (spfile o init.ora) y renómbralo con el nuevo SID:

cd $ORACLE_HOME/dbs

cp initANTIGUO_SID.ora initNUEVO_SID.ora

O si usas SPFILE:

cp spfileANTIGUO_SID.ora spfileNUEVO_SID.ora

En mi caso tengo que copiar el archivo SPFILE spfileorcl.ora a spfileorclnew.ora

cp spfileorcl.ora spfileorclnew.ora

Abre el archivo y asegúrate de que el parámetro db_name esté correcto y que los paths (en caso de tener rutas absolutas) estén bien configurados.

4. Iniciar la instancia con el nuevo SID y probar

Verificamos que la base levante con el nuevo SID y verificamos el nuevo SID

echo $ORACLE_SID
sqlplus / as sysdba
select instance_name from v$instance;
STARTUP;

Recargar el listener y Conectarnos desde afuera

lsnrctl reload
lsnrctl status

sqlplus system@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.20.28)(PORT=1521)))(CONNECT_DATA=(SID=ORCLNEW)))
select instance_name from v$instance;

5. Opcional: Eliminar archivos de la instancia anterior

Una vez que confirmes que todo está bien funcionando, puedes eliminar archivos de parámetros o configuraciones relacionadas con el antiguo SID.

¿Qué más debo tener en cuenta?

Actualiza tus scripts de arranque del sistema. en caso de que tengas configurado inicio automático
Actualiza configuraciones en oratab (en Unix/Linux).
Revisa listener.ora y tnsnames.ora si contienen referencias al SID antiguo.

Conclusión

Aunque no se puede cambiar el SID directamente en Oracle, puedes crear una nueva instancia que apunte a la misma base de datos y así "renombrarla" desde el punto de vista del sistema operativo. Este procedimiento es seguro si se siguen los pasos correctamente y es muy útil en entornos donde se clonan bases de datos o se reubican servidores.

¿Te ha tocado cambiar un SID antes? ¡Déjanos tu experiencia o pregunta en los comentarios!

martes, 22 de abril de 2025

Cómo funciona la función LISTAGG en Oracle

En algun momento te has encontrado en la necesidad de concatenar valores de varias filas en una sola cadena de texto. Por suerte, Oracle ofrece una función muy útil para esto: LISTAGG. En este artículo, te explicaremos cómo funciona LISTAGG, para qué sirve, y te mostraremos ejemplos prácticos para que la pongas en uso.

¿Qué es LISTAGG?

LISTAGG es una función de agregación introducida en Oracle 11g Release 2 que permite combinar (o concatenar) múltiples valores de un grupo en una sola cadena de texto, separados por un delimitador que tú defines.

Sintaxis básica

LISTAGG(columna, 'delimitador') WITHIN GROUP (ORDER BY columna)

columna: el nombre de la columna cuyos valores quieres concatenar.
'delimitador': el carácter o cadena que separará cada valor.
WITHIN GROUP (ORDER BY ...): define el orden en el que se concatenarán los valores.

Ejemplo básico

No ha mejor forma que entender que con un ejemplo asi que supongamos que tienes la siguiente tabla llamada empleados:

create table empleados (departamento varchar2(50), nombre varchar2(50), fecha_ingreso date);

insert into empleados values('Ventas','Ana','01/01/2002');

insert into empleados values('Ventas','Lui','01/01/2001');

insert into empleados values('Ventas','Marta','01/01/2000');

insert into empleados values('Finanzas','Carlos','01/01/2005');

insert into empleados values('Finanzas','Laura','01/01/2004');

commit;

Y quieres obtener una lista de nombres por departamento, separados por comas. Usarías:

SELECT departamento,
LISTAGG (nombre, ', ') WITHIN GROUP (ORDER BY nombre) AS nombres
FROM empleados
GROUP BY departamento;

Resultado

Características adicionales

Ordenamiento personalizado

Puedes ordenar los resultados concatenados según otra columna si lo necesitas:

SELECT departamento,
         LISTAGG (nombre, ', ') WITHIN GROUP (ORDER BY nombre) AS nombres,
         LISTAGG(nombre, ', ') WITHIN GROUP (ORDER BY fecha_ingreso) nombres_por_fecha
    FROM empleados
GROUP BY departamento;

Manejo de resultados largos

A partir de Oracle 12.2c, puedes usar la cláusula ON OVERFLOW para evitar errores cuando la cadena resultante excede el límite de 4000 caracteres:

SELECT departamento,
LISTAGG (nombre, ', ' ON OVERFLOW TRUNCATE WITH COUNT) WITHIN GROUP (ORDER BY nombre) AS nombres
FROM empleados
GROUP BY departamento;

Esto te permite truncar la cadena y añadir un indicador (como el número de valores que no se mostraron).

Posibles errores

Un error común es:

ORA-01489: result of string concatenation is too long

Este error aparece cuando el resultado de la concatenación excede el límite permitido. Puedes prevenirlo como se explicó con ON OVERFLOW.

Conclusión

LISTAGG es una herramienta poderosa para transformar filas en una única cadena, ideal para reportes, agrupaciones y análisis. Te ahorra el trabajo de hacer subconsultas complicadas o usar funciones personalizadas para concatenar datos.

martes, 15 de abril de 2025

Instalar Oracle 19C en Oracle Linux 9

Este blog explicare paso a paso los requisitos e instalación de la base de datos Oracle 19C para Oracle Linux 9, pero para realizarla durante la instalación del motor de base de datos debemos aplicar un parche ya que si usan solo el instalador LINUX.X64_193000_db_home nos dará error.

Revisa articulo Oracle:

Requirements for Installing Oracle Database/Client 19c (19.22 or higher) on OL9 or RHEL9 64-bit (x86-64) (Doc ID 2982833.1)

Recursos

IP:192.168.20.28
HOST:SRVORACLE
SO RHL8
10G Ram 4Cpus y 200GB de disco
Usuario:root

Archivos a descargar desde la pagina de Oracle

Instalador del motos de base de datos LINUX.X64_193000_db_home.zip

OPatch for DB 19.0.0.0.0 Patch 6880880, Asegúrate de seleccionar la versión y plataforma correcta

19.22 GI RU Patch 35940989 o superior

Preparar Sistema Operativo

Vemos la versión

cat /etc/oracle-release

Actualizar

sudo yum update

Preparar Sistema Operativo - Instalar componente para la instalación grafica

Si tienes el sistema operativo con la interfaz grafica omite estos paso, como yo no lo tengo lo realizaré con MobaXterm. https://mobaxterm.mobatek.net/download.html

En el servidor para que nos permita usar el instaldor de Oracle con la interfaz gráfica instalaremos los componentes necesarios.

sudo yum install xterm* xorg*

Editar el archivo sshd_config y establecer X11Forwarding yes

nano /etc/ssh/sshd_config

Reiniciar

sudo reboot

Desactivar Firewalld

Para nuestro ejercicio vamos a desactivar el firewall de OLinux pero en un escenario real es mejor agregar las reglas de input a los puertos que usa oracle.

sudo systemctl stop firewalld
sudo systemctl disable firewalld

Nombre del servidor

Si no tienes establecido el nombre del host sigue estos pasos.

Editar el archivo /etc/hosts, vamos a indicar el nombre del servidor el cual será SRVORACLE para que resuelva la ip 192.168.20.28

sudo nano /etc/hosts

También lo realizamos con el comando hostnamectl

hostnamectl set-hostname SRVORACLE

Prerequisitos Oracle

sudo yum install -y oracle-database-preinstall-19c

Usuario oracle y parametros

Setear la clave del Usuario oracle.

sudo passwd oracle

Modificar archivo /etc/selinux/config y cambiar el parámetro SELINUX=permissive

sudo nano /etc/selinux/config

Guarda y reiniciar el servidor

sudo reboot

Crear oracle home y dar los permisos

Vamos a crear todo el directorio requerido para la instalación de los archivos binarios de Oracle y donde se alojarán los archivos de la base de datos.

mkdir -p /u01/app/oracle/product/19.3/db_home
mkdir -p /u01/install
mkdir -p /u02/oradata
chown -R oracle:oinstall /u01
chown -R oracle:oinstall /u02
chmod -R 775 /u01
chmod -R 775 /u02

Variables de ambiente

Crear las variables de ambiente para que se carguen en la sesión del usuario oracle.

su - oracle

Creamos directorio donde alojaremos el script

mkdir /home/oracle/scripts

Creamos archivo setEnv.sh para establecer las variables

nano /home/oracle/scripts/setEnv.sh

Pegamos la configuración

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=SRVORACLE
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3/db_home
export ORA_INVENTORY=/u01/app/oraInventory
export ORACLE_SID=orcl
export PDB_NAME=noracle
export DATA_DIR=/u02/oradata
export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Agregamos la linea en el bash profile del usuario oracle para que al iniciar sesión cargue las variables

echo ". /home/oracle/scripts/setEnv.sh" >> /home/oracle/.bash_profile
echo "export PATH" >> /home/oracle/.bash_profile

Con Usuario root cambiamos los permisos de /home/oracle/scripts/setEnv.sh

sudo chown -R oracle:oinstall /home/oracle/scripts
sudo chmod u+x /home/oracle/scripts/setEnv.sh

Subir los archivos descagador al servidor, descomprimir e instalamos

Subir los archivos descargador previamente: instalador del motor de base de datos, el OPatcha y parche de oracle a la carpeta /u01/install/

chown oracle:oinstall -R /u01/install/

En una sesión del usuario oracle

Descromprimir

#$ORACLE_HOME Asegúrate que este seteado
. /home/oracle/scripts/setEnv.sh

#Instalador de oracle
unzip -qqd $ORACLE_HOME /u01/install/LINUX.X64_193000_db_home.zip

#OPatch
mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch_bkp
unzip -qqd $ORACLE_HOME /u01/install/p6880880_190000_Linux-x86-64.zip

#Parche 19.26
unzip -qqd /u01/install /u01/install/p35940989_190000_Linux-x86-64.zip

#Con usuario oracle
su - oracle
#establecer compatibilidad de la instalacion con OL8
export CV_ASSUME_DISTID=OL8

Establecemos variables para permitir ejecución via xhost para la instalación visual de Oracle

DISPLAY=MI_IP:0.0; export DISPLAY
echo $DISPLAY
xhost +