Critérios de armazenamento no Oracle

Márcio d'Ávila, 19 de janeiro de 2003.
Categoria: Banco de Dados: Oracle

Tablespaces: Organização e Nomenclatura

Definir critérios para a divisão de um banco de dados Oracle em tablespaces é sempre uma questão importante para os DBAs na organização do armazenamento. Uma abordagem amplamente adotada e recomendada tem sido dividir o armazenamento de segmentos em tablespaces de acordo com três características:

De acordo com estes critérios, uma sugestão de formato para nomes de tablespaces no banco de dados Oracle é a seguinte:  S_O_T , onde:

  1. S: Nome ou sigla do sistema a qual pertencem seus objetos.
  2. O: Tipo de objeto dos segmentos contidos
  3. T: Categoria de tamanho dos segmentos contidos

Exemplo: PESSOAL_T_P seria o nome de uma tablespace para o armazenamento das tabelas de tamanho pequeno de um sistema de controle de pessoal (RH). Os respectivos índices destas tabelas estariam em PESSOAL_I_P.

Normalmente, os índices de uma tabela seguem a mesma categoria de tamanho da respectiva tabela. O mesmo pode não ser verdade para segmentos de LOB. Tabelas pequenas em número e tamanho de linhas podem conter colunas LOB (BLOB binário ou CLOB caractere/texto) com grande volume de conteúdo armazenado, de forma que a tabela seja de tamanho P (pequena) mas um segmento de coluna LOB desta tenha categoria de tamanho M ou G.

Tablespaces: Parâmetros por categoria tamanho

Um notório artigo da Oracle "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation", por Bhaskar Himatsingka e Juan Loaiza, Oracle Corporation, apresenta considerações e métricas para uma organização que obtém eficiência e baixa fragmentação e é ao mesmo tempo simples e fácil de implementar e gerenciar. O artigo está disponível no site Oracle Technology Network (OTN), em formato PDF, como paper #711. Para acessar OTN, é necessário criar, gratuitamente, uma conta de usuário no site.

O referido artigo apresenta um conjunto de regras administrativas denominado SAFE, para o armazenamento no Oracle Server 7 e 8 (válido também em versões posteriores). SAFE é uma sigla para Simple Algorithm for Fragmentation Elimination (Algortimo Simples para Eliminação de Fragmentação). A seguir compilamos o conjunto de regras SAFE como parâmetros efetivos para os comandos de criação de tablespace, em cada versão de Oracle Server, para as categorias de tamanho pequena (P), média (M) e grande (G).

Oracle 7

Parâmetro P M G
DEFAULT STORAGE INITIAL 160K 5M 160M
NEXT 160K 5M 160M
PCTINCREASE 0 0 0
DATAFILE SIZE 168K 5128K 161M
AUTOEXTEND ON NEXT 160K 5M 160M
MAXSIZE 1921M 1921M 1921M

Oracle 8

Parâmetro P M G
DEFAULT STORAGE INITIAL 128K 4M 128M
NEXT 128K 4M 128M
PCTINCREASE 0 0 0
DATAFILE SIZE 192K 4160K 129M
AUTOEXTEND ON NEXT 128K 4M 128M
MAXSIZE 1921M 1921M 1921M

Oracle 8i em diante

No Oracle 8i em diante, as métricas para alocação são as mesmas do Oracle 8, mas no formato e sintaxe de criação das tablespaces, deve-se usar o novo modo de gerenciamento de extensões local (mais simples e eficiente que o gerenciamento via dicionário de dados), com alocação uniforme (todas as extensões com mesmo tamanho).

Parâmetro P M G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K 4M 128M
DATAFILE SIZE 192K 4160K 129M
AUTOEXTEND ON NEXT 128K 4M 128M
MAXSIZE 1921M 1921M 1921M

Tabelas: Categorias de tamanho

Além dos parâmetros de tamanho de extensão (extent) por categoria de tamanho de segmento, existe também uma recomendação para o número total de extensões em cada segmento, de forma a garantir o gerenciamento eficiente destas extensões pelo Oracle. Esta recomendação é basicamente válida para todas as versões de Oracle Server citadas aqui.

Assim, é responsabilidade do DBA monitorar o crescimento dos segmentos de tabelas, índices e LOBs, observando aqueles que estiverem próximos de atingir a quantidade de 505 extensões ocupadas. Uma vez atingido esse limiar por um segmento, é aceitável que este segmento continue crescendo sem grande perda de desempenho, mas antes que ele atinja o total máximo recomendado de 1024 extensões, o DBA deve agendar a migração deste segmento para uma tablespace da categoria de tamanho acima da atual.

Considerando os parâmetros de tamanho de extensão em cada categoria de tamanho de segmento no Oracle 8 em diante, multiplicando estes valores pelas quantidades desejada e máxima recomendadas para o número total de extensões de cada segmento, podemos inferir facilmente as faixas de tamanho para que um segmento seja considerado pequeno, médio ou grande, conforme o quadro a seguir.

Desejável (até 505 extents) Máximo (1024 extents)
Pequena Menor que ~63MB Menor que 128MB
Média Entre ~63MB e ~2GB Entre 128MB e 4GB
Grande Acima de ~2GB Acima de 4GB

Estas faixas de tamanho são métricas práticas e particularmente úteis para DBAs, ADs e analistas de sistemas avaliarem o provável tamanho de uma nova tabela que estiver sendo modelada e implementada no banco de dados. A partir de uma avaliação do tamanho médio de uma linha e quantidade máxima de linhas para esta tabela, obtém-se o tamanho provável da tabela, que então pode ser enquadrada como P, M ou G de acordo com as faixas apresentadas.

Tabelas: Classificação de tamanho

Podemos montar e executar o seguinte script SQL-Plus para classificar o tamanho das tabelas já existentes no banco de dados Oracle, de acordo com os critérios e métricas aqui apresentados para definição das categorias de tamanho, e com os dados de armazenamento atuais obtidos na view dinâmica USER_SEGMENTS. Para obter a classificação de todas as tabelas do banco de dados (em todos os esquemas), deve-se usar um usuário DBA no Oracle e substituir a view pela DBA_SEGMENTS.

/*
 * Obt�m o tamanho-base de extent, de acordo com a vers�o do Oracle:
 * 160 para Oracle7
 * 128 para as demais vers�es (8 em diante)
 */
UNDEFINE v_tam_extent_base
COLUMN tam_extent_base NEW_VALUE v_tam_extent_base
select decode(to_number(substr(banner, instr(banner, 'Release ')+8,
        instr(banner, '.', instr(banner, 'Release ')+8)
        - instr(banner, 'Release ')-8))
    , 7, '160', '128') tam_extent_base
from v$version
where banner like 'Oracle%'
/
COLUMN tam_extent_base CLEAR


-- Classifica cada tabela do usu�rio em Pequena, M�dia, ou Grande
COLUMN segment_name FORMAT A30

select segment_name,
  decode(trunc(bytes/1024), 0, to_char(bytes, '9G990'),
    decode(trunc(bytes/1024/1024), 0, to_char(bytes/1024, '9G990D99') || 'K',
      decode(trunc(bytes/1024/1024/1204),
        0, to_char(bytes/1024/1024, '9G990D99') || 'M',
        to_char(bytes/1024/1024/1024, '9G990D99') || 'G'))) tamanho,
  decode(trunc(bytes/&v_tam_extent_base/1024/505),
    0, 'P = &v_tam_extent_base.K * ',
    decode(trunc(bytes/(&v_tam_extent_base/32)/1024/1024/505),
      0, 'M =   ' || &v_tam_extent_base/32 || 'M * ',
      'G = &v_tam_extent_base.M * ')) categoria,
  decode(trunc(bytes/&v_tam_extent_base/1024/505),
    0, ceil(bytes/&v_tam_extent_base/1024),
    decode(trunc(bytes/(&v_tam_extent_base/32)/1024/1024/505),
      0, ceil(bytes/(&v_tam_extent_base/32)/1024/1024),
      ceil(bytes/&v_tam_extent_base/1024/1024))) extents
from user_segments
where segment_type = 'TABLE'
order by bytes desc
/

Firefox - A web de volta
Creative Commons License

© 2003-2020, Márcio d'Ávila, mhavila.com.br, direitos reservados. O texto e c�digo-fonte apresentados podem ser referenciados, distribu�dos e utilizados, desde que expressamente citada esta fonte e o crédito do(s) autor(es). A informação aqui apresentada, apesar de todo o esforço para garantir sua precisão e correção, é oferecida "como está", sem quaisquer garantias explícitas ou implícitas decorrentes de sua utilização ou suas conseqüências diretas e indiretas.