Olá pessoal,
No artigo de hoje vou apresentar um assunto que gera bastante polêmica entre os profissionais que trabalham com Banco de Dados (BD) Oracle e que pretendem otimizar o acesso aos dados através de configurações dos tablespaces. Existe um mito no mundo Oracle que diz que somente separar dados e índices em tablespaces diferentes (ver Imagem 01) pode otimizar consultas aos dados das tabelas envolvidas.
 |
| Imagem 01: Armazenamento de tabelas e índices em Tablespaces Fonte: Oracle Corporation |
A verdade é que somente separar os dados e índices de uma tabela em tablespaces diferentes não é suficiente para otimizar o desempenho das consultas e atualizações. Ao ler as documentações oficiais da Oracle OracleDatabase Performance Tuning Guide 10GR2 e OracleDatabase Performance Tuning Guide 11G Release 2, percebemos que em alguns trechos desses documentos, há informações incompletas que informam que separar os dados e índices em tablespaces diferentes pode otimizar o acesso aos dados. Um benefício pode existir se você configurar o tablespace de índices para não gerar log, porém essa configuração também pode não ter efeito se o BD foi configurado para forçar log (ALTER DATABASE FORCE LOGGING), configuração recomendada para ambientes com Data Guard.
Indo mais a fundo nas documentações citadas no parágrafo anterior, a Oracle informa que a leitura dos dados e índices de uma tabela é feita de forma sequencial, onde o índice é lido primeiro e a tabela é lida em seguida (quando necessário). Esta é uma informação que torna evidente o fato de que apenas separar dados e índices em tablespaces diferentes não pode melhorar a performance das consultas, pois o otimizador de queries do Oracle nunca irá ler as tabelas e índices ao mesmo tempo (ao contrário do que muitos pensam)!
Para melhorar a performance das consultas ao planejar o armazenamento de dados e índices, um dos melhores métodos é não apenas separar dados e índices em tablespaces diferentes, mas além disso, configurar o tablespace de índices para não gerar logs (muito útil p/ qdo for necessário efetuar o rebuild dos índices, mas deve-se avaliar muito bem o ambiente, pois em alguns casos nologging pode não ser uma boa opção) e criar o tablespace de índices com tamanho de blocos maior que o tamanho de blocos padrão do BD (8 kb). Nos treinamentos FABIOPRADO.NET de SQL Tuning e Database Performance Tuning, eu comento que blocos de 16 kb ou 32 kb para índices são ótimas opções para otimizar o acesso aos dados, mas é necessário tomar cuidado com a contenção de linhas quando há muitas atualizações na tabela.
Veja na Imagem 02 o resultado de testes (a média de 10 execuções) que eu fiz para provar o conceito de que as consultas podem ficar mais rápidas se os tablespaces de índices tiverem tamanhos de blocos maiores. Na coluna com fundo verde podemos ver o desempenho otimizado das consultas que utilizam índices armazenados em tablespaces de 32k (comparando-as com tablespaces de 8k, da coluna com valores em cor laranja). Apesar de ficar mais rápido, temos que implementar isso com muita cautela. Em ambientes OLAP geralmente não ocorrem problemas, mas em ambientes OLTP, os tamanhos de blocos maiores podem causar sérios problemas de contenção de linhas nas operações de atualização.
Indo mais a fundo nas documentações citadas no parágrafo anterior, a Oracle informa que a leitura dos dados e índices de uma tabela é feita de forma sequencial, onde o índice é lido primeiro e a tabela é lida em seguida (quando necessário). Esta é uma informação que torna evidente o fato de que apenas separar dados e índices em tablespaces diferentes não pode melhorar a performance das consultas, pois o otimizador de queries do Oracle nunca irá ler as tabelas e índices ao mesmo tempo (ao contrário do que muitos pensam)!
Para melhorar a performance das consultas ao planejar o armazenamento de dados e índices, um dos melhores métodos é não apenas separar dados e índices em tablespaces diferentes, mas além disso, configurar o tablespace de índices para não gerar logs (muito útil p/ qdo for necessário efetuar o rebuild dos índices, mas deve-se avaliar muito bem o ambiente, pois em alguns casos nologging pode não ser uma boa opção) e criar o tablespace de índices com tamanho de blocos maior que o tamanho de blocos padrão do BD (8 kb). Nos treinamentos FABIOPRADO.NET de SQL Tuning e Database Performance Tuning, eu comento que blocos de 16 kb ou 32 kb para índices são ótimas opções para otimizar o acesso aos dados, mas é necessário tomar cuidado com a contenção de linhas quando há muitas atualizações na tabela.
Veja na Imagem 02 o resultado de testes (a média de 10 execuções) que eu fiz para provar o conceito de que as consultas podem ficar mais rápidas se os tablespaces de índices tiverem tamanhos de blocos maiores. Na coluna com fundo verde podemos ver o desempenho otimizado das consultas que utilizam índices armazenados em tablespaces de 32k (comparando-as com tablespaces de 8k, da coluna com valores em cor laranja). Apesar de ficar mais rápido, temos que implementar isso com muita cautela. Em ambientes OLAP geralmente não ocorrem problemas, mas em ambientes OLTP, os tamanhos de blocos maiores podem causar sérios problemas de contenção de linhas nas operações de atualização.
 |
| Imagem 02: Testes de consultas com índices em tablespaces de tamanhos de blocos variados |
Bom... por hoje é só pessoal!
[]s
Mitos caem! ehehe
ResponderExcluirBelo post, muito esclarecedor!
Abraços
capin
Valeu Capin! []s
ExcluirOlá Fabio,
ResponderExcluirum ponto não ficou claro. Lendo o segundo parágrafo fica claro que separar índices de dados em tablespaces diferentes não seria proveitoso "...pois o otimizador de queries do Oracle nunca irá ler as tabelas e índices ao mesmo tempo"
Mas, no terceiro parágrafo, você diz:
"Para melhorar a performance das consultas ao planejar o armazenamento de dados e índices, um dos melhores métodos é, além de separar dados e índices em tablespaces diferentes..."
Afinal, vale a pena separar índices de tabelas ou não?
Sds
Eduardo Morelli
Leonardo, alterei o 3º parágrafo, veja se agora dá para entender melhor!
ExcluirOi Fabio,
Excluirhmmm....
"é não apenas separar dados e índices em tablespaces diferentes,"
Então essa separação é boa?
Sds
Eduardo Morelli
Se vc somente separar dados e índices em tablespaces diferentes, onde ambos os tablespaces tenham as mesmas características, vc não obterá benefício algum de performance!
ExcluirPara melhorar performance, sugiro criar o tablespace de índice com tamanho de bloco maior e/ou com a opção de não gerar log habilitada (NOLOGGING)!
Bom dia,
ExcluirA melhora de separar indices e dados pode também estar associado em seu crescimento natural para aliviar na fragmentação dos dados. Principalmente nos casos de recreate ou reorganização dos indices.
É logico pensar assim ... ?
Att.,
Edson Koller
Edson, existem muitas variantes que podem influenciar na fragmentação, mas acredito também que ao separar qq coisa, consequentemente, os riscos de fragmentação são menores. Seu raciocínio está de acordo com o meu, mas é difícil de encontrar uma confirmação clara disso em documentos da Oracle.
Excluir[]s
Fabio,
ExcluirMe tira uma dúvida. Depois de criado uma instância e definido tamanho do bloco em 8K, eu não consigo criar novas tablespaces com tamanho superior ?
Estou tentando fazer um teste e não aceito block maiores... somente 8192K.
Bancos de dados com uso exclusivo para processamento de FECHAMENTO de informações de várias empresas(pouquíssimos updates) é interessante criar as tablespaces de DADOS e INDICES com block de 32K ?
Obrigado,
Edson Koller
Perfeito.
ResponderExcluirOlá Fábio,
ResponderExcluirPor que na coluna F onde você configura as tablespaces dados e índices com tamanho de 8 e 32K respectivamente e a tablespace de índices como nologging a consulta demora mais do que na configuração inicial com logging e 8K para ambas?
Pelo que entendi no texto esta deveria ser a mais performática, não?
Agradecido desde já.
Franky, nologging não influencia no desempenho de instruções SELECT. Eu nem deveria ter acrescentado estas colunas nestes testes, desconsidere... porém se fossem instruções INSERT, UPDATE ou DELETE, aí sim, o nologging teria que ter um desempenho melhor.
ExcluirDe qq forma, o desempenho tbém não deveria ter caído.. o que eu posso afirmar é que é muito difícil realizar estes tipos de testes com instruções SQL que executam em frações de segundos, portanto,a única explicação que eu tenho é que naquele momento demorou mais pq o processo de usuário deveria estar concorrendo ou aguardando outros processos do BD.
[]s
Entendi. Obrigado Fábio.
ExcluirAbraço
Fala Fábio! Parabéns pelo teste...
ResponderExcluirÉ o seguinte, quando pensamos em separar dados e indices em tablespaces diferentes, podemos ter em mente que ao menos no quesito "concorrência" podemos nos beneficiar, pois como você disse, pode ser que a busca não necessite acessar dados na tablespace da tabela, logicamente seria menos concorrência de acesso à essa tablespace (pelo menos para o usuário que está fazendo consultas). Seria correto pensar assim, ou você acha que essa métrica é praticamente irrelevante ?|
Obrigado e um forte abraço!
Danilo, obrigado pelos comentários!
ExcluirSeu raciocínio está correto! Se tivermos muitos objetos no mesmo tablespace, teoricamente ao separar os índices em um novo tablespace, com datafiles diferentes, em discos diferentes, vc teria menos concorrência, e poderia, deste modo, ter melhor desempenho na aplicação, de um modo geral. Na prática, nunca consegui ver este ganho, até porque, atualmente, na maior parte dos ambientes de produção, os datafiles dos tablespaces estão separados em LVMs, que por trás, gravam os arquivos em vários discos, utilizando por exemplo RAID 10. Resumindo... em um ambiente como este que lhe falei, vc nem precisaria se preocupar em separar os dados das tabelas e índices em tablespaces diferentes, espalhamento dos dados em vários discos já é feita automaticamente. A separação em tablespaces ainda é útil mais para fins de organização, gerenciamento e recuperação, ok?
[]s