FACULDADE MERIDIONAL – IMED
SISTEMAS DE INFORMAÇÃO
Vinicius Vieira Pires
Identificando e corrigindo problemas de performance em
banco de dados
Passo Fundo
2013
Vinicius Vieira Pires
Identificando e corrigindo problemas de performance em
banco de dados
Trabalho de Conclusão de Curso apresentado à Escola de Sistemas de Informação, da Faculdade Meridional – IMED, como requisito parcial para obtenção do grau de Bacharel em Sistemas de Informação, sob a orientação do Prof. Me. Fahad Kalil.
Passo Fundo
2013
Vinicius Vieira Pires
Identificando e corrigindo problemas de performance em
banco de dados
Banca Examinadora: Prof. Esp. Rafael Bilibio Prof. Esp. Ralph Rassweiler
Prof. Me. Fahad Kalil
Passo Fundo
2013
Dedico este trabalho especialmente a minha família que esteve comigo me apoiando durante todo o desenvolvimento deste projeto.
AGRADECIMENTOS
Assim como um signo linguístico não constrói, nem reconstrói sentido sozinho, meu trabalho também não. Por isso, obrigado a todos que contribuíram para sua realização, dos quais destaco:
a IMED, pela oportunidade;
ao Prof. Marciano Tagliani, pelas orientações iniciais;
ao Prof. Fahad Kalil, pelas orientações e ajuda na realização deste trabalho;
aos colegas, pelo companheirismo e amizade; a minha família pelo apoio;
a Deus pela oportunidade de realização deste trabalho.
Que os vossos esforços desafiem as impossibilidades, lembrai-vos de que as grandes coisas do homem foram conquistadas do que parecia impossível.
Charles Chaplin
RESUMO
Este trabalho tem como objetivo apresentar algumas técnicas de identificação e correção de problemas de desempenho em banco de dados, voltado especialmente para a otimização de SQL, usando como estudo de caso um banco de dados Oracle. Inicialmente será mostrado as principais estruturas integrantes da arquitetura do sistema gerenciador do banco de dados Oracle, de suma importância no processo de otimização de consultas SQLs, bem como algumas técnicas de identificação de gargalos de desempenho e formas de correção, até algumas ferramentas de apoio. A metodologia utilizada se dará por meio de uma pesquisa bibliográfica para construir o embasamento teórico para a aplicação prática das técnicas vistas. A aplicação prática será efetuada em cima de um estudo de caso em cima de um banco de dados Oracle devido à ampla utilização do mercado deste e da vasta documentação oferecida,o banco de dados utilizado é a base de desenvolvimento de uma grande empresa do setor de desenvolvimento de software onde os resultados finais serão conhecidos.
ABSTRACT
This paper aims to present some techniques to identify and fix performance problems in database, geared especially for SQL optimization, using as a case study one Oracle database. Initially the main structures will be shown members of the system architecture manager Oracle database, of paramount importance in the process of query optimization SQL statements, as well as some techniques for identifying performance bottlenecks and ways to fix up some tools support. The methodology used will be through a literature search to build the theoretical basis for the practical application of the techniques seen. The practical application will be made upon a case study on an Oracle database because of the wide use of this market and the extensive documentation provided , the database used is the basis for development of a large company sector software development where the final results will be known
LISTA DE ILUSTRAÇÕES
Figura 1.Arquitetura Oracle de instância única ... 13
Figura 2.Estrutura de armazenamento físico Oracle ... 15
Figura 3.Processamento de consulta. ... 26
Figura 4.Processamento de consulta no Oracle ... 27
Figura 5.Exemplo de plano de execução ... 43
Figura 6.Exemplo de uso de hint. ... 46
LISTA DE SIGLAS
ADDM: Automatic Database Diagnostic Monitor ARC: Archiver
CBO: Cost Based Optimizer CKPT: Checkpoint process CPF: Cadastro de Pessoa Física DBW: Database Writer
DBA: Data Base Administrator DDL: Data Definition Languague DML: Data Manipulation Language E/S: Entrada/Saída
GB: Gigabyte HD: Hard Disk I/O: Input/output LGWR: Log Writer
MMAN: Memory Manager
MMNL: Manageability Monitor Light MMON: Manageability Monitor NVD: Number Value Distinct OLTP: Online Transaction Protocol PGA: Private Global Area
PMON: Process Monitor
RAM: Random Access Memory RAC: Real Application Cluster
RAID: Redundant Array of Independent Drives RBO: Rule Based Optimizer
RECO: Recoverer Process SGA: System Global Area
SGBD: Sistema Gerenciador de Banco de Dados SMON: System Monitor
SQL: Structured Query Language TB: Terabyte
S U M Á R I O
1. INTRODUÇÃO ... 10
2. REFERENCIAL TEÓRICO... 12
2.1. Arquitetura Oracle ... 12
2.2. Estruturas de armazenamento lógico Oracle ... 13
2.3. Estruturas de armazenamento físico Oracle ... 14
2.4. Estruturas de memória Oracle ... 15
2.4.1. Estruturas da SGA ... 16
2.5. Tuning de banco de dados ... 20
2.5.1. Níveis de ajuste para otimização ... 22
2.5.2. Ajuste de hardware ... 22
2.5.3. Ajuste de esquema de banco de dados ... 23
2.5.4. Ajuste de índices ... 24
2.5.5. Ajuste de SQL ... 25
2.6. Processamento de consultas ... 25
2.6.1. Funcionamento do otimizador de consultas ... 28
2.6.2. Métodos de acesso a tabelas ... 29
2.6.2.1. Full table scan ... 29
2.6.2.2. RowID scan ... 30
2.6.2.3. Cluster scan ... 30
2.6.2.4. Hash scan ... 30
2.6.2.5. Sample table scan ... 31
2.6.2.6. Index scan ... 31
2.6.2.7. Index unique scan ... 32
2.6.2.8. Index range scans ... 32
2.6.2.9. Index range scan descending ... 33
2.6.2.10. Index skip scan ... 33
2.6.2.11. Full index scan ... 33
2.6.2.12. Fast full index scan ... 34
2.6.2.13. Index join ... 34
2.6.2.14. Bitmap indexes ... 35
2.6.3. Como o otimizador escolhe o caminho de acesso ... 35
2.6.3.1. Métodos de junção (JOINS) ... 36
2.6.3.3. Como o otimizador escolhe o plano de execução para os joins ... 36
2.6.3.4. Nested loops join ... 38
2.6.3.5. Hash join ... 38
2.6.3.6. Sort Merge join ... 39
2.6.3.7. Cartesian join ... 39
2.6.3.8. Outer joins ... 40
2.6.3.9. Nested loop outer join ... 40
2.6.3.10. Hash outer joins ... 41
2.6.3.11. Sort merge outer join ... 41
2.6.3.12. Full outer join ... 41
2.7. Planos de execução ... 42
2.7.1. Analisando planos de execução ... 42
2.7.2. Estatísticas do otimizador ... 44
2.8. Hints ... 45
2.8.1. Tipos de hints ... 46
2.8.2. Hints por categoria ... 46
2.8.3. Hints para otimização ... 47
2.8.4. Hints para caminhos de acesso ... 47
2.8.5. Hints de ordem de junção ... 48
2.8.6. Hints de operação de junção ... 48
2.9. Etapas do SQL tuning ... 48
2.9.1. Regras gerais de otimização de consultas ... 49
2.9.2. Escrevendo SQLs eficientes ... 49
2.9.3. Ferramentas de otimização ... 51
2.9.4. SQL tuning advisor ... 51
2.9.5. Automatic database diagnostic monitor ... 52
2.9.6. SQL access advisor ... 52
3. METODOLOGIA ... 53
3.1. Metodologia dos experimentos ... 53
4. DESENVOLVIMENTO E APLICAÇÃO ... 54 4.1. Experimentos ... 54 4.2. Otimização 1 ... 54 4.3. Otimização 2 ... 56 4.4. Otimização 3 ... 57 4.5. Otimização 4 ... 58 4.6. Otimização 5 ... 60 4.7. Otimização 6 ... 61
4.8. Otimização 7 ... 63
4.9. Otimização 8 ... 64
5. RESULTADOS ... 66
6. CONCLUSÃO ... 68
BIBLIOGRAFIA ... 69
Apêndice A – Consulta problemática com uso de hint ... 71
Apêndice B – Plano de execução da consulta problemática com uso de hint ... 73
Apêndice C – Consulta otimizada sem o uso de hint... 74
Apêndice D – Consulta problemática devido a falta de estatísticas ... 75
Apêndice E – Consulta problemática devido a falta de índices ... 77
Apêndice F – Plano de execução consulta problemática devido À falta de índices ... 78
Apêndice G – Plano de execução após criação de índices ... 79
Apêndice H – Consulta problemática devido a falta de índices ... 80
Apêndice I – Plano de execução consulta problemática ... 81
1. INTRODUÇÃO
Com o aumento do uso de sistemas de informação e o aumento do volume de dados torna-se imprescindível manter sistemas com alta operabilidade e desempenho máximo.
Muitas vezes o projeto de banco de dados não prevê um alto crescimento dos dados causando após a entrada em produção problemas de desempenho e até de operabilidade. Neste cenário, bases com desempenho abaixo do esperado fazem com que geralmente todos os sistemas dependentes desta tenham o tempo de resposta afetado negativamente. Surge então, necessidade de aplicar técnicas de otimização visando melhorar o desempenho, assim garantindo tempos de respostas aceitáveis e a alta operabilidade.
O banco de dados é umas das camadas mais importantes de um sistema informatizado, onde hoje em dia a eficiência e velocidade de processamento principalmente em áreas consideradas críticas é fundamental. Dessa forma há a importância em se manter uma base de dados operando com seu desempenho ao máximo garantindo o bom funcionamento das aplicações.
Existem técnicas e ferramentas disponíveis capazes de diagnosticar e solucionar problemas de desempenho em bancos de dados relacionais, abrangendo tanto a área de configuração de banco de dados, hardware e principalmente tuning SQL.
A tarefa de otimização de um banco de dados em produção é uma tarefa extremamente complexa e que exige do DBA grande conhecimento de conceitos, técnicas, ferramentas e acima de tudo da arquitetura e implementação do banco de dados utilizado, onde a falta de documentos ou de um conjunto de regras e processos a serem tomados para o aperfeiçoamento de desempenho do SGBD quando escassos dificultam o trabalho do DBA.
Serão apresentadas algumas técnicas de otimização de SQL para o banco de dados Oracle auxiliando os desenvolvedores ou DBAs inexperientes no processo de otimização do banco de dados, utilizando uma abordagem prática em consultas SQLs reais problemáticas, otimizando-as e mostrando a importância dos processos de otimização.
Nos primeiros capítulos deste estudo do capítulo 2.1 ao capítulo 2.4.1 será apresentado a arquitetura e o funcionamento interno do sistema gerenciador de banco de dados Oracle, suas principais estruturas e processos. Nos capítulos posteriores será mostrado o conceito de tuning de banco de dados, com ênfase especialmente ao tuning SQL, serão apresentadas algumas técnicas e ferramentas para esta atividade, e também como o banco de dados processa uma consulta, mostrando as principais etapas e meios utilizados pelo SGBD para
este processamento. Por fim serão mostrados alguns estudos de caso, onde serão aplicadas algumas técnicas de tuning SQL em consultas problemáticas reais.
2. REFERENCIAL TEÓRICO
Para delimitar o estudo em questão, apresenta-se uma revisão bibliográfica com estudos realizados na área.
2.1. Arquitetura Oracle
Nos próximos capítulos abordaremos sobre a arquitetura de um banco de dados Oracle bem como suas principais estruturas que influenciam direta ou indiretamente o desempenho de uma base de dados. Também serão levantadas questões sobre o gerenciamento e a configuração de um ambiente Oracle.
Segundo LONEY e BRYLA (2005) um banco de dados é um conjunto de dados relacionados entre si, gravados em disco e gerenciados por um servidor de banco de dados que coleta e mantém estas informações através de estruturas físicas e lógicas.
De modo a descrever o ambiente no qual um banco de dados pode estar contido, temos como exemplo o SGBD Oracle que pode ser executado em instância única ou distribuído, podendo ainda estar em um cluster como RAC (Real Application Cluster). (WATSON, 2008)
Conforme WATSON (2008) um banco de dados de instância única atua como uma única instância em um computador acessando um banco de dados em disco locais, com estrutura de memória, processos e característica volátil, ou seja, caso se desligue o computador todos os seus dados serão perdidos a não ser que tenha sido efetuada a persistência em disco.
Banco de dados distribuídos, como RAC (Real application cluster) WATSON (2008) são definidos como varias instâncias trabalhando em locais ou maquinas diferentes que provêem alta escalabilidade, desempenho e tolerância a falhas, de forma que uma instância poderá ser desativada e todos os seus dados serão migrados para outra instância sem que haja perda de dados. A Figura 1 mostra como é composta a estrutura de um banco de dados de instancia única.
Figura 1. Arquitetura Oracle de instância única. Adaptado de WATSON (2008).
2.2. Estruturas de armazenamento lógico Oracle
O banco de dados Oracle possui diversas divisões e subdivisões para organizar e agrupar os dados de forma lógica no SGBD, estas estruturas são:
Espaços de Tabelas (Tablespaces): Um tablespace consiste em um ou mais arquivos de dados, sendo esse uma e somente uma parte do espaço da tabela. (LONEY e BRYLA, 2005). De acordo com WATSON (2008), o Oracle possui pelo menos duas tablespaces: (i) a SYSTEM que armazena o dicionário de dados; (ii) SYSAUX que armazena os dados que auxiliam o dicionário de dados.
Blocos: LONEY e BRYLA definem blocos como “as menores unidades de armazenamento do Oracle”. Os arquivos de dados são formatados em blocos do Oracle e seu tamanho é fixado na criação do arquivo, onde seu tamanho geralmente é o mesmo do banco de dados inteiro (WATSON, 2008). O tamanho do bloco deve ser um múltiplo do tamanho do bloco do sistema operacional para se ter uma E/S (estrada/saída) eficiente em disco e é especificada pelo parâmetro DB_BLOCK_SIZE (LONEY e BRYLA, 2005). Segundo o autor WATSON (2008) o tamanho do bloco pode variar de 2KB até 64KB dependendo do ambiente físico.
Extensões: Uma extensão consiste em um ou mais blocos de banco de dados. (LONEY e BRYLA, 2005)
Segmentos: LONEY e BRYLA (2005) relatam que segmentos é um conjunto de extensões que formam um objeto de banco de dados tratados como unidades, tabelas ou índices.
2.3. Estruturas de armazenamento físico Oracle
Existem três estruturas de armazenamento físico obrigatório do Oracle: Arquivos de dados, arquivos de controle (controlfile) e arquivos de redo log.
Arquivos de dados: WATSON (2008) define que os arquivos de dados são os repositórios de dados, ou seja, estruturas físicas visíveis para os administradores e programadores. LONEY e BRYLA (2005) defendem que os arquivos de dados correspondem a um arquivo físico do sistema operacional em disco.
Arquivos de controle: Arquivo de controle mantém informações sobre a estrutura física do banco de dados como o nome do banco de dados, nomes e localizações dos arquivos de dados e de redo log, também armazena informações sobre o RMAN. Segundo WATSON (2008) um banco de dados deve possuir no mínimo um arquivo de controle, porém deve se ter pelo menos algumas cópias deste para uma boa segurança do banco de dados.
Arquivos de redo log: Arquivos de redo log são arquivos que armazenam cópias das alterações efetuadas e são utilizados para restaurar o estado do banco de dados anterior a uma falha, um banco de dados deve ter pelo menos dois arquivos de redo log, mas por segurança sempre é bom ter varias cópias de arquivos de redo log (LONEY e BRYLA, 2005).
Abaixo a Figura 2 mostra graficamente as estruturas de armazenamento físico do SGBD Oracle.
Figura 2.Estrutura de armazenamento físico Oracle. Adaptado de (LONEY e BRYLA, 2005)
2.4. Estruturas de memória Oracle
Para adotarmos os melhores plano de ações e obtivermos os diagnósticos mais eficazes para a otimização do banco, antes primeiramente devemos conhecer como funciona o SGBD, bem como sua estrutura, processos e funcionamento.
O SGBD Oracle é composto por duas entidades: a instância e o banco de dados. As instâncias são caracterizadas pelas estruturas de memória e os processos, já o banco de dados são os arquivos no disco (WATSON, 2008). Na arquitetura do banco de dados Oracle existe uma separação da estrutura lógica para a estrutura física, porém estas duas partes são ligadas entre si, onde a configuração inadequada ou a escrita de consultas ineficazes podem acarretar o mau funcionamento dos processos entre essas duas grandes áreas. Uma instância é criada quando o banco de dados é iniciado, após seu encerramento a sua instância desaparece, diferentemente do banco de dados que são os arquivos físicos onde ficam armazenados os dados e arquivos necessários para o funcionamento do SGBD, que permanecem após o SGBD ser encerrado. Na sua estrutura lógica (instância) temos duas grandes áreas de memória: a
PGA e a SGA como mostra a figura a seguir (WATSON, 2008).
SGA (System Global Area) são estruturas de memória, implementados em segmentos de memória compartilhada fornecidos pelo sistema operacional (WATSON, 2008). Nessa área existem diversos processos extremamente importantes para o correto funcionamento do SGBD e do desempenho do banco de dados. Tais áreas são responsáveis por gerenciar, por exemplo, os processos de I/O (leitura e escrita do disco), gravação de logs de transações entre outros processos importantes como: o cache dos buffers do banco, o buffer de log , large pool entre outras.
WATSON (2008) define PGA (Private Global Area) como uma área de memória não compartilhável associada a cada processo do servidor, sendo uma área privada de sessão do usuário e reservada para o mesmo que após ser desconectado é liberada novamente.
2.4.1. Estruturas da SGA
A SGA como visto anteriormente possui diversas estruturas importantes para o bom funcionamento do SGBD, nesta área existem três estruturas de dados obrigatórias (cache de buffer do banco de dados, buffer de log e shared pool) e três opcionais (large pool, java pool e stream pool) (WATSON, 2008).
Cache de buffer do banco de dados é uma área do banco de dados responsável pela execução dos SQLs. Quando um usuário efetua uma transação SQL ela não é atualizada diretamente em disco sendo antes copiada para esta área em forma de blocos de dados onde permanecerão por determinado tempo até que outra transação requisite este espaço memória e enfim este bloco é gravado em disco (WATSON, 2008).
De acordo com LONEY e BRYLA (2005), a área de memória SGA que contém esses dados é dinâmica, ou seja, pode-se alterar os valores de acordo com a necessidade sem precisar reiniciar a instância do banco de dados, os parâmetros responsáveis por alterar estes valores são o DB_CACHE_SIZE e o DB_Nk_CACHE_SIZE, ainda o Oracle pode utilizar dois caches adicionais com o mesmo tamanho de bloco do DB_CACHE_SIZE, o pool de buffers KEEP e o pool de buffer RECYCLE, sendo que na hora da criação de uma tabela que será muito usada o interessante seria configurá-la para posicioná-la no BUFFER_POOL_KEEP para minimizar o I/O de leitura dos blocos em disco.
acessados freqüentemente estarão no cache de buffer do banco de dados, entretanto caso configuração inadequada seja realizada, pode acontecer do cache não ser utilizado corretamente e por conseqüência haverá perda de desempenho no banco.
O buffer de log é uma área que armazena as alterações realizadas nas transações DML, vital para que o banco de dados em caso de falhas recupere o estado anterior, o buffer de redo log é a garantia que os dados não serão perdidos em caso de falhas (WATSON, 2008). Segundo LONEY e BRYLA (2005) os dados contidos no buffer de redo log são gravados nos arquivos de redo a cada três segundos ou até que o buffer de log esteja um terço cheio. Ainda segundo WATSON (2008) o buffer de redo log não pode ter seu valor alterado sem a reinicialização da instância do banco de dados, além de ser uma área pequena e crítica se comparada a outras áreas de memória, pois não se pode realizar uma operação DML mais rápido do que o processo responsável (LGWR) possa gravar os dados no arquivos de redo log em disco, o buffer de log é um verdadeiro gargalo da arquitetura Oracle.
O Shared Pool ou Pool Compartilhado para WATSON (2008) é a estrutura mais complexa da SGA, sendo composta por diversas subestruturas das quais as mais importantes são:
1. Cache de biblioteca: O cache de biblioteca armazena as instruções e códigos SQL e PL/SQL recentemente usados na sua forma analisada por parse, ou seja, com seu plano de execução e sua árvore de análise sintática (LONEY e BRYLA, 2005). WATSON (2008) relata que armazenar em cache o código analisado por parse para ele ser reutilizado sem a necessidade de ser reanalisado melhora muito o desempenho, pois a análise por parse é demorada. Ainda LONEY e BRYLA (2005) defendem que caso o tamanho do cache de biblioteca seja dimensionado com um tamanho muito pequeno, as consultas e códigos analisados por parse serão esvaziados do cache, exigindo reanálise freqüente das transações. Isso acarretará a queda de desempenho das instruções DML e DDL.
2. Cache de dicionário de dados: Armazenam as definições de objetos usados recentemente como as descrições de tabelas, índices, usuários e outras definições de metadados (WATSON, 2008). LONEY e BRYLA (2005) afirmam que caso o tamanho do cache de dicionários de dados seja dimensionado muito pequeno, pode ocorrer chamadas recursivas, ou seja, solicitações ao dicionário de dados gerando E/S extras.
3. Área PL/SQL: Objetos PL/SQL como functions, triggers, procedures ou packages são armazenados no dicionário de dados como código-fonte e compilados, quando uma sessão chama um destes objetos o Oracle faz a leitura em disco no dicionário de dados para recuperar estes objetos. Para diminuir a necessidade de I/O em disco o Oracle armazena estes objetos na área PL/SQL do shared pool na primeira chamada do mesmo, assim as chamadas subseqüentes serão muito mais rápidas, pois não precisaram exercer leitura em disco para recuperá-los (WATSON, 2008).
4. Cache de resultados: Uma mesma consulta ou chamada a um objeto PL/SQL pode ser executada diversas vezes em uma mesma sessão, por isso o Oracle armazena os resultados gerados por estas consultas para que na próxima vez que a mesma chamada aconteça não precise executá-la novamente o que torna a velocidade de retorno muito mais rápida. O cache de resultados é inteligente o bastante para saber se houve atualização dos dados de uma tabela, por isso não há perigo em receber um resultado desatualizado (WATSON, 2008).
5. Large Pool: Segundo LONEY e BRYLA (2005) o large pool é uma área opcional utilizada para as transações de banco de dados paralelos, o parâmetro de inicialização LARGE_POOL_SIZE controla o tamanho do large pool e é um parâmetro dinâmico.
6. Java Pool: WATSON (2008) define java pool como um espaço de heap necessário para instanciar os objetos Java, assim como ocorre com os objetos PL/SQL na área PL/SQL.
7. Streams Pool: Para LONEY e BRYLA (2005) o streams pool contém estruturas de dados e controle que suportam o recurso do Oracle Streams. Para WATSON (2008) o Oracle Streams é um recurso que permite compartilhar os dados entre diversos bancos de dados.
Quando uma instância Oracle é iniciada, inúmeros processos importantes são executados, em segundo plano, os quais são blocos de código executável que realizam uma operação especifica (LONEY e BRYLA, 2005).
SMON é o System Monitor, processo responsável pela recuperação de falhas, onde em caso de falhas na instância ou queda de energia o SMON aplica os dados que estão nos vetores de alteração aos arquivos de dados (LONEY e BRYLA, 2005). WATSON (2008) defende que o SMON tem a tarefa de inicializar o banco de dados validando os arquivos de controle, os arquivos de dados e os arquivos de redo log, também é responsável por gerenciar
o espaço livre nos arquivos de dados com o banco de dados em funcionamento.
De acordo com (LONEY e BRYLA, 2005) o PMON é o Process Monitor, ou seja, é o monitor de processos do usuário, caso um processo do usuário falhe ou a sessão do usuário seja encerrada por quaisquer motivos, o PMON efetua a limpeza do cache de buffer do banco de dados juntamente com todos os recursos que a sessão estava utilizando. Conforme WATSON (2008) o PMON “monitora todos os processos do servidor e detecta problemas com as sessões.”
O DBWn grava as alterações contidas no cache de buffer do banco de dados nos arquivos de dados (LONEY e BRYLA, 2005). Para WATSON (2008) uma instância do banco de dados pode ter vários “database writers”, no máximo 20. A regra para o DBW gravar no disco um buffer é de nenhum buffer livre, um timeout de 3 segundos, muitos buffers sujos ou um checkpoint.
LGWR (Log Writer) é responsável por gravar o que tem no buffer de log nos arquivos de log em disco. Quando uma transação DML é realizada o SGBD Oracle cria vetores de alterações no buffer de log e para que nenhuma informação seja perdida o log writer grava estas informações em disco o mais rápido possível, por isso o LGWR é um dos maiores gargalos da arquitetura Oracle (WATSON, 2008).
Para WATSON (2008) o CKPT é um processo de recuperação que repara os danos causados por uma falha da instância do banco de dados, ou seja, todos os buffer sujos que não foram gravados nos arquivos de dados pelo DBWn devem ser recuperados do redo log e colocados nos blocos de dados. Segundo LONEY e BRYLA (2005) o CKPT minimiza o tempo necessário para a recuperação da instância.
O MMON monitora as estatísticas de desempenho e de atividade do banco de dados por padrão de hora em hora. Ele é responsável por parte dos recursos de auto ajuste e auto monitoramento do banco de dados e foi introduzido a partir da versão 10G do SGBD (WATSON, 2008).
ARCn ou Archiver é um processo que faz a leitura dos arquivos de log online, sendo este responsável pela copia dos arquivos de log a medida que forem preenchidos e antes de serem reutilizados, caso estas cópias dos arquivos de log chamados cópias de arquivos de log arquivados esteja disponível sempre será possível restaurar o banco de dados de qualquer falha. Para LONEY e BRYLA (2005) o processo arquivador termina antes do log de redo ser necessário novamente, caso isso não ocorra acontecerá sérios problemas de desempenho no banco de dados, para eles isto desencadeará uma série de problemas tais como:
[...] os usuários não poderão completar suas transações ate que as entradas sejam gravadas nos arquivos de log de redo, e o arquivo de log de redo não esta pronto para receber novas entradas porque continua a ser gravado na localização do repositório de arquivos. Há pelo menos três potenciais soluções para este problema: aumente os arquivos de log de redo, o número de grupos do log de redo e o número de processos ARCn [...].(Ibidem)
De acordo com WATSON (2008) o MMNL (Manageability Monitor Light) é um processo para auxiliar o MMON. Caso os buffers de memória usados para guardar as informações referentes às estatísticas de desempenho e atividades do banco de dados ficarem cheios antes do MMON gravar as informações em disco, o MMNL assumirá a responsabilidade pelo flush dos dados.
MMAN ou Memory Manager é o processo que executa o gerenciamento automático de alocações de memória e está presente desde a versão 10g do banco de dados. O MMAN automatiza muito a função do DBA que fica restrito a apenas definir um objetivo geral para o gerenciamento de memória e então com base nos parâmetros impostos pelo DBA o MMAN gerencia a memória SGA e PGA mantendo dentro de um limite definido (WATSON, 2008).
RECO é o processo de verificação (recoverer process) é responsável pelo controle de falhas nas transações de banco de dados distribuídos, ou seja, quando é realizada uma transação em uma tabela, por exemplo, e esta alteração não consegue ser replicada nas tabelas em outros bancos devido a uma falha na rede, então o RECO desfaz esta alteração (Loney e Bryla, 2005).
2.5. Tuning de banco de dados
Bancos de dados reais precisam ser monitorados constantemente, pois a quantia que o tempo passa e seu uso em ambientes de produção cresce e o seu volume de dados aumenta os problemas de desempenho começam a aparecer algumas vezes estes problemas aparecem logo no inicio da entrada em produção, porem são mais comuns após um período de atividade maior. Segundo GONÇALVES (2006) a maioria dos problemas de performance em aplicações que utilizam a linguagem SQL esta relacionada ao modelo de dados físico do SGBD, representando cerca de 25% dos problemas. Ligado ao problema de aplicação temos 65% e outros 10% esta entre SGBD, redes e sistemas operacionais.
MOLINARI (2003) afirma que a otimização de dados de um SGBD tem dois objetivos: otimizar o armazenamento da informação e otimizar o acesso a informação. Tuning também chamado de sintonia ou afinação de banco de dados tem o objetivo de refinar o SGBD maximizando seu desempenho.
Existem diversas técnicas de otimização de desempenho de banco de dados, desde a otimização em nível de hardware, em nível de configuração de SGBD como em nível de consultas SQL e blocos PL/SQL. Como cada SGBD possui uma arquitetura própria suas técnicas são diferentes, por isso a necessidade de se conhecer profundamente o funcionamento do SGBD a ser otimizado.
Silberschatz; Korth; Sudarshan (2012) afirmam que de fato é comum descobrir que quando uma aplicação é montada ela fica mais lenta do que o esperado, onde uma aplicação que responde com muita demora pode se tornar inutilizável nos piores dos casos. O ajuste de desempenho consiste em localizar e eliminar gargalos e acrescentar hardware apropriados como memória e discos, além de ajustar vários parâmetros e opções de projeto a fim de melhorar o desempenho de uma aplicação, como
[...] diversos aspectos de um projetos de sistemas de banco de dados – variando desde aspectos de alto nível, como projeto de esquema e transação, ate parâmetros de banco de dados, como tamanhos de buffer , e aspectos de hardware, como números de discos – afetam o desempenho de uma aplicação [...](Ibidem)
A limitação do desempenho de um sistema em geral, por um ou mais de seus componentes é chamado de gargalo, sendo exemplo um sistema que gasta 80% do seu tempo em determinada parte do código e os outros 20% nas demais partes restantes. A eliminação deste gargalo pode representar melhoria de 80% no desempenho da aplicação, enquanto que ajustar as demais partes do sistema representaria apenas 20% de ganho de desempenho. Antes de tudo, para ajustar o desempenho de um sistema é preciso mapear os seus gargalos para serem removidos, porém quando se remove um gargalo podem aparecer outros gargalos e assim por diante, por isso o monitoramento deve ser constante (Silberschatz; Korth; Sudarshan, 2012).
Existem algumas ferramentas que automatizam a tarefa de mapear gargalos nos banco de dados, no entanto o nível de conhecimento do desenvolvedor sobre a aplicação pode facilitar neste processo de identificação manual destes gargalos, tais técnicas e ferramentas
serão mostradas nas seções subsequentes.
2.5.1. Níveis de ajuste para otimização
De acordo com Silberschatz; Korth; Sudarshan (2012) os sistemas de banco de dados podem ser ajustados em três níveis, sendo o nível mais baixo relacionado ao hardware como, por exemplo, acrescentar mais discos ou usar um sistema RAID se o gargalo for um problema de I/O, trocar para um processador mais rápido se o uso de CPU for um gargalo ou ate mesmo aumentar a memória se o tamanho do buffer de disco for problema de desempenho.
O segundo nível compreende os parâmetros de sistema de banco de dados, variando este conjunto de parâmetros de acordo com o SGBD específico. Parâmetros estes que podem ser tamanho de buffer ou intervalos de checkpoints, onde geralmente a documentação dos bancos de dados oferece informações sobre como e quais parâmetros devem ser ajustados corretamente e quais os valores devem ser passados para eles. Bancos de dados mais consistentes fazem o máximo de ajuste automaticamente para que o administrador de banco de dados seja liberado desta responsabilidade e reduzindo os erros humanos de ajuste, como, por exemplo, em alguns bancos o tamanho do buffer é iniciado fixo com um valor, porém pode ser ajustada a qualquer hora à vontade do administrador (Silberschatz; Korth; Sudarshan, 2012).
Existe ainda o terceiro nível que é considerado o nível mais alto da pirâmide, este nível inclui o esquema de transações, onde o DBA pode ajustar o esquema de objetos, os índices e as transações que são executadas (consultas, blocos PL/SQL, etc.) para melhorar o desempenho. O ajuste neste nível é comparativamente independente de esquema.
2.5.2. Ajuste de hardware
Um sistema bem projetado e desenvolvido pode sofrer com problemas de desempenho caso o hardware seja inferior ao numero de processamento e requisições que o software necessita. Um fator muito importante no que diz respeito ao hardware é ter certeza que o subsistema de disco pode lidar com as operações de E/S exigidas pelo sistema. O número de
operações de E/S por minuto podem ser reduzidas aumentando a memória disponível para o sistema e armazenando-os nela, como por exemplo se todos os dados estiverem em memória não haverá E/S exceto para as operações de escritas, manter os dados usados com freqüência em memória reduz significativamente o numero de operações de I/O (input e output ou entrada e saída) de disco, compensando o custo extra com memória, já que a memória é mais cara que os discos. (Silberschatz; Korth; Sudarshan,2012)
2.5.3. Ajuste de esquema de banco de dados
Em um banco de dados projetado em uma determinada forma normal escolhida é possível dividir as relações verticalmente, ou seja, dividir uma tabela, como no exemplo abaixo:
curso(id_curso,titulo,nome_dept,creditos) pode ser dividida em
curso_credito(id_curso_creditos) curso_titulo_dept(id_curso,titulo,dept)
As duas representações são logicamente equivalentes, porem as características de desempenho pode ser muito diferente. Por exemplo, se a maioria dos acessos a esta tabela for para selecionar apenas o id_curso e os créditos ele podem ser executados sobre a relação curso_credito, onde o desempenho será provavelmente um pouco maior, pois os atributos titulo e dept não serão selecionados, o que significa que mais linhas de curso_credito caberão no buffer de cache do banco de dados do que da relação curso, o que aumenta o desempenho, este aumento pode ser significativo se os atributos titulo e dept forem grandes, portanto um esquema dividido com curso_credito e curso_titulo_dept em vez de apenas curso será mais rápido. Por outro lado se caso os acessos a informações requererem mais dept e créditos o uso do esquema apenas com curso terá o desempenho melhor, pois evitara o custo com a junção entre curso_credito e curso_titulo_dept. (Silberschatz; Korth; Sudarshan,2012)
A técnica chamada de armazenagem por coluna é baseada no particionamento vertical onde é levado ao limite ao armazenar cada atributo (coluna) em um arquivo separado, este tipo de técnica tem mostrado bom desempenho em data warehouses (Ibidem).
Segundo Silberschatz; Korth; Sudarshan (2012) views materializadas oferecem benefícios que só as formas desnormalizadas oferecem, ao custo de armazenamento extra pois
as views ocupam um grande espaço para sua armazenagem, onde sempre que uma consulta é executada diversas vezes em um determinado espaço de tempo é de se pensar em materializar esta consulta, porem as views dever ser usadas com cuidado pois podem afetar o desempenho das operações de inserção de dados ou oferecer dados que não estejam atualizados. Existem dois tipos de manutenção de views materializadas a manutenção de view imediata e a manutenção de view adiada.
A manutenção de view imediata as atualizações da tabela que afetarem a view,então esta precisa ser atualizada juntamente como parte integrante da atualização, por isso a atualização pode ser mais lenta do que caso não existisse a view.
A manutenção de view adiada esta é atualizada depois da atualização, ate que seja atualizada a view apresenta os dados inconsistentes ou desatualizados, o uso de manutenção adiada pode melhorar a performance das atualizações no banco de dados. (Silberschatz; Korth; Sudarshan,2012)
2.5.4. Ajuste de índices
Há a possibilidade de ajustar os índices de um sistema de banco de dados para melhorar o desempenho. Caso as consultas forem o gargalo, podemos agilizá-las criando índices sobre as relações. Se as atualizações forem o gargalo, podem existir muitos índices que precisam ser atualizados quando as relações forem atualizadas, por isso a remoção de índices pode melhorar o tempo de execução de algumas atualizações. (Silberschatz; Korth; Sudarshan, 2012)
Escolher o tipo de índice também é muito importante, alguns sistemas de banco de dados admitem tipos de índices de hash e índices de árvore B, se as consultas de intervalo forem comuns, os índices do tipo de árvore B serão preferíveis. Um índice pode ser agrupado, caso este beneficia o maior número de consultas e atualizações, onde somente um índice pode ser agrupado sobre uma relação, armazenando a relação classificada sobre os atributos de índice (Ibidem).
Ainda de acordo com Silberschatz; Korth; Sudarshan(2012), a maioria dos sistemas de banco de dados atuais oferecem assistentes de ajuste para ajudar a identificar quais índices devem ser criados, excluídos ou agrupados. Essas ferramentas utilizam-se do histórico de transações chamadas de carga de trabalho ou workload para estimar o efeito de diversos
índices sobre o tempo de execução das consultas e execuções sobre a carga de trabalho e as recomendações sobre quais índices criar são baseadas nessas estimativas.
2.5.5. Ajuste de SQL
Para Silberschatz; Korth; Sudarshan (2012), tuning SQL é o processo de selecionar o plano de avaliação mais eficiente perante as diversas formas de execução de uma determinada consulta.
Dificilmente um desenvolvedor ou DBA escreverá a melhor consulta e mais eficiente possível, isto será papel do otimizador do banco de dados procurar a melhor forma de execução de modo a fazer com que a consulta seja executada o mais rápido possível, é neste cenário que a otimização de SQL entra.
Uma parte da otimização ocorre no nível da álgebra relacional, onde o sistema tenta encontrar uma expressão que seja equivalente a expressão dada, porem cuja execução seja eficiente. Outro aspecto diz respeito à seleção de uma estratégia para o processamento da consulta, como a escolha do algoritmo a ser utilizado para a execução de uma operação, a escolha de índices e a forma de acesso a tabelas etc.
Para melhor entender as técnicas de otimização de consultas e tirar o maior proveito delas antes primeiramente deve-se conhecer como funciona o processamento de consultas em sistemas de banco de dados.
2.6. Processamento de consultas
Processamento de consulta refere-se ao conjunto de atividades envolvidas na extração de dados de um banco de dados. Estas atividades são a tradução da consulta em linguagens de banco de dados de alto nível para expressões que podem ser usadas no nível físico do sistema de arquivos do SGBD, além de muitas transformações de otimização de consulta e de avaliação de custo das consultas (Silberschatz; Korth; Sudarshan, 2012). A Figura 3 mostra o funcionamento do processamento de consultas em SGBDs relacionais.
Figura 3.Processamento de consulta. Adaptado de (Silberschatz; Korth; Sudarshan, 2012)
Ainda de acordo com os mesmos autores existem por padrão três etapas no processamento de uma consulta:
1. Análise e tradução; 2. Otimização;
3. Avaliação;
Antes de qualquer processamento de consulta o SGBD precisa traduzi-la para uma forma utilizável, a álgebra relacional em vez de SQL que é adequada ao uso humano, mas não ao uso pela máquina. Este trabalho é semelhante ao trabalho de um analisador sintático de um compilador (parser). Ao gerar a forma interna de consulta, o analisador verifica a sintaxe da consulta e se os nomes dos objetos utilizados correspondem realmente aos objetos existentes no esquema de banco de dados. Como existem diferentes formas de se expressar uma consulta SQL em álgebra relacional, o sistema de banco de dados precisa avaliar qual a melhor forma de acessar estes dados e qual o melhor algoritmo algébrico utilizar, sabendo que existem diversos algoritmos, como por exemplo, em determinada consulta percorrer toda a tabela ou localizar os registros procurando por determinados índices. Uma operação da álgebra relacional anotada com instruções de como anotá-la chama-se primitiva de avaliação, uma seqüência de operações primitivas é chamada de plano de execução de consultas, onde o mecanismo de execução de consulta utiliza este plano de execução, executa-o e retorna os resultados (Ibidem).
No SGBD Oracle o processamento da consulta ocorre da seguinte forma segundo a documentação oficial ORACLE (2002):
O analisador verifica a análise semântica e a sintaxe da consulta;
O otimizador utiliza métodos de verificação de custo, como cost based optimizer (CBO – Otimizador baseado em custo), ou normas internas, rule based optmizer (RBO – Otimizador baseado em regras), para determinar a forma mais eficiente de executar a consulta e retornar os resultados;
O gerador de linha recebe o plano ideal do otimizador e gera o plano de execução da consulta;
O mecanismo de execução da consulta opera em cima do plano de execução associado a uma instrução SQL e retorna os resultados;
Abaixo a Figura 4 exemplifica os passos para o processamento da consulta:
Figura 4.Processamento de consulta no Oracle. Adaptado de (ORACLE,2002)
processamento para o sistema de banco de dados. Não é esperado que os usuários escrevam a consulta de forma ser executada pelo melhor plano de execução, esta tarefa é de responsabilidade do otimizador de consultas do sistema escolher o melhor plano de execução que minimize o custo de avaliação e retorne os resultados (Silberschatz; Korth; Sudarshan, 2012)
2.6.1. Funcionamento do otimizador de consultas
O otimizador determina o caminho mais eficiente para uma consulta ser executada e ter os dados retornados aos usuários, depois de ter avaliado diversos fatores relacionados aos objetos e a instrução SQL. Este é um importante processo referente ao processamento de consultas no Oracle e que pode afetar muito o tempo de execução de uma consulta. A saída gerada pelo otimizador é um plano que descreve o melhor acesso aos dados e o mais eficiente plano de execução, o Oracle prove o tipo de otimização por CBO (Otimização baseada em custo) (Oracle, 2002).
Para cada instrução SQL, segundo a Oracle (2002) o otimizador de consultas realiza as seguintes tarefas:
Avaliação de expressões e condições: primeiramente o otimizador avalia as expressões e as condições que contém constantes sempre que possível;
Transformação de instruções: Para instruções SQL complexas, que envolvem subqueryes ou views, o otimizador pode transformar a instrução SQL original em uma equivalente;
Escolha de um objetivo de otimização: O otimizador escolhe entre as diversas formas de otimização como, por exemplo, o CBO;
Escolha de caminhos de acesso: Para cada tabela acessada pela instrução SQL o otimizador escolhe um ou mais caminhos de acesso disponíveis para obter os dados da tabela;
Escolha da ordem dos Joins: Em consultas que apresentam junção entre duas ou mais tabelas, o otimizador escolhe qual o par de tabelas será unidas primeiro;
Escolha dos métodos de junção: para cada método de junção, o otimizador escolhe uma operação para realizar a junção;
2.6.2. Métodos de acesso a tabelas
Métodos de acesso é a forma com que o sistema de banco de dados utiliza para acessar as informações contidas em uma tabela. De modo geral, os acessos por meio de índices são usados para tabelas com um pequeno número de linhas retornadas enquanto que os acessos de tabela por completo são mais eficientes em tabelas com um grande tamanho de dados e de linhas (Oracle, 2013).
2.6.2.1. Full table scan
Este tipo de acesso percorre toda a tabela lendo todas as linhas e filtrando aqueles que não atendem aos critérios de seleção, cada linha é verificada para ver se satisfaz aos critérios da clausula WHERE. Cada vez que um full table scan é executado os blocos são lidos seqüencialmente, como os blocos são adjacentes o banco de dados pode fazer uma leitura de I/O maior do que o comum, sendo assim um full table scan ser muito eficiente, porém a leitura de vários blocos de uma vez deve estar devidamente configurada, esta é indicada pelo parâmetro de inicialização DB_FILE_MULTIBLOCK_READ_COUNT (Oracle, 2013).
Ainda segundo a documentação oficial da Oracle um full table scan é mais eficiente para acessar tabelas com grande quantidade de dados, pois este tipo de método de acesso consegue utilizar uma chamada de I/O maior, fazendo com que se tenham menos chamadas do que, por exemplo, o acesso por índices, que fazem chamadas menores e em maior quantidade. O full table scan é usado quando ocorre as seguintes situações:
Falta de índices: Caso a consulta não possa utilizar índices devido a falta deles ou o uso de uma função na coluna indexada não permitindo o uso deste índice, então o SGBD Oracle utiliza do full table scan;
Grande quantidade de dados: Se o otimizador verifica que é necessário buscar a maioria dos blocos de uma tabela, então utiliza-se desta forma de acesso ignorando os índices;
Tabela pequena: Se uma tabela é menor que o tamanho de bloco definido na inicialização do banco de dados, então a sua leitura pode ser feita com uma única
chamada de I/O, sendo mais eficiente do que ter varias chamadas de I/O utilizando índices;
Elevado grau de paralelismo: caso a tabela apresente alto grau de paralelismo, isto força o otimizador a utilizar o full table scan ao invés de índices. É possível consultar a coluna DEGREE na tabela ALL_TABLES para determinar o grau de paralelismo existente na tabela;
2.6.2.2. RowID scan
O rowid de uma linha especifica o arquivo de dados e o bloco de dados que contém a referida linha. Localizar uma linha pelo seu rowid é a forma mais rápida de acesso para recuperação de dados, pois ele aponta a localização exata da linha no banco de dados. Para acessar uma tabela e seus dados pelo rowid scan o banco de dados primeiro obtém os rowids das linhas selecionadas pela clausula WHERE através de uma verificação de índices, em seguida localiza a linha através de sua rowid. O otimizador não utiliza este método de acesso quando todas as colunas contempladas na cláusula WHERE já apresentam um índice. (Oracle, 2013)
2.6.2.3. Cluster scan
Segundo a Oracle (2013) o banco de dados utiliza o método de acesso por cluster quando uma tabela encontra-se em um cluster indexado. Em um cluster indexado, o banco de dados armazena todas as linhas com o mesmo valor de chave do cluster no mesmo bloco de dados.
2.6.2.4. Hash scan
De acordo com a documentação oficial da Oracle (2013) o banco de dados usa um hash scan para localizar linhas em um cluster de hash baseado em um valor de hash.
2.6.2.5. Sample table scan
A varredura de tabelas simples ou sample table scan recupera uma amostra aleatória dos dados de uma tabela simples ou de uma instrução select complexa. O banco utiliza esta forma de acesso apenas quando uma instrução na clausula from inclui a cláusula sample ou sample block (Oracle, 2013).
2.6.2.6. Index scan
Neste tipo de método de acesso um registro é recuperado por meio de varredura de índices, usando os valores da coluna indexada especificada na instrução SQL. Para executar uma varredura de índice o banco de dados Oracle busca o índice para os valores de colunas indexadas pela instrução. Se a instrução acessa apenas os valores do índice, então o banco de dados Oracle não precisa varrer a tabela. Um índice não contém apenas os valores de uma coluna, mas também os rowIds de linhas na tabela com esse valor, então se a consulta não acessa apenas as colunas indexadas o Oracle pode encontrar o registro pelos rowids das linhas que contenham o valor desejado pela instrução SQL (Oracle , 2013).
Segundo a documentação oficial do SGBD Oracle (2013) existem 8 tipos de escaneamento por índice:
1. Full scans
2. Fast full index scans 3. Index joins
4. Bitmap indexes 5. Index skip scans
6. Index range scans descending 7. Index range scans
8. Index unique scans
O Oracle realiza Leitura/Escrita (I/O) por blocos, por isso a decisão do otimizador de qual forma utilizar para acessar uma tabela é feita de acordo com a porcentagem de blocos
acessados e não de linhas. Este é o denominado fator de agrupamento de índices, caso os blocos contenham linhas únicas, então a quantidade de linhas acessadas é a mesma que a quantidade de blocos. Porém na maioria das tabelas tem varias linhas em cada bloco, conseqüentemente um determinado numero de linhas podem ser agrupados em poucos blocos ou podem espalhar-se por um numero maior de blocos. Embora o fator de agrupamento é uma propriedade do índice, o fator de agrupamento relaciona também com a propagação de valores semelhantes da coluna indexada dentro dos blocos de dados na tabela. Um fator de agrupamento mais baixo indica que as linhas individuais são concentradas dentro de poucos blocos da tabela, por outro lado um alto fator de agregação indica que as linhas são mais espalhadas aleatoriamente em blocos da tabela. Por isso um alto fator de agregação significa que é mais custoso para o SGBD buscar os dados por index scan ou rowid do que uma varredura completa porque mais blocos deverão ser lidos para recuperar os dados (Oracle, 2013).
2.6.2.7. Index unique scan
Este tipo de verificação retorna no máximo uma única rowid (linha). O Oracle realiza o index unique scan caso a declaração contém uma restrição de chave primária que garanta que uma única linha seja acessada. O banco de dados usa este tipo de varredura quando o usuário especifica todas as colunas de um índice B-Tree ou um índice criado como resultado de restrição de primary key com condições de igualdade (Oracle, 2013).
2.6.2.8. Index range scans
A varredura por range scan é uma operação muito comum utilizada pelo Oracle para retornar os dados. Ela pode ser limitada ou ilimitada, e os dados são retornados em ordem crescente dos índices. Diversas linhas com valores idênticos são retornados em ordem crescente pelo rowid. Se você quer que os dados sejam retornados ordenados então deve usar uma cláusula order by, não dependendo de um índice, caso o índice satisfaça a cláusula order by o banco de dados utiliza esta opção e evita a classificação dos resultados. O index range scan é usado quando ocorre as seguintes situações:
col1 = :b1
col1 < :b1
col1 > :b1
Declarações que contenham o operador coringa „%‟ não utilizam desta forma de varredura, assim como as declarações que contenham ORDER BY e GROUP BY com as colunas dos índices (Oracle, 2013).
2.6.2.9. Index range scan descending
O index range scan descending é idêntico ao index range scan, porém os dados são retornados em ordem decrescente, este tipo de varredura é utilizado quando o índice satisfaz uma declaração ORDER BY DESC (Oracle, 2013).
2.6.2.10. Index skip scan
Index Skip Scan é um tipo de varredura para índices compostos. No Skip Range Scan caso umas das colunas que compõe o índice composto não é explicitado na declaração o banco de dados “pula” a coluna em questão, ou seja, esta coluna é ignorada. O Oracle determina o número de sub-índices lógicos ao número de valores distintos na coluna inicial, este tipo de verificação é vantajosa quando existem poucos valores diferentes na coluna inicial que compõe o índice composto e muitos valores distintos na segunda coluna que compõe o índice (Oracle, 2013).
2.6.2.11. Full index scan
A vantagem do Full Index Scan é que este tipo de varredura elimina a classificação dos resultados, porque os dados são ordenados pelo índice. O banco lê os blocos isolados. O Oracle utiliza desta forma de acesso quando acontece uma das seguintes situações: Uma cláusula ORDER BY que atende ao seguintes requisitos esta presente na consulta:
Todas as colunas que esta na clausula ORDER BY devem estar no índice;
A ordem das colunas na clausula ORDER BY deve coincidir com ordem das colunas do índice;
A consulta exige um sort-merge join. O Oracle pode usar um full index scan em vez de um full table scan seguido por uma classificação quando a query atende aos seguintes requisitos:
Todas as colunas referenciadas na query estão indexadas;
A ordem das colunas referenciadas na query devem ser as mesmas das presentes nas colunas do índice;
A query pode conter todas as colunas do índice ou um sub-conjunto de colunas do índice;
A cláusula GROUP BY está presente na consulta, e as colunas da cláusula GROUP BY estão presentes no índice. A ordem das colunas na cláusula GROUP BY não precisa necessariamente estar na mesma ordem das colunas do índice (Oracle, 2013).
2.6.2.12. Fast full index scan
Este é um tipo de varredura alternativa ao full table scan quando o índice contém todas as colunas necessárias para a consulta, e pelo menos uma das colunas do índice tem a restrição not null. Este tipo de verificação acessa os dados do índice em si, sem acessar a tabela. O banco de dados não pode usar este tipo de verificação para eliminar uma operação de classificação ou ordenação porque os dados não estão ordenados no índice. O banco de dados lê o índice inteiro utilizando leituras de multiblocos, além de poder fazer varreduras em paralelo. Este tipo de varredura é mais rápido que o full index scan porque pode utilizar leituras de multiblocos e fazer outras varreduras em paralelo (Oracle, 2013).
2.6.2.13. Index join
O Index Join é uma junção de hash de vários índices, que juntos contém todas as colunas da tabela referenciada na consulta. Se o banco de dados usa o index join então o
acesso a tabela não é necessário, pois o banco de dados consegue recuperar todos os valores dos índices.
2.6.2.14. Bitmap indexes
Um bitmap join utiliza um bitmap para os valores chave e uma função de mapeamento que converte cada posição de bit para um rowid. Bitmaps podem eficientemente unir índices que correspondem a varias condições em uma cláusula WHERE, usando as operações booleanas para resolver condições AND e OR (Oracle, 2013).
2.6.3. Como o otimizador escolhe o caminho de acesso
Segundo a Oracle o otimizador escolhe o melhor caminho de acesso de acordo com os seguintes fatores:
Os caminhos de acesso disponíveis para a instrução;
O custo estimado da execução da declaração, utilizando todos os caminhos de acesso disponíveis e ou a combinação dos caminhos;
Para escolher um caminho de acesso, primeiramente o otimizador verifica quais as formas de acesso estão disponíveis, examinando as condições da declaração da clausula FROM e da cláusula WHERE. O otimizador então gera um conjunto de possíveis planos de execução usando os caminhos de acesso disponíveis e estima o custo de cada plano, utilizando-se das estatísticas dos índices, colunas e tabelas acessadas pela declaração. Quando o otimizador escolhe um plano de execução ele pode ser influenciado pelos HINTS, que são dicas passadas para o otimizador “obrigando-o” a utilizar determinado plano de execução, ou também pode ser influenciado pelas estatísticas desatualizadas, onde, por exemplo, uma tabela pode não ter sido analisada desde sua criação, portanto caso esta estatística desatualizada informe que a tabela possui tamanho inferior ao parâmetro DB_FILE_MULTIBLOCK_READ_COUNT, então o otimizador poderá usar um full table scan impactando negativamente na execução da consulta. Existe uma coluna
LAST_ANALYZED que informa a ultima vez que a tabela foi analisada, esta coluna encontra-se na tabela ALL_TABLES do Oracle (Oracle, 2013).
2.6.3.1. Métodos de junção (JOINS)
Joins são declarações que retornam dados de diversas tabelas. A cláusula FROM define as associações entre as várias tabelas. A existência de um join na cláusula WHERE define qual o relacionamento entre as tabelas, e em um join um conjunto de linhas é chamado de INNER e o outro conjunto de OUTER (Oracle, 2013).
2.6.3.2. Como o otimizador executa os métodos de join
O otimizador para escolher como será feito o método de união das tabelas toma algumas decisões inter-relacionadas:
Métodos de acesso: Para declarações simples, o otimizador deve escolher um método de acesso para retornar os dados de cada tabela envolvida no join;
Métodos de junção: Para juntar cada tabela, o Oracle excuta uma operação de união (Join), existem diferentes métodos de join incluindo nested loop, sort merge, cartesian e hash como veremos adiante;
Ordem de Join: Para executar uma união de mais de duas tabelas, o Oracle junta duas tabelas, e em seguida as une as linhas resultantes da operação com a outra tabela até que todas as tabelas sejam unidas (Oracle, 2013).
2.6.3.3. Como o otimizador escolhe o plano de execução para os joins
O otimizador considera os seguintes aspectos para a escolha do plano de execução dos joins:
tabelas retornam mais de uma linha de resultados. O otimizador reconhece tais situações baseando-se nas constraints UNIQUE e PRIMARY KEY. Se tal situação realmente existe, então o otimizador coloca estas tabelas em primeiro lugar na ordem dos joins. O otimizador então otimiza o restante dos joins entre as tabelas.
Para cada declaração de Join que tenha a condição de OUTER JOIN, a tabela com o operador OUTER JOIN deve vir primeiramente que a outra tabela na ordem dos Joins. O otimizador não considera a ordem dos joins que viola esta regra. Igualmente, quando uma subconsulta que foi convertida em um semijoin ou um antijoin , as tabelas da subconsulta devem vir antes das demais tabelas que estão no outro bloco da consulta às quais estão conectadas ou correlacionadas. Entretanto, hash antijoins e semijoins podem sobrescrever esta condição de ordenação em certas circunstâncias.
O otimizador gera um conjunto de planos de execução, de acordo com os possíveis ordens de junção (Join Order), métodos de join, a métodos de acessos disponíveis. O otimizador então estima o custo de cada plano e escolhe o que apresenta o menor custo.O otimizador estima o custo de acordo com as seguinte:
O custo das operações de Nested Loops é baseado no custo de leitura de cada linha selecionada na outra tabela e em cada linha da tabela inner na memória. O otimizador estima este custo usando as estatísticas do dicionário de dados.
O custo de um sort-merge join é baseado no custo de leitura de todo o conteúdo da memória e da ordenação destes.
O custo dos hash joins são baseados no custo de criação das tabelas de hash de um dos lados do join e usando as linhas do outro lado do join.
O otimizador também leva em consideração outros fatores para determinar o custo de cada operação. Por exemplo:
Uma sort area pequena é propícia para aumentar o custo de um sort-merge join porque ordenar exige mais tempo de CPU e I/O em uma pequena sort area.
Um alto valor para o multiblock read count é propício para diminuir o custo de um sort-merge join em relação a um nested loop. Se o banco de dados pode ler um número grande de blocos seqüenciais do disco em uma única chamada de I/O, então um índice na inner table para um nested loop é menos eficiente que um full table scan (Oracle , 2013).
2.6.3.4. Nested loops join
Segundo a Oracle (2013) o nested loop ou loops aninhados são úteis quando as seguintes situações são verdadeiras:
O banco de dados junta pequenos conjuntos de dados;
A condição de junção é uma boa forma de acessar a segunda tabela;
É importante assegurar que a tabela interna (inner table) é dependente da tabela externa (outer table). Se o caminho da tabela interna é independente da tabela externa, em seguida, as mesmas linhas são recuperadas para cada iteração do loop externo, isto diminui o desempenho consideravelmente. Nestes casos uma junção de hash une as duas tabelas de formas independentes o que acarreta uma melhora de desempenho.
A união por nested loop une as tabelas seguindo as seguintes etapas:
O otimizador determina a tabela de condução e a designa como tabela externa;
A outra tabela é designada como tabela interna.
Para cada linha da tabela externa o Oracle acessa todas as linhas da tabela interna. O loop externo aparece antes do loop interno no plano de execução.
O otimizador usa o método de junção nested loop quando um pequeno número de linhas existe para a união, sendo que o loop se dirige da tabela externa para a interna, então a ordem das tabelas é muito importante.
O laço interno é iterado para cada linha do laço externo, de preferência por uma varredura de índice. Se o caminho de acesso da tabela interna não é dependente do laço externo então pode acontecer um produto cartesiano produzindo o mesmo conjunto de linhas, portanto deverá ser usado um outro tipo join quando duas fontes de linhas independentes estão unidas.
2.6.3.5. Hash join
O Oracle utiliza do método de junção hash para unir grandes conjuntos de dados. O otimizador usa a menor das duas tabelas para a criação de uma tabela hash na chave de junção na memória. Após isto ela examina a tabela maior, verificando a tabela hash para encontrar as linhas da junção.
Esta forma de junção é melhor quando a tabela menor se encaixa na memória disponível, então o Oracle consegue fazer uma única leitura. O otimizador utiliza o hash join se caso as duas tabelas são unidas por um equijoin e se qualquer uma das seguintes condições forem verdadeiras:
Uma grande quantidade de dados deve ser unida;
Uma grande fração de uma pequena tabela deve ser unida (Oracle, 2013).
2.6.3.6. Sort Merge join
Segundo a Oracle (2013) Sort merge Joins podem unir linhas de duas fontes independentes. Hash joins geralmente tem uma performance melhor do que o sort merge join. Entretanto o sort merge join pode ter um desempenho melhor que o hash join se algumas condições forem verdadeiras:
As fontes de dados já estão classificados;
Uma operação de classificação não deve ser realizada;
Caso um sort merge join envolva um método de acesso mais lento, então a vantagem de se usar um merge join pode ser eliminada.
Sort merge joins são muito úteis quando a condição de junção de duas tabelas são condições de desigualdade como <, <=, >, ou >=. Sort merge join possui um desempenho melhor do que os nested loops para grandes quantidades de dados, e não pode usar hash se caso não exista uma condição de igualdade.
No merge join não existe o conceito de uma tabela de condução. A junção é feita em duas etapas:
Operação de junção e classificação: Ambas as entradas de dados são classificadas na chave de junção.
Operação de junção: As listas ordenadas são mescladas.
O otimizador pode escolher um sort merge join para unir uma grande quantidade de dados em vez de um hash join se as seguintes condições são verdadeiras:
A condição de união das duas tabelas não é uma igualdade (equijoin).
