¿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

Diferencias entre DBA_FREE_SPACE y DBA_TABLESPACE_USAGE_METRICS


Resultado de imagen de tablespaces

 

 

He estado durante  muchísimo  tiempo  realizando consultas nivel de ocupación de los tablespaces en Oracle 9  que utilizan las vistas del sistema DBA_FREE_SPACE y DBA_DATA_FILES   , lo cual esta muy bien,  pero recientemente he sabido que  hay personas que  en  Oracle 11  están usando  otra vista del sistema para conocer  el  nivel de ocupación de los tablespaces   que es muy rápida: DBA_TABLESPACE_USAGE_METRICS .

Esta  vista contiene información sobre espacios de tablas permanentes, temporales y de deshacer, y tiene solo cuatro columnas:

  • TABLESPACE_NAME TABLESPACE_NAME
  • USED_SPACE ESPACIO USADO
  • TABLESPACE_SIZE TABLESPACE_SIZE
  • USED_PERCENT USED_PERCENT

Hay tres cosas importantes acerca de esas columnas:

  1. Los números de USED_SPACE y TABLESPACE_SIZE están en bloques , no en bytes, por lo que necesitamos unir   esta vista a la vista del sistema DBA_TABLESPACES para terminar con números en bytes. ( ellos la ofrecen  tal cual sin hacer la  conversión a  bytes , de modo que  lo que nos están dando son el nº de bloques y no el de bytes)
  2. La columna TABLESPACE_SIZE muestra el tamaño total posible , no el tamaño actual  
  3. USED_PERCENT muestra el uso del tamaño total posible de un espacio de tabla.

Es decir ,    la información de esta vista no es la que muestra el tamaño real , sino el posible  tamaño   máximo lo cual  explica  la gran diferencia   que nos encontraremos  entre  cálculos realizados sobre la vista  DBA_FREE_SPACE   o sobre la  vista  DBA_TABLESPACE_USAGE_METRICS

Algo muy importante es que la vista  DBA_TABLESPACE_USAGE_METRICS nos ofrece la volumetría en bloques lo cual no es muy útil , por lo que para traducirlo a bytes  tendremos que saber  el tamaño del bloque . Con esta consulta podemos ver el tamaño del bloque:

show parameter block_size

Ahora , lo mejor es hacer la conversión  usando el valor de Block_size de modo que obtengamos  en la  consulta el  tamaño de los Tablespaces  en MB y no en bloques

select * from(
SELECT
a.tablespace_name,
ROUND((a.used_space * b.block_size) / 1048576, 2) AS “Used space (MB)”,
ROUND((a.tablespace_size * b.block_size) / 1048576, 2) AS “Tablespace size (MB)”,
ROUND(a.used_percent, 2) AS “Used %”
FROM dba_tablespace_usage_metrics a
JOIN dba_tablespaces b ON a.tablespace_name = b.tablespace_name) where TABLESPACE_NAME LIKE ‘%EJEMPLO_TB%’

 

También podemos usar el método “antiguo ” basado en las vista   DBA_FREE_SPACE  uniéndolas con las vistas DBA_DATA_FILES  y s.DBA_TABLESPACES  tanto en oracle 9, 10 y 11:, 

select ‘EJEMPLO2_TB’, usados, total ,100*usados/total used_percent,libre from

(select sum(total)total , sum(usados) usados, sum(libre) libre

from (
Select t.tablespace_name Tablespace, t.status Estado,
ROUND(MAX(d.bytes)/1024/1024,2) Total,
ROUND((MAX(d.bytes)/1024/1024) –
(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) Usados,
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) Libre,
t.pct_increase “% incremento”,
SUBSTR(d.file_name,1,80) “Fichero de datos”
FROM  sys.DBA_FREE_SPACE f, sys.DBA_DATA_FILES d, sys.DBA_TABLESPACES t
WHERE

t.tablespace_name = d.tablespace_name AND
f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id

GROUP BY t.tablespace_name,
d.file_name, t.pct_increase, t.status ORDER BY 1,3 DESC )

where Tablespace=’EJEMPLO2_TB’)

 

 

El resultado de ambos consultas sobre la  vista    DBA_TABLESPACE_USAGE_METRICS  o sobre la  vista  DBA_FREE_SPACE  con datos de ejemplo   las podemos ver reflejados en la siguiente tabla:

TABLESPACE_NAME Used space (MB) Tablespace size (MB) Usado % Esta consulta usa dba_tablespace_usage_metrics
dba_tablespaces
EJEMPLO1_ 454310 572776,94 79,3
  234853 295259,47 79,5
EJEMPLO2 454246 459200,94 98,9 Esta consulta  solo usa dba_fee_space
  234853,3 241833,53 97,1

Es decir,   en los cálculos anteriores    teniendo en cuanta  que la vista  dba_tablespace_usage_metric solo muestra el tamaño total posible ,  como vemos como   la información anterior , solo nos están dando  la volumetría  máxima   posible  y no la real dba_tablespace_usage_metrics

Como  puede  apreciar querido lector en este ejemplo las diferencias son  notables  entre los resultados arrojados por  ambas consultas   :para el caso del primer da una ocupación del 79’3% para el  TB de datos  y 79,5 % de ocupación  para los  TB de los indices  usando la vista dba_tablespace_usage_metric, lo cual da resultados esperanzadores ( aun quedaría  mas del 20% por ocupar) mientras en el segundo caso la query  da una ocupación del 98’9 para el  TB de datos  y 97,1 % de ocupación  para los  TB de los indices  usando la vista dba_fee_space, lo cual son resultados  reales   bastantes alejados de los resultados obtenidos en la primer vista

 

 

 

Espero que encuentre útil la consulta. Aquí está el enlace de la documentación de Oracle sobre la vista:  Oracle Documentation – DBA_TABLESPACE_USAGE_METRICS

 

Como crear un tunel ssh para acceder a una BBDD Oracle


En una presentación de krisrice y thatjeffsmith en KScope15, Jeff señaló algunas nuevas características, incluyendo una para crear túneles SSH usando el programa gratuito que ofrece Oracle para manejar cualquier BBDD ORacle :  el   SQL Developer.

SQL Developer es un entorno de desarrollo de uso general (y gratuito) para la base de datos Oracle. Esta característica parece que debería funcionar independientemente de si realmente desea conectarse a una base de datos Oracle

Sí, puede crear túneles SSH de la forma tradicional utilizando su cliente de línea de comandos SSH favorito   tambien deberia poderse hacer de un ambiente mas potente   como es el entorno de SQL Developer. Este post no es un pros / contras, es sólo una introducción a la función.

Si usted necesita a veces crear túneles de SSH  para lanzar  SQL entonces usted puede encontrar esto interesante.

En esta  introducción se hacen los siguientes supuestos::

  • Tiene un servidor remoto que tiene SSH en ejecución y admite la autenticación basada en claves.
  • Ya tiene sus claves públicas y privadas SSH generadas y correctamente instaladas en el servidor remoto.
  • Debe enviar las solicitudes a cualquier servicio que se ejecute en un puerto del servidor remoto que pueda estar bloqueado por un servidor de seguridad o, de lo contrario, sólo es accesible una vez que haya iniciado sesión en ese servidor remoto. Para este último punto,  se supone que se cuenta con una instancia de una base de datos Oracle que se ejecuta detrás de un firewall en un servidor remoto y el único puerto accesible a los hosts externos es el puerto TCP 22 (predeterminado para SSH). Usando un túnel SSH con reenvío de puertos, puede obtener SQL Developer para arriba y corriendo en su PCl para que pueda conectarse a su instancia de base de datos remota de Oracle. 

Aquí está el  modo  de hacerlo:

  • En primer lugar, si no lo tiene todavía tendrá que descargar SQL Developer versión 4.1 o posterior (en la versión 4.0  esta característica aún no estaba disponible)

 

  • A continuación, abra SQL Developer y elija View > SSH que abrirá el panel de conexiones SSH.
  • En el panel SSH, haga clic con el botón derecho en Hosts SSH y seleccione Nuevo host SSH …
  • Rellene el cuadro de diálogo con sus datos de conexión según sea necesario y pulse OK. Nota: si especifica el puerto incorrecto (como lo hice) en el servidor remoto o desea especificar el puerto TCP para enlazar sus conexiones locales, puede editar las conexiones posteriormente.

 

 

 

  • En este ejemplo,se  esta utilizando claves SSH para conectarse al servidor remoto. Esta es la práctica recomendada y también eliminará el paso de tener que ingresar una contraseña en el futuro. Nota: La clave que especifique en este cuadro de diálogo debe ser su clave privada y su clave pública ya debe estar configurada en el servidor remoto con los permisos correctos para que esto funcione correctamente.
  • El primer puerto especificado es el puerto del servicio (Oracle DB) que se ejecuta en el servidor remoto y el segundo (puerto local) puede ser cualquier puerto disponible en su máquina que desee utilizar. Este túnel enviará el tráfico de su máquina en el puerto local al servidor remoto. En este caso, probablemente recomendaría usar el mismo puerto que el servidor remoto a menos que tenga varios túneles ejecutándose a servidores remotos al mismo tiempo.

 

  • En el panel SSH debe aparecer su nueva conexión. Haga clic con el botón derecho en la conexión y elija Connect .
  • En este punto, si su conexión tiene éxito usted no necesariamente recibe ninguna retroalimentación positiva. Puede comprobar si está conectado al pasar el ratón sobre el túnel SSH con su ratón o haciendo clic con el botón Disconnect en él de nuevo para ver si se le pide que Disconnect .
  • Por último, debe tener un túnel SSH que se ejecute en su máquina local que puede utilizar para acceder a su base de datos remota de Oracle mediante la conexión a localhost:<local port> que reenvía su solicitud de conexión y todos los otros SQL Developer TCP tráfico Al servidor remoto en el puerto TCP que ha especificado. Excepto para el nombre de host y el número de puerto TCP, debe especificar todos los demás detalles de conexión como de costumbre.

 

 

Desde  luego era  una característica muy demandada por la comunidad de usuarios de BBDD Oracle que necesitan conectarse  en un ambiente de host .  Espero que haya encontrado esta visión general de la creación de túneles SSH en Oracle SQL Developer  útil y que le ayude en el camino.