(Parte 1 de 3)

Dicas para otimização de consultas

Índice

Introdução 3

1. Método de Acesso 4

1.1 Full Table Scan 4

1.2 Table Access by Rowid 4

1.3 Index Scan 5

2. Tipos de Join 8

2.1 Nested Loops 8

2.2 Sort-Merge 9

2.3 Hash Join 10

2.5 Star Join 11

2.6 Star transformation 12

3. Hints 14

3.1 Tipo de Otimizador 14

3.2 Subquery / View 14

3.3 Acesso 15

3.4 Join 16

3.5 Parallel Query (PQ) 17

3.7 Variados 17

4. Dicas 18

4.1 Evitar função em coluna indexada 19

4.2 Join com colunas do mesmo tipo 20

4.3 Coletar as estatísticas com regularidade 22

4.4 Sql Trace 24

Introdução

Este documento tem o objetivo de auxiliar os desenvolvedores e dbas na otimização de consultas.

Inicialmente revisaremos alguns pontos importantes: os métodos de acesso, os algoritmos usados para realização do join e os hints usados para modificar o plano de execução das consultas.

Em seguida veremos algumas dicas para se aproveitar o máximo da eficiência deste banco de dados.

1. Método de Acesso

Representa o tipo de acesso para obter os dados de uma determinada tabela. O Oracle possui os seguintes métodos:

1.1 Full Table Scan

  • Realiza a leitura direta da tabela para determinar os registros que satisfazem a cláusula Where

  • Permite o uso do multiblock I/O (leitura de vários blocos seqüenciais com um simples I/O)

  • Permite a paralelizar ção

  • Nem sempre representa um método de acesso ruim

Representação no explain plan:

OPERATION OPTIONS OBJECT_NAME

------------------------------ -------------------- ---------------

0 SELECT STATEMENT .

1 SORT AGGREGATE .

2 TABLE ACCESS FULL TESTE.TAB_LOC

1.2 Table Access by Rowid

  • Realiza o acesso a tabela pelo Rowid

  • É a maneira mais rápida de acessar um registro

Representação no explain plan:

OPERATION OPTIONS OBJECT_NAME

------------------------------ -------------------- -------------------

0 SELECT STATEMENT .

1 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC

2 INDEX RANGE SCAN TESTE.TAB_LOC_DATA

1.3 Index Scan

  • Realiza o acesso aos dados através de um índice

  • A maioria é do tipo b*-tree (padrão)

Estrutura interna da B*-tree

  • Possui os seguintes tipos:

Unique Scan: é usado quando a tabela possui uma chave única ou primária. Retorna apenas 1 rowid.

OPERATION OPTIONS OBJECT_NAME

------------------------- -------------------- -------------------

0 SELECT STATEMENT .

1 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC

2 INDEX UNIQUE SCAN TESTE.TAB_LOC_PK

Range Scan: é usado para obter um determinado intervalo de dados. Retorna 0 ou mais rowids.

OPERATION OPTIONS OBJECT_NAME

------------------------- -------------------- --------------------

0 SELECT STATEMENT .

1 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC

2 INDEX RANGE SCAN TESTE.TAB_LOC_DATA

Full Scan: é usado quando a query usa pelo menos uma coluna do índice. Também é usado quando a consulta não possui predicado e se deseja evitar a operação de sort.

Fast Full Scan: é um Full Table Scan alternativo usado quando o índice possui todas colunas necessárias para a query. Para ler todos dados do índice, é mais rápido que o Index Scan por possibilitar o Multiblock I/O e a paralelização.

Obs: não pode ser usado para evitar a operação de sort (os dados não são retornados de forma ordenada).

OPERATION OPTIONS OBJECT_NAME

------------------------- -------------------- -------------------

0 SELECT STATEMENT .

1 INDEX FAST FULL SCAN TESTE.TAB_LOC_DATA

Index Join: é um hash join com alguns índices da tabela. Juntos obtêm todas colunas referenciadas na query.

OPERATION OPTIONS OBJECT_NAME

------------------------- ----------------- -----------------------

0 SELECT STATEMENT .

1 VIEW TESTE.index$_join$_001

2 HASH JOIN .

3 INDEX FAST FULL SCAN TESTE.TAB_LOC_DATA

4 INDEX FAST FULL SCAN TESTE.TAB_LOC_PK

Bitmap: utiliza um bitmap como chave para cada valor distinto da coluna e uma função de mapeamento que converte cada bit para um rowid. Se a coluna indexada tiver baixa cardinalidade, são rápidos e ocupam pouco espaço.

Obs: uma coluna é considerada de baixa cardinalidade se o número de valores distintos for menor ou igual a 1% do total de registros.

OPERATION OPTIONS OBJECT_NAME

------------------------- -------------------- --------------------

0 SELECT STATEMENT .

1 TABLE ACCESS BY INDEX ROWID TESTE.VENDAS

2 BITMAP CONVERSION TO ROWIDS .

3 BITMAP INDEX SINGLE VALUE TESTE.VENDAS_TIPO_BM

Exemplo: tabela Customer com 6 registros

Índice Bitmap sobre a coluna Region

Consulta de clientes casados e da região central e oeste.

SELECT COUNT(*) FROM customer

WHERE STATUS = 'married'

AND REGION IN ('central','west');

2. Tipos de Join

O join é uma operação que permite combinar o resultado de duas ou mais tabelas baseando se nos valores das colunas em comum. O Oracle utiliza os seguintes algoritmos

2.1 Nested Loops

  • O otimizador escolhe uma tabela para ser a outer table (driving table) e outra para ser a inner table

  • Para cada registro da outer table, o Oracle acha todos registros da inner table que satisfazem o join condition

  • O Oracle combina os dados de cada par de registros que satisfazem o join condition e então retorna os registros resultantes

select *

from tab_loc l,

tab_loc_peq p

where l.codigo = p.codigo;

OPERATION OPTIONS OBJECT_NAME

------------------------- -------------------- ------------------

0 SELECT STATEMENT .

1 NESTED LOOPS .

2 TABLE ACCESS FULL TESTE.TAB_LOC_PEQ

3 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC

4 INDEX UNIQUE SCAN TESTE.TAB_LOC_PK

2.2 Sort-Merge

  • Os registros de cada tabela são ordenados pelas colunas do join condition

  • É feito um merge das duas ordenações e os registros que satisfizerem o join condition são retornados

select /*+ use_merge(l p) */ *

from tab_loc l,

tab_loc_peq p

where l.codigo = p.codigo;

OPERATION OPTIONS OBJECT_NAME

------------------------- -------------------- ----------------------

0 SELECT STATEMENT .

1 MERGE JOIN .

2 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC_PEQ

3 INDEX FULL SCAN TESTE.TAB_LOC_PEQ_PK

4 SORT JOIN .

5 TABLE ACCESS FULL TESTE.TAB_LOC

Obs: a etapa 2 não precisou realizar o sort porque o range scan já retornou os dados ordenados.

2.3 Hash Join

  • O Oracle escolhe uma tabela menor para construir a tabela hash e o maior para verificar a tabela hash

  • Geralmente o Hash Join é mais rápido que o Sort-Merge

select /*+ use_hash(l) */ *

from tab_loc l,

tab_loc_peq p

where l.codigo = p.codigo;

OPERATION OPTIONS OBJECT_NAME

------------------------- -------------------- --------------------

0 SELECT STATEMENT .

1 HASH JOIN .

2 TABLE ACCESS FULL TESTE.TAB_LOC_PEQ

3 TABLE ACCESS FULL TESTE.TAB_LOC

2.5 Star Join

  • Join realizado entre uma tabela fato e algumas dimensões (tabelas lookup com informações dos atributos da tabela fato)

  • Geralmente é usado quando o schema tem tabelas fato grandes e dimensões bem pequenas

  • Cada tabela lookup possui um join com a tabela fato (PK_Lookup = FK_Fato_Lookup), mas as tabelas lookups não têm join entre si.

  • Os joins são realizados nas seguintes etapas:

    1. produto cartesiano entre as dimensões

    2. join com a tabela fato usando um índice concatenado por nested loops

Exemplo de um star join

  • Não confunfir o start join com o star transformation

2.6 Star transformation

  • Transformação de uma consulta realizada entre uma tabela fato e algumas dimensões

  • É realizado apenas se houver redução no custo

  • Não há necessidade de criar índices concatenados para atender a combinação de tabelas referenciadas, pois combina índices bitmap da tabela fato

  • Pré-requisitos

- Deve existir um índice bitmap para cada FK

- STAR_TRANSFORMATION_ENABLED = TRUE

- CBO deve estar sendo usado

  • A consulta é realizada da em 2 fases:

  1. O Oracle transforma a consulta e utiliza os índices bitmap das colunas FKs da tabela fato para obter o result set (conjunto exato dos registros necessários para avaliar a consulta). Em seguida os registros da tabela fato são obtidos.

  2. O join entre a tabela fato e as dimensões é realizado. Geralmente o hash join é o mais eficiente.

Exemplo:

Comando original

Tabela fato: sales

Dimensões : store, time e product

SELECT store.sales_district,

time.fiscal_period,

SUM(sales.dollar_sales) revenue,

SUM(dollar_sales) - SUM(dollar_cost) income

FROM sales, store, time, product

WHERE sales.store_key = store.store_key

AND sales.time_key = time.time_key

AND sales.product_key = product.product_key

AND time.fiscal_period IN ('3Q95', '4Q95', '1Q96')

AND product.department = 'Grocery'

AND store.sales_district IN ('San Francisco', 'Los Angeles')

GROUP BY store.sales_district, time.fiscal_period;

Transformação

1ª parte: obtenção do result set

SELECT ...

FROM sales

WHERE store_key IN (SELECT store_key FROM store

WHERE sales_district IN ('WEST','SOUTHWEST'))

AND time_key IN (SELECT time_key FROM time

WHERE quarter IN ('3Q96', '4Q96', '1Q97'))

AND product_key IN (SELECT product_key FROM product

WHERE department = 'GROCERY');

2ª parte: join entre o result set e as dimensões

Plano de execução

SELECT STATEMENT

HASH JOIN

HASH JOIN

HASH JOIN

TABLE ACCESS SALES BY INDEX ROWID

BITMAP CONVERSION TO ROWIDS

BITMAP AND

BITMAP MERGE

BITMAP KEY ITERATION

TABLE ACCESS STORE FULL

BITMAP INDEX SALES_STORE_KEY RANGE SCAN

BITMAP MERGE

BITMAP KEY ITERATION

TABLE ACCESS TIME FULL

BITMAP INDEX SALES_TIME_KEY RANGE SCAN

BITMAP MERGE

BITMAP KEY ITERATION

TABLE ACCESS PRODUCTS FULL

BITMAP INDEX SALES_PRODUCT_KEY RANGE SCAN

TABLE ACCESS TIME FULL

TABLE ACCESS PRODUCTS FULL

TABLE ACCESS STORE FULL

3. Hints

Hint é um comentário padronizado que se inclui no meio do comando para modificar o plano de execução. Geralmente ele é utilizado nos casos em que o Oracle não consegue definir uma boa estratégia.

Sintaxe:

select /*+ Hint... */

colunas ...

from tabelas ...

Abaixo segue a listagem dos hints para consultas:

3.1 Tipo de Otimizador

All_rows – força o cost-based optimizer(CBO) a otimizar o comando para retornar todos registros com o menor tempo possível (maior throughput)

First_rows – força o CBO a otimizar o comando para obter o melhor tempo de resposta

Rule – força a utilização do rule-based optimizer(RBO)

Choose – deixa para o Oracle optar por CBO ou RBO. Se pelo menos uma das tabelas possuir estatísticas coletadas então CBO é optado, senão é considerado o RBO.

3.2 Subquery / View

Push_Subq – executa as subqueries antes. Normalmente são executadas por último.

Exemplo

select /*+ push_subq */

count(*)

from tabela_teste

where codigo in (select codigo

from tabela_teste2

(Parte 1 de 3)

Comentários