Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


terça-feira, 1 de janeiro de 2013

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

Por Eduardo Legatti


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).



12 comentários:

Anônimo disse...

Muito bem explicado o artigo. Obrigado por compartilhar seu conhecimento!!

Neella´s disse...

Artigo muito bom!! Parabens!! Voce poderia me dizer qual a query utilizada que mostra exatamente os valores informados da figura 01?

Obrigada,

Eduardo Legatti disse...

Olá Neella's

Eu utilizei uma ferramenta gráfica para mostrar essas informações. Não sei qual o SQL que foi utilizado, mas acredito que usando as views de dicionário de dados abaixo e fazendo os joins certos, você chegará em um resultado similar.

As views são:

dba_free_space, dba_data_files, dba_tablespaces

Abraços,

Legatti

Erik Castilho disse...

Eduardo, é possível consultar quais objetos estão dentro de um tablespace?

Eduardo Legatti disse...

Olá Erik,

Sim. Você pode consultar a view DBA/ALL/USER_SEGMENTS. Por exemplo, quero ver todos os objetos (segmentos) que estão na tablespace USERS:

select * from dba_segments where tablespace_name='USERS';

Abraços,

Legatti

Erik Castilho disse...

Eduardo, obrigado pela resposta acima. Me tire outra dúvida se puder por favor, sobre a tablespace Temporary, tenho uma com datafile de 2gb que esta 100% do espaço utilizado e a equipe de desenvolvimento esta com uma aplicação onde não esta conseguindo listar alguns dados e eles estão me alegando que o problema é nesta tablespace, criei vários outros datafiles, deixei ela com 32g e mesmo assim ela fica 100% do espaço utilizado, sabe me dizer o porque que isso pode estar ocorrendo?

Eduardo Legatti disse...

Olá​ Erik,

Tem que verificar quais sessões e instruções SQL estão consumindo espaço na tablespace TEMP no momento em que ela está sendo usada.

O SQL abaixo pode ajudar.

SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;


Abraços,

Legatti

Erik Castilho disse...

Olá Eduardo,

Vou acompanhar nos próximos dias, obrigado!

Abraços!

Analista de Sistemas Senior disse...

Legatti, bom dia ! Como vai ?
Excelente esse artigo sobre tablespace.
Uma dúvida, por gentileza, mesma tem a configuração de autoextensible para Sim, com o tamanho máximo definido em 32GB, se chegar nesse valor, continua extendendo ? Caso negativo, o que seria necessário fazer, aumentar o tamanho Máximo ?
Grato.

Eduardo Legatti disse...

Olá,

Se o máximo a ser estendido é de 32GB, então o mesmo não será mais estendido após chegar a esse valor. Se o bloco que o banco de dados/Tablespace está usando é de 8KB, então o tamanho máximo de um datafile será de 32GB. Nesse o caso, o que se deve fazer é criar um novo datafile na tablespace em questão para comportar o crescimento dos dados.

Abraços,

Legatti

flaviogcmelo disse...

Legatii, parabéns pelo artigo, bem explicado. Minha questão é se existem visões que podem ser acessadas por usuários que não tem a role de DBA?? Em certos casos somos surpreendidos por cargas muito grandes que acabam levando a gargalos ou em último caso precisam ser abortadas para que possamos entregar novos data files ou até discos para o ASM.

Eduardo Legatti disse...

Olá Flávio,

Acredito que voce pode criar uma role específica para ser concedida a outros usuários não DBAs e conceder permissoes de leitura a essas roles em algumas views, como GRANTS SELECT ANY VIEW... Pode também ser concedidas acessos a views especificas
a usuários específicos.


Postagens populares