Performance de consultas em Tablespaces separados para Dados e Índices
Introdução
No artigo de hoje vou falar rapidamente sobre 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 pode otimizar consultas aos dados das tabelas envolvidas.
A verdade
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 Oracle Database Performance Tuning Guide 10GR2 e Oracle Database 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)!
Como realmente otimizar performance
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 (avaliar muito bem o ambiente pois em alguns casos isso 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). Em testes que eu demonstro nos meus treinamentos de SQL Tuning, blocos de 16 kb ou 32 kb para índices são ótimas opções para otimizar o acesso aos dados. Mais detalhes sobre o porquê do tamanho de bloco maior melhorar performance e como configurá-los, eu deixo para explicar nos meus treinamentos de SQL Tuning e Performance Tuning em Bancos de Dados.
CONCLUSÃO
Apenas separar dados e índices em tablespaces separados não é suficiente para otimizar performance. Para otimizar performance, além de fazer a separação, é necessário implementar configurações diferenciadas (tais como nologging e tamanhos de blocos maiores) nos tablespaces de índices.
Referências
Vc colocou blocos de 16 kb ou 32 kb para índices são ótimas opções para otimizar o acesso aos dados. Mas, neste caso essa blocagem favorece operações de DML ?
Jorge, ótima pergunta. Tamanho de blocos maiores só favorece consultas. A performance de DMLs é normalmente pior com bloco maiores em tabelas onde há muita concorrência e linhas pequenas, pois haverá mais linhas por bloco, e consequentemente maior concorrência (espera por latches) de acessso a esses blocos na Buffer Cache.