Upload
internet
View
109
Download
2
Embed Size (px)
Citation preview
Otimização de Consultas em Data Warehouse (DW)
Cláudio E. C. Campelohttp://claudiocampelo.com
Universidade Federal de Campina GrandeMestrado em Ciência da Computação
Disciplina Banco de Dados Multidimensionais
Campina Grande – PB , Setembro de 2006
Roteiro
• Motivação;
• Background;
• Materialized Views;
• Query ReWrite;
• Aproximações;
Motivação
• Tempo de Resposta Aceitável:
Cinco Segundos!
• Grande quantidade de dados;• Urgência da Informação;• Informações de nível gerencial –
agregações;
Background
• Modelo Star (Estrela): existe uma tabela dominante no centro, chamada tabela de fatos, com múltiplas junções conectando-a a outras tabelas, sendo estas chamadas de tabelas de dimensão.
• Modelo Snow Flake (Floco de Neve): consiste em uma extensão do modelo Estrela onde cada uma das "pontas da estrela" passa a ser o centro de outras estrelas.– Não aconselhável;– Desnormalização (~3FN);
Background
• OLTP X OLAP
Background
• Índices BITMAP– Um vetor de bits por valor de atributo;– O tamanho do BITMAP é o número de
registros da relação indexada;– Por exemplo, dia da semana teria 7 vetores;– Podemos associar cada bit a faixas de
valores, por exemplo 0 to 20.000 reais, 20.000,01 a 35.000 reais;
Background
• Índices BITMAP– Vantagem: Maior facilidade para manipulação
interna de vários BITMAPs para responder uma consulta;
– Eficiente em Queries com muitos predicados;– Comando CREATE BITMAP INDEX
Background
• Query com Índices BITMAP
Materialized Views (MV)
• Pode contar agregações e junções;
• O otimizador decide se usa a MV;
• Alterações nas consultas são transparentes ao usuário/desenvolvedor -Não é necessário alterar a consulta SQL ou mesmo a aplicação;
• Aplicações e ferramentas de query ad hoc podem se beneficiar.
Materialized Views (MV)
• Interessante quando a quantidade de dados é bem maior que o resultado;
• Pode ser útil também quando envolve junções e projeções custosas!
Materialized Views (MV)
• Quais visões criar para ajudar o otimizador ?
• Como prover atualizações eficientes nas MV se os dados base mudam?
Dica: Na dúvida se seria necessário, o SQL Access Advisor (Oracle 10g) pode ajudar fazendo uma análise a partir da carga de trabalho (workload);
Query ReWrite
• Vários tipos possíveis;• A mais óbvia e simples: Quando os textos são iguais;• É necessário aproveitar a mesma MV para várias
consultas!
Dica: O Explain Plan ajuda a descobrir, através do plano de execução da consulta, os meios de acesso que o Oracle está utilizando para acessar as tabelas do banco de dados.
A seguir, mostraremos o resultado exibido pelo Explain Plan, para todas as consultas exibidas como exemplo.
Query ReWrite
• Transparência:
Nosso Exemplo
• Esquema em estrela:
Agregate Computation
• Exemplo 1: MV que computa a soma e o total dos preços das compras por mês, por produto;
• Observar a cláusula ENABLE QUERY REWRITE;
Agregate Computation
• Exemplo 1:
CREATE MATERIALIZED VIEW monthly_sales_mvENABLE QUERY REWRITEASSELECT t.month, p.product_id, SUM(ps.purchase_price) as
sum_of_sales, COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_idGROUP BY t.month, p.product_id;uto;Observar a cláusula ENABLE QUERY REWRITE;
Agregate Computation
• Exemplo 2: Computa a média dos preços das compras por mês, por produto;
SELECT t.month, p.product_id, AVG(ps.purchase_price) as avg_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.month, p.product_id;
Agregate Computation
• O otimizador pode utilizar a MV monthly_sales_mv para calcular o AVG !
• Explain Plan do Ex. 2:
JoinBack
• Exemplo 3: Total de compras por mês, por categoria do produto
SELECT t.month, p.category, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.month, p.category;
JoinBack
• A categoria não está na MV;• A coluna product_id, chave primária de
PRODUCT, está na MV;• O otimizador faz a Junção da MV com
PRODUTO para obter a categoria;
• Explain Plan do Ex. 3:
Query ReWrite Usando Dimensão
• Um DW típico que possui hierarquias nas dimensões;
• Por exemplo, dia → mês → ano…• Oracle permite criar objetos dimensões
com o comando CREATE DIMENSION;• Este objeto não consome espaço além
dos metadados;• Oracle assume que o DBA sabe o que
está fazendo!
Query ReWrite Usando Dimensão
• Exemplo de Dimensão Oracle:
CREATE DIMENSION time_dimLEVEL time_key IS time.time_keyLEVEL month IS time.monthLEVEL quarter IS time.quarterLEVEL year IS time.yearHIERARCHY calendar_rollup ( time_key CHILD OF month CHILD OF
quarter CHILD OF year)ATTRIBUTE time_key determines (day_of_week, holiday)ATTRIBUTE month determines (month_name);
Query ReWrite Usando Dimensão
• Exemplo 4: Compras por ano
SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.year, p.category;
Query ReWrite Usando Dimensão
• O otimizador pode utilizar a MV monthly_sales_mv, uma vez que possui informações sobre a hierarquia;
• Utiliza ainda a técnica anterior (JoinBack) para obter os valores da coluna year a partir de month na MV;
• Explain Plando Ex. 4:
• Dica: Se o resultado do ReWrite não for o esperado, use a procedure DBMS_MVIEW .EXPLAIN_REWRITE (a partir do Oracle 9i) para ajudar a diagnosticar o problema
Query ReWrite Usando Dimensão
• A cláusula ATTRIBUTE determina uma ralação um-para-um;
• É possível determinar day_of_week a partir de time_key.;
• Por exemplo (ver Exemplo 5), calculamos a soma das compras para "January" em cada ano.
• Ainda é possível usar a MV monthly_sales_mv !
Query ReWrite Usando Dimensão
• Exemplo 5:
SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
t.month_name = 'January‘
• Note que no WHERE há atributos que não estão na MV!
Query ReWrite Usando Dimensão
• Explain Plan do Ex. 5:
Filtered Data
• Até agora, vimos exemplos contendo uma MV com todos os dados da tabela PURCHASE;
• A partir do Oracle 9i, é possível utilizar reescrever a consulta a partir de uma MV contendo apenas um sobconjunto dos dados!
Filtered Data
• Nossa MV do Exemplo 1 foi modificada para conter apenas os dados de 1997 a 2002:
CREATE MATERIALIZED VIEW five_yr_monthly_sales_mvENABLE QUERY REWRITE
ASSELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales, COUNT (ps.purchase_price) as total_salesFROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year between 1997 and 2002GROUP BY t.month, p.product_id;
Filtered Data
• Agora, o otimizador utiliza esta nova MV caso a consulta seja referente a dados nesse subconjunto, por exemplo a consulta do Exemplo 6 (soma das compras em 2000):
SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
t.year = 2000
GROUP BY t.month, p.product_id;
Filtered Data
• No Oracle 9i, se a condição da consulta não está na MV, ela não é utilizada;
• No 10g, ele fornece uma solução híbrida;
• Na consulta do Exemplo 7, deseja-se compras mensais de 2000 a 2003;
• Neste exemplo, o Oracle 10g usa a MV para os dados de 2000 a 2002;
Filtered Data
• Explain Plan do Ex. 6:
Filtered Data
• Exemplo 7: Compras mensais de 2000 a 2003
SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
t.year BETWEEN 2000 and 2003
Filtered Data
• Explain Plan do Ex. 7:
Stale Materialized Views
• O que acontece quando a tabela base é alterada?
• A Query ReWrite continua usando a MV?
• Depende do parâmetro QUERY_REWRITE_INTEGRITY
Stale Materialized Views
• Valores possíveis para QUERY_REWRITE_INTEGRITY:
– STALE_TOLERATED: Usa a MV mesmo quando a tabela base é alterada;
– TRUSTED: Assume que a MV fornecida não é “Staled”. Usado também para declarações em dimensões e constraints;
– ENFORCED (Default): Garante os mesmos resultados sempre, ou seja, não usa MV “Staled” ou Relacionamentos TRUSTED;
Partition Change Tracking
• Oracle9i introduziu o Partition Change Tracking (PCT);
• Sabe-se qual parte da MV corrsponde a parte alterada da tabela base;
• MV pode continua sendo usada para outras partes!
Partition Change Tracking
• Exemplo 8: Nossa MV foi alterada, adicionando DBMS_MVIEW.PMARKER
CREATE MATERIALIZED VIEW monthly_sales_pct_mvENABLE QUERY REWRITEASSELECT DBMS_MVIEW.PMARKER(ps.rowid) pm, t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales, COUNT (ps.purchase_price) as total_salesFROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_idGROUP BY DBMS_MVIEW.PMARKER(ps.rowid), t.month, p.product_id;
Partition Change Tracking
• Com a tabela PURCHASES particionada pelo time_key, ao adicionar uma nova partição para Abril de 2003, não afetará a consulta do Exemplo 9 (para março de 2002):
SELECT t.month, p.product_id, SUM(ps.purchase_price)FROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND ps.time_key >= TO_DATE('01-03-2002', 'DD-MM-YYYY') AND
ps.time_key < TO_DATE('01-04-2002', 'DD-MM-YYYY') GROUP BY t.month, p.product_id;
• Oracle 10g adiciona soluções híbridas para esta abordagem!
Partition Change Tracking
• Explain Plan do Ex. 9:
Query Rewrite com várias MVs
• Soluções híbridas do Oracle fazem uso não apenas de MV + Tabela Base, mas também de várias MV;
• Exemplo: Suponha que temos diversas MVs monthly_sales_1990-1994, monthly_sales_1995_to_2000, monthly_sales_2001_to_2005, etc.
Query Rewrite com várias MVs
• Exemplo 10: Usa-se as 3 MVs
SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales,
FROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year between 1993 and 2003GROUP BY t.month, p.product_id;
Query Rewrite com várias MVs
• Explain Plan do Ex. 10:
Query Rewrite com várias MVs
• Exemplo 11: Usa-se monthly_sales_1990_to_1994 e monthly_sales_1995_to_2000 e obt’’em os dados de 1989 a partir da tabela base
SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases psWHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year between 1989 and 1999GROUP BY t.month, p.product_id;
Query Rewrite com várias MVs
• Explain Plan do Ex. 11:
Aproximações
• Utilização de amostras estatísticas dos dados;
• Técnicas de aproximação podem promover resultados interessantes;
• Atributos cuja adição de novos valores não alteram os agregados (Média Salarial);
Aproximações
• Suponha R sendo uma tabela de fatos e as demais de dimensão;
• Colhendo uma amostra R_ de R e então fazendo joins baseados em R_ -> S _, T _;
• Se a consulta envolve R, S, T então a query pode retornar bons resultados utilizando R _, S _, T _.
Aproximações
Aproximações
Aproximações
Conclusões
• Muitas possibilidades de melhorias;
• Responsabilidades ao DBA!
• Ferramentas são esseciais;
• Novas versões do Oracle têm adicionado melhorias consideráveis;
Bibliografia Consultada
• Oracle 9i Data Warehousing Guide. Junho/2005.• Oracle OLAP Application Developer's Guide 10g Release 2.
Agosto/2005.• Oracle Database Performance Tuning Guide 10g Release 2.
Junho/2005.• Hillson, Susan et Al. Improve Results with Query Rewrite. Oracle
Magazine Setembro/Outubro, 2003.• Shasha, Dennis e Bonnet, Philippe. Data Warehouse Tuning:
What’s Different About Data Warehouses. Dbazine, Abril/2005. A partir de Database Tuning: Principles, Experiments, and Troubleshooting Techniques, Morgan Kaufmann Publishers, 2003.