Tunea tu Base Oracle

Fragmentacion de tablas Oracle

2025-07-21T14:34:00.000-07:00

Como sabemos, las base de datos transacciones por las operaciones DML (Update, Insert y Delete) generan un efecto de fragmentación en los segmentos de tablas e indices.

En un blog anterior se especifica a detalle como realizar la recuperación del espacio sub-utilizado Recuperar espacio inutilizado de los segmentos SHRINK SPACE

Ahora en este blog vamos a determinar la cantidad de información que podemos recuperar de los segmentos de tablas e indices.
Veamos un ejemplo.

Creamos una tabla FRAMENTACION.

CREATE TABLE FRAGMENTACION TABLESPACE USERS AS SELECT ROWNUM FILA, O.* FROM ALL_OBJECTS OCROSS JOIN (SELECT ROWNUM FROM DUAL CONNECT BY LEVEL<=5);

Veamos la asignación al segmento de tabla FRAMENTACION.

SELECT SEGMENT_TYPE,BLOCKS,BYTES / (1024*1024)MB FROM DBA_SEGMENTS WHERE OWNER='SCOTT' AND SEGMENT_NAME='FRAGMENTACION';

SEGMENT_TYPE BLOCKS MB------------------ ---------- ----------TABLE 10240 801 row selected.

Borremos algunos datos de la tabla FRAMENTACION.

DELETE FROM FRAGMENTACION WHERE MOD(FILA,10)<=3;

223779 rows deleted.

Veamos el espacio asignado al segmento de la tabla FRAMENTACION

SELECT SEGMENT_TYPE,BLOCKS,BYTES / (1024*1024)MB FROM DBA_SEGMENTS WHERE OWNER='SCOTT' AND SEGMENT_NAME='FRAGMENTACION';

SEGMENT_TYPE BLOCKS MB------------------ ---------- ----------TABLE 10240 801 row selected.

Nos damos cuenta que el espacio asignado es el mismo, esto debido a que los bloques de datos no se liberan por completo, a esto se le llama fragmentación.

Optimiza los imports con IMPDP agregando el parametros TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

2025-07-21T13:15:00.000-07:00

Oracle Data Pump (impdp) es una herramienta poderosa para la importación de datos en bases de datos Oracle. Uno de los parámetros menos conocidos pero altamente efectivos para mejorar el rendimiento durante la importación es TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y.

¿Qué hace este parámetro?

El parámetro TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y desactiva temporalmente el registro en el archivo de redo log (archive logging) durante la operación de importación. Esto significa que las operaciones de escritura realizadas por impdp no se registran para recuperación en caso de fallo, lo cual puede acelerar significativamente el proceso de importación.

Beneficios de usarlo

Mejora del rendimiento: Al evitar la escritura en los redo logs, se reduce la carga de I/O en el sistema.
Importaciones más rápidas: Ideal para entornos de desarrollo o pruebas donde la recuperación no es crítica.
Menor uso de espacio en disco: Se evita la generación de grandes volúmenes de redo logs.

Ejemplo de uso

impdp usuario/password DIRECTORY=dir DUMPFILE=datos.dmp LOGFILE=import.log TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

Consideraciones importantes

Este parámetro no debe usarse en entornos de producción donde la recuperación ante fallos es crítica.
Solo afecta a objetos que se importan; no desactiva el archive logging para toda la base de datos.
Requiere privilegios adecuados para que impdp pueda aplicar esta transformación.
Es compatible con Oracle 12c y versiones posteriores.

Conclusión

El uso de TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y en Oracle impdp puede ser una estrategia eficaz de tuning para acelerar importaciones en entornos controlados. Como siempre, se recomienda realizar pruebas antes de aplicar en escenarios reales y evaluar el impacto en la recuperación de datos.

Optimización del Rendimiento en Oracle con el Parámetro filesystemio_options

2025-07-21T12:55:00.000-07:00

El rendimiento es una prioridad constante. Uno de los parámetros menos comprendidos pero altamente influyentes en entornos que utilizan almacenamiento basado en archivos es filesystemio_options. Este parámetro puede marcar una diferencia significativa en la forma en que Oracle interactúa con el sistema operativo y el subsistema de almacenamiento.

¿Qué es `filesystemio_options`?

Disponible desde la version 11g de Oracle y por defecto filesystemio_options es NONE, este parámetro filesystemio_options controla cómo Oracle accede a los archivos del sistema operativo cuando se utilizan archivos de datos en sistemas de archivos (en lugar de dispositivos RAW o ASM). Este parámetro puede afectar directamente el rendimiento de I/O (entrada/salida) de la base de datos.

Valores posibles:

NONE: Oracle utiliza el acceso estándar del sistema operativo (buffered I/O).
DIRECTIO: Habilita la E/S directa, que omite la caché del búfer del sistema operativo, lo que puede mejorar el rendimiento al reducir la sobrecarga, especialmente en entornos con sus propios mecanismos de almacenamiento en caché.
ASYNCH: Habilita la E/S asíncrona, donde las operaciones de Oracle se pueden paralelizar y el sistema operativo maneja la E/S en segundo plano.
SETALL: Habilita tanto el direct I/O como el asynchronous I/O si están disponibles. El mas recomendable ya que Oracle eligirá el mejor método.

¿Por qué es importante?

El acceso a disco es uno de los cuellos de botella más comunes en bases de datos. Usar directIO puede reducir la duplicación de caché entre Oracle y el sistema operativo, mientras que asyncIO permite que Oracle continúe procesando mientras espera que se completen las operaciones de disco.

Mejores prácticas para su uso

Evaluar el entorno de almacenamiento: No todos los sistemas de archivos o plataformas soportan directIO o asyncIO. Verifica la compatibilidad con tu sistema operativo y tipo de almacenamiento.
Usar setall en la mayoría de los casos: Si tu sistema lo soporta, setall es generalmente la mejor opción, ya que permite a Oracle aprovechar tanto el acceso directo como el asincrónico.
Monitorear el rendimiento: Usa herramientas como AWR, ADDM o iostat para medir el impacto de los cambios.
Evitar cambios en producción sin pruebas previas: Siempre prueba en un entorno de desarrollo o staging antes de aplicar en producción.

Cómo verificar y cambiar el parámetro

Puedes verificar el valor actual con:

SHOW PARAMETER filesystemio_options;

Y cambiarlo (requiere reinicio de instancia):

ALTER SYSTEM SET filesystemio_options='SETALL' SCOPE=SPFILE;

Consideraciones adicionales

En sistemas con Oracle ASM, este parámetro no tiene efecto ya que Oracle ASM maneja su sistema de archivos.
En entornos virtualizados o con almacenamiento en red (NFS), el comportamiento puede variar.
Algunos sistemas de archivos como XFS o EXT4 en Linux tienen mejor soporte para directIO.

Referencias

Database Performance Tuning Guide

Database Reference

Oracle Base de Datos y Listener Automatizar el inicio (Oracle Auto Start)

2024-02-23T11:54:00.000-08:00

Oracle Base de Datos y Listener Automatizar el inicio

Ya sabemos que cada vez que enviamos reiniciar el servidor de la base de datos el servicio no levanta de forma automática por lo que es necesario realizarlo de forma manual. Este proceso se vuelve tedioso cuando están en ventanas de mantenimiento y tienes múltiples servidores de base de datos que reiniciar.

Aquí les comparto como lo pueden hacer que sea de forma automática el inicio, cabe indicar que este articulo aplica para a base de datos standalone.

Antes de empezar debemos de tener los siguientes datos:

ORACLE_HOME	/u01/app/oracle/product/19.3/db_home
DB Name	orcl
Nombre Listener	LISTENER
Archivo Oratab	/etc/oratab

1. Editar archive /etc/oratab cambiar flag de :N a :Y

nano /etc/oratab

Como debe de quedar

orcl:/u01/app/oracle/product/19.3/db_home:Y

2. Crear archivo dboracle

nano /etc/init.d/dboracle

Pegar código reemplazado los valores según tu configuración

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_HOME=/u01/app/oracle/product/19.3/db_home
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi

case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
# Remove "&" if you don't want startup as a background process.
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start LISTENER" &
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart &
touch /var/lock/subsys/oracle
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
Su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
Su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop LISTENER"
rm -f /var/lock/subsys/oracle
;;
esac
exit

3. Permisos al archivo dboracle

chmod 750 /etc/init.d/dboracle

4. Asociar dboracle como servicio del sistema operativo

systemctl enable dboracle
chkconfig dboracle on
service dboracle start

5. Reiniciar el sistema para probar

Init 6

Espero les sirva para optimizar sus tiempos de manteniento

Comprimir tablas Oracle -Compress Table

2020-04-09T11:20:00.002-07:00

Comprimir tablas es una forma eficiente de reducir espacio de almacenamiento utilizado por los segmentos de tablas, indices, particiones , etc.

Oracle ha introducido este concepto desde las 9i y conforme han salido las nueva versiones ha ido incluyendo mejoras, pero como toda buena característica de Oracle solo aplica para version enterprise.

Se puede aplicar según sea la necesidad y donde se pueda aplicar, como por ejemplo en datos que representen historia así como particiones.

La compresión de las tablas se puede realizar en la creación o después de la creación.

Creacion
Vamos a realizar el ejemplo de crear dos tabla una sin compresión y otra con compresión.

--SIN COMPRESION

CREATE TABLE TEST_TAB_NOCOMPRESS( NUMERO INTEGER, DATO VARCHAR2(600), FECHA DATE) TABLESPACE USERS;

--COMPRESA

CREATE TABLE TEST_TAB_COMPRESS( NUMERO INTEGER, DATO VARCHAR2(600), FECHA DATE) TABLESPACE USERS COMPRESS;

Realizaremos la inserción de datos.

SET TIMING ON

insert into TEST_TAB_NOCOMPRESS (NUMERO,DATO,FECHA) SELECT ROWNUM, LPAD('X',500,'A'),SYSDATE FROM DUAL CONNECT BY LEVEL <=1000000;

1000000 rows created.

Elapsed: 00:01:01.05

insert into TEST_TAB_COMPRESS (NUMERO,DATO,FECHA) SELECT ROWNUM, LPAD('X',500,'A'),SYSDATE FROM DUAL CONNECT BY LEVEL <=1000000;

1000000 rows created.

Elapsed: 00:01:04.47

--actualizamos estadisticas

analyze table TEST_TAB_NOCOMPRESS compute statistics;analyze table TEST_TAB_COMPRESS compute statistics;

Revisemos el espacio usado y la cantidad de registros

Espacio usado

SELECT SEGMENT_NAME, BYTES/(1024*1024)MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN('TEST_TAB_NOCOMPRESS','TEST_TAB_COMPRESS')

SEGMENT_NAME MB

TEST_TAB_NOCOMPRESS 610

TEST_TAB_COMPRESS 528

Registros

SELECT TABLE_NAME, NUM_ROWS, COMPRESSION, COMPRESS_FOR FROM DBA_TABLES WHERE TABLE_NAME IN('TEST_TAB_NOCOMPRESS','TEST_TAB_COMPRESS')

TABLE_NAME NUM_ROWS COMPRESSION COMPRESS_FOR

TEST_TAB_NOCOMPRESS 1000000 DISABLED

TEST_TAB_COMPRESS 1000000 ENABLED BASIC

Como nos hemos dado cuenta la tabla compresa tiene 82M menos que sin compresión un 13% menos.

No es mucho verdad? pues si por lo único que tiene la tabla TEST_TAB_COMPRESS es un pctfree 0 que hace uso al máximo del bloque para los registros.

Para poder hacer mas efectivo la compresión debemos hacer efectiva la compresión de los datos con un MOVE COMPRESS.

--COMPRIMIR

ALTER TABLE TEST_TAB_COMPRESS MOVE COMPRESS;

--Vemos el tamaño reducido

SELECT SEGMENT_NAME, BYTES/(1024*1024)MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN('TEST_TAB_NOCOMPRESS','TEST_TAB_COMPRESS')

SEGMENT_NAME MB

TEST_TAB_COMPRESS 12

TEST_TAB_NOCOMPRESS 610

Ahora si podemos ver que es mas efectiva la compresión de los datos de 12M llegando a un 98% de reducción de la tabla no compresa. Esta optimización dependerá de los datos y según la empresa o negocio va a variar.

Alterar tabla para la compresion.

Si queremos comprimir una tabla lo hacemos con el MOVE COMPRESS.

--COMPRIMIR

ALTER TABLE TEST_TAB_NOCOMPRESS MOVE COMPRESS;

SELECT SEGMENT_NAME, BYTES/(1024*1024)MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN('TEST_TAB_NOCOMPRESS','TEST_TAB_COMPRESS')

SEGMENT_NAME MB

TEST_TAB_COMPRESS 12

TEST_TAB_NOCOMPRESS 12

Consideraciones

Solo para ediciones enterprise
No esta diseñada para base de datos OLTP(BD Transaccionales) sino para cargas directas de base de datos OLAP(BD para analitica de datos)
En version 11g o superior se implementa la compresion OLTP. Esto permite realizar transacciones DML.
La compresion de los datos varia según los datos.

Quest Spotight Oracle DB & OS Windows/Linux

2019-05-17T10:26:00.003-07:00

Con Quest Spotlight puedes monitirear el estado actual de todos los componentes que conforman la base de datos asi como como los componentes del sistema operativo.

Spotlight es una gran herramienta para de forma proactiva ver el estado actual del rendimiento de nuestras bases de datos en entornos Single o RAC. Como sabemos las base de datos funcionan sobre un host con sistema operativo del cual tambien se depende que el motor de base de datos tenga un buen rendimiento, por ello es que el Spotlight tambien puede monitorear el rendimiento del host para los sistemas operativos Windows, Linux y Unix.

Spotlight Oracle
Spotlight Oracle Data Guard
Spotlight Oracle RAC
Spotlight UNIX
Spotlight Windows

SPOTLIGHT Oracle

Cuando monitoreamos la base de datos Oracle, vemos en tiempo real el comportamiento de todos los componentes de procesos y memoria que conforman la arquitectura de base de datos Oracle.

Componentes de base de datos

Consola de monitor Spotlight Oracle

En la pantalla Spotlight Oracle vemos varios paneles que muestran información de los componentes de la arquitectura de base de datos, estos son:

Service Panel: El panel de Servicio indica el estado de las comunicaciones de Oracle entre el cliente y el servidor.
Host Panel: El panel del host muestra las siguientes métricas para la máquina que reside la base de datos Oracle como CPU Usage, Run Queue, etc.
Server Processes: Indica el estado de los procesos del servidor de Oracle que realizan actividades de la base de datos en nombre de los usuarios finales y que median las conexiones de la base de datos.
SGA Panel: Muestra detalles de áreas de memoria específicas dentro del área global del sistema (SGA). El SGA es un área de la memoria de proceso compartida o común que se utiliza para almacenar en caché los datos, sentencias de SQL, procedimientos y otras estructuras utilizadas con frecuencia.
Background Processes Panel: Monitorea los procesos Database Writer (DBWRn), The Recovery Writer (RVWRn), The Log Writer (LGWRn), The Archiver (ARCHn), Data Guard Overhead and Apply Lag y Predictive Diagnostics.
Disk Storage Panel: Comportamiento de la base de datos con el disco.

Cada unos de estos componentes tienen métricas predefinidos y dependiendo el valor capturado el color varia indicando una novedad, siendo verde normal, celeste mas arriba de lo normal, amarillo fuera de los normal, naranja casi critico y rojo critico. Estas métricas son personalizables ya que que una base de datos no es igual a otra. Por ejemplo no vamos aplicar la misma métrica de BlockRead una base de datos OLTP que a una OLAP.

En este ejemplo podemos ver como podemos editar la métrica SQL Parse,

SPOTLIGHT Windows

Aqui vamos a monitorea los componentes asociados al sistema operativo como memoria, procesador, disco, E/S, red entre otros.

Arquitectura de componentes de un sistema Windows

Consola de monitor Spotlight Windows

En la pantalla Spotlight Windows vemos varios paneles que muestran información de los componentes de la arquitectura de base de datos, estos son:

System Panel: Muestra información sobre el sistema operativo de la máquina a la que está conectado actualmente.

Network Panel: Panel: muestra el número total de usuarios conectados y la velocidad a la que el sistema envía y recibe los paquetes (Usuarios de redes de Windows: muestra la cantidad de clientes conectados a este sistema)
Event Log: Proporciona un enlace al desglose del registro de eventos: Le alerta sobre los elementos que han alcanzado el estado de alarma según su configuración de alarmas del registro de eventos. El icono del botón muestra el número de alarmas del registro de eventos que se han activado pero que aún no se han eliminado. Puede reconocer y borrar alarmas a través del registro de eventos desplegable.
CPU Panel: Muestra información de procesador y carga para el sistema Windows: CPUs, Speed, Type, Uptime, Total CPU Used, Threads
Memory Panel: Muestra información detallada sobre el sistema de Windows .: RAM total: muestra la cantidad de RAM real (memoria de acceso aleatorio) en la máquina. Libre (memoria física): muestra la cantidad de memoria física disponible para las aplicaciones. Pct libre (memoria física): muestra el porcentaje de memoria física que está actualmente disponible para las aplicaciones.
Paging Files Panel: Muestra información sobre el espacio de paginación para todos los archivos de paginación activos en el sistema.
Disks Panel: Muestra información para cada disco lógico en el sistema : La letra de unidad, El tamaño , el disco (MB), El espacio utilizado en el disco (MB).
Files Panel: Muestra la cantidad de archivos cuyo tamaño de archivo está siendo rastreado actualmente por Spotlight en Windows.

En cada uno de los componentes del panel podemos ver mas a detalle :

Actividad el Disco Físico

Actividad el Disco Lógicos

Actividad el Disco Fisico/Lógico

En la actividad del disco podemos ver el comportamiento tanto los discos lógicos como físicos. Dentro de este comportamiento vemos metricas como Disk Read, Disk Write, Disk IO, Disk Load, Disk Transfer Time.

Como vemos el Spotlight es uan herramiente muy útil no solo para motores de base de datos oracle, sino para servidores windows, linux y unix. Asi podemos ver un comportamiento en conjunto de todos los sistemas que conformar componen nuestro servicios de aplicaciones y base de datos.

Mejora el rendimiento PL/SQL con PLSQL_CODE_TYPE=NATIVE

2018-12-18T11:20:00.001-08:00

En la base de datos Oracle desde la version 9i y 10g se incluyo el tipo de compilación Native, pero su implementación requería de la ayuda de un DBA para la incorporación de un compilador C externo y la configuración del repositorio donde se almacenarían estas compilaciones.

Interpreted: El codigo PL/SQL en su compilación es almacenado de forma intermedia y es necesario interpretarlo cada vez que se ejecute. Esta es una compilación no nativa del motor de base de datos la cual se almacena de forma intermedia.

Native: el codigo PL / SQL en su compilación es almacenado en un código nativo que no necesita ser interpretado en tiempo de ejecución. En comparación, el código que no se compila de forma nativa se almacena en una forma intermedia que debe interpretarse en tiempo de ejecución.

Desde la version 11g el tipo Native se simplifcó a tal punto que es implementado a nivel del sistema o sesión. El repositorio se lo incluyó dentro de la base de datos.

Debido a que el código compilado de forma nativa no necesita ser interpretado en tiempo de ejecución, la expectativa es que se ejecutará más rápido. Sin embargo, la compilación nativa solo se aplica al código de procedimiento en una unidad de programa PL/SQL, no a cualquier sentencia de SQL. Como resultado, las mejoras en el rendimiento que se deben realizar realmente dependen de la cantidad de código de procedimiento existente en relación con el código SQL. Como mínimo, una unidad de programa compilada de forma nativa que sea SQL pesada debe ejecutarse al menos tan rápido como su contraparte no compilada de forma nativa. A medida que aumenta el volumen de código de procedimiento, más rápido debería ser el código compilado de forma nativa.

Como se indico este parametro puede ser modificado a nivel del sistema "Alter system" o a nivel de la sesion "Alter session", cabe indicar que el parámetro por defecto de PLSQL_CODE_TYPE es "INTERPRETED", podemos cambiar el parametro en cualquier momento del tiempo, los PLSQL compilados antes de cambiar el parametro quedaran con el tipo que se compiló, para actualizar al tipo del parámetro establecido se deberá recompilar el PLSQL.

Ahora si explicado, realizaremos un ejemplo para comprar rendimientos. Vamos a crear dos funciones con el mismo contenido F_INTERPRETED_1 y F_NATIVE_1 pero se se compilaran con tipo Interpreted y Native respectivamente.

CREATE TABLE DATOS_TEST AS SELECT * FROM ALL_OBJECTS WHERE ROWNUM<=50;

--Objetos PL/SQL INTERPRETED

alter session set plsql_code_type=interpreted;

CREATE OR REPLACE FUNCTION F_INTERPRETED_1 (P_VALOR NUMBER,P_VALOR_2 NUMBER)RETURN NUMBER IS

L_VALOR_N NUMBER;

L_VALOR_PROM_N NUMBER;

BEGIN

for i in 1..P_VALOR_2 loop

L_VALOR_N:=L_VALOR_N+I;

L_VALOR_PROM_N:=L_VALOR_N/I;

end loop;

RETURN L_VALOR_PROM_N;

END;

CREATE OR REPLACE FUNCTION F_INTERPRETED_2 RETURN NUMBER IS

L_VALOR_N NUMBER;

L_VALOR_N2 NUMBER;

BEGIN

FOR C_DAT IN (SELECT * FROM DATOS_TEST)LOOP

IF MOD(C_DAT.OBJECT_ID,2)=0 THEN

L_VALOR_N:=L_VALOR_N+C_DAT.OBJECT_ID+ C_DAT.DATA_OBJECT_ID;

ELSE

L_VALOR_N:=L_VALOR_N+C_DAT.OBJECT_ID+ C_DAT.DATA_OBJECT_ID-2;

END IF;

IF C_DAT.OBJECT_NAME LIKE '%AS%' THEN

L_VALOR_N:=L_VALOR_N-5;

END IF;

IF MOD(C_DAT.DATA_OBJECT_ID,2)=0 THEN

SELECT AVG(OBJECT_ID) INTO L_VALOR_N2 FROM DATOS_TEST;

L_VALOR_N:=L_VALOR_N+L_VALOR_N2;

ELSE

SELECT MIN(OBJECT_ID) INTO L_VALOR_N2 FROM DATOS_TEST;

L_VALOR_N:=L_VALOR_N-L_VALOR_N2;

END IF;

END LOOP;

RETURN L_VALOR_N;

END;

--Objetos PL/SQL NATIVE

alter session set plsql_code_type=native;

CREATE OR REPLACE FUNCTION F_NATIVE_1 (P_VALOR INTEGER,P_VALOR_2 INTEGER)RETURN NUMBER IS

L_VALOR_N NUMBER;

L_VALOR_PROM_N NUMBER;

BEGIN

for i in 1..P_VALOR_2 loop

L_VALOR_N:=L_VALOR_N+I;

L_VALOR_PROM_N:=L_VALOR_N/I;

end loop;

RETURN L_VALOR_PROM_N;

END;

CREATE OR REPLACE FUNCTION F_NATIVE_2 RETURN NUMBER IS

L_VALOR_N NUMBER;

L_VALOR_N2 NUMBER;

BEGIN

for c_dat in (select * from datos_test)loop

if mod(c_Dat.OBJECT_ID,2)=0 then

L_VALOR_N:=L_VALOR_N+c_dat.OBJECT_ID+ c_dat.DATA_OBJECT_ID;

else

L_VALOR_N:=L_VALOR_N+c_dat.OBJECT_ID+ c_dat.DATA_OBJECT_ID-2;

end if;

if c_Dat.OBJECT_NAME like '%AS%' then

L_VALOR_N:=L_VALOR_N-5;

end if;

if mod(c_Dat.DATA_OBJECT_ID,2)=0 then

select avg(OBJECT_ID) into L_VALOR_N2 from datos_test;

L_VALOR_N:=L_VALOR_N+L_VALOR_N2;

else

select min(OBJECT_ID) into L_VALOR_N2 from datos_test;

L_VALOR_N:=L_VALOR_N-L_VALOR_N2;

end if;

end loop;

RETURN L_VALOR_N;

END;

Veamos ahora como quedo registro en la base de datos con el SQL "select name, type,plsql_code_type from USER_PLSQL_OBJECT_SETTINGS"

NAME	TYPE	PLSQL_CODE_TYPE
F_INTERPRETED_1	FUNCTION	INTERPRETED
F_INTERPRETED_2	FUNCTION	INTERPRETED
F_NATIVE_1	FUNCTION	NATIVE
F_NATIVE_2	FUNCTION	NATIVE

Como vemos se han copilado la funciones e identificamos que se compilaron con como INTERPETRED y NATIVE.

Ahora realizaremos el llamado a la funciones para ver los tiempos de ejecución.

Comparación F_INTERPRETED_1 con F_NATIVE_1

set timing on

declare

l_val integer;

begin

for n in 1..2000 loop

l_val:=F_INTERPRETED_1 (12,100000);

end loop;

end;

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:18.64

declare

l_val integer;

begin

for n in 1..2000 loop

l_val:=F_NATIVE_1 (12,100000);

end loop;

end;

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:14.12

Comparación F_INTERPRETED_2 con F_NATIVE_2

set timing on

declare

l_val integer;

begin

for n in 1..9000 loop

l_val:=F_INTERPRETED_2;

end loop;

end;

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:21.46

declare

l_val integer;

begin

for n in 1..9000 loop

l_val:=F_NATIVE_2;

end loop;

end;

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:19.72

A simple vista los tiempos de ejecución disminuyen con la compilación tipo NATIVE. Ahora veamos el comparativo de la compilación INTERPRETED VS NATIVE.

FUNCION		Tiempo Ejecucion Seg.		Reducción
INTERPRETED	NATIVE	INTERPRETED	NATIVE	Segundos	%
F_INTERPRETED_1	F_NATIVE_1	18,64	14,12	4,52	24,25
F_INTERPRETED_2	F_NATIVE_2	21,46	19,72	1,74	8,11

Como vemos en el comparativo, el tiempo de ejecución se reduce en la ejecución de los PLSQL compilados del tipo NATIVE.

La conclusión de este ejercicio que vamos a obtener un mejor rendimiento de nuestros PL/SQL si los compilamos de forma nativa. Para aplicar el cambio del parametro de forma global debemos configurar el parametro del system:

ALTER SYSTEM SET PLSQL_CODE_TYPE=NATIVE SCOPE=BOTH;

Deben de tener en cuenta que para que su código anterior al cambio del parametro quede establecido con el nuevo parámetro debemos de compilara de nuevo nuestros objetos PL/SQL.

Referencias:
https://docs.oracle.com/database/121/REFRN/GUID-19CE54C7-6B2A-4F3E-92C5-D30AFCD01E3F.htm#REFRN10253
https://logicalread.com/oracle-11g-native-compilation-mc02/#.XBkN2c2QyCg

Oracle Lage Page Support - Para windows

2017-09-18T10:32:00.001-07:00

Saludos,

Para los que se preguntan, que parametros del sistema operativo windows podemos modificar para mejorar el rendimiento de nuestras base de datos, pues aqui les va uno:

Large Page Support:
Large Pager suport (LPS) o Soporte de Paginas Largas es una caracteristica implementada para versiones 10.1 o superior para versiones en sistemas operativos windows server 2003 o superior, esta caracteristica permite a la instancia de base de datos Oracle poder soportar manejo de paginas grandes en memoria. Cuando habilitamos LPS las n Cpu's podran acceder a el buffer de la base de datos Oracle en la RAM mas rapidamente, como lo hace? en si es por que los procesadores en vez de usar memorias pequeñas que van desde 4k, se le dice que maneje tamañoas de 2Mb a 4Mb de buffer para las base de datos.

Como se habilita?
Tenermos que seguir los siguientes pasos:

Habilitar en el regedit ORA_LPENABLE
Otorgar el permiso al usuario "Lock pages in memory"
Reiniciar todo el servidor

1 Habilitar en el regedit ORA_LPENABLE
Abrimos el regedit y nos vamos a la seccion donde esta el home de la instalacion de la base de datos Oracle HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDB12Home2 y agregamos un nuevo valor de cadena REG_SZ de nombre ORA_LPENABLE y valor 1

Nota: Para saber con seguirad donde agregar el parametro ORA_LPENABLE puden ver la dirección viendo el archivo oracle.key ubicado en ORACLE_HOME\BIN\oracle.key

2 Otorgar el permiso al usuario "Lock pages in memory"

En el panel de control hacemos click a Local Security Policy o Directiva de Seguirdad Local, de ahi ir a "Security Settings\Local Security\User Rights Assignment"

Hacer bloble click en Lock pages in memory, en la pestaña de "Local Security Settings window" damos click en add User o Group.Seleccionamos el usuario de inicio del servicio Oracle. En mi caso yo realice la instalacion con el usuario Oracle, otros podran haber establceido el system.

Nota: El usuario que vamos a buscar es con quien configuramos el inicio de sesion de los servicios Oracle, por lo general en la instalación usamos el usuario oracle, pero pueden verificar viendo en la seccion de los servicio "services.msc" buscamos Oracle y vemos quien es el que inicia la sesion.

3 Reiniciar todo el servidor
Reiniciamos todo el servidor

Y listo, hemos configurado el LPS, con esto se ba a obtener un mejor rendimiento de nuestras instancias Oracle que corren sobre Windows, sobre todo en entornos de instancias de varios GB.

Precauciones:
No usar el parametro de base de datos LOCK_SGA cuando el LPS esta habilitado ya que esto genera error en el inicio de la base de datos.

Monitoreo de indices

2012-03-30T18:40:00.000-07:00

El uso de los índices es de gran ayuda para mejorar el rendimiento de la aplicaciones el momento de filtrar los datos, pero, como podemos saber si los índices que tenemos en nuestra base de datos están verdaderamente siendo usados por nuestras aplicaciones?

¿Porque puede pasar que los índices no se usen?

La aplicación sea adquirida y no desarrollada. Esto implica que ya viene con sus índices pre-establecidos.

El funcionamiento o políticas de la empresa cambien lo que también afecta al funcionamiento de nuestras aplicaciones.

La creación de un nuevo índice sea más óptima en costo con el que ya existía. Ya que el CBO tomará el índice con menor costo para su planificación.

Para todos estos casos, esto nos generaría tener índices inútiles que ocupan espacio y hacen más costos las operaciones de insert, delete y update.

Oracle introdujo desde la versión 10g el monitoreo de índices lo cual nos permite determinar que índices se usan o no se usan.

Acción	Sentencia
Activar monitoreo	ALTER INDEX indice_01 MONITORING USAGE;
Desactivar monitoreo	ALTER INDEX indice_01 NOMONITORING USAGE;

EJEMPLO

/*Creamos una tabla*/
CREATE TABLE mi_tablas
AS SELECT * FROM all_tables;

/*Creamos el indice*/
CREATE INDEX mi_indice_01 ON mi_tablas (table_name);

Veamos la información

/*Vemos la informacion del indice*/
SELECT iu.name owner, io.name index_name, T.name table_name,
       DECODE(bitand(i.flags, 65536), 0, 'NO', 'YES') MONITORING,
       DECODE(bitand(ou.flags, 1), 0, 'NO', NULL, NULL, 'YES') used,
       ou.start_monitoring,
       ou.end_monitoring
FROM   sys.obj$ io,
        sys.obj$ T,
        sys.ind$ i,
        sys.USER$ iu,
        sys.USER$ tu,
        sys.object_usage ou,
        dba_segments s
WHERE io.owner# = iu.USER#
AND    i.obj# = io.obj#
AND    io.obj# = ou.obj# (+)
AND    T.obj# = i.bo#
AND    T.owner# = Tu.USER#
AND    i.type# not in (4, 8, 9)
AND    iu.name = user --Usuario dueño del objeto
AND    bitand(io.flags, 128) <> 128
AND     S.SEGMENT_NAME=io.name
AND     S.OWNER=iu.name

Descripción de las columnas
OWNER	Dueño del índice
INDEX_NAME	Nombre del índice
TABLE_NAME	Tabla asociada al índice
MONITORING	YES/NO si está o no siendo monitoreado
USED	YES/NO si fue usado o no el índice
START_MONITORING	Fecha desde que inició el monitoreo
END_MONITORING	Fecha que finalizó el monitoreo

Vemos que en la columna MONITORING el valor es NO lo cual indica que el índice no está siendo monitoreado.

Ahora activemos el monitoreo del índice.

/*Activar el monitoreo del indice*/
ALTER INDEX mi_indice_01 MONITORING USAGE;

Ejecutamos la consulta para ver información del índice

Aquí vemos que en la columna MONITORING ya indica que el índice está siendo siendo monitoreado, en la columna USED en cambio indica NO lo cual indica que desde que se activo el monitoreo no se ha hecho uso del índice.

Ahora ejecutemos una consulta usando como filtro el campo de la tabla asociada al índice.

/*Selecionamos los datos usando el indice*/
select * from mi_tablas where table_name='DATO';

Veamos la consulta de información del índice.

Ya con esto vemos que cuando ejecutamos una consulta que hace uso del campo asociado al índice el monitoreo registra que el índice fue usado de tal manera que ya podemos determinar si utilizo o no.

El tiempo que monitoreemos los índices dependerá de la frecuencia de uso de los objetos, como por ejemplo si tenemos consultas que solo se hacen uso a fines, en este escenario debemos esperar hasta que todos los procesos o consultas se ejecuten durante el periodo. Con esto garantizaremos que si eliminamos el índice estemos 100% seguro de que no se use por ningún proceso o consulta.

PCTFREE y PCTUSED

2011-12-27T17:55:00.001-08:00

PCTFREE y PCTUSED son parámetros que nos permite especificar comportamientos de almacenamiento de los bloques de datos. Se los configura a nivel de tabla durante la creación o posterior a la creación.
PCFREE: Especifica cuál es la cantidad de espacio que se deberá dejar libre en el bloque para las futuras actualizaciones que se vayan a realizar en los registros previamente almacenados en el bloque. Esto quiere decir que Oracle insertará registros en el bloque hasta que se llegue al % especificado en el PCFREE para las futuras actualizaciones de los registros que se encuentran en el bloque y tamaño puede aumentar, con esto se evita la migración de datos.
PCTUSED: Especifica cuanto espacio disponible debe de haber para realizar nuevas inserciones después de que el bloque llego a su máxima capacidad definida por el PCTFREE. Supongamos que tenemos un PCTFREE de 10% para una tabla y un PCTUSED de 60%, cuando realizamos inserciones se realizaran hasta llegar a 10% libre del bloque, pero que pasa después de que realizamos borrados de filas, este bloque queda disponible para inserciones de nuevos registros? Pues el bloque se pondrá disponible para inserciones hasta que se llegue un valor usado definido por el PCTUSED, es decir cuando llegue a un 60%

En una base de datos es muy importante poder establecer este parámetro al momento de crear una tabla o tener en cuenta poder modificarlo según la evaluación que le demos a la tabla. Que deberíamos considerar para definir el PCTFREE? Según el tipo de transacciones SQL(Insert, delete o update) que se le aplique a la tabla.

Si es una tabla log, histórica que solo se aplican transacciones Insert debería tener un PCTFREE de 0%.
Si es una tabla que sirve como data warehouse el PCTFREE deberá configurarse con 0%.
Si es una tabla transaccional que sufre de inserts, update y delete deberá configurarse un PCTFREE superior a 0%.

En el caso de tablas transaccionales que sufren de inserts, updates y deletes el PCTFREE deberá configurarse con un valor de acuerdo a un previo análisis que dependerá de la cantidad de columnas que se actualizaran posteriores a la inserción y del tipo de dato. Supongamos que tenemos una estructura de "Analisis_Credito" el cual es llenada por un analista de ventas para poder dar crédito a un cliente, en principio esta se llena con información básica que luego es actualizada por un flujo de trabajo que va desde el análisis, verificación y aprobación durante este flujo se actualizaron datos de comentarios, referencias, teléfono y otros como vemos un %0% de esta información es llenada posterior a la creación del registro, por lo que se debería considerar un PCTFREE de 60%.
Cuando se crea la tabla por default el PCTFREE es de 10%, entonces si ya sabemos que tipo de información se va almacenar, podemos establecer al momento o después de la crear la tabla.
EJEMPLO
Supongamos que tenemos que realizar una estructura que almacene todos los días el listado de los objetos de base de datos, esta tabla sólo se ingresaran datos, no se realizarán actualizaciones ni borrados, por lo que el PCTFREE debe de ser 0%. Crearemos una tabla HIS_OBJETOS_PCTFREE_0 (Definiremos 0%) y HIS_OBJETOS (no definimos nada).

SQL> --PCTFREE 0
SQL> create table HIS_OBJETOS_PCTFREE_0 PCTFREE 0
2 as
3 select * from all_objects;
Tabla creada.
SQL>
SQL> --PCTFREE SIN DEFINIR (Defecto 10%)
SQL> create table HIS_OBJETOS
2 as
3 select * from all_objects;
Tabla creada.
SQL>
SQL> select segment_name, bytes/1024/1024 mb, blocks
2 from dba_segments where segment_name like 'HIS_OBJETOS%'
3 and segment_type='TABLE';
-------------------------------------
SEGMENT_NAME MB BLOCKS
-------------------------------------
HIS_OBJETOS_PCTFREE_0 6 768

HIS_OBJETOS 7 896

Observemos que los MB de la tabla HIS_OBJETOS_PCTFREE_0 es de 6 y los bloques usados de 768, mientras que HIS_OBJETOS los MB usados es de 6 y los bloques 896.
Imaginen estos valores con tablas que tienen millones de registros, la cantidad de espacio que optimizaríamos además de mejorar el número de aciertos en consultas ya que entraran mas registros en un bloque con PCTFREE de 0 que uno superior a cero.

Particionamiento

2011-11-14T08:39:00.001-08:00

El Particionamiento es una técnica utilizada para distribuir el almacenamiento de tablas, índices y vistas materializadas en más de estructura de almacenamiento que se administren de forma independiente. Esta división de almacenamiento es lo que llamamos Particionamiento.

¿Cuál es el beneficio de particionar?
Ya hemos escuchado el dicho "Divide y vencerás", pues eso es lo que lograremos con el Particionamiento, logrando mejorar el rendimiento, disponibilidad y mejor manejabilidad de los datos en la base de datos.

Oracle ofrece esta utilidad desde la versión 8i y desde que se introdujo con cada nueva versión han aplicado mejoras y nuevas técnicas de particionamiento.

En la imagen se muestra la tabla 1 que no esta particionada y la tabla 2 que se muestra particionada. La tabla 1 tiene se guarda en una sola partición mientras que la tabla 2 se almacena en tres particiones que agrupan la información por mes.

Este particionamiento se lo realiza utilizando una clave de particionado (Partitioning Key) que es la que identifica a que parte de la partición se almacenara el dato, en el ejemplo anterior la tabla particionada usa como clave de particionamiento el campo MES para direccionar a que partición se almacenara el dato. Esta llave sir durante la inserción o actualización del registro.

Oracle ofrece tres técnicas básicas de particionamiento según su la necesidad de la organización y es totalmente transparente para las aplicaciones que accedan a los datos y estas son:

Range Partitioning.- Esta técnica usa un rango de valores para determinar a qué partición de datos se almacenara el registro. Este es el método más común de particionar los datos y se usa a menudo como clave de partición los campos fechas.

Método de particionamiento Range

List Partitioning.- Para esta técnica se define una lista de valores para la clave de particionado, la ventaja de este particionamiento es que podemos agrupar y organizar los datos que tengan una relación común, como por ejemplo podemos definir como clave de partición el dato de PAIS.

Método de particionamiento List

Hash Partitioning.- Esta se basa en un algoritmo hash sobre la columna que se definió como clave de partición, esto es una técnica que distribuye a los datos en las particiones de manera equitativa y es usado cuando no se encuentra claros criterios de particionados.

Método de particionamiento Hash

A partir de esta tres técnica básica se definieron dos técnica mas que no son nada más que la combinación de las anteriores y estas son:

Composite Partitioning.- Esta es una técnica que se compone de las tres técnicas básicas anteriormente mencionadas, con esto podemos seleccionar para un primer nivel de partición una técnica y para el otro nivel de partición otra técnica, las combinaciones que podemos realizar son:

Range-Range Partitioning
Range-Hash Partitioning
Range-List Partitioning
List-Range Partitioning
List-Hash Partitioning

List-List Partitioning

Método de particionamiento Composite

En la imagen que se muestra dos ejemplos particionamiento compuesto Range-Hash y Range-List.

Ejemplo del uso de la partición Range
Como describimos hace un momento, esta técnica se basa en que se asigna la partición según el rango de valores en que coincida la clave de partición. Supongamos que tenemos una tabla de ventas con información del 2009 al 2011 y deseamos realizar el particionamiento tipo rango utilizando como llave la fecha, definiendo para cada año una partición.

CREATE TABLE pos_ventas
(
id_venta NUMBER,
fecha DATE,
valor NUMBER
)
PARTITION BY RANGE (fecha)
(
PARTITION pos_ventas_p1 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')) TABLESPACE ts_datos2009,
PARTITION pos_ventas_p2 VALUES LESS THAN (TO_DATE('01/01/2011','DD/MM/YYYY')) TABLESPACE ts_datos2010,
PARTITION pos_ventas_p3 VALUES LESS THAN (TO_DATE('01/01/2012','DD/MM/YYYY')) TABLESPACE ts_datos2011
);

Lo que realizamos es crear particiones por años, para esto seleccionamos como clave de particionamiento al campo fecha y se definió para cada partición un tablespace, con esto logramos una clara distribución de los datos.
Cada una de estas particiones se las puede administrar de manera independiente, ¿a que me refiero? Que podemos trabajarlos como estructuras independientes como si fueran tablas diferentes. Podemos establece definiciones de storage para cada partición, borrar una partición (Solo afectara a los datos contenidos dentro de la partición) y truncar la partición.
Si queremos ver que datos están contenidos en alguna de las particiones de la tabla Oracle nos proporciona la forma de hacerlo usando la clausula PARTITION(Nombre de la partición):

SELECT * FROM pos_ventas PARTITION(pos_ventas_p1);

Ejemplo del uso de la partición List
Este método se caracteriza por que la clave de particionamiento se define por una lista de valores. Tenemos una tabla de ventas, pero queremos realizar la división por el código de almacén de tal manera que tengamos los agrupemos por sector norte, sur y centro.

CREATE TABLE pos_ventas
(
id_venta NUMBER,
id_almacen VARCHAR2(5),
fecha DATE,
valor NUMBER
)
PARTITION BY LIST (id_almacen)
(
PARTITION pos_norte VALUES ('10','20','30') TABLESPACE ts_datos_nor,
PARTITION pos_sur VALUES ('40','50') TABLESPACE ts_datos_sur,
PARTITION pos_centro VALUES ('90','120','130','150') TABLESPACE ts_datos_cen
);

Vemos que las agrupaciones de las particiones dependerán de la lista de datos que se defina en la sección VALUES y que también podemos definir tablespaces o no para cada una de las particiones.

Ejemplo del uso de la partición Hash
Esta es una forma de distribución equitativa de datos definiendo un campo como llave de particionado para que la función Hash nos indique a que partición irá el dato.
Tenemos la tabla detalle de ventas y vamos a usar id_producto como clave de particionamiento para la función hash.

CREATE TABLE pos_ventas_detalles
(
id_venta NUMBER,
id_detalle_venta NUMBER,
id_producto VARCHAR2(20),
cantidad number,
valor number
)
PARTITION BY HASH (id_producto) PARTITIONS 4;

De esta manera definimos que se crearan hasta 4 particiones para la tabla POS_VENTAS_DETALLES y la clave de particionado esta definida por el campo ID_PRODUCTO, de esta manera distribuiremos de manera equitativa los datos. Con esto de manera implícita se crearan las particiones hasta un máximo de 4 particiones, pero también podemos especificar de manera especifica el nombre de las particiones con sus respectivos tablespaces.

CREATE TABLE pos_ventas_detalles
(
id_venta NUMBER,
id_detalle_venta NUMBER,
id_producto VARCHAR2(20),
cantidad number,
valor number
)
PARTITION BY HASH (id_producto)
(
PARTITION pos_vta_det_p1 TABLESPACE ts_datos_01,
PARTITION pos_vta_det_p2 TABLESPACE ts_datos_02
);

¿Cuando se necesita particionar una tabla?
Hay varios criterios a considerar para determinar si se necesita particionar una tabla:

El tamaño de la tabla supera los 2G de tamaño.
La tabla contiene información histórica que tiende a ser modificada, un ejemplo es en la facturación donde se genera mucha información por año y mucha de esta información se mantiene para análisis de los históricos, dicha información podríamos dividirla en dos particiones una histórica y otra actual.

En algunas ocasiones me ha tocado revisar base de datos cuyas técnicas de particionamiento es el uso de programación para traspasar datos de una tabla transaccional a una histórica y esta dependerá de que una persona sea la encargada de ejecutar el proceso, lo cual es un desperdicio ya que con el uso del particionamiento de Oracle esta tarea se la define una vez y es transparente para los usuarios. Solo tenemos que realizar un buen plan de distribución para aplicarla en nuestro particionamiento.

¿Cuál es la mejor técnica de particionamiento?
No hay repuesta especifica para esta pregunta, ya que se tomará la que mejor se adapte a nuestro requerimientos es como cuando jugamos al golf(solo en play station lo he jugado) seleccionamos el palo que se adapte al terreno en el que nos encontramos.

¿Cómo reemplazar el uso de Sinónimos?

2011-11-04T15:22:00.001-07:00

Una de las desventajas del uso de sinónimos privados es que ocupa mas espacio en el diccionario del SYS y en memoria, ya que para cada usuario de base de datos se deberá crear los sinónimos privados para que este pueda ver los objetos sin necesidad de escribir la ruta absoluta.
¿Ruta absoluta? Con esto quiero indicar que no debemos anteponer el nombre del esquema propietario seguido de un punto y el nombre del objeto, pero para entenderlo mejor veamos un ejemplo.
Vamos a crear una tabla BD_USUARIOS con el usuario SISOWN.

C:\>sqlplus sisown@orcl
Introduzca la contrase±a:
SQL> create table bd_usuarios as select * from dba_users;
Tabla creada.
SQL> select count(*)cantidad from bd_usuarios;
CANTIDAD
----------
23

Creamos al usuario PRUEBA_1 con los permisos de consulta a la tabla BD_USUARIOS.

SQL> create user prueba_1 identified by hola;
Usuario creado.
SQL> grant connect to prueba_1;
Concesi¾n terminada correctamente.
SQL> grant create synonym to prueba_1;
Concesi¾n terminada correctamente.
SQL> grant select on bd_usuarios to prueba_1;
Concesi¾n terminada correctamente.
SQL> connect prueba_1/hola
Conectado.
SQL> select count(*)cantidad from bd_usuarios;
select count(*)cantidad from bd_usuarios
*
ERROR en lÝnea 1:
ORA-00942: la tabla o vista no existe
SQL> select count(*)cantidad from sisown.bd_usuarios;
CANTIDAD
----------
23

Al conectarnos con PRUEBA_1 y realizar la consulta a la tabla BD_USUARIOS nos da error de que la tabla o vista no existe ya que a nivel de usuario PRUEBA_1 no existe la tabla, pero en la segunda consulta lo realizamos anteponiendo el nombre del usuario propietario SISOWN.BD_USUARIOS y no nos da error la consulta.

El error se presenta por que al realizar la consulta lo primero que se hace es ver si la tabla existe a nivel en el esquema PRUEBA_1 y al no encontrarlo se produce el error.
Para evitar ubicar el nombre del esquema antes del objeto en toda la programación tenemos 3 opciones:

Utilizar sinónimos privados
Utilizar sinónimos públicos
Cambiar la variable de sesión CURRENT_SCHEMA

Utilizar sinónimos privados
Los sinónimos privados son objetos que pertenecen a cada usuario de la base y son alias que le damos a los objetos de base de datos y nos evitaría llamar a los objeto por rutas absolutas.

C:\>sqlplus prueba_1@orcl
Introduzca la contrase±a:
SQL> select count(*)cantidad from bd_usuarios;
select count(*)cantidad from bd_usuarios
*
ERROR en lÝnea 1:
ORA-00942: la tabla o vista no existe
SQL> create or replace synonym bd_usuarios for sisown.bd_usuarios;
Sin¾nimo creado.
SQL> select count(*)cantidad from bd_usuarios;
CANTIDAD
----------
23

Vemos como creando el sinónimo privado ya no se necesita de ubicar ruta absoluta (Esquema y objetos) para poder llamar a la tabla BD_USUARIOS. Esto deberá definirse para cada usuario de todos los objetos (Tablas, procedimientos, vistas, secuencias, etc) del usuario propietario

Utilizar sinónimos públicos
Los sinónimos públicos a diferencias de los privados es que son de dominio público, es decir que solo se necesita definir una sola vez y todos para que sean visto por los demás usuarios, la ventaja de esto es que no debemos definirlo por cada usuario sino una sola vez.

C:\>sqlplus sisown@orcl
Introduzca la contrase±a:
SQL> create or replace public synonym bd_usuarios for sisown.bd_usuarios;
Sin¾nimo creado.
SQL> connect prueba_1/hola
Conectado.
SQL> select count(*)cantidad from bd_usuarios;
CANTIDAD
----------
23

Cambiar la variable de sesión CURRENT_SCHEMA
Esta manera es más eficiente que las dos anteriores, ya que no se necesita de crear objetos adicionales, solo se debe de indicar a la sesión de usuario que esquema de datos se va usar(Es decir en donde primero debe de buscar el objeto). La variable de sesión que modificamos es la CURRENT_SCHEMA la cual indica hacia que esquema se debe de buscar los objetos. Por default el CURRENT_SCHEMA es igual al nombre de la sesión de usuario.

C:\>sqlplus prueba_1@orcl
Introduzca la contrase±a:
SQL> select count(*)cantidad from bd_usuarios;
select count(*)cantidad from bd_usuarios
*
ERROR en lÝnea 1:
ORA-00942: la tabla o vista no existe
SQL> alter session set current_schema=SISOWN;
Sesi¾n modificada.
SQL> select count(*)cantidad from bd_usuarios;
CANTIDAD
----------
23

Si me preguntaran cual es la mejor método a implementar, pues les daría el siguiente orden: Cambiar CURRENT_SCHEMA, Creación de sinónimos públicos y creación de sinónimos.

¿Por qué es mejor alterar el CURRENT_SCHEMA?
Es mejor porque no se necesita la creación de objetos en el diccionario del SYS, además de que en la memos ocupación de la Library Cache(Ya que por cada usuario no se cargaria metadata del objeto sinonimo) lo que es una ventaja por que dejamos más espacio disponible en la shared pool.

Si tenemos un ambiente de base de datos con 100 usuarios y esos usuario hacen uso de un promedio de 3,000 objetos de un esquema propietario (Tablas, vistas, procedimientos, secuencias, etc), para esto tendriamos que crear 300,000 sinonimos y considerar que cada vez que se cree un usuario el tiempo que nos tomará crear todos los sinonimos para este usuario.

Ademas otro punto en contra de los sinónimos es que cada vez que realices un cambio en los objetos del esquema propietario ya se estructural o de progrmación, los sinonimos relacionados a estos objeto se invalidan por lo que hay que recrearlos para evitar problemas.

¿Se puede hacer de manera automática el cambio del CURRENT_SCHEMA?
Si no deseas cambiar código de programación en tu sistema y deseas que esto se haga de manera automática en la base de datos lo que puede usar es un trigger de base de de datos AFTER LOGON:

CREATE OR REPLACE TRIGGER SISOWN.DB_CONTROL_SESION
AFTER LOGON
ON DATABASE
DECLARE
/***********************************************************
Desarrollado por : Víctor Endara
Objetivo : Cambiar de manera automática la el CURRENT_SCHEMA
************************************************************/
BEGIN
if user not in ('SYS','SYSTEM','SYSAUX','SYSMAN') then
execute immediate 'alter session set current_schema=SISOWN';
end if;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
/

TOP SQL

2011-10-19T19:52:00.001-07:00

Saludos,

En ocasiones no basta solo con realizar una buena (me refiero a que use buenas practicas SQL o que use los índices correctos) consulta SQL eficiente y cuyo costo no se alto, puede ser que durante una arranque inicial de un sistema , las consultas SQL no se comportan nada anormal ya que el rendimiento y tiempo de respuesta es eficiente, pero que pasará luego de 1, 2, 3 o 12 meses? Nos reportan los usuarios que cada mes el reporte o sistema se pone más lento?

Eso pasa en todo sistema que arranca desde cero o que se implementa nuevos módulos o funcionalidades que requieren crea tablas, agregar columnas o crear nuevas consultas. Es por ello que se necesita siempre de un constante monitoreo de dichas consultas SQL que se están ejecutando o ejecutaron en la base de datos para posterior a esto realizar un afinamiento que mejore el desempeño.

¿Cómo podemos monitorear los SQL pesados?
Podemos ayudarnos de herramientas o scripts para obtener esta información:

Oracle Enterprise Manager
TOAD SGA Trace
Oracle SQL Developer
Scripts SQL

Oracle Enterprise Manager:
El Enterprise manager proporciona una utilidad para el análisis de los TOP SQL que se han ejecutado y analizar la sentencia.

Aquí nos proporciona una lista de los SQL que se han ejecutado que tienen mayor consumo.
Esta utilidad se encuentra dentro del Enterprise Manager de Oracle Data Base.

TOAD SGA Trace
Es una utilidad integrada al TOAD que nos permite visualizar los SQL almacenados en el área SQL del Oracle. Aquí se muestra información estadística sobre los SQL ejecutados, pero ¿cómo podemos determinar los SQL pesados? yo siempre me baso en tres columnas para encontrarlos:

Hit Rate.- Es un valor porcentual que va desde 0 hasta 100. Este valor representa una relación de lecturas de bloques consistentes (Lecturas/Escrituras en la SGA, para ser mas exacto en la Buffer Cache) vs las lecturas físicas (Lecturas/Escrituras de bloques de datos en disco) que se realizan al ejecutar un SQL. Lo óptimo es que este valor este entre 85% y 100%, ya que eso nos indicaría que la sentencia trabaja más sobre los bloques de datos de la Buffer Cache que sobre los bloques de datos de los datafile almacenados en disco, si el valor retornado es menor a 85 es necesario analizar la sentencia para ver que se puede optimizar.
Buffer Gets.- Es la cantidad de bloques de datos recuperados del área Buffer Cache de la SGA para que la sentencia SQL necesitó para poder elaborarse.
Disk Reads.- Es la cantidad de bloques de datos recuperados de los datafiles para que la sentencia SQL necesitó para poder elaborarse.

Toad es una herramienta pagada muy buena herramienta, pero puedes usar la versión de prueba que puedes descargar del siguiente link Toad 10

Oracle SQL Developer

Esta es una herramienta de Oracle para desarrolladores SQL y PL/SQL y puedes descargarla de manera gratuita desde la siguiente link OracleSqlDeveloper

Esta herramienta ofrece una opción de informes relacionados con la base de datos y que incluye informes de rendimientos SQL que los listará por:

SQL Principal por CPU.- Por el consumo de CPU que realice la ejecución de esta sentencia.
SQL Principal por Ejecuciones.- Por la cantidad de veces que se ha ejecutado.
SQL Principal por Esperas.- Por el tiempo que ha demorado en ejecutarse el SQL.
SQL Principal por Lecturas de Disco.- Por la cantidad de lecturas de bloques a disco que se realiza.
SQL Principal por Obtenciones en Buffer.- Por la cantidad de lecturas de bloques a la Buffer Cache
SQL Principal por Obtenciones en Buffer/Procesos.- Por la cantidad de lecturas de bloques a la Buffer Cache con relación al procesamiento

Scripts
La manera más artesanal de verificarlo es por medio de los scripts. A continuación script que les servirá para obtener los SQL con alto consumo.

SQL con Hit Rate inferior al 85% y con mas de 1000 bloques leídos en disco, ordenados de manera ascendente por el Hit Rate

SELECT sql_text, disk_reads, buffer_gets, module,
(buffer_gets - disk_reads) / buffer_gets hit_rate
FROM v$sqlarea
WHERE buffer_gets != 0
AND (buffer_gets - disk_reads) / buffer_gets < .85
AND disk_reads > 1000
ORDER BY 4;

SQL con mas de 1000 bloques leídos en disco, ordenados de manera descendente por el campo Disk_Reads

SELECT sql_text, disk_reads, buffer_gets, module,
(buffer_gets - disk_reads) / buffer_gets hit_rate
FROM v$sqlarea
WHERE buffer_gets != 0
AND disk_reads > 1000
ORDER BY 4;

Ejecución de las Estadísticas

2011-10-17T21:11:00.001-07:00

Saludos,

Algo importante en considerar en una base de datos es mantener actualizado las estadísticas de tablas e índices. Y como me diría un amigo ¿Y esto pa que nos sirve? Sirve de mucho al CBO (Optimizador Basado en Costes) al momento de seleccionar el plan de ejecución con menor costo para ejecutar las consultas SQL.

CBO (Cost Based Optimizer)
Es un método interno del motor de base de datos que selecciona de varios planes de ejecución el menos costoso. Antes de ejecutar una sentencia SQL esta pasa por un análisis que genera varios planes de ejecución cuya selección dependerá de que costo. Este método fue introducido desde la versión 7i. A diferencia del método antecesor RBO (Optimizador Basado Reglas) el CBO busca un plan de ejecución de le represente menor costo a la base de datos. Para poder evaluar el costo de una ejecución este método se ayuda de la información de las tablas alimentadas por las estadísticas.

RBO (Rule Based Optimizer)
Este método se basa en realizar un plan de ejecución basado en las reglas de accesos y no estima costo de ejecución por lo que no necesita de las estadísticas. RBO es un método que Oracle va a desaparecer y no recomienda su uso.

¿Cómo ejecutamos las estadísticas?
Bueno, existen varias formas de ejecutar las estadisticas y a continuación se las indicaré:

ANALISIS POR OBJETO

--Estadísticas por tabla y los índices relacionados a la tabla
ANALYZE TABLE tabla COMPUTE|ESTIMATE|DELETE STATISTICS
--Estadísticas solo por tabla
ANALYZE TABLE tabla COMPUTE|ESTIMATE|DELETE STATISTICS FOR TABLE
--Estadísticas solo de los índices relacionadas a la tabla
ANALYZE TABLE tabla COMPUTE|ESTIMATE|DELETE STATISTICS FOR INDEX
--Estadísticas por índice
ANALYZE INDEX indice COMPUTE|ESTIMATE|DELETE STATISTICS
--Estadísticas por cluster
ANALYZE CLUSTER cluster COMPUTE|ESTIMATE|DELETE STATISTICS

Parámetro	Descripción	Ejemplo
COMPUTE	Calcula las estadísticas con todos los registros de la tabla	`ANALYZE TABLE tabla COMPUTE STATISTICS;`
ESTIMATE	Calcula las estadísticas con una muestra de filas basadas en cantidad o porcentaje. Es recomendable usarlo cuando la tabla es muy grande y la estadística demora mucha en realizarse.	`--Estimación por porcentaje` `ANALYZE TABLE tabla ESTIMATE STATISTICS SAMPLE porcentaje_filas PERCENT;` `--Estimación por filas` `ANALYZE TABLE tabla ESTIMATE STATISTICS SAMPLE cantidad_filas ROWS;`
DELETE	Permite el borrado de las estadísticas	`ANALYZE TABLE tabla DELETE STATISTICS;`

ANALISIS POR ESQUEMA

--Estadistica a todas las tablas de un esquema por medio de la función ----UTILITY
DBMS_UTILITY.ANALYZE_SCHEMA(
USUARIO_PROPIETARIO,
METODO_ANALISIS,
FILAS_A_ESTIMAR,
PORCENTAJE_A_ESTIMAR);

Parámetro	Descripción
USUARIO_PROPIETARIO	Es el usuario propietario de las tablas a realizar el análisis de estadísticas
METODO_ANALISIS	Tiene dos opciones: COMPUTE y ESTIMATE. Si el parámetro es COMPUTE el análisis es por todos los registros de la tabla y los siguientes dos parámetros no se usan. Si el parámetro es ESTIMATE se debe de indicar uno de los dos parámetros siguientes, si el análisis se lo realiza tomando una determinada muestra de registros.
FILAS_A_ESTIMAR	Se usa si el método a usar es ESTIMATE se define la cantidad de registros para tomar como muestra para realizar el análisis.
PORCENTAJE_A_ESTIMAR	Se usa si el método a usar es ESTIMATE se define el porcentaje de registros para tomar como muestra para realizar el análisis.

ANALISIS A TODA LA BASE

--Estadistica a todas las tablas de la base
DBMS_UTILITY.ANALYZE_DATABSE(
METODO_ANALISIS,
FILAS_A_ESTIMAR,
PORCENTAJE_A_ESTIMAR);

Parámetro	Descripción
METODO_ANALISIS	Tiene dos opciones: COMPUTE y ESTIMATE. Si el parámetro es COMPUTE el análisis es por todos los registros de la tabla y los siguientes dos parámetros no se usan. Si el parámetro es ESTIMATE se debe de indicar uno de los dos parámetros siguientes, si el análisis se lo realiza tomando una determinada muestra de registros.
FILAS_A_ESTIMAR	Se usa si el método a usar es ESTIMATE se define la cantidad de registros para tomar como muestra para realizar el análisis.
PORCENTAJE_A_ESTIMAR	Se usa si el método a usar es ESTIMATE se define el porcentaje de registros para tomar como muestra para realizar el análisis.

Ejemplo
Este ejemplo va ilustrar la forma en que las estadísticas ayudan a mejorar el método CBO para la selección del plan con menor costo.
Creamos la tabla clientes con índices por identificación y tipo_identificacion

CREATE TABLE clientes
(
id_cliente NUMBER,
identificacion VARCHAR2(20),
tipo_identificacion VARCHAR(20),
id_provincia VARCHAR2(5),
nombres VARCHAR2(60),
apellidos VARCHAR2(60),
PRIMARY KEY (id_cliente)
);

--Creamos dos indices uno por identificacion y otra por el id_provincia
CREATE INDEX ind_iden_cliente ON clientes(identificacion);

CREATE INDEX ind_id_pro_cliente ON clientes(id_provincia);

Insertamos 300 registros

Insert Into Clientes
(Id_Cliente, Identificacion, Tipo_Identificacion, Id_Provincia, Nombres, Apellidos )
Select Rownum Id_Cliente, Trim(To_Char(Rownum,'000000000')) Identificacion, Mod(Rownum,2)+1Tipo_Identificacion, Mod(Rownum,4) Id_Provincia, 'Nombre '||Rownum Nombres, 'Apellido '||Rownum Apellidos
From Dual
Connect By Level <= 300;

Commit;

Revisamos el plan de ejecución se la siguiente consulta sin la estadística ejecutada:

Select * From Clientes Where Id_Provincia='1';

Plan de ejecución con el SQLPLUS

Plan de ejecución con el TOAD

Ahora ejecutemos el plan de ejecución pero antes ejecutemos la estadística a la tabla Clientes.

ANALYZE TABLE clientes COMPUTE STATISTICS;

Plan de ejecución con el SQLPLUS

Plan de ejecución con el TOAD

Ahora vemos que el plan de ejecución se va por el indice y no por acceso full a la tabla

¿Cuando ejecutar las Estadisticas?No existe un periodo estándar la realizar la ejecución de las estadísticas, esto dependerá de la transaccionalidad y cambios en los datos que se realicen en la base, puede ser cada mes, quincena, semana o diario, en mi caso tengo programado la ejecución de las estadísticas cada quincena y en horario nocturno para que no afecte a los usuarios.

Es calro que si las estadisticas no son reales o aproximadas, el metodo CBO no estará seleccionando el plan de ejecución correcto.

Conclusión
Hemos encontrado que el CBO (Optimizador Basado en Costes) funciona de manera óptima siempre y cuando tengamos actualizados las estadísticas de tablas e índices para que al momento de elegir el plan de ejecución menos costoso este se base en estadísticas reales.

Recuperar espacio inutilizado de los segmentos SHRINK SPACE

2011-10-11T23:27:00.000-07:00

En una base de datos transaccional nos encontraremos con tablas que son afectadas con transacciones insert, update y delete. Las transacciones que nos puede dejar bloques sub-utilizados (Fragmentado) son el delete y update.

Para recuperar este espacio antes de la version 10G de Oracle las técnicas utilizadas eran:

Exportar la tabla, truncar la tabla incluido el storage y finalmente importar los datos.
Con la ayuda de DML y DDL: CREATE TABLE MI_TABLA_TMP AS SELECT * FROM MI_TABLA, DROP TABLE MI_TABLA y finalmente RENAME TABLE MI_TABLA_TMP TO MI_TABLA

Ya desde la version 10G existe la utilidad SHRINK que nos ayuda mucho con la tarea de recuperar espacio de bloques fragmentados.

Para explicar como nos va ayudar la utilidad SHRINK primero veamos una pequeña explicación de la estructura de almacenamiento y bloques de datos.

Definición de la Estructura de Almacenamiento
Primero entendamos como esta diseñada la estructura logica de alacenamiento de Oracle.

Estructura Lógica y Física de almacenamiento

Vemos que un tablespace es un conjunto de segmentos, un segmento es un conjunto de extensiones y una extension es un conjunto de bloques( la minima expresión lógica de almecenamiento).

Bloque de Datos
Nuestro estudio se centrará en el comportamiento de los bloques de datos, por lo que debemos entender su estructura, para ello a continuación presentaré de manera breve su estructura:

Estructura del Bloque de Datos Oracle

Son cinco partes fundamentales que conforman un bloque de datos:

Cabecera: En esta sección del bloque se almacena la información genera y de control del bloque como: Tipo de segmento (de tabla, indice, lob, rollback, cluster, etc) e información de la ubicación del bloque, número de sincronización.
Directorio de Tabla: Contiene información de la tabla relaciona a la fila almacenada al bloque.
Directorio de Fila: Contiene información de las filas almacendas en el bloque (dirección para cada fila de la tabla almacenada en el bloque).
Espacio Libre: Esta sección esta destinada para los nuevos datos o actualizaciones de datos que requieran otorgar (Siempre y cuando exista disponivilidad de espacio en el bloque). Para el caso de una actualización esta puede incrementar o descrementar el espacio disponible del bloque.
Datos: Esta es la sección en donde se almacena la los datos de las tablas o indices. Los datos de un registro puede encontrarse la necesidad de que ocupen mas de un bloque, esto puede suceder por dos situciones que son: Encadenamiento y Migracion de Filas.

Ahora si con la breve explicación de as esctructuras fisicas y lógicas que Oracle maneja ahora entendamos que es lo que sucede cuando se crea una tabla. Cuando se crea una tabla, a esta se le indica que en que tablespace se almacenará y de manera automatica se asigna a un segmento llamado segmento de tabla y para los indices es igual con un numero inicial de extensiones.

A medida que se insertan datos y se va agotando el espacio asignado a la tabla en el segmento, automaticamente se va asignando al segmentos mas extensiones para dar mas espacio disponible para los datos nuevos.

En la siguiente vista vemos los segmentos y extents asignado a las tabla o indice:

select * from dba_segments where segment_name='nombre tabla o indice';
select * from dba_extents where segment_name='nombre tabla o indice';

Donde segment_name es el nombre de la tabla o indice.

Comportamiento de una tabla que subre insert y delete
En el siguiente ejercicio veremos como se comporta el almacenamiento asignado a una tabla realizando varias transacciones insert y delete.

drop table mi_tabla purge;
----------------------------------------------------------
--Creamos la tabla
----------------------------------------------------------create table mi_tabla
(
numero number,
es_par varchar2(1)
)
PCTFREE 0
tablespace ts_datos;

--Realicemos un analisis del espacio utilizado, para esto
--usaremos el procedimiento P$ANALIZA_ESPACIO que dará un
--resumen del almacenamiento
call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------
ASIGNADO
Bloques: 8
KBytes: 64.00
USADO
Bloques: 3
KBytes: 24.00
LIBRE
Bloques: 5
KBytess: 40.00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 0
Registros KBytes: .00
ANALISIS
% SUB UTILIZADO: 100.00

Vemos que % SUB UTILIZADO nos da 100, pero no hay problema, ya que recien creamos tabla.

----------------------------------------------------------
--Ahora realicemos inserción de datos
----------------------------------------------------------
insert into mi_tabla
select rownum, mod(rownum,2)
from dual
connect by level<=1000000;
commit;
Un millon de registros insertados

--Realicemos un analisis del espacio utilizado
call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------
ASIGNADO
Bloques: 1536
KBytes: 12,288.00
USADO
Bloques: 1536
KBytes: 12,288.00
LIBRE
Bloques: 0
KBytess: .00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 1000000
Registros KBytes: 9,765.63
ANALISIS
% SUB UTILIZADO: 20.53

Ahora vemos que el % SUB UTILIZADO es de 20.53, pero no es problema ya que es un valor aceptable considerando que existe entre un 10 y 15 % de espacio del bloque que es usado para controles internos de oracle. Vemos que la cantidad de registros es de un millon y el tamaño en KB de los registro es de 9,765.63

----------------------------------------------------------
--Ahora realicemos borrado de datos
----------------------------------------------------------
delete mi_tabla where es_par=0;
commit;
Quinientos mil registros borrados

--Realicemos un analisis del espacio utilizado
call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------
ASIGNADO
Bloques: 1536
KBytes: 12,288.00
USADO
Bloques: 1536
KBytes: 12,288.00
LIBRE
Bloques: 0
KBytess: .00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 500000
Registros KBytes: 4,882.81
ANALISIS
% SUB UTILIZADO: 60.26

Nos damos cuenta que lo único que vario es la cantidad de registros y el el % SUB UTILIZADO de 60.26 (Alta fragamentación)

Ahora si ponemos en práctica el uso del SHRINK, pero tenemos dos formas de utilizarlo:

SHRINK SPACE COMPACT

--------------------------------------------------------------

--Previo a esto habilitamos el movimiento de filas en la tabla

--y realizamos el shrink space compact

--------------------------------------------------------------

alter table mi_tabla enable row movement;
alter table mi_tabla shrink space compact;

--Realicemos un analisis del espacio utilizado

call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------

ASIGNADO
Bloques: 1536
KBytes: 12,288.00
USADO
Bloques: 1536
KBytes: 12,288.00
LIBRE
Bloques: 0
KBytess: .00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 500000
Registros KBytes: 4,882.81
ANALISIS
% SUB UTILIZADO: 60.26

SHRINK SPACE

--------------------------------------------------------------

--Previo a esto habilitamos el movimiento de filas en la tabla

--y realizamos el shrink space

--------------------------------------------------------------

alter table mi_tabla enable row movement;
alter table mi_tabla shrink space;

--Realicemos un analisis del espacio utilizado

call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------
ASIGNADO
Bloques: 768
KBytes: 6,144.00
USADO
Bloques: 764
KBytes: 6,112.00
LIBRE
Bloques: 4
KBytess: 32.00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 500000
Registros KBytes: 4,882.81
ANALISIS
% SUB UTILIZADO: 20.11

SHRINK SPACE COMPACT Se compactan los bloques, dejando lleno los que estan a medio uso y dejando libre los demas bloques pero siguen asignados al segmento tabla o indice.

SHRINK SPACE Se compactan los bloques, dejando lleno los que estan a medio uso y dejando libre los demas bloques y se libera la asignación al segmento de las extensiones libres.

Al realizar el SHRINK SPACE notamos que los valores de ASIGNADO, USADO y % SUB UTILIZADO disminuyeron, esto ya que se compactaron y liberaron del segmento los bloques libres.

Otras opciones del SHRINK
ALTER INDEX MI_INDICE SHRINK SPACE Tambien podeis aplicar el SHRINK en los indices.

ALTER TABLE MI_TABLA SHIRINK SPACE CASCADE. Realizas el SHRINK a todos los indices relacionados a las tablas.

Restricciones:
No puedes usarlo en tablas clusterizadas o que contengan columnas LONG.
No funciona en indices basados en función o indices bimap.
No puedes usar esta clausula para tabla comprimidas.

Consideraciones.
Tener cuidado al realizar el ALTER TABLE ENABLE ROW MOVEMENT ya que esto afecta a los triggers asociados a las tablas que hacen referencia a :OLD ya que las filas estan en movimiento.

UNDO SEGMENT TUNING & DEFINICION

2011-08-09T22:50:00.000-07:00

INTRODUCCION

A partir de la versión 9i de Oracle el UNDO SEGMENT entró a reemplazar a los ROLLBACK SEGMENTS que cumplían con la misma tarea, pero el UNDO SEGMENTS necesita de menor administracion que los ROLLBACK SEGMENT. Esto es gracias a que el UNDO SEGMENT tiene una gestión automática.

Por que usar UNDO SEGMENT en vez del ROLLBACK SEGMENT?

Oracle siempre busca a la gestión automática de estos segmentos y esto se logra gracias a la gestión automática del UNDO SEGMENT, indicando cual es el tablespace UNDO para cada instancia de base de datos. Con los ROLLBACK SEGMENT hay que definir cada uno de estos segmentos para cada tablespace.

La tarea del UNDO SEGMENT es garantizar la integridad de los datos que son:

o Transaction Rollback( Deshacer transacciones): Guardar en el UNDO SEGMENT los regisros de datos antes de la modificación. Si la transacción es deshecha (Rollback) los registros se restablecen con la imagen almacenada en el UNDO SEGMENT para revertir los cambios no confirmados.

o Transaction Recovery( Recuperación de transacciones) : Esto en los caso de que la base de datos Oracle falla, todas las transacciones en progreso y no confirmadas deben de revertirse.

o Read Consistency( Lectura Consistente): Oracle mantiene el concepto de lectura consistente y esto consiste en que muchos usuarios están accesando simultáneamente a los datos. Cada sesión de usuario no presentara a las demás sesiones de usuarios los cambios realizado de los datos hasta que los confirme (commit). De esto se trata la consistencia, mantener oculto las modificaciones de datos a las demás sesiones de usuarios hasta que las confirme.

o Apoya a la generación de consultas SQL complejas que necesitan crear temporalmente bloques de datos en el UNDO_SEGMENT. Si tenemos un reporte o consulta muy compleja que necesita de mucha información, Oracle se apoyará del UNDO_SEGMENT para armar el set de información requerido.

Lectura consistente de datos

En la imagen "Lectura consistente de datos", se muestra cuando una sesion de usuario realiza modificaciones a la tabla employee, en la tabla se realiza el cambio realizado por el update, pero antes se saca una imagen del dato (Undo image) para almacenarlo en el UNDO SEGMENT, las demas sesiones de usuarios que intenten accesar a los datos de la tabla employee no leeran los datos modificados en la tabla si no de la imagen almacenda en el UNDO SEGMENT, esto se realizara hasta que la sesion que modifique realice un commit o rollback.

Si se realiza un commit, se liberará el segmento de UNDO usado.

Si se realiza un rollback, se reversará el cambio realizado en la tabla employee copiando la imagen respaldada en el UNDO SEGMENT a la tabla y posterior a eso se liberar el segmento de UNDO usado.

ADMINISTRACION AUTOMATICA DEL UNDO

Crear el tablespace tipo UNDO.

CREATE UNDO TABLESPACE UNDOTBS2

DATAFILE '+DATA' SIZE 12400M

AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

ONLINE;

UNDO_MANAGEMENT

Hay que cambiar la configuración a gestión automática la administración del UNDO, esto se logra cambiando el parámetro de base de datos UNDO_MANAGEMENT;

ALTER SYSTEM SET UNDO_MANAGEMENT =AUTO SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP;

UNDO_TABLESPACE

Configurar cual es el tablespace UNDO.

ALTER SYSTEM SET UNDO_TABLESPACE =UNDOTBS2 SCOPE=BOTH;

UNDO_RETENTION

Este parámetro viene configurado por defecto con 900 (15 minutos ) medido en segundos y especifica el tiempo que el segmento perdurará en el UNDO SEGMENT antes de ser liberado para ser reutilizado.

TUNEA LOS PARAMETROS DEL UNDO SEGMENT.

Cuáles son los parámetros ideales?

No existe una configuración inicial perfecta con la que podamos iniciar, pero si podemos afinarla con el transcurso del tiempo y es una tarea del DBA obtener el valor optimo para el mejor desempeño de la base de datos.

NOTA: Estas evaluaciones, deberán realizarse después de un periodo de operación de la base para que las estadiscas tengan la suficiente información como para dar un resultado adecuado. Esto por los casos en que es una base de datos que arranca su operación y se quiera estimar estos valores.

Cuál es el valor ideal para el UNDO_RETENTION?

Para esto podemos usar la siguiente fórmula:

Fórmula para determinar cual es el valor ideal para el UNDO_RETENTION

ALTER SYSTEM SET UNDO_RETENTION =300 SCOPE=BOTH;

Donde:

ACTUAL TAMAÑO DEL UNDO

SELECT SUM (a.bytes) / 1024 / 1024 "MB TAMAÑO ACTUAL UNDO"

FROM v$datafile a,

v$tablespace b,

dba_tablespaces c

WHERE c.contents = 'UNDO'

AND c.status = 'ONLINE'

AND b.name = c.tablespace_name

AND a.ts# = b.ts#;

UNDO_BLOCK_PER_SEC: Cantidad de bloques undo que se transfieren por segundo

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

"UNDO_BLOCK_PER_SEC"

FROM v$undostat;

DB_BLOCK_SIZE: Tamaño del bloque de datos que maneja la base.

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE KB"

FROM v$parameter

WHERE name = 'db_block_size';

VALOR OPTIMO PARA EL UNDO RETENTION

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE MB",

SUBSTR(e.value,1,25) "ACTUAL UNDO RETENTION [Sec]",

ROUND((d.undo_size / (to_number(f.value) *

g.undo_block_per_sec))) "SUGERIDO UNDO RETENTION [Sec]"

FROM (

SELECT SUM(a.bytes) undo_size

FROM v$datafile a,

v$tablespace b,

dba_tablespaces c

WHERE c.contents = 'UNDO'

AND c.status = 'ONLINE'

AND b.name = c.tablespace_name

AND a.ts# = b.ts#

) d,

v$parameter e,

v$parameter f,

(

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

undo_block_per_sec

FROM v$undostat

) g

WHERE e.name = 'undo_retention'

AND f.name = 'db_block_size'

Cuál es el tamaño ideal para el tablespase UNDO?

Fórmula para determinar el tamaño ideal del UNDO TABLESPACE

Se deberá modificar el tamaño del tablespace con el valor recomendado.

SELECT d.undo_size/(1024*1024) "TAMAÑO ACTUAL UNDO MB",

SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

(TO_NUMBER(e.value) * TO_NUMBER(f.value) *

g.undo_block_per_sec) / (1024*1024)

"TAMAÑO UNDO NECESARIO MB"

FROM (

SELECT SUM(a.bytes) undo_size

FROM v$datafile a,

v$tablespace b,

dba_tablespaces c

WHERE c.contents = 'UNDO'

AND c.status = 'ONLINE'

AND b.name = c.tablespace_name

AND a.ts# = b.ts#

) d,

v$parameter e,

v$parameter f,

(

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

undo_block_per_sec

FROM v$undostat

) g

WHERE e.name = 'undo_retention'

AND f.name = 'db_block_size'

Si el valor recomendado es menor al tamaño actual, estara a criterio del DBA disminuir el tamaño o en su defecto aumentar el UNDO_RETENTION, por que a mayor tamaño de lo necesario, mayor tiempo de retención podemos especificar.

ERRORES QUE SE PUEDEN PRESENTAR RELACIONADOS CON EL UNDO SEGMENT.

ORA-30036: Unable to extend segment by nombre_segmento in undo tablespace nombre_tablespace

CAUSA

Este error pasa cuando el tamaño del UNDO TABLESPACE se agotó y no se pueden crear nuevos segmentos.

El parámetro UNDO RETENTION tiene un valor muy elevado que no deja liberar los segmentos del UNDO_SEGMENT para que sean reutilizados.

SOLUCION

Aumentar el tamaño al UNDO TABLESPACE y como recomendación habilitar el AUTO EXTENT con un valor del 10% de tamaño del tablespace.

Analizar el tamaño del UNDO_RETENTION y evaluar disminuir el tamaño.

ORA-30019: Illegal rollback segment operation in Automatic Undo mode

CAUSA

Esto ocurre cuando tratamos con ROLLBACK SEGMENT en bloque PL/SQL (Es una programación antigua donde se usa el comando “SET TRANSACTION USE ROLLBACK SEGMENT”) y la base de datos Oracle está configurada en administración automática del UNDO.

SOLUCION

Oracle permite coexistir al UNDO SEGMENT y el ROLLBACK SEGMENT para mantener la compatibilidad con versiones anteriores de Base de datos, pero para esto se deberá configurar el parámetro de base de datos UNDO_SUPPRESS_ERRORS=TRUE.

Si no desea realizar esta configuración, se debera cambiar la linea de código que hace el seteo del ROLLBACK SEGMENT.

ORA-1555 snapshot too old

CAUSA

Esto ocurre cuando Oracle no puede lograr una lectura consistente de los datos. Alguna sesión trata de accesar a datos que deberían encontrarse en la UNDO SEGMENT, pero no los encuentra.

SOLUCION

Se deberá evaluar el valor con el que se deberá aumentar el UNDO_RETENTION para poder mantener la lectura consistente.

TIP'S PARA EL TUNING DE TU SQL

2011-08-06T22:16:00.000-07:00

En ocasiones no solo basta con realizar un buen afinamiento a la base de datos Oracle para mejorar el rendimiento. Podemos hacer que mejore el rendimiento aplicando buenas prácticas al realizar nuestros SQL y PL/SQL.

A continuación les indicaré unos tips que pueden usar para las buenas prácticas en el desarrollo.

USAR VARIABLES BIND:

Esto es sobre todo en aplicaciones desarrollo de aplicaciones JAVA, .NET, VSB6, VFP, PHP, Etc. Ya que la las herramientas developer de Oracle no ocurre mucho.

Para esto primero entendamos que:

· El análisis de una sentencia SQL y averiguar su plan de ejecución óptimo consumen mucho tiempo para Oracle, por eso Oracle mantiene las sentencias SQL en memoria después de haber analizado y ejecutado la sentencia SQL para reutilizar no realizar de nuevo el análisis y usar el plan de ejecución definidos en su primera ejecución.

· Oracle antes de realizar el análisis primero busca la librería SQL el contexto (SGA) para ver si hay una declaración idéntica existente.

· Para que una sentencia SQL sea compartida debe de ser idéntica en todo sentido de la palabra.

Veamos un ejemplo,

· Aunque las siguientes sentencias hagan lo mismo no son iguales, ya que el analizador es sensible a las mayúsculas y minúsculas:

Select * from empleados where numero_empleado=2012;

SELECT * FROM EMPLEADOS WHERE NUMERO_EMPLEADO=2012;

· Las dos sentencias SQL no son idénticas ya que el filtro en el campo NUMERO_EMPLEADO son diferentes, por lo que durante su ejecución las dos sentencias se analizaran y planificaran como diferentes:

SELECT * FROM EMPLEADOS WHERE NUMERO_EMPLEADO=2012;

SELECT * FROM EMPLEADOS WHERE NUMERO_EMPLEADO=2013;

· Para optimizar la ejecución de estas dos sentencias debemos usar en lo posible las variables bind

SELECT * FROM EMPLEADOS WHERE NUMERO_EMPLEADO=:NUM_EMP;

Si desean pueden ver mas a fondo de este tema con ejemplos en el siguiente link

Uso de VARIABLES BIND

USAR WHERE EN VEZ DE HAVING:

El evitar el uso del HAVING en la instrucción SELECT. Las condiciones del HAVING son de exclusividad para funciones agregadas como el COUNT, SUM, AVG, MAX, MIN, etc y no son para condiciones de datos.

· Si usamos el HAVING se extraerán todos los datos y luego se realizara el agrupamiento con el filtrado de los datos.

SELECT OWNER,OBJECT_TYPE, COUNT(*)
FROM ALL_OBJECTS
GROUP BY OWNER,OBJECT_TYPE

HAVING OWNER='SYSTEM';

· En cambio con el WHERE se filtran los datos y luego se realice el agrupamiento, siendo esto menos costoso.

SELECT OWNER,OBJECT_TYPE, COUNT(*)
FROM ALL_OBJECTS
WHERE OWNER='SYSTEM'
GROUP BY OWNER,OBJECT_TYPE;

USAR UNION ALL EN VEZ DEL UNION

· Con la clausula UNION se realizará un ordenamiento para obtener filas distintas y eliminar las repetidas, un ordenamiento es un costo adicional en el procesamiento de la CPU.

SELECT *

FROM ALL_TABLES

UNION

SELECT *

FROM DBA_TABLES;

· Con la clausula UNIOM ALL no se realiza ordenamiento ni eliminación de las filas repetidas, lo que hace es unir tal como vengan los datos de las sub-consultas.

SELECT *

FROM ALL_TABLES

UNION ALL

SELECT *

FROM DBA_TABLES

USAR EXISTS O NOT EXISTS EN VEZ DE IN O NOT IN

Es mejor usar la clausula EXISTS en vez del IN, esto ya que al usar el EXISTS es como si realizaramos el JOIN entre las tablas. El uso de la clausula IN es recomendable si el dominio de datos es pequeño (a mi criterio que no pase de 100 registros).

· Veamos el ejemplo con el NOT IN.

SELECT * FROM ALL_OBJECTS

WHERE OBJECT_ID NOT IN (SELECT OBJECT_ID

FROM USER_OBJECTS);

· Veamos el ejemplo con el NOT EXISTS.

SELECT * FROM ALL_OBJECTS A

WHERE NOT EXISTS (SELECT 1 FROM USER_OBJECTS B

WHERE A.OBJECT_ID=B.OBJECT_ID );

Perfiles del SQLNET.ORA

2011-07-15T17:53:00.001-07:00

Alguna vez se han preguntado qué utilidad le podemos dar al archivo SQLNET.ORA?

Este archivo por defecto se crea en la ruta %ORACLE_HOME%\network\admin, la configuración que realicemos en este archivo nos ayudará a:

Controlar el acceso a la base de datos por medio del protocolo de red, métodos de autenticación, definir los tiempos de comprobación de actividad de las sesiones de los clientes.

Estas son unas cuantas de las utilidades de SQLNET.ORA, y continuación le explicaré unas muy útiles.

SQLNET.EXPIRE_TIME

Este parámetro esta medido en minutos y es usado para especificar el intervalo de tiempo de envió de test a la conexiones clientes/servidor para comprobar si están activas. Este es un método que garantiza que las conexiones a la base de datos no se dejen abiertas indefinidamente, esto es una manera. Este parámetro por defecto esta seteado con cero pero es recomendable que se configure con valor superior a cero (Yo lo tengo configurado con 10).

Este parámetro podría solucionarles problemas de desconexión de los clientes a la base de datos para los problemas de redes intermitentes o desconexiones momentáneas de la red.

Me paso que me llegaban reportes de quejas de usuarios por las constantes desconexiones que sufrían, luego procedí a configurar este parámetro y las quejas disminuyeron no desaparecieron (Habían otros problemas) pero en su mayoría sí.

Consideraciones:

La configuración de un valor muy bajo podría traer consecuencias en el rendimiento de la red por el tráfico que se generaría para realizar las comprobaciones.

Ejemplo:

#Por Defecto: 0

#Mínimo : 0

#Recomendado: 10

SQLNET.EXPIRE_TIME=10

TCP.EXCLUDED_NODES

Nos permite indicar que clientes no podrán establecer conexión a la base de datos. Se puede definir por la ip o nombre de la máquina.

Consideraciones:

Si lo configuran deben de asegurarse que los host o ip a excluir no estén los procesos de respaldos, repositorio +ASM, etc.

Sintaxis:

TCP.EXLCUDED_NODES=(hostname | ip_address, hostname | ip_address, ...)

Ejemplo:

#Se deniega las conexiones a la base desde la ip 192.168.1.133 y al host pccliente05

tcp.validnode_checking =yes

tcp.excluded_nodes = (192.168.1.133,pccliente05)

TCP.INVITED_NODES

Nos permite indicar que clientes podrán establecer conexión a la base de datos. Se puede definir por la ip o nombre de la máquina.

Consideraciones:

Si lo configuran deben de asegurarse que todos hosts o ip de servicios o clientes que deban establecer conexión a la base de datos como procesos de respaldos, repositorio +ASM, etc.

Sintaxis:

TCP.INVITED_NODES=(hostname | ip_address, hostname | ip_address, ...)

Ejemplo:

#Se permite las conexiones a la base desde la ip 192.168.1.134 , las conexiones locales y al host pccliente01

tcp.validnode_checking =yes

tcp.invited_nodes = (192.168.1.134, localhost, pccliente01)

TCP.VALIDNODE_CHECKING

Nos permite habilitar la validación de la configuración TCP.INVITED_NODES y TCP.EXCLUDED_NODES.

Consideraciones:

Si el valor de TCP.VALIDNODE_CHECKING no está seteado en yes entonces no tiene efecto la configuración de TCP.INVITED_NODES y TCP.EXCLUDED_NODES.

Ejemplo:

#Por Defecto: no

#Permitido : yes, no

tcp.validnode_checking =yes

NOTA:

Si modificas el archivo SQLNET.ORA para que se apliquen los cambios deberás reiniciar el servicio del listener o recargar la configuración.

Ejemplo:

##Recargando la configuración

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 15-JUL-2011 18:47:06

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> reload

The command completed successfully

##Reiniciar

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 15-JUL-2011 18:47:06

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stop

The command completed successfully

LSNRCTL> start

The command completed successfully

Parámetro db_writer_processes

2011-06-13T21:43:00.000-07:00

Como podemos optimizar el proceso de escritura del SGA a los archivos de datos?

La respuesta la encontramos en el parámetro de base de datos db_writer_processes, quien es el que indica el número de procesos que el RDBMS de Oracle debe de levantar para escribir ta disco todos los cambios realizados en la SGA.

Gráfico de la arquitectura del Oracle Server

Como vemos en la gráfica del Oracle Server, se que el proceso Database Writer es el encargado de escribir a disco todos los cambios realizados en la SGA.

Este parámetro por defecto es 1 y el valor que se le pueda asignar va a depender de la cantidad de procesadores en el que este levantado el RDBMS de Oracle. El rango de valores que admite es de 1 a 20 para versiones de Oracle 10g y de 1 a 32 para Oracle 11g.

Para ver como esta configurado el parámetro db_writer_processes:

select name, value from v$parameter where name='db_writer_processes';
show parameter db_writer_processes;

Para cambiar el parámetro db_writer_processes:

alter system set db_writer_processes=valor_asignar scope=SPFILE

Nota: Al cambiar este parámetro se deberá reinicar la base de datos.

Si tu base e datos es muy transaccional entonces puedes modificar el parámetro db_writer_processes al número de procesadores que tenga el servidor en donde esta levantado el RDBMS de Oracle.

Ejemplo:
Supongamos que tenemos nuestra bas de datos en un servidor con 4 procesadores y deseamos setear el mismo número de procesos writer en nuestra base de datos.

//Seteamos en la variable de ambiente la instancia
>set oracle_sid=capacitacion

//Nos conectamos a la base como sys
>sqlplus / as sysdba

//Revisamos como esta seteado el parametro antes del cambio
SQL> show parameter db_writer_processes;
NAME TYPE VALUE
-------------------- ----------- ------------------------------
db_writer_processes integer 1

//Ateramos el parametro a 4
SQL> alter system set db_writer_processes=4 scope=SPFILE;
System altered.

//Bajamos la instancia
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

//Iniciamos la instancia

SQL> startup;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  1299316 bytes
Variable Size             285215884 bytes
Database Buffers         1317011456 bytes
Redo Buffers                7086080 bytes
Database mounted.
Database opened.
//Revisamos como quedo seteado el parametro antes del cambio
SQL> show parameter db_writer_processes;
NAME                 TYPE        VALUE
-------------------- ----------- ------------------------------
db_writer_processes  integer     4

Forzar el uso de variables bind con el parámetro CURSOR_SHARING

2011-06-11T23:52:00.000-07:00

Como evitar HARD PARSE al momento de que nuestro sistema ejecuta sentencias SQL que no usan Variables Bind?

Recordemos que al existir gran cantidad HARD PARSES, el tiempo de respuesta y rendimiento de la base de datos se degrada, ademas del llenado del area SQL LIBRARY del SGA. Lo ideal es que no existan demasiados Hard Parse si no Soft Parse.

En mi nota anterior se explicó la forma de optimizar el rendimiento y tiempo de respuesta al momento de ejecutar una sentencia SQL usando variables bind en la programación, pero en ocasiones no es fácil aplicarlo ya que los DBA o Lideres de progrmadores no desarrollamos toda la aplicación o no realizamos un control de calidad para ver que se cumplan estos estandares. Otro motivo es que así compramos el sistema y no lo desarrollamos!

Para esto debemos modificar la programación. Una tarea muy dura, no?

Pues la respuesta a este problema es cambiar el parámetro de base de datos CURSOR_SHARING.
Oracle tiene el parámetro de base de datos CURSOR_SHARING que por defecto esta configurado con EXACT.

Y esto en que me ayuda? El CURSOR_SHARING admite 3 valores: EXACT, SIMILAR y FORCE

EXACT Valida que la sentencia SQL a ejecutarse sea exactamente igual a la que existe en la librería SQL para que no se realice un HARD PARSE. Ejemplo

Las siguietes dos sentencia hacen consulta sobre el mismo objeto, son casi iguales pero el valor de filtro para el campo cédula son diferentes por lo que hace que las dos sentencias sean diferentes y cada una se ejecute por un HARD PARSE
--Sentencia en la librería SQL
select * from persona where cedula='0123456789';
--Sentencia a ejecutar
select * from persona where cedula='0000000000';

SIMILAR
Valida que la sentencia SQL sean similares a nivel de select, from y where pero los valores constantes son reemplazados con variables, es decir la tarea que no realizamos anivel de programación.

Con el parámetro en SIMILAR estas dos sentencias son iguales y no se realizará HARD PARSE al momento de ejecutarse.
--Sentencia en la librería SQL
select * from persona where cedula='0123456789'; => select * from persona where cedula="VAR_1";
--Sentencia a ejecutar
select * from persona where cedula='0000000000'; => select * from persona where cedula="VAR_1";

FORCE
Es parecido al parámetro SIMILAR si no que la diferencia es que SIMILAR no registrará en la librería SQL la sentencia evaluada evitando el llenado de la misma.

Si no quedó claro, imaginense que ejecutamos 10000 consultas de personas por el número de de cédula y estos número de cédula son diferentes y no usan variable bind. en CUSRSOR_SHARING igual SIMILAR solo se registraría una vez la sentencia en la librería SQL la sentencia. En FORCE se registrarían
10000 veces la sentecia en la librería SQL.

Veamos el ejemplo
Consultas sin variables bind con parámetro CURSOR_SHARING=EXACT
--Vemos que este configurado el parámetro como EXACT
show parameter cursor_sharing;
cursor_sharing                       string   EXACT
-- Liberamos la memoria shared pool
Alter System Flush Shared_Pool;
System Switch Log Altered.
Elapsed: 00:00:00:01
--Escript que realizará las consultas sin variables BIND
Declare
C_Cedula Varchar2(20);
N_Can       Number;
C_Sql    Varchar2(200);
Begin
For N_Id In 1..10000 Loop
    C_Cedula:=N_Id;
    C_Sql:='SELECT COUNT(*) FROM PERSONAS WHERE IDENTIFICACION='''||N_Id||'''';
    Execute Immediate C_Sql Into N_Can;
End Loop;
End;
Elapsed: 00:00:04:68
-- Revisamos la cantidad de sentencias almacenadas en la librería SQL
Select Count(*) Cantidad From V$Sql
Where Sql_Text Like 'SELECT COUNT(*) FROM PERSONAS WHERE IDENTIFICACION=%';
Cantidad
----------
     10000
Elapsed: 00:00:00:15

Ahora analicemos como esta la memoria

Gráfica de uso de la memoria SHARED POOL

Vemos como se vio afectada el tamaño disponible del Share Pool y el tamaño de la Sql Area aumentó por las 10,000 consultas SQL almacenadas en sentencias dentro de la Sql Library, esto representa 10,000 Hard Parse y 0 Soft Parse (Esto es malo para el Oracle Server).. El tiempo de ejecución fue de 4.68 segundos

Consultas sin variables bind con parámetro CURSOR_SHARING=SIMILAR

--Cambiamos el parámetro de base de datos CURSOR_SHARING=SIMILAR
alter system set cursor_sharing = SIMILAR scope=BOTH;
connect vendara/vendara
--Vemos que este configurado el parámetro como SIMILAR
show parameter cursor_sharing;
cursor_sharing                       string   SIMILAR

-- Liberamos la memoria del la shared pool
Alter System Flush Shared_Pool;
System switch log altered.
set timing on;
--Escript que realizará las consultas sin variables BIND
Declare
C_Cedula Varchar2(20);
N_Can       Number;
C_Sql    Varchar2(200);
Begin
For N_Id In 1..10000 Loop
    C_Cedula:=N_Id;
    C_Sql:='SELECT COUNT(*) FROM PERSONAS WHERE IDENTIFICACION='''||N_Id||'''';
    Execute Immediate C_Sql Into N_Can;
End Loop;
End;
/
Elapsed: 00:00:01:93
-- Revisamos la cantidad de sentencias almacenadas en la librería SQL
Select Count(*) Cantidad From V$Sql
Where Sql_Text Like 'SELECT COUNT(*) FROM PERSONAS WHERE IDENTIFICACION=%';
CANTIDAD
----------
         1
Elapsed: 00:00:00:01

Ahora analicemos como esta la memoria

Gráfica de uso de la memoria SHARED POOL

Vemos que ahora el tamaño disponible de la Shared Pool no se disminuyó tanto y de igual forma el Sql Area no aumento demasiado ya que solo se guardó una sentencia en la Sql Area aunque se hayan ejecutado 10,000 consultas con diferente identificación, lo que representa 1 Hard Parse y 9,999 Soft Parse (Esto es bueno para el Oracle Server). El tiempo de ejecución disminuyó a 1.93 segundos.

Conclusiones

Vemos que los mejores valores en resultados son con el parámetro de base de datos CURSOR_SHARING en SIMILAR para obtener mejores tiempos de respuesta, menos uso en MB de las Sql Library y dejando mas espacio disponible para la Shared Pool y reduciendo los Hard Parse.

Recomendaciones
Es cierto que este parámetro mejor los tiempo de respuesta, pero a que costo? el costo es que el motor de base de datos tiene mas trabajo para procesar cada sentencia.

Solo recomedaría cambiar el parámetro de forma temporal hasta que se logre modificar los programas para que usen variables bind, o en casos extremos, pero de ahi no lo recomedaría.

Este parámetro CURSOR_SHARING=SIMILAR lo aplique en mi base de datos Oracle RAC de dos nodos montados en servidores WS2008 R2, 2 procesadores con 4 nucleos de 2.93 Ghz de y con 24GB de memoria Ram, pero la medicina fue peor que la enfermedad se presentaba error :
ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], [].

Para este error en el metalink de Oracle recomendaba cambiar el parametro CURSOR_SHARING a EXACT.

Este error sólo se me presentó en Oracle RAC pero no en Oracle Single Instance, lo que me hace pensar que el parámetro CURSOR_SHARING=SIMILAR no funca para RAC.

Uso de Variables Bind

2011-05-19T21:32:00.000-07:00

Optimiza el rendimiento de tu base de datos aplicando buenas practicas de programación con el uso de Variables Bind.

Antes de entrar a explicar el uso de variables bind se han preguntado que es lo que pasa cuando se manda a ejecutar una sentencia SQL?. Toda sentencia SQL pasa por el denominado parse, el cual es un análisis de la sentencia antes de su ejecución.

El parseo no es nada mas que un análisis de vinificación de la sentencia antes de que se mande a ejecutar. Este análisis consiste en 5 pasos en el orden presentado:
1- Análisis sintáctico.
2- Análisis semántico.
3- Optimización.
4- Generación.
5- Ejecución.

Hay dos tipos de parses que se pueden dar: Soft Parse y Hard Parse.

Hard Parse
Es cuando la sentencia SQL se analiza por los 5 pasos del parseo esto por que Oracle no encuentra la sentencia en el SQL Area del SGA por lo que tiene que analizarse por completo.

Soft Parse
Es cuando la sentencia SQL pasa solo por los pasos 1 y 2 esto por que Oracle encuentra la sentencia en el SQL Area del SGA y determina que no es necesario ejecutar todos los pasos además sería un desperdicio.

Con esto vemos que no toda sentencia SQL se analiza por los 5 pasos, por lo menos se analiza por los dos primeros.

Pero por que una sentencia SQL llega analizarse solo hasta el paso 2?
Por que hasta el segundo paso Oracle encuentra la sentencia SQL parecida en el SQL Area y pasa directamente a ejecuta, con esto se gana mucho tiempo.
Ya con esta pequeña explicación sabemos que lo mejor que puede suceder en la ejecución de una sentencia SQL es el SOFT PARSE. Si hay mas HARD PARSE los tiempos de respuesta son mas altos y el rendimiento mas bajo.

Ahora si, como podemos lograr que las ejecuciones SQL se vayan por SOFT PARSE?
Pues aplicando las buenas practicas de programación usando bind variables al momento de utilizar sentencias dinámicas que se ejecutan por el comando execute immediate.

A continuación un ejemplo del como no se hace y como se debe de hacer.

--Comomo no debemos programar!!!
alter system flush shared_pool;
Sistema modificado.
alter system flush buffer_cache;
Sistema modificado.
set timing on

----------------------------------------------------------------
--Ejemplo de ejecucion sin uso de variables bind
----------------------------------------------------------------
declare
    ln_cantidad number;
begin
    for n_ind in 1..10000 loop
  execute immediate 'select count(*) from cl_personas where id_persona='||n_ind into ln_cantidad;
    end loop;
end;
/
Procedimiento PL/SQL terminado correctamente.
Transcurrido: 00:00:04.31
--Revisamos en el SQL AREA la cantidad de sentencias
--que se generaron
select count(*)cantidad from v$sql where sql_text
like 'select count(*) from cl_personas where id_persona=%';
CANTIDAD
----------
      9987
--Como si debemos programar!!!
----------------------------------------------------------------
--Ahora realizamos el ejemplo usando variables bind
----------------------------------------------------------------
alter system flush shared_pool;
Sistema modificado.
alter system flush buffer_cache;
Sistema modificado.

declare
ln_cantidad number;
begin
for n_ind in 1..10000 loop
    --:ID_PERSONA es la variable bind que se reemplazará
    --por lo que venga en la sección de USING
    execute immediate 'select count(*) from cl_personas where  id_persona= :ID_PERSONA'into ln_cantidad using n_ind ;
end loop;
end;
/
Procedimiento PL/SQL terminado correctamente.
Transcurrido: 00:00:01.03

--Revisamos en el SQL AREA la cantidad de sentencias
--que se generaron
select count(*)cantidad from v$sql where sql_text
like 'select count(*) from cl_personas where id_persona=%';
CANTIDAD
----------
         1

Conclusión
Vemos la diferencia en tiempo de respuesta y en la cantidad de sentencias en memoria de la librería SQL.

Tiempo de respuesta

Hard Parse 4.31 Segundos
Soft Parse 1.3 Segundos

Sql em memoria

Hard Parse 9987 sentencias
Soft Parse 1 sentencia

Beneficios del uso de bind variables :

Mejores tiempos de respuesta al momento de ejecutar sentencias SQL ya que generan SOFT PARSE al momento de analizarse
Disminución de la carga de trabajo en la base de datos.
Menor uso se la SQL Library

Desventaja del no uso de bind variables :

Peores tiempos de respuesta.
Aumento de la carga de trabajo en la base de datos.
Mayor uso se la SQL Library

Tunea tu Base Oracle

Fragmentacion de tablas Oracle

Optimiza los imports con IMPDP agregando el parametros TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

¿Qué hace este parámetro?

Beneficios de usarlo

Ejemplo de uso

Consideraciones importantes

Conclusión

Optimización del Rendimiento en Oracle con el Parámetro filesystemio_options

¿Qué es filesystemio_options?

Valores posibles:

¿Por qué es importante?

Mejores prácticas para su uso

Cómo verificar y cambiar el parámetro

Consideraciones adicionales

Oracle Base de Datos y Listener Automatizar el inicio (Oracle Auto Start)

Comprimir tablas Oracle -Compress Table

Quest Spotight Oracle DB & OS Windows/Linux

Mejora el rendimiento PL/SQL con PLSQL_CODE_TYPE=NATIVE

Oracle Lage Page Support - Para windows

Monitoreo de indices

PCTFREE y PCTUSED

Particionamiento

¿Cómo reemplazar el uso de Sinónimos?

TOP SQL

Ejecución de las Estadísticas

Recuperar espacio inutilizado de los segmentos SHRINK SPACE

UNDO SEGMENT TUNING & DEFINICION

TIP'S PARA EL TUNING DE TU SQL

Perfiles del SQLNET.ORA

Parámetro db_writer_processes

Forzar el uso de variables bind con el parámetro CURSOR_SHARING

Uso de Variables Bind

¿Qué es `filesystemio_options`?