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:
T
(ou TAB
): tabelasI
(ou IND
): índicesL
(ou LOB
): LOBs (large objects)P
(ou PEQ
): pequenosM
(ou MED
): médiosG
(ou GDE
): grandesExemplo: 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.
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).
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 |
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 |
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 |
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.
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 /
© 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.