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

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



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


SELECT número, apelido, nomeFROM empregadoWHERE cargo = “Gerente”AND função = 1AND departamento = “D01” ;

Varrimento por comparação exacta (matching index scan)


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)



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)


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 pela posição relativa (nonmatching index scan)


Pág Raíz

PágNonleaf

Pág.Nonleaf

PágNonleaf

PágNonleaf

PágNonleaf

Folha Folha Folha Folha Folha




• varrimento do índice > varrimento da tabela


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


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)


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)


• Não há I/O adicional à tabela

• “Encorajar” o acesso index-only

• Sobrecarga de colunas indexadas

Acesso múltiplo (multi-index access):


cargo função

SELECT número, apelidoFROM empregadoWHERE cargo = “Gerente”AND função = 1 ;

Acesso múltiplo (multi-index access):

Nota: AND; OR


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.

http://www.dba-oracle.com/art_otn_cbo_p1.htm

Análise do caminho de acesso

SQL Server:

• ShowPlan

Oracle:

• Explain

set timing on; (SQL*Plus)

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

http://video.google.com/videoplay?docid=6536314197123808953

SQL Analyse (Oracle): ver aqui.

Afinação da Aplicação - Oracle

http://download.oracle.com/docs/html/A86647_01/parthree.htm

Education

Gestão da Aplicação