¿Que se puede hacer para mejorar el rendimiento de las consultas en Oracle 11?


Tradicionalmente para mejorar el rendimiento de las consultas en SQL contra una BBDD Oracle , una vez optimizada la consulta con técnicas  de Tuneling ( por ejemplo usando Hints si procede) ,   siempre pasamos a dos técnicas clásicas :

  • Reconstruyendo los  índices existente en base de datos ,para lo cual previamente comprobaremos si esos índices deberían de ser reconstruidos  
  • Actualizando estadísticas sobre las tablas que realizamos las consultas.

Veamos mas despacio de que estamos hablando: 

 

Reconstrucción de indices

El índice de una base de datos es una estructura de datos que mejora la velocidad de las operaciones, permitiendo un rápido acceso a los registros de una tabla por lo que se suelen usar sobre aquellos campos sobre los cuales se vayan a realizar búsquedas frecuentes dado que su  funcionamiento es similar al índice de un libro: guardando duplas de elemento que se desea indexar junyo a su posición en la base de datos, de modo que para buscar un elemento que esté indexado, sólo necesitamos que buscar en el índice de dicho elemento para, una vez encontrado, devolver el registro que se encuentre en la posición marcada por el índice.

Los índices pueden ser creados usando una o más columnas, preparando la base de datos tanto para búsquedas rápidas al azar como para ordenaciones eficientes de los registros.

Los índices son construidos sobre árboles B, B+, B* o sobre una mezcla de ellos, funciones de cálculo u otros métodos.

El espacio en disco requerido para almacenar el índice es típicamente menor que el espacio de almacenamiento de la tabla (puesto que los índices generalmente contienen solamente los campos clave de acuerdo con los que la tabla será ordenada, y excluyen el resto de los detalles de la tabla), lo que da la posibilidad de almacenar en memoria los índices de tablas que no cabrían en ella. En una base de datos relacional un índice es una copia de parte de una tabla.

La siguiente consulta SQL mostrará el tamaño en megabytes de un índice determinado, en nuestro caso del índice PK_FACTURACION_CODIGO perteneciente a la tabla FACTURACION y el campo CODIGO del ejemplo. La consulta SQL para mostrar el tamaño ocupado por un índice es la siguiente:

select segment_name, sum(bytes)/1024/1024 MB
from dba_extents
where segment_name = ‘INDICE DE EJEMPLO’
group by segment_name

 

 

Es importante periódicamente examinar y determinar qué índices son susceptibles de ser reconstruidos. Cuando un índice está descompensado puede ser porque algunas partes de éste han sido accedidas con mayor frecuencia que otras dando como resultado problemas de contención de disco o cuellos de botella en el sistema.

Normalmente reconstruimos un índice con el comando ALTER INDEX  (esta sentencia se utiliza para cambiar o reconstruir un índice existente en la base de datos).

Para poder ejecutar este comando el índice debe de estar en el propio esquema donde intentes ejecutarlo o deberías de tener el privilegio alter any index. También tenemos que tener en cuenta que para realizar la reconstrucción de un índice deberíamos de tener cuota suficiente sobre el tablespace que lo lanzamos.

Para reconstruir un índice bastaría con lazar la siguiente sentencia

ALTER INDEX <index_name> REBUILD;

Para reconstruir una partición de un índice podríamos hacer lo siguiente

ALTER INDEX <index_name> REBUILD PARTITION <nb_partition> NOLOGGING;

Nota: En algunos casos cuando alguno de los índices tiene algún tipo de corrupción no es posible reconstruirlo. La solución en este caso es borrar el índice y recrearlo

 

 

 

Actualización de estadísticas

Cuando una base de datos Oracle recibe una sentencia “SQL” para resolver una consulta, se llevan a cabo diversas acciones para lograr la entrega del resultado.

Dentro de los diversos pasos uno de los más importantes es el llevado a cabo por el optimizador basado en costos “Cost Based Optimizer o CBO”. Para que el “CBO” pueda determinar de forma exacta el plan de ejecución de para un “SQL Query” debe disponer de la información de las estadísticas de las tablas e índices que participan en el “SQL Query”, esta información comúnmente es conocida como “Optimizer statistics” “Estadisticas del optimizador”, la misma describe como esta compuesto y distribuido internamente el objeto.

Estas estadísticas son utilizadas por el optimizador para elegir el mejor plan de ejecución para cada instrucción SQL.

El tiempo necesario para colectar las estadísticas en algunos casos puede ser de gran medida. En el manejador se pueden utilizar diversos métodos para tratar de reducir el tiempo de esta tarea en la mayor proporción posible.

 

Es importante tener por tanto actualizadas las estadísticas de la base de datos. Para saber si las estadísticas se están lanzando correctamente podemos hacer una consulta sobre la tabla ALL_INDEXES en oracle 11  (en Oracle 10  es  dba_indexes )y ver el campo last_analyzed para observar cuando se ejecutaron sobre ese índice las estadísticas.

Nota: la columna “LAST_ANALYZED” la cual puede ser encontrada en vistas tales como: “DBA_TABLES”, “DBA_INDEXES”, “DBA_TAB_COL_STATISTICS” indica la fecha en que fue calculada la estadística para dicho objeto por ultima vez.

 

Como ejemplo  , si queremos  saber cuando fue la ultima vez que se ejecutaron estadísticas sobre todas las tablas perteneciente a un determinado esquema de BBDD  lanzaremos la siguiente consulta:

SELECT LAST_ANALYZED,table_name FROM ALL_INDEXES ;

 

 

Como vemos con las fechas podemos  hacernos una idea , de lo actualizado que están las estadisticas   sobre cada tabla   

Para actualizar las estadísticas de forma global para  un  esquema de BBDD,  podemos utilizar  el paquete DBM_STATS  de la la siguiente forma:

Execute DBMS_STATS.gather_schema_stats(‘Esquema’);

 

Una vez actualizadas las estadísticas de los índices de la base de datos lanzamos la siguiente consulta:

SELECT LAST_ANALYZED,table_name FROM ALL_INDEXES ;
SELECT index_name, blevel,
DECODE(blevel,0,'OK BLEVEL',1,'OK BLEVEL',2,
'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK
FROM dba_indexes where table_owner='DBPROD08';

 

Con esta sentencia obtendremos el nombre nombre del índice, el blevel y si es correcto este indice.

Los índices que deberíamos de reconstruir son los que en la columna ok aparecen como BLEVEL HIGH.

Blevel (branch level) es parte del formato del B-tree del índice e indica el número de veces que ORACLE ha tenido que reducir la búsqueda en ese índice. Si este valor está por encima de 4 el índice debería de ser reconstruido.

.

Anuncios

Error ORA-03114


Hay aplicaciones que usan un cliente Oracle para gran parte de la funcionalidad ofrecidas ,  de modo que es vital en estos casos asegurar que el servicio de  acceso a la  BBDD este funcionando adecuadamente

Puede  que su aplicación  encuentre el siguiente error al ejecutar flujos de trabajo u otros procedimientos que requieran procesamiento dentro de la base de datos de Oracle:

25/01/2019 10:21:48: Sentencia sql: SELECT *  FROM  …………. 
25/01/2019 10:21:48: Error en el proceso de conversión. Error 5: ORA-03114: no conectado a ORACLE

Cuando esto ocurre, la conexión a la base de datos se perderá   lo cual  normalmente se traduce  que la  aplicación encuentra un error grave y probablemente se cerrara.

Este error puede  producirse  si su sesión de Oracle se ha quedado sin memoria.

Para reiniciar el valor de asignación de  memoria a una sesión de Oracle, lo mas fácil es seguir estos pasos:

  1. Cierre cualquier aplicación o servicio adicional  que pueda esta usando el cliente Oracle
  2. Reinicie  el cliente de Oracle  (en versiones antiguas puede llamarse oracleHome8ClientCache  )
  3. Para ello, en el menú Inicio de Windows, abra el panel de Control y vaya a Herramientas administrativas > Servicios. 
  4. Seleccione el cliente Oracle en la listen versiones antiguas puede llamarse oracleHome8ClientCache  ) y  pulse  en Detener el servicio.
  5. Vuelva al menú Inicio de Windows, abra el panel de Control y vaya a Herramientas administrativas > Servicios. 
  6. Seleccione el cliente  Oracle (en versiones antiguas puede llamarse oracleHome8ClientCache  ) en la lista y pulse en Iniciar  el servicio.

 

Para asignar más memoria a una sesión de Oracle se requiere conexión en el propio  servidor de BBDD  así como tambien a los clientes que estén usando esos servicios.Si es su caso puede seguir estos pasos:

  1. Cierre cualquier aplicación o servicio que pueda estar usando el cliente Oracle
  2. Detenga los servicios de Oracle en  Servicios de Windows. Para ello, en el menú Inicio de Windows, abra el panel de  Control y vaya a Herramientas administrativas > Servicios. Seleccione Oracle en la lista y detener el servicio.
  3. Asegúrese de que el servicio “OracleService” se ha detenido. De lo contrario, aparecerá un mensaje de error.
  4. Asegúrese de que los clientes no están conectadon al servidor.
  5. Cada sesión de Oracle tiene una cantidad limitada de memoria disponible. Para ver la memoria actual, escriba el  siguiente comando en la máquina en la que reside el servidor de Oracle :  orastack oracle.exe
  6. El resultado será como se indica a continuación, que muestra la asignación de memoria en el equipo:   Corriente de reserva memoria; PerThread = 1048576  y  Corriente de memoria asignada  = 4096 
  7. Nos situamos desde  [ORACLE_HOME] en la  subcarpeta bin. El siguiente comando aumentará la memoria para cada sesión (hasta 5 MB):           orastack oracle.exe 5000000 
  8. Reiniciar la los servicios de Oracle.
  9. Inicie la aplicación que usa  el cliente Oracle

 

 

Para asignar más memoria a una sesión de Oracle también se puede hacer desde los  clientes  que están usando  esos servicios.Si es su caso puede seguir estos pasos:

  1. Cierre cualquier aplicación o servicio que pueda estar usando el cliente Oracle
  2. Detenga los servicios de Oracle en  Servicios de Windows. Para ello, en el menú Inicio de Windows, abra el panel de  Control y vaya a Herramientas administrativas > Servicios. Seleccione Oracle en la lista y detener el servicio.
  3. Asegúrese de que el servicio “OracleService” se ha detenido. De lo contrario, aparecerá un mensaje de error.
  4. Asegúrese de que los clientes no están conectadon al servidor.
  5. Como el   servidor no es el mismo que  la máquina , este  se denomina con un nombre de servicio (p. ej. GISTEST) y, a continuación, el comando siguiente desde  [ORACLE_HOME] en subcarpeta bin  : orastack tnslsnr.exe 5000000
  6. Cuando el  servidor se reinicie o se reinicie la instancia  los  parámetros se pierden y se tienen que volver a aplicar.

 

 

Resultado:

Con esta opció, cada sesión de Oracle reserva 5 MB de memoria lo cual debería bastar para no dar problemas.

 Windows (32 bits) sólo puede direccionar 2 GB de memoria para cada proceso, lo que  significa que Oracle tiene un máximo de 2 GB de memoria, independientemente de la cantidad de memoria del servidor.Como el valor por defecto del parámetro de Oracle asigna 892 MB de área SGA (sistema global) más 297 MB de programa global PGA (área) por ejemplar.,por tanto, aproximadamente se  tiene 1,2 GB de memoria asignada para el proceso de Oracle, sin ninguna sesión de usuario. lo cual significa  que hay  aproximadamente 800 MB disponibles para sesiones de usuario

Con 5 MB por usuario  se pueden permitir un número máximo de 160 usuarios o 150 usuarios .Para estar seguros si se ejecuta con más de 150 usuarios simultáneos, puede considerar la posibilidad de reducir la memoria total por sesión de usuario.