Hélder Manoel Lima e Silva - hmls

1. sintonia de banco de dados SISTEMAS DE GERENCIAMENTO DE BANCO DE DADOS Hélder Manoel Lima e Silva - hmls

2. Visa melhorar o tempo de resposta na base de dados; • Exige conhecimento da arquitetura, SGBD, projeto do BD; • Sintonia x Otimização de consultas;

3. DESEMPENHO O QUE É? eficiência com que um sistema de computação atinge seus objetivos COMO MEDIR? • modelagem analítica: não precisa da implantação real do sistema, porém simplificações são necessárias e podem comprometer a qualidade e generalidade dos resultados. • simulação: permite resultados mais precisos, mas, exige maior esforço e conhecimento de ferramentas. • monitoramento: pode ser aplicado apenas em sistemas já existentes podendo gerar medições mais realistas, todavia, se constitui em carga extra para o sistema

4. SINTONIA MANUAL atividade do DBA: tarefa complexa pela necessidade de compreensão dos algoritmos utilizados pelo sistema e pela possibilidade de inter-relações entre os ajustes SINTONIA AUTOMÁTICA cenário ideal: sistemas alcançam desempenho adequado sem necessidade de ajuste manual

5. PROCESSAMENTO E OTIMIZAÇÃO DE CONSULTAS PRINCIPAIS ETAPAS DE AVALIAÇÃO DE UMA CONSULTA: Verificação da sintaxe Erros sintáticos são detectados Verificação de semântica verifica se os objetos utilizados no comando SQL existem, estão acessíveis para o usuário, e foram aplicados corretamente.

6. PROCESSAMENTO E OTIMIZAÇÃO DE CONSULTAS Reescrita de query Reescrita do comando SQL em uma representação mais adequada para a manipulação do SGBD Otimização de plano de acesso Simplificação do comando SQL em unidades mais simples para que o SGBD seja capaz de mapear isoladamente para suas rotinas básicas

7. PROCESSAMENTO E OTIMIZAÇÃO DE CONSULTAS Geração de código mapeamento do comando SQL em rotinas básicas do SGBD Complexidade NP (não é possível determinar solução ótima)

8. ÍNDICES • visam permitir acesso mais rápido aos dados • usualmente mantidos em estruturas de árvores B+ PRINCIPAIS TIPOS • não cluster: ponteiros para cada uma das tuplas da tabela de cada um dos valores indexados. dados separados dos índices. • cluster: nas folhas, alem das chaves, as tuplas correspondentes.

9. MÉTODOS DE ACESSO • varredura seqüencial (full table scan):leitura de todas as páginas do disco que contém dados referentes a uma tabela • busca baseada em rowid:busca a tupla sem percorrer toda a tabela. • varredura indexada única:o índice é percorrido para a obtenção de informações referentes a uma única tupla. • varredura indexada por faixa de valores :uma faixa de valores do índice é percorrida na busca por um conjunto de um ou mais valores da chave

10. JUNÇÃO E ORDENAÇÃO Além dos métodos de acesso, outras operações são necessárias para obter resultados a partir de comandos SQl como junção e ordenação. ALGORITMOS PARA IMPLEMENTAÇÃO • Laços aninhados • Ordenação fusão • Junção hash

11. OUTRAS CLÁUSULAS SQL SÃO RESOLVIDAS POR OPERAÇÕES DE ORDENAÇÃO • Order by • Group by • Union

12. OTIMIZAÇÃO POR CUSTO E POR REGRAS Para a escolha das operações a serem utilizadas e montagem dos planos de acesso devem-se tomar decisões baseadas em custo ou regras. REGRAS 1. utilizam um conjunto de regras para definir a ordem das operações. 2. operações mais restritivas devem ser realizadas antes.

13. OTIMIZAÇÃO POR CUSTO E POR REGRAS CUSTO 1. Um custo é atribuído a cada operação 2. O plano escolhido é o que apresentar menor custo total.

14. select nome from funcionário select e.nome, d.nome from empregado e inner join departamento d on e.depnum = d.num order by e.nome Resultado A Varredura completa da tabela Resultado C Ordenação select nome from funcionário Where matrícula = 10 Junção por laços aninhados Resultado B Varredura indexada completa em índice cluster (tabela empregado) Varredura indexada única em índice cluster (tabela departamento) Busca baseada em rowid Varredura indexada única

15. SINTONIA Alterações visando melhor desempenho na execução de um comando ou acréscimo da vazão (throughput) do SGBD. • Essencial conhecimento dos planos de acesso e como o SBGD os monta • Reduzir operações de I/O • Reescrita de comandos • Dicas

16. SINTONIA • Reescrita de Comandos Comandos diferentes resultados iguais Performances diferentes

17. A B C select distinct matricula,nome from empregado select matricula,nome from empregado e inner join departamento d on e.depnum = d.num where d.num = 5 select depnum,count(*) from empregado e group by depnum having depnum > 5 select and depnum < 10 select matricula,nome from empregado select depnum,count(*) from empregado e where depnum >5 and depnum <10 group by depnum select matricula, nome from empregado e where e.depnum = 5

18. CONSULTAS E SUB-CONSULTAS Não correlacionadas >>> tendem a não utilizar índice na sub-consulta Correlacionadas >>> referenciam atributo das tabelas da consulta externa Junções >>> usualmente, o melhor desempenho

20. DICAS indicam uma operação que deve ser realizada dentro do plano de acesso que não foi determinada quando o plano foi gerado pelo otimizador. EXEMPLOS Select /* + INDEX(nome_tabela nome_indice) */ Coluna1, coluna2 from tabela Select /* + FULL(nome_tabela) */ coluna1,coluna2 from tabela

21. FATOR DE FILTRO • Percentual de tuplas que satisfazem um predicado SQL • Baseado em Estatísticas coletadas pelo SGBD • Operadores AND ,OR

22. ÍNDICESCOMPOSTOS • IS NULL, NOT LIKE, NOT EXISTS:impedimento do uso de índices • USO DE ÍNDICES COMPOSTOS • CAUSA PROBLEMAS SE CRIADOS COM BASE EM ATRIBUTOS ATUALIZADOS FREQUENTEMENTE: necessidade de se atualizar os índices dos atributos que sofreram atualização.

23. SINTONIA DE PROJETO • Problemas de performance com junções >>> Desnormalização • Espaço em disco abundante >>> Views materializadas • Particionamento

24. HIT RATIO Manter operações mais necessárias em memória principal (minimizar leitura física) Hit Ratio =nº de leituras lógicas – nº de leituras físicas X 100 nº de leituras lógicas • Indicador de acertos na memória. • Pode ser utilizado como alerta para necessidade de sintonia.

25. BLOQUEIOS Transações longas >>> Dados bloqueados Padrão dos SGBD Read Commited (2PL restrito).

26. AUTO-SINTONIA • Transações longas >>> Criação de estruturas de apoio para acelerar buscas • Coleta de estatísticas sobre dados armazenados • Alocação dos dados em disco em memória • Controle de quantas transações serão atendidas pelo sistema e qual tempo de resposta

27. PROPOSTAS PARA AUTO SINTONIA • Definição do ambiente em que o componente de auto-sintonia esta inserido. (local ou global) • Modificação do sistema através de um ciclo de controle de realimentação • Coleção de estatísticas e informações de forma que haja pouco impacto no desempenho do sistema • Utilização de modelos matemáticos e estimativas para prever o desempenho futuro do sistema. • Realização de ajustes simples online e ajustes complexos offline. • Substituições de políticas de sintonia por decisões de política de uso

28. AUTO-SINTONIA GLOBAL Interações entre componentes podem causar desestabilização do sistema provocadas por sintonia em um recurso particular. • Sistemas que são projetados para satisfazer requisitos de desempenho << Projetar sistemas mais simples Possibilidade de modelar formalmente comportamento e ajuste << Nenhum sistema implementado até hoje • Sistemas aos quais são acrescentados módulos de auto-sintonia << Calcular vantagens da execução de cada possível sintonia escolher a melhor e intensidade da mudança. << Incluído no SQL Server (Index tuning wizard)Projetar sistemas mais simples Possibilidade de modelar formalmente comportamento e ajuste

29. AUTO-SINTONIA LOCAL • Projeto físico >> escolher a melhor organização física para um dado esquema • Alocação de dados >> determinar como deve-se alocar e realocar fragmentos de arquivos ou relações de modo que o equilíbrio de carga seja o menor possível • Controle de carga >> evitar que a vazão caia devido a conflitos de bloqueio • Substituição de páginas >> manter em memória as páginas mais acessadas do banco de dados • Ajuste de buffers >> buscar a quantidade de buffers ideal de buffers distintos para que o desempenho aumente • Refino de estatísticas >> definir quais estatísticas devem ser criadas no banco de dados

30. AUTO-SINTONIA ÍNDICE • Agente de software integrado ao SGBD • Coleta de informações sobre consultas ao SGBD • Com base em uma heurística, decide quando deve criar ou destruir índices.

31. fim