Diferenças entre os otimizadores de consulta do banco de dados

Curso de Engenharia de Computação
DIFERENÇAS ENTRE OS OTIMIZADORES DE CONSULTA
DO BANCO DE DADOS ORACLE
BASEADO EM REGRAS E BASEADO EM CUSTO
João Paulo Geraldini Nunes
Itatiba – São Paulo – Brasil
Dezembro de 2009
ii
Curso de Engenharia de Computação
DIFERENÇAS ENTRE OS OTIMIZADORES DE CONSULTA
DO BANCO DE DADOS ORACLE
BASEADO EM REGRAS E BASEADO EM CUSTO
João Paulo Geraldini Nunes
Monografia apresentada à disciplina, Projetos em
engenharia de computação II, do Curso de Engenharia de
Computação da Universidade São Francisco, sob a
orientação do Prof. Ms. José Aparecido Carrilho, como
exigência parcial para conclusão do curso de graduação.
Orientador: Prof. Ms. José Aparecido Carrilho
Itatiba – São Paulo – Brasil
Dezembro de 2009
iii
DIFERENÇAS ENTRE OS OTIMIZADORES DE CONSULTA
DO BANCO DE DADOS ORACLE BASEADO EM REGRAS E
BASEADO EM CUSTO
João Paulo Geraldini Nunes
Monografia defendida e aprovada em Dezembro de 2009 pela Banca
Examinadora assim constituída:
Prof. Ms. José Aparecido Carrilho (Orientador)
USF – Universidade São Francisco – Itatiba – SP.
Prof. Ms. Claudio Maximiliano Zaina
USF – Universidade São Francisco – Itatiba – SP.
Prof. Bel. Angelo Amaral
USF – Universidade São Francisco – Itatiba – SP.
O registro de arguição e defesa consta de “ATA DE ARGUIÇÃO FINAL DE
MONOGRAFIA”, devidamente assinada e arquivada na Coordenação do curso.
iv
"A mente que se abre a uma nova idéia jamais
voltará ao seu tamanho original."
(Albert Einstein)
v
Aos meus familiares e amigos que tanto contribuíram
para a minha formação.
vi
Agradecimentos
Agradeço a meu orientador José Aparecido Carrilho, aos supervisores das matérias de
Trabalho de conclusão de curso I e Projetos em engenharia de computação II, Alencar de
Melo Júnior e André Leon Sampaio Gradvohl respectivamente, a todos meus amigos e
familiares que colaboram para a elaboração dessa monografia.
vii
Sumário
Lista de Siglas ........................................................................................................................ viii
Lista de Figuras ....................................................................................................................... ix
Lista de Tabelas ........................................................................................................................ x
Resumo ..................................................................................................................................... xi
Abstract ................................................................................................................................... xii
1
Introdução .......................................................................................................................... 1
1.1 Objetivos ....................................................................................................................... 1
1.1.1 Objetivo geral ......................................................................................................... 1
1.1.2 Objetivo específico ................................................................................................. 2
1.2 Estrutura do trabalho ..................................................................................................... 2
2
Ambiente de Desenvolvimento dos Testes ....................................................................... 3
3
Planejamento de Desempenho .......................................................................................... 4
3.1 Escalabilidade................................................................................................................ 4
3.2 Componentes de Hardware ........................................................................................... 4
3.3 Componentes de Software............................................................................................. 5
3.4 Princípios de Projeto de Aplicações .............................................................................. 5
3.5 Plano de Execução ........................................................................................................ 6
4
Otimizadores ...................................................................................................................... 8
4.1 Vantagens da otimização ............................................................................................... 8
4.2 Otimizadores baseados em regras (RBO) ..................................................................... 9
4.2.1 Porque entender o RBO .......................................................................................... 9
4.2.2 Regras Internas ....................................................................................................... 9
4.3 Otimizadores baseados em custo (CBO) ..................................................................... 10
4.3.1 Estatísticas ............................................................................................................ 10
4.3.2 Recolhendo estatísticas ......................................................................................... 11
4.4 Sugestões ..................................................................................................................... 11
4.5 Comparação dos otimizadores .................................................................................... 11
4.5.1 Configuração das bases de dados ......................................................................... 12
4.5.2 Metodologia de comparação ................................................................................. 12
4.5.3 Consultas realizadas ............................................................................................. 12
5
Conclusão .......................................................................................................................... 23
5.1 Trabalhos futuros......................................................................................................... 23
Referências Bibliográficas ..................................................................................................... 24
viii
Lista de Siglas
CBO
Cost-Based Optimizer
CPU
Central Processing Unit
E/S
Entrada e saída
RBO
Rule-Based Optimizer
SEQUEL
Structure Query Language
SQL
Structure Query Language
ix
Lista de Figuras
Figura 3.1 - Visualização de uma consulta e seu plano de execução no Oracle. ....................... 6
Figura 3.2 – Redução no consumo de CPU na realização de uma consulta repetidas vezes. .... 7
Figura 4.1 – Processamento de consulta SQL. Fonte: Oracle9i Database Performance Tuning
Guide and Reference. Release 2 (9.2) ........................................................................................ 8
Figura 4.2 - Exemplo de uso de HINT. ..................................................................................... 11
Figura 4.3 - Comando SQL para alterar o método de otimização. ........................................... 12
Figura 4.4 - Consultando o otimizador da base de dados CBO. ............................................... 12
Figura 4.5 - Consulta 1 realizada para testes de desempenho. ................................................. 13
Figura 4.6 - Resposta obtida na primeira vez que a consulta 1 foi realizada na base RBO. .... 13
Figura 4.7 - Resposta obtida na vigésima vez que a consulta 1 foi realizada na base RBO. ... 14
Figura 4.8 - Resposta obtida na primeira vez que a consulta 1 foi realizada na base CBO. .... 14
Figura 4.9 - Resposta obtida na vigésima vez que a consulta 1 foi realizada na base CBO. ... 14
Figura 4.10 - Consulta 2 realizada para testes de desempenho. ............................................... 15
Figura 4.11 - Resposta obtida na primeira vez que a consulta 2 foi realizada na base RBO. .. 15
Figura 4.12 - Resposta obtida na vigésima vez que a consulta 2 foi realizada na base RBO. . 16
Figura 4.13 - Resposta obtida na primeira vez que a consulta 2 foi realizada na base CBO. .. 16
Figura 4.14 - Resposta obtida na vigésima vez que a consulta 2 foi realizada na base CBO. . 17
Figura 4.15 – Gráfico referente à consulta A ........................................................................... 19
Figura 4.16 – Gráfico referente à consulta B ........................................................................... 19
Figura 4.17 – Gráfico referente à consulta C ........................................................................... 20
Figura 4.18 – Consulta realizada na base RBO. ....................................................................... 20
Figura 4.19 - Gráfico referente às consulta para testes do uso de hints ................................... 21
Figura 4.20 – Plano de execução da consulta na base CBO. .................................................... 21
Figura 4.21 – Plano de execução da consulta na base RBO com o uso de hints. ..................... 22
Figura 4.22 – Plano de execução da consulta na base RBO sem o uso de hints. ..................... 22
x
Lista de Tabelas
Tabela 4.1 - Tempos de resposta para consulta 3. .................................................................... 17
Tabela 4.2 – Consultas realizadas............................................................................................. 18
Tabela 4.3 - Tempos de resposta para as consultas A, B e C nos 2 otimizadores. ................... 18
Tabela 4.4 – Tempos de respostas para a consulta no RBO com o uso de hints e no CBO. .... 20
xi
Resumo
Esse trabalho aborda as diferenças entre os otimizadores de consulta disponíveis no
banco de dados Oracle 11g. O trabalho descreve o planejamento de desempenho da Oracle®,
mostrando alguns dos principais pontos a serem observados para que seja possível o
entendimento do ajustamento (tuning) por parte do usuário. O trabalho explica o
funcionamento dos otimizadores de consultas, mostrando os dois tipos de otimizadores
disponíveis: o otimizador baseado em regra e o otimizador baseado em custo, mostrando as
vantagens da otimização, as funcionalidades de cada otimizador e algumas formas para
melhorar o desempenho, tais como o uso de sugestões (hints).
Após configurar duas bases de dados idênticas, cada uma com um otimizador, foi
iniciada a fase de testes. Para esses testes foram realizadas algumas consultas repetidas vezes
em cada base, visando observar a melhora do desempenho a cada consulta e com isso analisar
os otimizadores mostrando possíveis formas de utilização. A partir dessas analises, foi
possível tirar as conclusões sobre cada um dos otimizadores e do uso de sugestões para o
melhoramento do desempenho.
PALAVRAS-CHAVE: Banco de dados Oracle, Desempenho, Otimizador baseado em
Regra, Otimizador baseado em Custo, SQL.
xii
Abstract
This work focuses on the differences between the query optimizers available on the
Oracle Database 11g. The work refers to the planning performance of Oracle, showing some
of the main points to be observed for possible understanding of the tuning by the User. The
work explains how the query optimizer work, showing the two types of available optimizers:
the rule based optimizer and the cost-based optimizer, showing the benefits of optimization,
the features of each optimizer, and some ways to improve performance, such as the use of
hints.
After setting up two identical databases, each one with an optimizer type, begun the
testing phase. For these tests were carried out some repeated queries in each base in order to
observe the performance improvement at each visit and with that analyzing the optimizers
showing some possible ways of use. Based on this analysis it was possible to draw
conclusions about each one of the optimizers and the use of hints for improving the
performance.
KEY WORDS: Oracle Database, Performance, Rule Based Optimizer, Cost-Based Optimizer,
SQL, Hints.
1
1 INTRODUÇÃO
Os sistemas de banco de dados relacionais necessitam de uma linguagem que seja fácil
para os usuários obterem as informações neles armazenadas e a SQL (Structured Query
Language) é a linguagem comercial mais utilizada no mercado[1]. A SQL oferece diversos
recursos além da consulta aos dados, como instruções de manipulação e definição de dados.
Inicialmente chamada de SEQUEL foi criada pela IBM® no laboratório de pesquisa de San
Jose nos anos 70 [2], desde então a SQL tem evoluído e com o mercado cada vez mais
competitivo o desempenho dos softwares precisa ser cada vez maior.
Para melhorar o desempenho nas consultas a Oracle criou os otimizadores de consulta
que determinam o caminho mais eficiente para a execução de uma instrução SQL depois de
considerar vários fatores relacionados aos objetos referenciados e as condições especificadas
na consulta [3]. Otimizadores de consulta são essenciais para realização de tuning de
aplicações em Oracle. Tuning consiste em realizar as tarefas no menor tempo possível, com o
menor processamento e com maior qualidade. Para isso é necessário a construção de um
Planejamento de Desempenho (Performance Planning) e, na maioria das vezes, as aplicações
possuem vários pontos relevantes, tais como, tipo de máquina para ser o servidor, se a
aplicação é duas camadas ou três camadas, onde acontecerá o processamento, cliente ou
servidor, quais são as regras de negócio, qual a latência na rede, se essa latência é relevante,
entre outros vários pontos a serem tratados.
Conhecer o funcionamento dos otimizadores, apesar de ser apenas uma parte do tuning, é
de grande importância e deve ser tratado com atenção para obter a otimização esperada nas
consultas. No Oracle existem dois tipos de otimizadores, o otimizador baseado em regras de
negocio (Rule Based Optimizer - RBO) e o otimizador baseado em custo (Cost-Based
Optimizer - CBO). O CBO surgiu no Oracle 7 e a partir do Oracle 10g passou a ser o único
otimizador a ter suporte, porém a Oracle manteve as funcionalidades do RBO para que não
perdesse a compatibilidade com as implementações anteriores.
1.1 Objetivos
1.1.1 Objetivo geral
Esse trabalho analisa as diferenças entre os otimizadores de consultas implementados
pelo banco de dados da Oracle, com o objetivo de mostrar as funcionalidades de cada um e a
2
importância deles no tuning de aplicação, isso para que os usuários entendam o
funcionamento dos mesmos de maneira que facilite uma possível migração ou implementação
desses otimizadores.
1.1.2 Objetivo específico
Comparar os dois otimizadores, mostrando as qualidades e os problemas de cada um,
chegando a uma conclusão de qual o melhor otimizador para cada tipo de aplicação.
1.2 Estrutura do trabalho
No Capítulo 2 é descrito o ambiente no qual foram realizados os testes dos otimizadores
de consulta, bem como as tabelas que foram utilizadas. O Capítulo 3 contém uma introdução
ao Planejamento de Desempenho e seus principais pontos para a implementação do tuning.
No Capítulo 4 são analisados os otimizadores de consulta, descrevendo suas funcionalidades,
o uso sugestões (hints) e realizando comparações entre eles. As conclusões deste trabalho são
apresentadas no Capítulo 5.
3
2 AMBIENTE DE DESENVOLVIMENTO DOS TESTES
Nesse trabalho foram estudados dois otimizadores implementados pela Oracle,
comparando o funcionamento dos mesmos através de consultas realizadas em uma base de
dados com os CEP(s), mostrando alguns pontos fortes e fracos de cada um deles.
Para o desenvolvimento do trabalho, será utilizado um computador portátil da marca
DELL modelo LATITUDE 131L com a seguinte configuração:
•
Processador AMD Turion 64 X2 Mobile 1.6GHz
•
2 GB de RAM
•
Disco Rígido de 80 GB
•
Windows XP Home Edition SP2
•
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
As bases de dados utilizadas tanto para o RBO quanto para o CBO são exatamente
iguais, mudando apenas o otimizador utilizado nas consultas. Contendo um total de 738227
registros referentes aos CEPs do Brasil, as bases são descriminadas em 4 (quatro) tabelas:
•
ENDERECO, 699307 registros;
•
BAIRRO, 28871 registros;
•
CIDADE, 10022 registros;
•
UF, 27 registros.
4
3 PLANEJAMENTO DE DESEMPENHO
Com a evolução da Internet, os bancos de dados desempenham um papel mais importante
nos negócios em aplicações e com isso a importância no aumento do desempenho do sistema
tem crescido cada vez mais.
O desempenho deve ser projetado junto com o sistema. Quando isso não ocorre,
geralmente, tem-se como resultado problemas de contensão ou esgotamento de um ou mais
recursos do sistema[4]. Para isso é necessário observar alguns pontos importantes como,
escalabilidade, componentes de hardware, componentes de software e os princípios de design
de aplicações.
3.1 Escalabilidade
Escalabilidade é a capacidade de aumentar o trabalho dos processos com um aumento
proporcional do uso de recurso; basicamente, é a capacidade de atender ou estar preparado
para atender uma demanda crescente de trabalho. Uma escalabilidade ruim aumenta o número
de lógicas de E/S (entrada e saída) para um mesmo resultado final.
3.2 Componentes de Hardware
Os componentes de hardware podem ser divididos em quatro grupos[3].
•
Processador: pode haver um ou mais processadores, desde os sistemas mais
simples, tais como, computadores de mão e celulares, por exemplo, até servidores
de alta potência;
•
Memória: as aplicações exigem uma quantidade considerável de memória cache e
memória principal. Como esse recurso é finito, o uso otimizado da mesma é um
fator contribuinte para a otimização[5];
•
Subsistemas de E/S: o desempenho de muitas aplicações de banco de dados é,
muitas vezes, limitado pelos sistemas de E/S. Os subsistemas podem variar desde
os discos rígidos dos clientes até a alta performance dos storages dos servidores.
Podem ser executados milhares de operações E/S;
•
Rede: todos os computadores que usam o sistema devem estar conectados em uma
rede e as principais preocupações são a largura de banda e a latência.
5
3.3 Componentes de Software
Os componentes mais comuns são[3]:
•
Interface de gerenciamento: componente mais visível pelos usuários que utilizam
as aplicações;
•
Implementação de lógica de negócios: esse componente implementa o núcleo das
regras de negócio, qualquer erro pode ser muito custoso para o projeto;
•
Gerenciamento de requisição e alocação de recursos pelo usuário: as requisições
de recursos pelos usuários geralmente são tratadas pelo servidor de banco de dados
ou pelo sistema operacional. Deve-se ter um cuidado especial com esse
componente em sistemas onde o número de usuário é desconhecido ou cresce
muito rapidamente;
•
Gerenciamento de dados e transações: esse componente está em grande parte sob
responsabilidade do banco de dados e do sistema operacional.
3.4 Princípios de Projeto de Aplicações
Para um bom resultado final é necessário seguir alguns princípios no momento de fazer
o projeto. Quanto mais simples e fácil de compreender, maior é a chance de estar correto.
Estruturas, ferramentas e máquinas bem concebidas geralmente são mais viáveis e fáceis de
usar e manter.
A modelagem de dados deve ser feita cuidadosamente, de maneira que atenda as
práticas comerciais. O uso de ferramentas de modelagem pode gerar rapidamente esquemas e
definições, e é útil quando é necessário um rápido protótipo[4].
Tabelas e índices são componentes de grande responsabilidade pela flexibilidade e
desempenho do núcleo das transações. Ao criar o projeto das tabelas e índices deve-se seguir
a modelagem, além de normalizar as tabelas. As tabelas devem ser criadas observando alguns
pontos essenciais, como organização das colunas na tabela, tipos de índices utilizados, custo
do índice em uma consulta, uso de índices em série e adição de colunas em índices ou tabelas
de índices organizados.
Outra forma de simplificar o projeto de aplicação é a criação de visões. Elas podem
facilitar as coletas e armazenagem de dados. Entretanto, nem sempre é aconselhado o uso de
visões por dificultar o trabalho dos otimizadores.
Outros pontos muito importantes ainda devem ser estudados, tais como, implementação
da aplicação, as tendências no desenvolvimento da aplicação e a eficiência dos comandos
SQL a serem executados.
6
3.5 Plano de Execução
Uma forma de avaliar o planejamento de desempenho é executando um comando SQL e
observando o plano de execução por ele gerado.
A Figura 3.1 apresenta a visualização de uma consulta gerada nas tabelas de exemplo do
Oracle relacionando o primeiro nome, departamento, função, data inicial e data final de
trabalho para pessoas que trabalharam no período entre 01/01/90 e 31/12/99 inclusive e logo a
seguir exibindo o plano de execução para realizar essa consulta.
Figura 3.1 - Visualização de uma consulta e seu plano de execução no Oracle.
O plano de execução mostrado na Figura 3.1 é um recurso disponibilizado pela Oracle
para auxiliar o administrador a melhorar o desempenho das consultas criadas. Para visualizar
o plano de execução deve-se habilitar o recurso autotrace, além do plano de execução esse
recurso exibe para o usuário outros detalhes sobre a consulta realizada, como as estatísticas
sobre a consulta e o predicado da informação. Na primeira linha do programa é mostrado o
comando para se habilitar esse recurso. No plano de execução é possível observar o uso de
CPU gerado pela consulta, o tempo de execução, as etapas da consulta entre outras
informações sobre a execução do comando SQL. Como essa consulta foi gerada usando o
7
Oracle 10g utilizando otimizadores na base de custo, é possível observar na Figura 3.2 que a
mesma consulta após ser realizada algumas vezes possui um consumo de CPU bem menor,
pois o otimizador armazenou estatísticas sobre a consulta SQL.
Figura 3.2 – Redução no consumo de CPU na realização de uma consulta repetidas vezes.
8
4 OTIMIZADORES
A Oracle utiliza dois tipos de otimizadores, o otimizador baseado em regras de negócio (
Rule-Based Optimizer – RBO ) e o otimizador baseado em custo ( Cost-Based Optimizer –
CBO ).
Nesse Capítulo são descritas algumas vantagens da otimização e detalhes de cada
otimizador.
4.1 Vantagens da otimização
À medida que novos aplicativos aparecem, as reclamações sobre o desempenho
deficiente aumentam. O desempenho deve ser tratado com a mesma importância que a própria
programação, para que isso não ocorra ao escrever os aplicativos. As vantagens de uma
otimização bem sucedida podem ser a curto e longo prazo.[5]
O Oracle implementa a otimização examinando tabelas e selecionando o caminho que
custará menos para o CBO ou determina a eficiência de acordo com um conjunto de regras
internas para o RBO como mostra a Figura 4.1.
Figura 4.1 – Processamento de consulta SQL. Fonte: Oracle9i Database Performance Tuning Guide and
Reference. Release 2 (9.2)
9
Os otimizadores determinam uma maior eficiência na execução de uma consulta SQL
após considerar vários fatores relativos aos objetos referenciados e a condições especificas na
consulta [3].
4.2 Otimizadores baseados em regras (RBO)
O RBO deixou de ter suporte desde o Oracle 10g, que passou a dar suporte apenas para
o CBO. Entretanto a Oracle manteve as funcionalidades do RBO para não perder a
compatibilidade com bases de dados antigas. O RBO faz a recuperação de dados com base
nas regras internas. Ao encontrar a primeira forma aplicável, ele a utiliza e abandona o
processo sem verificar se existem outros mecanismos que podem ser aplicados de forma mais
eficiente.
4.2.1 Porque entender o RBO
Apesar de a Oracle ter deixado de dar suporte ao RBO, muitas empresas ainda mantém
suas bases de dados com esse otimizador e é necessário entende-lo para poder realizar a
manutenção ou até mesmo uma possível migração.
4.2.2 Regras Internas
Com as regras do RBO, o Oracle realiza as consultas de acordo com alguns fatores. O
Oracle classifica os caminhos de acesso com peso de 1 a 15, sendo que os melhores recebem
1 e os piores 15[5].
Uma breve explicação sobre algumas classificações[3]:
•
Classificação 1: Palavra-chave where iguala o rowid a um único valor;
•
Classificação 4: Quando todas as colunas pertencentes a uma chave exclusiva
ou primaria são referenciados pela instrução SQL em condição de igualdade;
•
Classificação 8: Se a cláusula where da instrução mencionar todas as colunas
em um índice composto, realizando comparações de igualdade;
•
Classificação 9: Quando a parte where/and usa um ou mais índices. Se
houver mais que um deve-se conectá-los com and;
•
Classificação 15: Não satisfaz nenhum dos critérios mencionados acima.
Todos os registros são lidos, passando apenas os que satisfaçam a cláusula
where.
10
4.3 Otimizadores baseados em custo (CBO)
O CBO utiliza estatísticas e histogramas para conseguir executar a consulta de uma
maneira que utilize o mínimo possível de processamento. O servidor de banco de dados
escreve o mesmo comando de diversas formas diferentes para que o comando a ser executado
seja sempre o melhor possível, consumindo o mínimo de processamento.
A grande diferença entre o RBO e o CBO é que, enquanto o RBO utiliza a primeira
forma aplicável, o CBO verifica todas as formas possíveis até encontrar a melhor. O CBO
realiza um trabalho maior antes da execução do comando, entretanto todo esse trabalho é
compensado no final com a execução que apresentará um melhor desempenho.
4.3.1 Estatísticas
É possível coletar estatísticas no Oracle sobre tabelas, colunas, índices e sistema.
Seletividade é o número de linhas que uma consulta SQL retorna satisfazendo a cláusula
WHERE do predicado[6]. A otimização baseada em custo utiliza essas informações para fazer
a seletividade dos predicados e gerar uma estimativa do custo de cada consulta SQL.
Essas estatísticas incluem os seguintes pontos:
•
Tabelas
o Número de linhas;
o Número de blocos;
o Média de comprimento da fila.
•
Colunas
o Número de valores distintos;
o Número de valores nulos;
o Distribuição de dados (histograma);
o Estatísticas de extensão.
•
Índices
o Número de blocos folha;
o Níveis;
o Fator de clusterização.
•
Sistema
o Consumo de E/S;
o Consumo de CPU.
11
As estatísticas são armazenadas em dicionários e histogramas. Essas estatísticas
podem ser atualizadas regularmente mantendo as consultas com maior precisão na
otimização.
4.3.2 Recolhendo estatísticas
A Oracle mantém as estatísticas automaticamente, mas é possível também, manter
esses dados manualmente.
Mesmo no processo automático podem-se realizar customizações para que a recolha
seja a mais adequada para cada aplicação. As estatísticas podem ser geradas de diversas
maneiras. Por exemplo, podem ser criadas em cada consulta SQL, através de procedures
criadas pelo administrador ou até mesmo manualmente com comandos passados diretamente
no console.
4.4 Sugestões
Uma possibilidade de melhoria é o uso de sugestões. Nos livros e documentos da
Oracle é possível encontrar como hints. Hints são sugestões embutidas nas instruções SQL
que modificam o caminho de acesso apenas para a instrução na qual foram declaradas [7].
Com as sugestões é possível determinar para o banco o caminho de acesso a ser usado,
como mostra o exemplo da Figura 4.2. A Hint ALL_ROWS, por exemplo, otimiza a consulta
para minimizar o tempo que leva para todas as linha serem devolvidas.
Figura 4.2 - Exemplo de uso de HINT.
Em alguns casos pode-se ter algumas informações que o otimizador não possui, por
exemplo, que um índice é mais seletivo que outro em uma determinada consulta e com o uso
de hints pode-se forçar o otimizador a escolher o caminho mais adequado[3].
Existem diversas hints, podendo ser utilizada a mais adequada a cada situação. Através
de hints é possível determinar, forma que serão retornados os dados, forma de acesso,
paralelização, etc.
4.5 Comparação dos otimizadores
Foi necessária a criação de duas bases de dados para a comparação dos otimizadores,
uma delas configurada com o CBO e a outra com o RBO. Ambas contendo exatamente os
mesmos registros, garantindo assim que a comparação seja válida.
12
4.5.1 Configuração das bases de dados
No Oracle 11g, utilizado para a realização dos testes, o CBO é configurado como
padrão, por isso, para a base de teste do otimizador por custo não foi necessária nenhuma
mudança na configuração.
Já no caso do otimizador por regra - RBO, foi preciso mudar o método de otimização
como mostra a Figura 4.3 a seguir.
Figura 4.3 - Comando SQL para alterar o método de otimização.
Como é possivel observar na Figura 4.3, também foi executado um comando select
logo após o comando alter para confirmar a mudança no modo de otimização. Observe na
Figura 4.4 o mesmo select realizado na base CBO, onde o otimizador não foi alterado.
Figura 4.4 - Consultando o otimizador da base de dados CBO.
4.5.2 Metodologia de comparação
O primeiro método de comparação será a realização de consultas. Cada consulta será
realizada vinte vezes. Com a ajuda do autotrace espera-se observar uma melhora de
desempenho a cada consulta realizada na base CBO enquanto a base RBO deverá continuar
sempre com o mesmo desempenho.
4.5.3 Consultas realizadas
Na primeira consulta foram requisitados dados sobre endereços pertencentes à cidade de
Itatiba, como mostra a Figura 4.5.
13
Figura 4.5 - Consulta 1 realizada para testes de desempenho.
As Figuras 4.6 a 4.9 apresentam os resultados obtidos pelo autotrace na primeira e na
vigésima consulta pelo CBO e pelo RBO.
Figura 4.6 - Resposta obtida na primeira vez que a consulta 1 foi realizada na base RBO.
14
Figura 4.7 - Resposta obtida na vigésima vez que a consulta 1 foi realizada na base RBO.
Figura 4.8 - Resposta obtida na primeira vez que a consulta 1 foi realizada na base CBO.
Figura 4.9 - Resposta obtida na vigésima vez que a consulta 1 foi realizada na base CBO.
A consulta realizada retorna 1514 registros. Observe na Figura 4.6 e 4.7 que as consultas
na base RBO foram praticamente iguais, na Figura 4.6 é possivel observar uma chamada
15
recursiva enquanto na outra não teve nenhuma. Essa pequena diferença ocorreu por ser a
primeira consulta realizada; da segunda até a vigésima consulta os desempenhos foram
exatamente iguais. Já na base otimizada pelo CBO, a Figura 4.8 mostra que o desempenho da
primeira consulta é muito pior que o desempenho da vigésima mostrada na Figura 4.9. Isso
porque, na primeira consulta, o CBO captura todas as informações e estatisticas sobre a
consulta e para isso faz a chamada da função de todas as formas possiveis.
Esse comportamento também é observado em outras consultas realizadas. Uma outra
consulta buscando informações sobre endereços das cidades de Jundiaí e de Valinhos
ordenadas pelo nome da cidade, do bairro e endereço também foi realizada vinte vezes em
cada base de dados. Como pode ser visto nas Figuras 4.10 a 4.14.
Figura 4.10 - Consulta 2 realizada para testes de desempenho.
Figura 4.11 - Resposta obtida na primeira vez que a consulta 2 foi realizada na base RBO.
16
Figura 4.12 - Resposta obtida na vigésima vez que a consulta 2 foi realizada na base RBO.
Figura 4.13 - Resposta obtida na primeira vez que a consulta 2 foi realizada na base CBO.
17
Figura 4.14 - Resposta obtida na vigésima vez que a consulta 2 foi realizada na base CBO.
Para a consulta 2, os resultados são os mesmos da consulta 1. O CBO armazenou as
estatisticas para as próximas consultas, fazendo com que elas obtivessem um desempenho
melhor.
Uma terceira consulta foi realizada com as mesmas caracteristicas da primeira, desta
vez para a cidade de Ribeirão Preto, também sendo realizada vinte vezes. Veja abaixo a tabela
com os tempos de cada vez que a consulta foi realizada.
Tabela 4.1 - Tempos de resposta para consulta 3.
Número da consulta
Tempo RBO (s)
Tempo CBO (s)
1
6,013
4,820
2
6,011
4,242
3
6,010
4,240
4
5,509
4,103
5
6,082
3,670
6
5,539
3,821
7
6,177
3,534
8
6,222
3,771
9
5,536
3,516
10
6,008
4,021
11
6,221
3,727
12
5,523
3,521
13
6,011
4,293
14
6,034
4,017
15
6,012
3,750
16
6,512
4,237
17
6,011
4,021
18
6,288
4,016
19
5,511
3,522
20
6,090
3,514
18
Observe na Tabela 4.1 que o tempo para as consultas na base CBO variam entre 3,514
segundos e 4,820 segundos enquanto a base RBO variou entre 5,509 segundos e 6,512
segundos. Taxa de variação de aproximadamente 1 segundo só não é maior para a base CBO
porque a base de dados já havia armazenado dados para esse padrão de consulta com a
primeira consulta realizada nos testes. Analisando os planos de execução e os tempos, é
possível observar que essa diferença de tempo entre as bases é causada pelos otimizadores,
isso porque melhorias causadas por cache por exemplo, são eliminadas uma vez que o número
de chamadas recursivas também diminui a cada consulta.
Novas consultas com utilização da cláusula INNER JOIN e sub-consultas, observando a
Tabela 4.2 veremos as três consultas realizadas e a Tabela 4.3 mostra os tempos para
processar estas consultas utilizando cada otimizador.
Tabela 4.2 – Consultas realizadas.
A
SELECT b.BAIRRO_DESCRICAO, e.ENDERECO_LOGRADOURO, e.ENDERECO_CEP
FROM BAIRRO b INNER JOIN ENDERECO e ON e.BAIRRO_CODIGO = b.BAIRRO_CODIGO
WHERE b.CIDADE_CODIGO IN (SELECT c.CIDADE_CODIGO
FROM UF u INNER JOIN CIDADE c ON c.UF_CODIGO = u.UF_CODIGO
WHERE u.UF_SIGLA='SP' AND c.CIDADE_DESCRICAO='GUARULHOS')
B
SELECT b.BAIRRO_DESCRICAO, e.ENDERECO_LOGRADOURO, e.ENDERECO_CEP
FROM ENDERECO e, BAIRRO b, CIDADE c, UF u
WHERE
e.BAIRRO_CODIGO
=
b.BAIRRO_CODIGO
AND
b.CIDADE_CODIGO
=
c.CIDADE_CODIGO
AND
c.UF_CODIGO = u.UF_CODIGO AND u.UF_SIGLA='SP' AND c.CIDADE_DESCRICAO='GUARULHOS'
C
SELECT b.BAIRRO_DESCRICAO, e.ENDERECO_LOGRADOURO, e.ENDERECO_CEP
FROM ENDERECO e INNER JOIN (BAIRRO b INNER JOIN (CIDADE c INNER JOIN UF u ON c.UF_CODIGO =
u.UF_CODIGO) ON b.CIDADE_CODIGO = c.CIDADE_CODIGO) ON e.BAIRRO_CODIGO = b.BAIRRO_CODIGO
WHERE u.UF_SIGLA='SP' AND c.CIDADE_DESCRICAO='GUARULHOS'
Tabela 4.3 - Tempos de resposta para as consultas A, B e C nos 2 otimizadores.
Nº
Consulta
A
RBO
B
CBO
RBO
C
CBO
RBO
CBO
1
5,512
3,512
4,507
2,997
4,509
3,187
2
5,184
3,120
4,500
3,252
4,507
3,004
3
5,004
3,117
5,010
3,266
4,513
2,996
4
5,009
3,088
4,499
3,212
4,753
3,012
5
5,205
3,016
4,538
3,209
4,503
3,012
6
5,003
3,037
4,525
2,999
4,598
3,017
7
5,000
3,106
4,494
2,530
4,510
3,013
8
5,227
3,182
4,720
3,002
4,499
3,017
9
5,007
3,024
5,002
2,995
4,635
3,717
10
5,002
3,149
4,496
3,213
4,502
3,014
19
Note na Tabela 4.3 a diferença de tempo nas consultas entre os otimizadores RBO e
CBO. Lembrando que no caso do CBO ele já havia armazenado informações das consultas
anteriores, por isso a diferença entre a primeira e a decima consulta é menor do que na Tabela
4.1. Observando os três gráficos a seguir, é mais fácil visualizar a diferença entre os
otimizadores.
Figura 4.15 – Gráfico referente à consulta A
Figura 4.16 – Gráfico referente à consulta B
20
Figura 4.17 – Gráfico referente à consulta C
O uso de hints pode ser muito útil quando se conhece as bases de dados. A primeira
consulta foi novamente realizada com o RBO, desta vez com o uso de sugestões. Observe que
o tempo da consulta baixou de forma que se compara com os tempos do CBO.
Tabela 4.4 – Tempos de respostas para a consulta no RBO com o uso de hints e no CBO.
RBO (sem hints)
RBO (com hints)
CBO (sem hints)
5,022
3,012
2,512
5,025
3,053
3,154
5,516
3,009
3,199
5,045
3,018
3,205
5,018
2,532
3,012
A Figura 4.18 apresenta as sugestões (hints) utilizadas na consulta.
Figura 4.18 – Consulta realizada na base RBO.
21
O gráfico dessas consultas é observado na Figura 4.19. E através dele é possível notar
que no quesito tempo, o RBO com o uso de hints se mostrou sutilmente melhor na maior parte
das consultas.
Figura 4.19 - Gráfico referente às consulta para testes do uso de hints
Observe também o plano de execução gerado pelas consultas nas Figuras 4.20 a 4.22.
Figura 4.20 – Plano de execução da consulta na base CBO.
22
Figura 4.21 – Plano de execução da consulta na base RBO com o uso de hints.
Figura 4.22 – Plano de execução da consulta na base RBO sem o uso de hints.
23
5 CONCLUSÃO
Com o estudo e os resultados obtidos nos testes realizados é possível chegar à conclusão
que o RBO é obsoleto se comparado ao CBO, e que nesse cenário se mostrou muito útil
utilizado com hints, onde a base não sofre muitas atualizações e a estrutura da base permanece
inalterada. As hints também podem ser utilizadas no CBO, o que outra sugere que o RBO é
obsoleto. Porém como muitas empresas ainda o utilizam, o seu entendimento junto com o
planejamento de desempenho faz com que manutenções ou até mesmo uma possível migração
seja ela parcial ou integral fique muito mais fácil e rápida.
O estudo do planejamento de desempenho auxilia a compreensão dos otimizadores e
obtenção de conclusões, pois na maioria das consultas foi utilizada a visualização dos planos
de execução e através deles foram observadas as principais diferenças entre os dois
otimizadores, tais como, forma de acesso (acesso total a tabela, acesso através da chave
primaria, acesso através de Hash, etc), quantidade de chamadas recursivas aos dados,
quantidades de blocos examinados, sequência de acesso, etc.
5.1 Trabalhos futuros
Como trabalhos futuros sugerem-se:
•
Realização de estudos mais aprofundados sobre tuning em banco de dados:
o mostrar as diferenças do tuning em SGBDs diferentes.
•
Análise de otimizadores de outros SGBDs:
o Estudos dos otimizadores de cada SGBD;
o Comparações entre otimizadores de diferentes SGBDs;
o Formas de otimização de cada otimizador.
•
Diferentes formas de otimização de SGBDs;
•
Realização de estudos mais detalhados sobre sugestões (hints).
24
Referências Bibliográficas
1.
SILBERSCHATZ, Abraham; CANHETTE, Claudio Cesar; KORTH, Henry F.; SUDARSHAN, S.
Sistema de Banco de Dados. 3. ed. São Paulo: Makron, 1999. 778 p. ISBN 85-346-1073-8
2.
KREINES, David C. Oracle SQL: The essential reference. Beijing: O´Reilly, 2000. 382 p. ISBN 156592-697-8
3.
GREEN, Connie D. Oracle9i Database Performance Tuning Guide and Reference. Release 2 (9.2),
October 2002. URL: http://www.oracle.com/technology/documentation/oracle9i.html. Recuperado em
25/02/2009.
4.
HOLDSWORTH, Andrew Oracle9i Database Performance Planning. Release 2 (9.2), March 2002.
URL: http://www.oracle.com/technology/documentation/oracle9i.html. Recuperado em 25/02/2009.
5.
ABBEY, Michael; COREY, Michael J.; ABRAMSON, Ian. Oracle8i: Guia Introdutório. Rio de
Janeiro: Campus, c2000. 701 p. ISBN 8535206256
6.
CHAN, Immanuel Oracle Database Performance Tuning Guide, 11g Release 1 (11.1), July 2008.
URL: http://www.oracle.com/technology/documentation/database.html. Recuperado em 27/05/2009.
7.
ARONOFF, Eyal; LONEY, Kevin; SONAWALLA, Noorali. Oracle8 advanced tuning &
administration. Berkeley, Calif.: Osborne McGraw-Hill: Oracle Press, c1998. xxii, 660 p., ill., 24 cm.
+. ISBN 0078825342.