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


terça-feira, 17 de maio de 2011

Obtendo o tamanho dos segmentos de um schema de banco de dados: (Tabelas, Índices, LOBs)

Por Eduardo Legatti

Olá,

O objetivo deste artigo será responder a seguinte pergunta. Qual é mesmo o tamanho de uma tabela no Oracle? Bom, através das views de dicionário de dados DBA/ALL/USER_SEGMENTS, pode-se facilmente obter essa informação, e de várias formas. No entanto, a intenção deste artigo é demonstrar que o tamanho de uma tabela (em bytes) vai muito além do que focar especificamente no segmento da tabela. Sabemos que no Oracle existem vários tipos de segmentos, como segmentos de tabela, índice, LOB (large objects), UNDO, TEMP, PARTITION, ente outros, mas irei focar naqueles que geralmente estão mais presentes na maioria dos schemas de banco de dados. Possivelmente alguém não irá concordar comigo, mas, na minha visão, o tamanho real de uma tabela compõe-se não só do tamanho do seu próprio segmento, mas também da soma do tamanho de todos os outros segmentos que dependem dela. Para demonstrar tais informações, eu criei uma consulta SQL que mostra essas informações logo mais abaixo.

Bom, após criar 9 tabelas devidamente populadas no meu schema de banco de dados, a soma do tamanho de todos os segmentos foi de aproximadamente 3,14 GB como demonstrado pela query abaixo:

SQL> select count(*) segments,
  2         round(sum(bytes)/1024/1024,2) size_mb
  3    from user_segments;

  SEGMENTS    SIZE_MB
---------- ----------
       250    3216,19

Vale a pena salientar que algumas dessas tabelas são particionadas, outras possuem índices normais, locais ou globais, e outras tabelas possuem colunas do tipo CLOB. Portanto, podemos verificar pelo resultado da query acima que a criação de 9 tabelas gerou um total de 250 segmentos. Agora irei executar a query que eu criei de forma a demonstrar como esses 3,14 GB estão distribuídos entre essas 9 tabelas.

SQL> break on report
SQL> compute sum of data_mb on report
SQL> compute sum of indx_mb on report
SQL> compute sum of lob_mb on report
SQL> compute sum of total_mb on report

SQL> select table_name,
  2    decode(partitioned,'/','NO',partitioned) partitioned,
  3    num_rows,
  4    data_mb,
  5    indx_mb,
  6    lob_mb,
  7    total_mb
  8     from (select data.table_name,
  9             partitioning_type
 10             || decode (subpartitioning_type,
 11                        'none', null,
 12                        '/' || subpartitioning_type)
 13                    partitioned,
 14             num_rows,
 15             nvl(data_mb,0) data_mb,
 16             nvl(indx_mb,0) indx_mb,
 17             nvl(lob_mb,0) lob_mb,
 18             nvl(data_mb,0) + nvl(indx_mb,0) + nvl(lob_mb,0) total_mb
 19             from (  select table_name,
 20                   nvl(min(num_rows),0) num_rows,
 21                   round(sum(data_mb),2) data_mb
 22                      from (select table_name, num_rows, data_mb
 23                          from (select a.table_name,
 24                                a.num_rows,
 25                                b.bytes/1024/1024 as data_mb
 26                                  from user_tables a, user_segments b
 27                                  where a.table_name = b.segment_name))
 28                 group by table_name) data,
 29                 (  select a.table_name,
 30                        round(sum(b.bytes/1024/1024),2) as indx_mb
 31                     from user_indexes a, user_segments b
 32                       where a.index_name = b.segment_name
 33                    group by a.table_name) indx,
 34                 (  select a.table_name,
 35                       round(sum(b.bytes/1024/1024),2) as lob_mb
 36                    from user_lobs a, user_segments b
 37                   where a.segment_name = b.segment_name
 38                    group by a.table_name) lob,
 39                   user_part_tables part
 40             where     data.table_name = indx.table_name(+)
 41                   and data.table_name = lob.table_name(+)
 42                   and data.table_name = part.table_name(+))
 43  order by table_name;


TABLE_NAME  PARTITIONED  NUM_ROWS   DATA_MB   INDX_MB    LOB_MB  TOTAL_MB
----------- ----------- --------- --------- --------- --------- ---------
T1          NO            5912285       576      1160         0      1736
T2          RANGE         1597647     75,81     70,94         0    146,75
T3          NO             700890        80         0         0        80
T4          NO              24008         4        ,5       287     291,5
T5          RANGE/HASH      60000      66,5     11,25       1,5     79,25
T6          RANGE/LIST    1572864    236,44    154,31     96,69    487,44
T7          LIST          1527191        23        95         0       118
T8          NO              19441         3      1,25       271    275,25
T9          NO              10565         2         0         0         2
                                  --------- --------- --------- ---------
sum                                 1066,75   1493,25    656,19   3216,19

9 linhas selecionadas.
Link
Perceberam que do total de 3216,19 MB, 33% (1066,75 MB) representam segmentos de dados de tabela, 47% (1493,25 MB) segmentos de índices e 20% (656,19 MB) segmentos de LOB? O gráfico abaixo nos dá uma visão mais clara.



Através das informações retornadas pela query, vemos que o segmento de dados da tabela T1 possui um tamanho de 576 MB (em aproximadamente 6 milhões de linhas), e que os índices desta tabela somam 1160 MB dando um total de 1736 MB. Portanto, na minha visão, o tamanho real desta tabela é de 1736 MB, e não 576 MB como muitos poderão dizer. Lembrem-se de que eu não estou falando apenas do segmento de tabela (T1), mas de todos os outros que dependem dela, como segmentos de índice e de lob por exemplo.

Vejamos agora a tabela T4. Apesar do tamanho do seu segmento de dados ter apenas 4 MB, a mesma tem como dependentes um ou mais índices que somam 0,5 MB e um ou mais segmentos lob que somam 287 MB. Portanto, para mim, o tamanho real desta tabela é de 291,5 MB e não apenas 4 MB.

Se alguém me perguntasse qual o espaço necessário que precisaria ser alocado na tablespace para duplicar as linhas da tabela T6 nela própria, eu diria que precisaríamos de no mínimo 487,44 MB e não 236,44 MB.

Portanto, deixo aqui a dica e o meu ponto de vista de não apenas focarmos no tamanho do segmento da tabela, mas também de prestarmos atenção em todos os outros segmentos dependentes dela. Isso com certeza fará diferença quando fizermos algum trabalho relacionado à estimativa para alocação de espaço destes objetos, ou até mesmo nas estimativas de espaço necessárias para uma migração de dados, etc...

Por fim, o gráfico abaixo nos mostra todos os tipos de segmentos que foram criados e a proporção de espaço alocado de cada um.



Google+

3 comentários:

Eduardo Legatti disse...

Olá,

Para quem necessitar do código SQL da query, a mesma poderá ser baixada pelo link abaixo:

http://dl.dropbox.com/u/15423203/oracle/user_size_tables.sql

Abraços

Legatti

Airton Roos disse...

Bom dia Eduardo,

Tudo beleza? muito legal o post, só que ao fazer o teste no banco 10g (Windows), os totais não bateram, isto é:

Na query abaixo, retornou o total de 3048,31 (size_mb).

select count(*) segments, round(sum(bytes)/1024/1024,2) size_mb
from user_segments


Ao executar a outra query o total foi de 2901,19 (size_mb).

Fiz uma alteração no código abaixo:
.
.
.
SELECT a.table_name, a.num_rows, b.bytes/1024/1024 AS data_mb
FROM user_tables a, user_segments b
WHERE a.table_name = b.segment_name(+)
.
.
.
Executei novamente a consulta e o total foi de 3047,97 (size_mb)

Mesmo assim ficou uma pequena diferença, isso pode ocorrer? (3048,31 - 3047,97 = 0,34)

Obrigado.

Abraço,

Airton

Eduardo Legatti disse...

Olá Airton,

Acredito que não era para dar diferença, a não ser que no seu schema de banco de dados tenha algum tipo de segmento específico que a consulta não esteja abordando. Tente analisar qual ou quais tabelas específicas não estão batendo. Vai ser um pouco trabalhoso, mas você vai ter que comparar o total de segmentos para cada tabela e verificar quais não estão batendo. Por exemplo, para a tabela X tente veirificar o tamanho dela (data_mb), o tamanho total de todos os índices dela (indx_mb) e o tamanho total de todos os lobs dela (lob_mb) para então comparar com os valores de data_mb, indx_mb e lob_mb dessa tabela específica na consulta. Pode ser que algum segmento específico não está sendo abordado pela consulta.

Tente verificar se existe algum algum tipo de segmento no seu schema que talvez não esteja sendo abordado na consulta. ex: select distinct segment_type from user_segments;

Abraços,

Legatti

Postagens populares