Gestão da Aplicação
-
Upload
carlos-pampulim-caldeira -
Category
Education
-
view
404 -
download
1
Transcript of Gestão da Aplicação
Afinação da Aplicação(Desempenho da Aplicação)
Administração de Bases de Dados
Carlos Pampulim Caldeira
http://www.di.uevora.pt/~ccaldeira
http://makingdatawork.blogspot.pt
Optimizador Relacional
• Permite a adaptação do SQL ao ambiente dinâmico da base de dados:• Tabelas crescem / diminuem
• Índices adicionados / removidos
• Base dados fragmentada / desfragmentada
• SQL, standard para acesso à informação• Alto nível de abstracção• Quais são os dados pretendidos• Não especifica como os ir buscar• Access paths, caminhos de acesso aos dados• Forma desestruturada de escrita• Operações a nível de conjuntos de dados
Optimizador Relacional
Aplicações estão isoladas:
• Estrutura (independência Lógica)
• Forma armazenamento (independência Física)
Index Selectivity
Taxa de duplicação numa coluna indexada. Selectividade ideal é 1, apenas alcansável em índices únicos.
Is = total valores distintos / número de linhas da tabela
Ex. Boa selectividade: 88 000 /100 000 = 0.88
Is < 0,1
Medição: Index SelectivitySELECT COUNT (DISTINCT “Disciplina”)
“Valores distintos” FROM disciplina;
Valores distintos--------------- 5
SELECT COUNT(*) “Nº Total Linhas” FROM disciplina;
Nº Total Linhas----------------- 14
Is = 5 / 14
= 0,35(714…)
Medição: Index SelectivityCardinalidade
[Código Postal] Quantos distintos haverá em PT?
Valores distintos--------------- 1000?
Se tiver uma tabela com registo de 10 milhões de licenças de condução?
Selectividade = 1000 / 10 000 000
Is = 0,0001%
Medição: Index Selectivity
Ajuda a decidir entre:
• B*Tree
• Bitmap
1. Se SELECTIVIDADE > 4% é B*Tree
2. Se SELECTIVIDADE < 4% é Bitmap
Os índices, com uma selectividade inferior a 0,1%, devem ser do tipo Bitmap pois caso contrário nem serão levados em consideração pelo optimizador relacional.
Medição: Index Selectivity
Auditoria aos índices em uso (Oracle)
SELECT INDEX_NAME "Nome", DISTINCT_KEYS / NUM_ROWS * 100 "SELECTIVITY %", NUM_ROWS, DISTINCT_KEYS "DISTINCT", LEAF_BLOCKS, CLUSTERING_FACTOR, BLEVEL "LEVEL", AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY"FROM DBA_INDEXESWHERE DISTINCT_KEYS / NUM_ROWS < .1 AND NUM_ROWS > 0ORDER BY "SELECTIVITY %" DESC;
Index Density
Percentagem de duplicados numa coluna indexada
Supor tabela com 2 milhões linhas:
1 / NUM_DISCTINCT (not null)
1 / 2 000 000 = 0,0000005
1 / 2 = 0,5
Joins
Dois métodos de join:
• Join em ciclo (nested-loop join)
uma linha é identificada na tabela outer e em seguida a tabela inner é varrida à procura de uma ligação e, assim sucessivamente até à última ocorrência na tabela outer.
• Join combinado (merge-scan join)
as linhas das tabelas são ordenadas e depois cada uma das tabelas é lida sequencialmente e as colunas coincidentes são assinaladas e as linhas devolvidas.
Notas: O join combinado deve ser o escolhido quando a tabela joined um número muito grande linhas ou quando há muitas linhas que satisfazem a condição, ou ainda quando os predicados do join não são muito selectivos.
Joins
Nº de tabelas na junção Nº de possíveis (ordens) de junções
1 1
2 2
3 6
4 24
5 120
6 720
7 5040
8 40320
9 363880
10 3628800
11 39916800
12 479001600
13 6227020800
14 87178291200
15 1307674368000
16 20922789888000
Caminho de acesso aos dados
• não existe(m) índice(s) ou as condições [WHERE] excluem o(s) índice(s)
• grande número de linhas que satisfazem as condições
• índices com baixo clustering (index page cluster ratio)
• a tabela é demasiado pequena (poucas linhas)
Varrimento das tabelas:
Acesso directo pelo índice (direct index lookup)
cargo função departamento
Caminho de acesso aos dados
SELECT número, apelido, nomeFROM empregadoWHERE cargo = “Gerente”AND função = 1AND departamento = “D01” ;
Varrimento por comparação exacta (matching index scan)
Caminho de acesso aos dados
Varrimento do índice:
SELECT número, apelido, nomeFROM empregadoWHERE cargo = “Gerente”AND função = 1AND departamento = “D01” ;
Varrimento por comparação exacta (matching index scan)
Caminho de acesso aos dados
Varrimento do índice:
Pág Raíz
PágNonleaf
Pág.Nonleaf
PágNonleaf
PágNonleaf
PágNonleaf
Folha Folha Folha Folha Folha
Varrimento pela posição relativa (nonmatching index scan)
Caminho de acesso aos dados
Não há predicado de entrada no índice, logo a estrutura do índice não pode ser utilizada.
SELECT número, apelido, nomeFROM empregadoWHERE função = 1AND departamento = “D01” ;
Varrimento do índice:
Varrimento pela posição relativa (nonmatching index scan)
Caminho de acesso aos dados
Pág Raíz
PágNonleaf
Pág.Nonleaf
PágNonleaf
PágNonleaf
PágNonleaf
Folha Folha Folha Folha Folha
Varrimento do índice:
Caminho de acesso aos dados
Clustered ou unclustered:
• Acesso associado ao índice: lê pág. dados uma única
• Acesso desassociado ao índice: múltiplas leituras mesma pág. de dados
A CP não é uma boa opção para ser um índice clustered dado que o acesso aos seus valores é aleatório enquanto que o clustered favorece o acesso sequencial.
Os índices clustered ao lerem as páginas das folhas nunca lêm a mesma duas vezes. Já os índices unclustered fazem múltiplos pedidos pois os dados estão espalhados pela tabela.
cargo função departamento salário
Caminho de acesso aos dados
Index screening:
matching
pos. relativo
SELECT número, apelidoFROM empregadoWHERE cargo = “Gerente”AND função = 1AND salário > 40000 ;
Acesso restrito aos índices (index covering)
Caminho de acesso aos dados
cargo função departamento salário apelido número
SELECT número, apelidoFROM empregadoWHERE cargo = “Gerente”AND função = 1AND salário > 40000 ;
Acesso restrito aos índices (index covering)
Caminho de acesso aos dados
• Não há I/O adicional à tabela
• “Encorajar” o acesso index-only
• Sobrecarga de colunas indexadas
Acesso múltiplo (multi-index access):
Caminho de acesso aos dados
cargo função
SELECT número, apelidoFROM empregadoWHERE cargo = “Gerente”AND função = 1 ;
Acesso múltiplo (multi-index access):
Nota: AND; OR
Caminho de acesso aos dados
Por vezes pode minimizar-se o número de índices criados promovendo múltiplos índices simples, i.e., com uma única coluna, em vez de muitas colunas compostas indexadas. A decisão de aplicar uns e/ou outros depende da eficiência do SGBDR na utilização das diversas formas de indexação.
A utilização de índices para evitar SORT's:
• Distinct• Union• Group by• Order by
Ordenação de dados
O optimizador não utiliza o índice:
Desprezo pelo índice
• Sem predicados
• Join com várias/muitas tabelas
• Estatísticas desactualizadas
Dois métodos:
Views: acesso
• View merging
• View materialization
O view merging é o método mais eficiente. O SQL aplicado no DDL da view é aglutinado (merged) com o SQL que refere a view. O SQL resultante é então utilizado para determinar o caminho de acesso aos dados.
Quando não é possível combinar o SQL da view com o SQL que acede à view é criada uma tabela temporária que armazena os resultados da view. O SQL que acede à view é depois aplicado aos resultados guardados na tabela temporária. Daí a relativa ineficiência da view materialization.
Alguns optimizadores:
Re(escrita) de queries
WHERE coluna1 >= 1 AND coluna1 <= 100 , podem transformar esta
cláusula nesta:
WHERE coluna1 BETWEEN 1 AND 100
Re(escrita) de queries
transitividade do predicado (predicate transitive closure)
SELECT d."Nome do Departamento", e."Nome", e."Número de Funcionário"
FROM empregado e, departamento d
WHERE e."Código do Departamento" = d."Código do Departamento"
AND d."Código do Departamento" = "DO59";
SELECT d."Nome do Departamento", e."Nome", e."Número de Funcionário"
FROM empregado e, departamento d
WHERE e."Código do Departamento" = d."Código do Departamento"
AND e."Código do Departamento" = "DO59";
Optimização pelo custo
• Efeito da modificação dos parâmetros de configuração (Oracle):o cost-based
O optimizador relacional é o subsistema de um SGBDR que gera planos de execução.
Codificação do SQL para a eficiência:
Regra 1: Depende...Regra 2: Posição na query da cláusula
mais restritivae prosseguindo até à 11ª regra.
Mullins, C. 2002. Chapter 12. Application Performance. Database Administration: The Complete Guide to Practices and Procedures. Addison Wesley.
SQL: regras de Mullins
Video [“esclarecedor”] sobre SQL Optimization em Oracle: ver aqui.
Optimização do SQL
SQL Analyse (Oracle): ver aqui.
Afinação da Aplicação - Oracle