Solução - Técnico Lisboa

Número:_________________ Nome: _________________________________________
1
--------------------------------------------------------------------------------------------------------------
INSTITUTO SUPERIOR TÉCNICO
Administração e Optimização de Bases de
Dados
Exame 2 - solução 5 Julho 2008
--------------------------------------------------------------------------------------------------------------






A duração deste exame é de 2H30.
É um exame com consulta.
O número total de pontos é 20.
Marque as suas respostas NA FOLHA DE EXAME.
Escreva o seu número e nome no topo de cada página.
Escreva todas as fórmulas.
Para o uso oficial somente
1
2
2
2
3
4
4
4
5
4
6
4
SUM
20
Número:_________________ Nome: _________________________________________
2
1. (2v) Miscelânea
Indique, nas alíneas seguintes, quais as verdadeiras e as falsas.
a) O optimizador do SQL Server 2005 iria optar por um algoritmo hash join em lugar de um
nested loop join no caso das duas tabelas envolvidas na junção serem grandes.
Verdadeiro.
b) O SQL Server 2005 suporta o particionamento horizontal de indices e tabelas.
Verdadeiro.
c) O SQL Server 2005 oferece suporte nativo para índices do tipo hash e B+Tree.
Falso.
d) Na técnica de write-ahead logging, todas as páginas de dados em memória devem ser
escritas em disco antes que as páginas de log em memória possam ser escritas em disco.
Falso.
e) Uma junção efectuada através do algoritmo nested-loop tem sempre um custo maior do
que uma junção efectuada com um algoritmo sort-merge-join.
Falso.
f) Um grafo de precedências acíclico corresponde a um escalonamento que não pode resultar
em deadlock.
Falso.
g) Numa selecção apenas com disjunções, basta não existir índices para uma das condições,
para que seja necessário executar um scan a toda a tabela.
Verdadeiro.
h) O desempenho de um índice do tipo hash degrada-se com o crescimento da tabela
indexada.
Verdadeiro.
Número:_________________ Nome: _________________________________________
3
2. (2v) Sistema de Ficheiros
Os disco rígidos modernos contêm mais sectores nas pistas exteriores (i.e. uma vez que a
velocidade de rotação é constante, a transferência sequencial de dados é maior nas pistas
exteriores, mantendo-se o tempo de pesquisa para um acesso aleatório). Descreva boas
estratégias (isto é, em que pistas – exteriores ou interiores - devem ser colocados o ficheiro de
dados e o ficheiro de índice, se existir) para colocar neste disco rígido as tabelas de uma BD
que apresentem os seguintes padrões de acesso:
a) Uma tabela grande na qual se executam operações que envolvem a consulta de todos os
tuplos da tabela.
b) Uma tabela grande com um índice non-clustered, na qual se executam operações que
envolvem o acesso a tuplos individuais com base na chave do índice.
c) Uma tabela pequena na qual se executam operações que envolvem a consulta de todos os
tuplos da tabela.
d) Uma tabela pequena na qual se executam operações que envolvem o acesso a tuplos
individuais.
Solução:
a) A tabela deve ser colocada nas pistas exteriores. A velocidade de acesso sequêncial é mais
importante e as pistas exteriores maximizam este parâmetro.
b) A tabela e o índice devem ser colocada nas pistas interiores, poupando assim espaço nas
pistas exteriores para outras tabelas. Ter um índice fisicamente próximo da tabela também
traz a vantagem de minimizar o tempo envolvido nos acessos alternados às páginas do indice
e da tabela.
c) A tabela deve ser colocada nas pistas interiores, poupando assim espaço nas pistas
exteriores para outras tabelas.
d) A tabela deve ser colocada nas pistas interiores. O acesso sequêncial a uma tabela pequena
é semelhante ao acesso aleatório, na medida em que é dominado pelo custo da procura da
primeira página.
Número:_________________ Nome: _________________________________________
4
3. (4v) Índices
Considere um índice do tipo árvore B+, em que o número máximo de descendentes de cada
nó, N, é 3.
a) Desenhe a árvore resultante da inserção dos seguintes valores, pela ordem dada:
1 10 5 3 6 8 4
b) Qual seria a alteração no índice da alínea anterior, se fosse inserido novamente o valor 4?
c) Desenhe a árvore resultante da alínea (a), após a remoção dos valores 3 e 10.
Nas alíneas (a) e (c) , deve apresentar a árvore após a inserção/remoção de cada elemento, e
não apenas a árvore final. Em cada árvore, não é necessário repetir os nós que não mudaram.
Solução:
a)
b)
A árvore não seria modificada, mas seria inserido mais um apontador no bucket
correspondente ao valor 4 nas folhas da árvore.
Número:_________________ Nome: _________________________________________
c)
5
Número:_________________ Nome: _________________________________________
6
4. (4v) Processamento e optimização de interrogações
4.1. (3v) Considere as seguintes relações e a seguinte interrogação:
Bolseiro(idb,id_inst,nome,salario); // 10000 tuplos
Instituicao(idi,nome,orcamento); // 1000 tuplos
Projecto(idp,id_inst,nome);
// 1000 tuplos
SELECT B.idb, I.idi, P.idp
FROM Bolseiro B, Instituicao I, Projecto P
WHERE I.orcamento>20.000
AND B.salario=1000
AND B.id_inst=I.idi
AND I.idi=P.id_inst
Assuma que o atributo Bolseiro.salario está uniformemente distribuido no intervalo 500-3000
e que o atributo Instituicao.orcamento está uniformemente distribuido no intervalo 15.00025.000. Assuma ainda que existe um índice clustered no atributo Bolseiro.salario, um índice
clustered em Instituicao.idi, e um índice clustered em Projecto.idp
a) Liste os planos de execução considerados por um optimizador semelhante ao do sistema-R
para a interrogação apresentada.
b) Dos planos considerados anteriormente, qual seria o que apresenta um custo estimado
mais baixo?
c) Se o indice em Projecto.id fosse unclustered, o custo estimado anteriormente mudaria
substâncialmente? Justifique a sua resposta.
d) Se o indice em Bolseiro.salario fosse unclustered, o custo estimado anteriormente mudaria
substâncialmente? Justifique a sua resposta.
e) Considere que existe um índice sobre o atríbuto Instituição.orcamento. Imagine agora que
o plano de execução gerado pelo optimizador não utilizava este índice para calcular a
selecção sobre este atributo. Indique uma possível causa para este facto, e indique como
procederia para que o índice fosse considerado.
Solução:
a) Um optimizador semelhante ao do sistema R iria aplicar as condições de selecção tão cedo
quanto possível e iria ignorar planos envolendo produtos cartesianos. Seriam assim
considerados os seguintes dois planos de execução:
select-salario(B) |x| select-orcamento(I) |x| P
select-orcamento(I) |x| P |x| select-salario(B)
b) O plano de execução com o custo mais baixo corresponde a utilizar o indice no atríbuto
salário para eliminar tuplos da tabela Bolseiros, seguindo-se o join com a tabela Instituição
usando o índice em Instituição.id. De seguida, seria aplicado o predicado de selecção sobre o
atríbuto orcamento ao resultado desse join. Finalmente, seria feito o join com a tabela
Projecto.
Número:_________________ Nome: _________________________________________
c) O indice non-clustered na tabela Projecto iria aumentar o número de IOs envolvido mas
não substâncialmente, uma vez que o número de tuplos a ser retornado da tabela é
relativamente pequeno.
d) O indice non-clustered na tabela Bolseiro teria um impacto mais significativo no número
de IOs, pois o número de tuplos envolvido na tabela Bolseiros é maior do que o número de
tuplos envolvido na questão anterior. O segundo plano de execução considerado podia vir a
ser mais eficiente, consoante o número de tuplos envolvido.
e) Uma possível razão para a não utilização dos indice sobre o atríbuto salário seria o facto
das estatísticas disponíveis sobre o índice estarem desactualizadas (e.g. a selectividade da
condição de selecção era estimada de uma forma errada). Para resolver este problema,
poderiam ser recalculadas as estatísticas associadas ao índice.
7
Número:_________________ Nome: _________________________________________
8
5. (4v) Transacções, controlo de concorrência e gestão de recuperação
Considere o seguinte excerto de um log do algoritmo ARIES, lido após uma falha no sistema.
100: T1, start
110: T2, start
120: T1, insert P5, 100
125: checkpoint
130: T4, start
140: T1, delete P6, 120
150: T2, delete P5, 110
170: T4, insert P5, 130
180: T1, end
190: T2, rollback
195: CLR: T2, undo(delete P5), 110
Os registos de update do log indicam:
LSN, "transacção", "operação" "página alterada", lastLSN
No momento do checkpoint, foram escritas para o disco as tabelas:
Dirty Page Table
PID PageLSN
P5
120
Transaction Table
RecLSN
TID LastLSN
120
T1
120
T2
110
T3
80
O PageLSN das páginas P5 e P6, no disco é, respectivamente, 170 e 30.
a) Mostre, em cada passo da fase de análise, o conteúdo das tabelas Dirty Page Table,
Transaction Table e Undo List. Em cada passo, indique o LSN que está a ser analisado.
b) Usando apenas o trecho do log que é aqui mostrado, consegue determina todas
instruções que serão desfeitas? Porquê?
c) Suponha que a falha na base de dados se deu antes do registo do log com LSN 195 ter
sido escrito no disco, mas depois do UNDO correspondente ter sido executado. Isto poderia
ocasionar uma inconsistência nos dados, após a execução do algoritmo ARIES? Justifique.
Solução:
a)
125: DPT = (P5,120,120)
TT = (T1,120), (T2,110), (T3,80)
UL = T1, T2, T3
130: DPT = (P5,120,120)
TT = (T1,120), (T2,110), (T3,80), *(T4,130)*
Número:_________________ Nome: _________________________________________
9
UL = T1, T2, T3, *T4*
140: DPT = (P5,120,120), (P6, 140,140)
TT = (T1,*140*), (T2,110), (T3,80), (T4,130)
UL = T1, T2, T3, T4
150: DPT = (P5,*150*,120), (P6, 140,140)
TT = (T1,140), (T2,*150*), (T3,80), (T4,130)
UL = T1, T2, T3, T4
170: DPT = (P5,*170*,120), (P6, 140,140)
TT = (T1,140), (T2,150), (T3,80), (T4,*170*)
UL = T1, T2, T3, T4
170: DPT = (P5,*170*,120), (P6, 140,140)
TT = (T1,140), (T2,150), (T3,80), (T4,*170*)
UL = T1, T2, T3, T4
180: DPT = (P5,170,120), (P6, 140,140)
TT = (T2,150), (T3,80), (T4,170)
UL = T2, T3, T4
195: DPT = (P5,*195*,120), (P6, 140,140)
TT = (T2,*195*), (T3,80), (T4,170)
UL = T2, T3, T4
b) Não. Uma das transacções a ser desfeita é a T3. No entanto, nenhuma das suas
instruções está presente neste trecho do log.
c) Não. O algoritmo ARIES usa Write-Ahead Logging (WAL). Esta regra obriga a que os
dados resultantes de uma operação sejam escritos em disco apenas depois do registo de log
correspondente ter sido escrito no disco. Assim, se o registo LSN 195 não está em disco,
podemos ter a certeza que os dados alterados também não estão.
Número:_________________ Nome: _________________________________________
10
6. (4v) Database tuning
6.1(0,5v) No contexto da gestão de buffer no SQL Server 2005, explique o conceito de readahead processing (aka prefetching) e o porquê da sua importância.
Solução:
O Read-ahead processing é um mecanismo usado pelo SQL Server para reduzir o tempo de
espera associada com a leitura física de dados do disco. Aquando da execução de uma
operação que envolva o acesso sequêncial a páginas de dados ou de indice (e.g. um table
scan), o SGBD começa a fazer o "pre-fetching" de páginas para a cache antes mesmo que seja
feito um pedido para a sua leitura. Desta forma, maximizam-se as leituras sequênciais de
dados no disco (geralmente mais rápidas) e minimizam-se os acessos aleatórios.
6.2 (0,5v) Considere os padrões de acesso típicos a bases de dados para aplicações de Data
Warehousing ou OLTP. Em qual delas poderia fazer sentido usar um nível de isolamento
mais baixo (e.g. READ UNCOMMITED). E um nível de isolamento mais elevado (e.g.
SERIALIZABLE)? Justifique a sua resposta.
Solução:
Data Warehousing - menor isolamento, visto muitas vezes estarmos interessados em obter
tendências e valores aproximados.
- OLTP - maior isolamento, visto queremos maiores garantias sobre a execução concorrente
das transacções
Número:_________________ Nome: _________________________________________
11
6.3.(3v) Considere o seguinte esquema relacional normalizado correspondente a um
subconjunto da base de dados de uma universidade:
Professor(nmec, nomep, gabinete, idade, sexo, investigacao, deptid)
Departamento(did, nomed, orcamento, nummajors, profresp)
Suponha que sabe que as seguintes interrogações são as mais frequentes e que são igualmente
equivalentes em frequência e importância:
a) Quais os nomes, idades e gabinetes dos professores de um determinado sexo e cujo
tema de investigação é um determinados (por exemplo, bases de dados). Assuma que
não é muito comum que vários professores investiguem exactamente no mesmo
assunto.
b) Listar toda a informação sobre departamento para aos departamentos com professores
numa determinada gama de idades.
c) Qual o id, nome, e responsável dos departamentos com um determinado número de
majors.
d) Qual o orçamento mínimo de todos os departamentos da universidade?
e) Listar toda a informação dos professores que são responsáveis de departamento.
Assumindo que estas interrogações são mais importantes do que quaisquer actualizações,
execute para cada alínea o seguinte:

Escreva as interrogações correspondentes em SQL.

Diga quais os atributos que devem ser indexados, qual o tipo de índice que deve ser
usado (árvore B+ ou hash), e se é clustered ou non-clustered.

Para os índices indicados na alínea anterior, indique os casos em que a interrogação
pode ser respondida só com a informação do índice (index-only plan), se o SGBD o
permitir. Caso o SGBD não o permita, indique quais os índices alternativos, se
existirem.
Solução:
a) select P.nomep, P.idade, P.gabinete
from Professor P
where P.sexo = ‘M’
and P.investigacao = ‘BD’
-- poucos profs têm esta especialidade
Unclustered hash index sobre Profesor<investigacao, sexo> ou
Unclustered hash index sobre Professor<investigacao> (melhor ainda, porque só
existem dois valores possíveis de sexo)
b) select D.*
from Departamento D, Professor P
where D.did = P.deptid
and P.idade > k1
and P.idade < k2
Número:_________________ Nome: _________________________________________
-- k1 e k2 são constantes inteiras
Clustered B+tree sobre Professor<idade, deptid> (=> index-only plan)
e unclustered hash index sobre Departamento<did>
c) select D.did, D.dnomed, P.nomep
from Departamento D, Professor P
where D.nummajors = k
and P.nummec = D.profresp
-- k é uma constante inteira
Unclustered hash index sobre Departamentot<num_majors>
d) select min(orcamento)
from Departamento
Clustered B+ tree index sobre Departamento<orcamento>
e) select P.*
from Professor P, Departamento D
where P.nmec = D.profresp
Unclustered B+ tree sobre Departamento<profresp> (=> index-only scan, se
suportado) e Hash index sobre Professor<nmec>
12
Número:_________________ Nome: _________________________________________
13