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

 

Deja una respuesta