Processamento e Otimização de Consultas em Bancos de Dados. SGBD Parte 2. Prof. Sérgio Lifschitz. Departamento de Informática PUC-Rio - Brasil

Processamento e Otimização

de Consultas em Bancos de Dados

Prof. Sérgio Lifschitz

Departamento de Informática PUC-Rio - Brasil

SGBD – Parte 2

Idéia de um VLDB

• 500GB de dados ou muito mais

– VLDBs = terabytes (cartões de crédito, fast mail,…)

• 5K tabelas e índices + 2K views

• Tanto BD Produção como ODS

• Algumas tabelas com mais de 50M tuplas

• 10 a 20 consultas por minuto, 12K

usuários (200 simultâneos)

Processando Consultas

Parse Query Check de Semântica Query Rewrite Otimização do Plano de Acesso Geração de Código Relational Manager Run time executor

Etapas da Consulta

• Parse Query:

– verifica erros de sintaxe.

• Check de Semântica:

– verifica existência de tabelas, views e colunas, verifica tipos de dados, atributos.

• Query Rewrite:

Etapas da Consulta

• Otimização do Plano de Acesso:

– escolhe estratégia de acesso considerada mais eficiente.

• Geração de Código:

– chamadas às rotinas básicas do SGBD.

Componentes do SGBD

• Gerenciadores (Managers):

– RM - Relational Manager – DM - Data Manager – FM - File Manager – BM - Buffer Manager – RM - Recovery Manager – LgM - Log Manager – LkM - Lock Manager

Componentes do SGBD

DM

Predicados outros predicados indexáveis funções

RM

expressões predicados select max(salario) , avg(salario) * 2 from empregados where salario * 2 < 1000 and dept <> 600 and cargo = ‘as’

BM

solicita I/O

localiza páginas no buffer

FM

operações de I/O

open e close de arquivos

Componentes do SGBD

• Relational Manager - RM / Relational

Engine

– executa as funções relacionais do DBMS. – processa e otimiza SQL do usuário.

– seleção de dados via predicados. – sorts.

Componentes do SGBD

• Data Manager - DM

– processa páginas de dados e índices. – transforma pedidos do RM em getpages. – seleção de dados via predicados indexáveis.

Componentes do SGBD

• Buffer Manager - BM

– gerencia páginas de dados e índices na memória. – recebe pedidos do Data Manager:

• getpage , release page , set write pending

– verifica se páginas solicitadas já estão em memória – solicita operações de I/O ao File Manager.

– mantém filas:

• updated pages • em uso

• available pages

– LRU (least recently used) , MRU (most recently used) , . . .

Otimizador Relacional

• componente do RM responsável pela

geração de um

plano de acesso

• coração e inteligência do SGBD

Plano de Acesso

– conjunto de passos necessários para obter o resultado de um comando SQL

Plano de Acesso

• É especificado no plano de acesso:

– ordem com que as tabelas serão acessadas

– ordem das operações de seleção, projeção e junção – tipos de join usados

– índices utilizados

– ordenações necessárias

– tamanho, tipo e duração dos locks – tabelas intermediárias

Performance

• O tipo de acesso é determinado por:

– tipo de predicados.

– propriedade dos predicados. – volume de dados.

– estatísticas do catálogo.

Tipos de predicados

• simples: matricula ≥ 1000.

• composto: matricula > 1000 and sexo = ‘F’. • igual: usa o operador = e nenhum NOT. • range: >, < , ≥ , ≤ , like , between. • in-list: usa uma lista de valores. • not: contém o operador NOT. • subquery: inclui outro comando Select.

Propriedades dos predicados

• indexáveis: aplicados pelo DM antes do acesso às páginas de dados. • não indexáveis: aplicados pelo DM ou RDS

após acesso às páginas de dados. • estágio 1 (sargable): avaliados pelo DM.

• estágio 2 (non-sargable): avaliados pelo RM.

• termo booleano: não participa de OR.

Otimização e Execução de

Consultas

• Envolve

– Reescrita da consulta

– Determinação do melhor plano de acesso

O Otimizador

• Problema “difícil”: muitas alternativas

• O otimizador de consultas determina o

plano de acesso através de:

– Heurísticas (otimização por regras)

– Busca de plano de melhor custo (otimização por custo)

Estatísticas

• A otimização por custo demanda

estatísticas

• Para gerar estatísticas em uma tabela

cada SGBD disponibiliza um comando:

– Oracle: analyze; – DB2: runstats;

Principais Estatísticas

• Tabelas:

– cardinalidade. – número de páginas.

• Índices:

– número de níveis. – número de leaf pages.

– número de valores distintos das colunas da chave.

Principais Estatísticas

• Colunas:

– número de valores distintos. – “maior” e “menor” valor.

– valores mais freqüentes e percentuais de distribuição.

Fator de Filtro

• Fator de filtro de um predicado:

– Estimativa do percentual de linhas da tabelas que satisfazem tal predicado.

– 0 ≤ FF ≤ 1

FF → 0 poucas linhas FF → 1 todas as linhas

Fator de Filtro

• Distribuição uniforme:

– Tabela de Empregados com 10.000 linhas: – coluna Sexo: apenas 2 valores distintos. – estimativa supondo distribuição uniforme:

FF(M) = 0.5 FF(F) = 0.5 – M: 5.000 empregados (linhas) – F: 5.000 empregados (linhas)

Fator de Filtro

• Distribuição uniforme:

select * from empregados where sexo = ‘F’ FF(F) = 0.5

– Índice sobre a coluna Sexo: • não-clusterizado (provavelmente)

– Otimizador pode decidir não usar índice.

– Se a distribuição não for uniforme, o Otimizador pode ser enganado.

Fator de Filtro

• Distribuição não-uniforme:

– tabela de Empregados com 10.000 linhas: – coluna Sexo: distribuição não uniforme. – estatísticas de distribuição não uniforme:

M: 9.000 empregados F: 1.000 empregados – FF(F) = 0.1

Fator de Filtro

– coluna Município da tabela de empregados – se a distribuição for uniforme: FF = 0.085

– muitos valores distintos (3.106) e distribuição não uniforme.

– SGBD com distribuição dos N valores mais freqüentes. 1 - FF(rio de janeiro) = 0.201 2 - FF(salvador) = 0.079 : 10 - FF(recife) = 0.011 • FF(xique-xique) < 0.011

Fator de Filtro

• Vários predicados: AND

select * from empregados

where dept = ‘600’ and sexo = ‘m’ and cargo = ‘pgmr’

FF(sexo) = 0.9 FF(dept) = 0.2 FF(cargo) = 0.1

FF = 0.9 * 0.2 * 0.1 = 0.018

Fator de Filtro

• Vários predicados: OR

select * from empregados

where dept = ‘600’ or sexo = ‘m’

FF(sexo) = 0.9

FF(dept) = 0.2

FF = 0.9 + 0.2 - ( 0.9 * 0.2 ) = 1.10 - 0.18 = 0.92 – estimativa para a tabela resultado: 9200 linhas

Fator de Filtro

• Se não existem estatísticas disponíveis são assumidos defaults.

• DB2 (mainframe):

• cardinalidade: 10.000 linhas • páginas: 1.000 páginas • col = valor 1 / 25

• col like valor 1 / 10

Fator de Filtro

• “Enganando” o Otimizador: – Predicados correlacionados:

select * from empregados where cidade = ‘rio de janeiro’

OK: FF = 1 / 25 → R = 10.000 / 25 = 400 linhas

select * from empregados where cidade = ‘rio de janeiro’

and estado = ‘rj’

NÃO OK:FF = (1 / 25) * (1 / 25)

→R = 10.000 / 625 = 16 linhas

Métodos de Acesso e Planos

de Execução

Plano de Acesso

• Dada a consulta:

SELECT ender, datanasc FROM empregado

WHERE nome = ‘Gal Costa’ Qual seria o plano de acesso gerado pelo SGBD??

Planos de Execução

• É o resultado da otimização

• É especificado no plano de execução:

– Ordem de acesso às tabelas

– Ordem de operações de seleção, projeção e junção

– Índices utilizados – Tipos de junção

Planos de Execução

• Existem dois tipos básicos de operação:

– Métodos de Acesso

• varreduras seqüenciais e indexadas – Outras operações

• junções, uniões, eliminação de duplicatas

Varredura Seqüencial

• Leitura seqüencial de todas as páginas de

uma tabela

– Comumente chamada de full table scan – SGBDs costumam implementar sequential

prefetch (read ahead)

– Eficiente para consultar todas ou quase todas as linhas de uma tabela

Varredura Indexada

• Leitura dos dados através de índices

– Comumente chamada de indexed table scan – Vários índices possíveis: árvores B+, bitmaps,

árvores R

– Eficiente para obter subconjunto restrito dos dados

– Índices podem ser "clusterizados" ou "não-clusterizados"

Índices Não-clusterizados

Ordenação

• Operação básica para diversas outras

operações físicas

• Resolve a cláusula ORDER BY

• Se o conjunto a ser ordenado não cabe

em memória, esta operação pode ser

muito cara

Eliminição de Duplicatas e

Agregação

• Estas operações são implementadas em

geral através de uma ordenação (sort)

• Após a ordenação pelo critério de

agregação, o resultado é varrido e a

operação é computada

• Também é possível resolver este tipo de

operação através de técnicas de hash

União

• Podemos usar UNION ou UNION ALL em

consultas

– UNION ALL: realiza uma operação de concatenação entre os conjuntos (concatenate)

– UNION: realiza a concatenação (concatenate) seguida de eliminação de duplicatas (sort ou hash)

Interseção e Diferença

• Operações de conjunto semelhantes a

eliminação de duplicatas

• Em geral, são resolvidas com ordenações

(sort) seguidas da operação propriamente

dita

• Também é possível utilizar métodos de

hash

Junções

• Os algoritmos clássicos utilizados em

junções são:

– Loops Aninhados (Nested Loops Join) – Ordenação/Intercalação (Merge Join) – Hashing (Hash Join)

• Existem outros algoritmos:

– Híbrido (Hybrid hash join) – Em estrela (Star join)

Nested Loops

60 50 40 30 20 10 T1 Outer Table 70 60 50 20 20 05 70 T2 Inner Table

Nested Loops

• Eficiente quando:

– Outer table pequena ou filtrada

– Índice clusterizado para acessar inner table – Poucas páginas da inner table acessadas

Merge Join

60 50 40 30 20 10 Outer Table 70 60 50 20 20 05 70 Inner Table O rd en ad a O rd en ad a

Merge Join

• Eficiente quando:

– São acessadas muitas linhas das duas tabelas

– Não existem índices adequados

– Atributos de junção com muitos valores repetidos

Hash Join

60 50 40 30 20 10 Outer Table ₇₀ 60 50 20 20 05 70 Inner Table Build Phase f(chave) 10 20 50 30 40 60 Probe Phase

Hash Join

• Eficiente quando:

– Hash table é pequena, cabendo em memória – Atributo de junção da menor tabela é único – Não existem índices para a junção

Entendendo um Plano de Acesso

Considere a seguinte consulta SQL:

SELECT cgc, nome, cidade, estado

FROM revendedoras WHERE cgc not in

(SELECT G.cgc

FROM automoveis a, garagens g WHERE a.codigo = g.codigo and

a.pais = 'Franca') and cgc not in

(SELECT N.cgc

FROM automoveis a, negocios n WHERE a.codigo = n.codigo and

a.pais = 'Franca')

Explain Plan

• Normalmente o SGBD executa os SQLs sem informar aos usuários como eles são processados.

• Se desempenho ruim, torna-se necessário examinar como determinado comando está sendo processado. • Os gerenciadores comerciais fornecem um meio,

indicando passo a passo a lógica de execução, através de uma função:

– Oracle e DB2: EXPLAIN PLAN – SQL Server: SHOW PLAN

Explain Plan

• Oracle e DB2 armazenam as informações do Otimizador na tabela denominada PLAN_TABLE. • As colunas da plan_table normalmente indicam:

– custo do comando sql.

– cardinalidade da tabela resultado.

– as tabelas acessadas e a ordem de acesso.

– se o acesso é feito via índice(s) e quais índices utilizados. – tipo de join escolhido.

– sorts necessários. – locks adquiridos.

Oracle - Planos

• EXPLAIN PLAN + DBMS_XPLAN:

criar plan_table:

$ORACLE_HOME/rdbms/admin/utlxplan.sql explain plan for select * from dual;

Oracle - Planos

• SQL*Plus + set autotrace on

• EXPLAIN PLAN + DBMS_XPLAN

• OEM SQL Analyze

Oracle - Planos

EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR SELECT *

FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH';

SET LINESIZE 130 SET PAGESIZE 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC')); ---| Id ---| Operation ---| Name ---| ---| 0 ---| SELECT STATEMENT ---| ---| | 1 | NESTED LOOPS | | | 2 | TABLE ACCESS FULL | EMP | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT |

SQL Server – Planos

• Query Analyzer

– Query -> Display Estimated Execution Plan – Query -> Show Execution Plan

– SET SHOWPLAN_TEXT ON – SET SHOWPLAN_ALL

DB2 – Planos

• Para Visualizar

– Consultas às EXPLAIN TABLES – db2exfmt

– Interface Gráfica

• Centro de Comando ou

EXEMPLOS

Listar as vendas mensais da região 1 para os seguintes códigos de produtos: 1, 2, 3, 8, 9, 10.

SELECT cod_regiao, tipo_prod, mes, vendas FROM Estatisticas_Vendas

WHERE cod_regiao = 1 AND(

(tipo_prod BETWEEN 1 AND 3) OR

(tipo_prod BETWEEN 8 AND 10) );

EXEMPLOS

Temos o plano:

EXEMPLOS

SELECT *

FROM Automoveis

ORDER BY codigo, ano desc,

preco_tabela desc;

Chave primária = (codigo asc , ano asc).

EXEMPLOS

temos o plano

Ainda assim não foi usado o índice !!

O que ocorreria se o ORDER BY fosse solicitado em ordem ascendente para o campo ano?

order by... select *

EXEMPLOS

SELECT codigo, ano FROM Automoveis

WHERE pais ='Italia'

AND ano in ('95', '96', '98') AND fabricante =‘Fiat’;

Índice:

IX1(chave primária): codigo ASC, ano ASC,

Múltiplos índices:

IX2: pais ASC, ano ASC IX3: fabricante ASC

• Nesta consulta, o otimizador pode escolher acessar pelo índice IX2 para resolver o predicado os dois primeiros predicados e logo varrer os

rowids obtidos para testar o predicado “fabricante=‘Fiat’”.

• Ou usar o IX3 para resolver “fabricante=‘Fiat’”, e depois combinar as linhas recuperadas.

EXEMPLOS

SELECT *

FROM Automoveis

WHERE pais = 'Italia' AND ano = '95' UNION

SELECT *

FROM Automoveis

WHERE pais = 'Italia' AND ano = '96' UNION

SELECT *

FROM Automoveis

WHERE pais = 'Italia' AND ano = '98';

UNION ALL: elimina a necessidade da última operação de SORT.

EXEMPLOS – Base empresa

R _ 0 2 R _ 0 3 R _ 0 1 R _ 0 4 R _ 0 5 R _ 0 6 D E P A R T A M E N T O N U M N O M E ID E N T G E R D A T A IN I N U M B E R C H A R (2 5 ) N U M B E R D A T E < p k> D E P E N D E N T E ID E N T E M P N O M E S E X O D A T A N A S C P A R E N T E S C O N U M B E R C H A R (2 0 ) C H A R D A T E C H A R (1 0 ) < p k,fk> < p k> D E P L O C D E P N U M L O C A L N U M B E R C H A R (1 5 ) < p k,fk> < p k> E M P R E G A D O ID E N T N O M E S A L E N D S E X O D A T A N A S C D E P N U M S U P E R ID E N T N U M B E R C H A R (2 0 ) N U M B E R (8 ,2 ) C H A R (5 0 ) C H A R D A T E N U M B E R N U M B E R < p k> < fk> P R O J E T O N U M N O M E L O C A L D E P N U M N U M B E R C H A R (1 5 ) C H A R (1 5 ) N U M B E R < p k> < fk> T R A B A L H A N O ID E N T E M P P R O J N U M H R S N U M B E R N U M B E R N U M B E R < p k,fk1 > < p k,fk2 >

EXEMPLOS

Na consulta …

SELECT E.nome, D.nomedep as Departamento,

P.projlocal as Local, P.projnome as Projeto FROM (Departamento D INNER JOIN Empregado E

ON E.depnum =D.depnum)

INNER JOIN Projeto P ON P.depnum = D.depnum WHERE P.projnome = 'COZUMEL CZM';

EXEMPLOS

• ...

PROJETO PROJETO EMPREGADO DEPARTAMENTO Hash join

---As tabelas resultados já são menores