Aula 1 OBD

(1)

Otimização de Banco de Dados

Rogério Morais Rocha

Bacharel em Ciência da Computação

Pós-Graduação Gestão Estratégica da Informação

e-mail : moraisrocha@gmail.com

(2)

Otimização de Banco de Dados

• Unidade 2 – Otimizando o projeto de banco de dados – Normalizações

– Utilização de índices auxiliares

(3)

Otimização de Banco de Dados

• Normalização

– Definição do processo – Primeira forma normal – Segunda forma normal – Terceira forma normal

(4)

Otimização de Banco de Dados

• Definição do processo

– No modelo entidade e relacionamento faz com que o

projetista identifique os tipos entidade e relacionamento e seus respectivos atributos, e que levam a um

agrupamento natural e lógico dos atributos em relações. – Entretanto precisa-se de alguma medida formal para

identificar por que um esquema de agrupamento de atributos em uma relação é melhor que outro.

(5)

Otimização de Banco de Dados

– Redução de valores redundantes nas tuplas

• Uma meta para o modelo de um esquema é

minimizar o espaço de armazenamento usado pelas relações básicas.

• Agrupar os atributos em esquemas de relações tem um efeito significativo no espaço de armazenamento. • Problemas como anomalias na inserção, na exclusão

(6)

Otimização de Banco de Dados

– Redução de valores redundantes nas tuplas

• Anomalias na inserção – ao inserir uma nova tupla será necessário inserir valores já informados ou nulos a

atributos sem significado a relação.

• Anomalias na exclusão– ao apagar uma tupla poderá apagar informações de atributos sem significado a relação.

• Anomalias na atualização – ao atualizar um atributo sem significado a relação todas as outras tuplas

correspondestes a este atributo e valor deverão ser alteradas.

(7)

Otimização de Banco de Dados

– Redução de valores redundantes nas tuplas – O que deve ser seguido

• Modelar esquemas de relações básicas de forma que nenhuma anomalia de inserção, exclusão ou

atualização possa ocorrer nas relações.

• Caso exista a possibilidade de ocorrer anomalias, registre-a claramente e certifique-se que os

(8)

Otimização de Banco de Dados

– Dependências funcionais e suas regras

• Exemplo: Nome, Peso e Endereço são dependentes funcionais de Matrícula.

Matrícula Nome Peso Endereço

5652 João 90 Rua X, 10

5658 José 60 Rua Y, 150

5659 Maria 55 Rua A, 89

5351 Joana 60 Rua B, 580

(9)

Otimização de Banco de Dados

– Dependência Funcional Total (Completa)

– Na ocorrência de uma chave primária composta, um atributo ou conjunto de atributos depende de forma

completa desta chave primária, se e somente se, a cada valor da chave está associado um valor único para cada atributo.

(10)

Otimização de Banco de Dados

– Dependência Funcional Total (Completa) • Data_Abertura é dependente funcional total

(completo) da chave composta (Conta, Agência).

Conta Agência Data_Abertura Nome_Agência Endereço

565-1 2556 10/03/01 Agência Savassi Rua X, 10

351-0 0985 11/01/01 Agência Centro Rua Y, 580

(11)

Otimização de Banco de Dados

– Dependência Funcional Parcial

– Na ocorrência de uma chave primária composta, um

atributo ou conjunto de atributos depende parcialmente desta chave primária, se ele depende somente de parte da chave, e não dela como um todo.

(12)

Otimização de Banco de Dados

– Dependência Funcional Parcial

– Nome_Agência e Endereço são dependentes somente de (Agência).

– Nome_Agência e Endereço são dependentes funcionais parciais da chave composta (Conta, Agência)

Conta Agência Data_Abertura Nome_Agência Endereço

351-0 0985 11/01/01 Agência Centro Rua Y, 580

(13)

Otimização de Banco de Dados

– Dependência Funcional Transitiva

– Quando um atributo ou conjunto de atributos A depende de outro B que não pertence à chave primária, mas é dependente funcional desta, dizemos que A é

(14)

Otimização de Banco de Dados

– Dependência Funcional Transitiva

– Nome_Cliente e Endereço são dependentes transitivos de (Cod_Cliente).

Pedido Cod_Cliente Nome_Cliente Endereço

139 235 João Rua X, 10

140 129 Maria Rua Y, 100

141 258 José Rua Z, 155

142 139 Joaquim Rua A, 580

(15)

Otimização de Banco de Dados

• Primeira Forma Normal (1FN)

– Uma entidade está na 1FN quando todos os atributos estiverem baseados num domínio simples, ou seja, não contêm grupos ou valores redundantes.

– Todos os atributos devem ser monovalorados.

– Cada atributo deve possuir um significado único, por exemplo:

• Data de Pedido/Data de Entrega -> Data de Pedido, Data de Entrega.

(16)

Otimização de Banco de Dados

• Primeira Forma Normal (1FN) – Aplicação

• Defina as chaves candidatas e escolha a chave primária da entidade.

• Transforme os atributos compostos em atômicos. • Elimine dados redundantes.

– Exemplo:

Nome Endereço

João Rua X, 10 – Centro, BH

José Rua Y, 150 – Funcionários, BH Maria Rua A, 89 – Centro, Juiz de Fora Joana Rua B, 580 – Sion, BH

(17)

Otimização de Banco de Dados

• Primeira Forma Normal (1FN) – Exemplo:

Cod_Cliente Nome Rua No. Bairro Cidade

5658 João Rua X 10 Centro BH

5659 José Rua Y 150 Funcionários BH

5680 Maria Rua A 89 Centro Juiz de Fora

(18)

Otimização de Banco de Dados

• Primeira Forma Normal (1FN)

• Os grupos ou valores redundantes são transferidos para uma nova entidade.

• A chave primária da entidade original fará parte da chave primária da nova entidade formando uma chave composta.

Cliente (Cód., Nome, Telefones)

Cliente (Cód., Nome)

(19)

Otimização de Banco de Dados

• Segunda Forma Normal (2FN)

– Uma entidade está na 2FN se todo atributo não primário tem dependência funcional total da chave primária.

– Envolve verificar se os atributos do lado esquerdo das dependências funcionais fazem parte da chave primária. – Se a chave primária conter um único atributo, a

necessidade do teste não se aplica. Isto é, somente há necessidade de aplicar a 2FN quando a entidade conter uma chave composta.

(20)

Otimização de Banco de Dados

– Exemplo: Conta (No. Conta, Agência, Data_Abertura, Nome_Agência, Rua, Número)

No. Conta Agência Data_Abertura Nome_Agência Rua Número

565-1 2556 10/03/01 Agência Savassi Rua X 10

351-0 0985 11/01/01 Agência Centro Rua Y 580

(21)

Otimização de Banco de Dados

– Exemplo: Conta (No. Conta, Agência, Data_Abertura, Nome_Agência, Rua, Número)

No. Conta Agência Data_Abertura

565-1 2556 10/03/01

554-9 2556 09/01/01

552-9 2556 10/02/01

351-0 0985 11/01/01

(22)

Otimização de Banco de Dados

• Terceira Forma Normal (3FN)

– Está baseada no conceito de dependência transitiva. • Conclusão

– Banco de dados normalizados tipicamente possuem mais tabelas com menos colunas.

– É um processo de eliminação de dados duplicados. – Com a aplicação da 1ª, 2ª e 3ª formas normais já se

(23)

Otimização de Banco de Dados

• Utilização de índices auxiliares

– Uma consulta sobre uma base de dados encontrará sempre uma solução. No entanto, uma organização física dos dados adequada poderá devolver esta resposta de forma mais rápida.

– Diferentes organizações físicas existem :

• Heap; • Hash; • ISAM e

(24)

Otimização de Banco de Dados

• Modelo Físico de Dados – Heap

– Acesso seqüencial aos dados.

– Todos os dados devem ser percorridos para encontrar o dado pretendido.

(25)

Otimização de Banco de Dados

• Modelo Físico de Dados – Hash

– Organização aleatória dos dados para um acesso via

chave calculada na forma de uma função de hashing.

– Fórmula da função hash : hash (m) = m mod n

• m representa o valor de procura.

• n representa o espaço de endereçamento definido.

– Na função hash pode ocorrer colisões, isto é, retornar endereços iguais para valores de procura diferentes.

(26)

Otimização de Banco de Dados

• Modelo Físico de Dados – Exemplo Hash

– Colisão :

• N = 3

• Hash (3) = 0 • Hash (6) = 0

(27)

Otimização de Banco de Dados

• Modelo Físico de Dados

– ISAM

– Organização indexada caracterizada por uma ordenação segundo os dados armazenados permitindo um acesso rápido sobre um valor exato ou parte da chave.

– Esta organização depende de um índice estático que deve ser recalculado a cada introdução de novos dados.

(28)

Otimização de Banco de Dados

• Modelo Físico de Dados

– BTree

– Organização indexada caracterizada por uma ordenação segundo os dados armazenados permitindo um acesso rápido sobre um valor exato ou parte da chave.

(29)

Otimização de Banco de Dados

• Modelo Físico de Dados – ISAM versus BTree

– No método ISAM o índice é ordenado mas a introdução de um novo registro implicará a reordenação do índice. – No método Btree o índice é ordenado dinamicamente e

a introdução de um novo registro não implicará a reordenação de todo o índice mas só uma

(30)

Otimização de Banco de Dados

• Tunning

– A tradução literal de ‘tuning’ seria sintonia ou ajuste de alguma coisa para que funcione melhor.

– Um SGBD é um produto de software sofisticado

permitindo vários ajustes. Sua flexibilidade permite fazer pequenos ajustes que afetam a performance do banco de dados.

(31)

Otimização de Banco de Dados

• Considerações sobre a otimização

– O que é performance de banco de dados?

– Fazendo uma analogia em termos de oferta e demanda. Os usuários demandam informações do banco de dados. O SGBD fornece informação para aqueles que o pedem. – A taxa entre os pedidos que o SGBD atende e a demanda

para informação pode ser denominado performance de banco de dados.

(32)

Otimização de Banco de Dados

– ‘Workload’ são os pedidos do SGBD que definem a demanda. Ele é o conjunto de transações online, jobs batch, pesquisas, etc.

– ‘Throughput’ define a capacidade do computador de processar os dados. Ele é uma composição de

velocidade de I/O, velocidade da CPU, capacidades de paralelismo da máquina e a eficiência do sistema

(33)

Otimização de Banco de Dados

• Considerações sobre a otimização – Uma metodologia recomendada deveria seguir os seguintes passos:

(34)

Otimização de Banco de Dados

– Ajuste das Regras de Negócio

• Uma análise do sistema inteiro em mais alto nível. • Por exemplo, a função de negócio de impressão de

cheques.

– O requisito real é pagar dinheiro a pessoas; o requisito não é necessariamente imprimir pedaços de papel.

– Poderia ser bastante complicado imprimir milhares de cheques por dia.

– Seria relativamente mais fácil gravar os depósitos de

pagamento em uma fita que seria enviada ao banco para processamento.

(35)

Otimização de Banco de Dados

– Ajuste do projeto de Base de Dados

• Na fase de projeto de base de dados, deve-se determinar que dados são necessários para as aplicações.

• Precisa-se considerar que relações são importantes e quais são seus atributos.

• Finalmente, precisa-se estruturar a informação para melhor atingir as metas de performance.

(36)

Otimização de Banco de Dados

– Ajuste da Estrutura Lógica da Base de Dados

• Ajuste fino do projeto de índices, para garantir que não ocorra falta ou exagero de índices.

• Nesta fase pode-se criar índices adicionais além da chave primária e estrangeira.

– Ajuste das Operações de Base de Dados

• Utilizar todas as vantagens do SQL e das

características do gerenciador para o processamento da aplicação.

(37)

Otimização de Banco de Dados

– Ajuste da Alocação de Memória

• Alocação apropriada de recursos de memória para as estruturas do SGBD pode trazer efeitos positivos na performance.

– Ajuste de I/O e Estrutura Física

• I/O de disco tende a reduzir a performance de várias aplicações de software.

(38)

Otimização de Banco de Dados

– Ajuste da Contenção de Recursos

• O processamento concorrente de vários usuários

pode criar a contenção de recursos. A contenção faz com que os processos esperem até que os recursos sejam disponibilizados.

– Ajuste das Plataformas Básicas

• Conforme a versão do gerenciador para um determinado sistema operacional pode haver parâmetros diferentes de ajuste.

(39)

Otimização de Banco de Dados

– Modifique ou desabilite ‘triggers’

• A utilização de ‘triggers’ consome recursos do sistema.

• A utilização de muitas ‘triggers’, pode afetar negativamente a performance e pode precisar modificá-lo ou desabilitá-lo.

(40)

Otimização de Banco de Dados

– Conclusão

• O DBA é um parceiro estratégico no desenvolvimento e manutenção de aplicações.

– Gerenciar pró-ativamente os sistemas. Identificando problemas potenciais antes de eles ocorrerem,

prevenindo quedas do sistema e perda de dados; – Conhecimento profundo do funcionamento físico do

sistema.

– Como o volume de dados e a demanda por eles cresce exponencialmente, é necessário sugerir modos

melhores, mais rápidos e mais baratos para armazenar e movimentar informações.