Monitorando o percentual de espaço livre na tablespace com a view DBA_TABLESPACE_USAGE_METRICS

Olá,

Para qualquer DBA, o monitoramento constante e preventivo do banco de dados é fundamental. Se o mesmo não for monitorado de forma correta, as aplicações e usuários que conectam no banco dados poderão se deparar com problemas inesperados em algum momento. Dentre os mais variados tipos de problemas que podem ocorrer em um banco de dados, um dos que são bem conhecidos é o erro "ORA-01653: não é possível estender a tabela %s.%s em %s na tablespace %s". Apesar de podermos utilizar alguns recursos (RESUMABLE_TIMEOUT) conforme demonstrado no artigo de Agosto/2008 de forma a evitar erros relacionados a falta de espaço livre em uma tablespace, a ocorrência deste tipo de problema demonstra uma falha grave no monitoramento do banco de dados, e em especial, ao monitoramento do espaço livre nas tablespaces do banco de dados. A não ser que algo totalmente repentino e inesperado venha a acontecer de forma que não haja tempo hábil para detectar previamente o problema, este tipo de situação é inadmissível em um ambiente de produção. Neste artigo irei demonstrar como verificar o percentual de espaço livre nas tablespaces de forma pró-ativa. Para fins de didática, leve em consideração os comandos de criação das tablespaces abaixo:

CREATE TABLESPACE TBS_01 DATAFILE 
  'C:\ORACLE\ORADATA\BD01\TBS01.DBF' SIZE 100M AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE TBS_02 DATAFILE 
  'C:\ORACLE\ORADATA\BD01\TBS02.DBF' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 150M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE TBS_03 DATAFILE 
  'C:\ORACLE\ORADATA\BD01\TBS03.DBF' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

Após a criação das tablespaces acima, eu criei algumas tabelas e efetuei vários INSERTS. Após a realização dessas operações, veja na figura abaixo como ficou o percentual de uso nas tablespaces TBS_01, TBS_02 e TBS_03.

 

De acordo com a figura acima, qual das três tablespaces possui um problema crítico de espaço livre? Se sua resposta foi a tablespace TBS_03, infelizmente você errou. Apesar da tablespace TBS_03 estar com 97% do espaço em uso, é a tablespace TBS_01 com apenas 61% do espaço em uso que merece atenção. Por quê? Simplesmente porque a view de dicionário de dados DBA_TABLESPACE_USAGE_METRICS nos mostra isso. Veja o resultado do SQL abaixo:

SQL> select *
  2    from dba_tablespace_usage_metrics
  3   where tablespace_name in ('TBS_01', 'TBS_02', 'TBS_03')
  4  order by 1;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TBS_01                7808           12800           61
TBS_02               10368           19200           54
TBS_03               12416         4194302   ,296020649

Acima, podemos verificar que a coluna USED_PERCENT referente à tablespace TBS_03 possui o valor de cerca de 0,29% de espaço utilizado. Já as tablespaces TBS_02 e TBS_01 possuem os valores 54% e 61% respectivamente. Bom, mas como se chegou a estes valores? Segue a fórmula abaixo:

USED_PERCENT = USED_SPACE / TABLESPACE_SIZE * 100

Vale a pena salientar que os valores das colunas USED_SPACE e TABLESPACE_SIZE são mostrados em unidade de blocos (block size) e não em bytes. Para provar tal afirmação, verifique o quadro abaixo:

TABLESPACE USED_SPACE                     TABLESPACE_SIZE
---------- ------------------------------ -----------------------------------
TBS_01      7808 * 8192 / 1048576 = 61 MB   12800 * 8192 / 1048576 =   100 MB
TBS_02     10368 * 8192 / 1048576 = 81 MB   19200 * 8192 / 1048576 =   150 MB
TBS_03     12416 * 8192 / 1048576 = 97 MB 4194302 * 8192 / 1048576 = 32768 MB

Ficou com dúvidas em relação aos valores da coluna TABLESPACE_SIZE? Bom, já que os três arquivos de dados foram criados com o mesmo tamanho que é de 100 MB, porque somente a tablespace TBS_01 apresentou 100 MB? O interessante a ser notar nos resultados da view DBA_TABLESPACE_USAGE_METRICS é que ela não leva em conta somente a soma do tamanho atual dos arquivos de dados que compõem uma tablespace, mas o tamanho máximo que eles poderão atingir de acordo com a configurações de cada um, ou seja, dependerá também do valor de MAXSIZE caso a opção AUTOEXTEND do arquivo de dados esteja habilitada.

SQL> select file_id,
  2         tablespace_name,
  3         round(bytes/1024/1024) size_mb,
  4         blocks,
  5         autoextensible,
  6         increment_by,
  7         round(maxbytes/1024/1024) max_mb
  8    from dba_data_files
  9   where tablespace_name in ('TBS_01', 'TBS_02', 'TBS_03')
 10  order by 1;

FILE_ID TABLESPACE_NAME  SIZE_MB   BLOCKS AUT INCREMENT_BY    MAX_MB
------- --------------- -------- -------- --- ------------ ---------
      5 TBS_01               100    12800 NO             0         0
      6 TBS_02               100    12800 YES         1280       150
      7 TBS_03               100    12800 YES         1280     32768

Após consultar a view de dicionário de dados DBA_DATA_FILES acima, podemos verificar que os valores da coluna MAX_MB é exatamente o tamanho máximo que o arquivo de dados poderá se estender conforme demonstrado no comando de criação de cada tablespace no início do artigo. De acordo com o resultado acima podemos concluir que:

• TBS_01 - O arquivo de dados 5 pertencente à tablespace TBS_01 não está com AUTOEXTEND habilitado. Portanto, o arquivo não poderá se estender além dos já 100 MB alocados.

• TBS_02 - O arquivo de dados 6 pertencente à tablespace TBS_02 está com AUTOEXTEND habilitado. A coluna INCREMENT_BY nos mostra 1280 blocos de 8 KB, ou seja, quando necessário, o arquivo irá se estender de 10 MB em 10 MB até o máximo de 150 MB.

• TBS_03 - O arquivo de dados 7 pertencente à tablespace TBS_03, está com AUTOEXTEND habilitado. A coluna INCREMENT_BY nos mostra 1280 blocos de 8 KB, ou seja, quando necessário, o arquivo irá se estender de 10 MB em 10 MB até o máximo de 32 GB (32768 MB).

Para fins de informação, o OEM Database Console e OEM Grid/Cloud Control se utilizam da view DBA_TABLESPACE_USAGE_METRICS  para realização de notificações e envio de alertas sobre o percentual de uso das tablespaces, conforme as métricas estabelecidas (85% WARNING e 97% CRITICAL).

Por fim, vale a pena salientar que a view de dicionário de dados DBA_TABLESPACE_USAGE_METRICS não leva em consideração o espaço físico em disco, ou seja, mesmo que o arquivo de dados da tablespace TBS_03 possa se estender até 32 GB, mas o espaço livre em disco seja de 10 GB, o Oracle não terá como saber disso. Portanto, se não for tomada nenhuma atitude, o arquivo de dados continuará a se estender até esgotar todo o espaço em disco, o que geraria um erro catastrófico. Estes testes foram realizados utilizando não só o File System do sistema operacional como também o ASM (Automatic Storage Management).