SQL para conocer el tamaño de los Tablespaces

En las actividades de monitoreo, uno de los puntos es revisar los tablespaces de la Base de Datos, para las BD Oracle, esto es importante ya que todos aquellos que no tienen una asignación automática de espacio, en caso de que se llene, comienzan los errores y en cierto punto una caída de la BD


También es útil conocer este dato, para llevar un estadístico de crecimiento, de forma que pueda uno solicitar los soportes (discos duros) o infraestructura necesaria tomando como referencia dicha estadistica.


El query que nos permitirá conocer estos datos es el siguiente:


SELECT trunc(sysdate) FECHA,
df.tablespace_name TABLESPACE,
df.total_space_mb "TOTAL MB",
(df.total_space_mb - fs.free_space_mb) "UTILIZADO MB",
fs.free_space_mb "LIBRE MB",
ROUND(100 * (fs.free_space / df.total_space),2) "% LIBRE"
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;

El primer campo, que es la fecha, es para después poder hacer las comparativas de crecimiento entre periodos

Modificación, este query es el utilizado por el Enterprise Manager

SELECT trunc(sysdate) FECHA, d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
        TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", 
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') "Used (M)",
        TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
        FROM sys.dba_tablespaces d, (select 
tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select 
tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE 
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT 
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
        UNION ALL
        SELECT trunc(sysdate) FECHA, d.status "Status", d.tablespace_name "Name", d.contents "Type",
        d.extent_management "Extent Management", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
        TO_CHAR(NVL(t.bytes,0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999999.999') "Used (M)", 
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_tablespaces d, (select 
tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select 
tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE 
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND 
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';


Ya si requieren ver a nivel de Datafile, este query es el indicado

SELECT dd.tablespace_name tablespace_name,
dd.file_name file_name,
dd.bytes/1024 TABLESPACE_KB,
SUM(fs.bytes)/1024 KBYTES_FREE,
MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;

Calcular el Caché Hit Ratio o Aciertos de Caché

Uno de los detalles que he tenido como DBA es la dependencia de una herramienta para realizar el monitoreo de mi BD.


Buscando en Internet y con un poco de paciencia, probando los resultados, me encuentro con unos querys que retornan el famodo Cache Hit Ratio:


SELECT (1-(physical_reads/(db_block_gets+consistent_gets)))*100 "Cache Hit Ratio" FROM v$buffer_pool_statistics;


ó



SELECT to_char(sysdate,'DD-MM-YY HH:Mi:SS') as SYS_DATE, Sum(Decode(a.name, 'consistent gets', 
       a.value, 0)) "Consistent Gets",
       Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
       Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
       Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
         Sum(Decode(a.name, 'db block gets', a.value, 0)) -
         Sum(Decode(a.name, 'physical reads', a.value, 0))  )/
           (Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
             Sum(Decode(a.name, 'db block gets', a.value, 0))))
             *100,2) "Hit Ratio %"
FROM   v$sysstat a;



Con ellas pueden obtener el Hit Ratio y tomar la decisión necesaria para optimizar su BD

Inauguración

Este es un espacio para subir preguntas, y dar un aporte en agradecimiento a las personas que me han apoyado en mi formación como DBA, aunque no les devuelva el favor directo, espero poder apoyar a otras personas.