Performance de Consultas
Otimizador – É o programa que está preparado internamentamente pelo oracle para diagnosticar qual será a melhor proposta para se recuperar a informação. Seria ele o responsável por gerar o plano (caminho) para se buscar os dados das tabelas e indices. O Oracle nas versões mais recentes ( a partir 9iR2 se não me engano) se utiliza-se de custo como padrão e não recomenda usar regra em suas consultas.
Custo – É a melhor estimativa do numero de I/Os que devem ser feitos para a execução de uma consulta.
Estimativas – São empregadas técnicas para se gerar o melhor plano e são usadas como base as estimativas. Os tipos são: seletividade, cardinalidade e custo;
A cardinalidade é derivada da seletividade e o custo depende da cardinalidade.
Se as estatisticas estão atualizadas, serão empregadas na geração dos planos, se caso não estejam serão usadas valores padrão de estatisticas usando se o parametro para isso: OPTIMIZER_DYNAMIC_SAMPLING.
Seletividade – É uma proporção estimada de um conjunto de linhas retorndas a partir de um predicado (filtro) ou uma combinação de predicados.
Fórmula: (numero de linhas que satisfazem o filtro/total de numero de linhas – baseado em histogramas)
Ex:
select last_name from employees where last_name =’JOHN’;
A fórmula pode ser melhor entendida assim:
a) O número de linhas que satisfazem o filtro para o predicado é 2.
b) O número de linhas que precisam ser lidas para satisfazer o retorno é 200 baseando se em nomes distintos que existem na coluna last_name.
A seletividade será: 2/200 = 0,01 – alta seletividade – utilizará indices.
Os valores sao entre 0.0 e 1.0, se diz uma alta seletividade: baixo numero de linhas, baixa seletividade: numero alto de linhas.
– Um indice não será usado porque temos uma baixa seletividade. É melhor percorrer a tabela ao inves de percorrer varias linhas no indice.
– Um indice pode ser usado porque temos uma alta seletividade. O numero de linhas pequeno a ser retornado é mais provavel se usar o indice para localizar rapidamente estas linhas na tabela quando esta quantidade de linhas nao exceder 3% a 4% do total de linhas da tabela.
O cálculo da seletividade se base em duas fontes:
a) Estatísticas – se caso nao exista usuará exemplos dinamicos.
b) Histogramas – Analisa a distribuição das linhas, como a quantidade de informações distintas em uma coluna (ideal para indices BTREE e quando varios valores distintos melhor para BITMAP).
Cardinalidade – Expectativa do número de linhas a serem retornadas de uma tabela para uma operação em particular. Analisa o número de linhas distinas, o número total de linhas da tabela e a seletividade. É usado para determinar junções, filtros, agrupamentos e custo de ordenação.
Fórmula – seletividade/número total de linhas baseadas no histograma
Fórmula: (numero de linhas que satisfazem o filtro/número total de linhas baseadas no histograma)/número total de linhas da tabela
Custo – É o melhor estimativa que existe para saber qual seria o número de I/Os que se devem ser feitos para satisfazer o retorno de uma consulta. Uma unidade de custo equivale a leitura de uma leitura para um bloco em forma randômica.
São feitos os seguintes calculos para se conseguir a estimativa correta:
a) Estimativa de Tempo de leitura de blocos de forma randômica.
b) Estimativa de Tempo de leitura de blocos em leitura em lote.
c) Estimativa de Tempo de uso de CPU pelo processo para leitura de uma unidade de custo.
Geração de planos
Para se gerar planos, o melhor é utilizar da seguinte forma:
a) Sqldeveloper – da própria oracle – gratuito.
b) Linha de comandos no sqlplus.
C:UsersAndre>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 – Production on Qua Nov 17 09:41:49 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> alter user scott identified by tiger account unlock;
Usuário alterado.
SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = ‘badsql1’
3 FOR
4 SELECT ename FROM emp;
Explicado.
SQL> SELECT cardinality “Rows”,
2 lpad(‘ ‘,level-1)||operation||’ ‘||
3 options||’ ‘||object_name “Plan”
4 FROM PLAN_TABLE
5 CONNECT BY prior id = parent_id
6 AND prior statement_id = statement_id
7 START WITH id = 0
8 AND statement_id = ‘badsql1’
9 ORDER BY id;
Rows
———-
Plan
——————————————————————————–
14
SELECT STATEMENT
14
TABLE ACCESS FULL EMP
http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/ex_plan.htm
Uma consulta eficiente
Alta seletividade – Index scan – Número de retorno de linhas pequeno. Uso aconselhado de índices na pesquisa onde as tabelas são maiores que 4% do valor do total de numero de linhas retornadas. Aconselhado em sistemas comerciais ou OLTP.
Baixa seletividade – Full table scan – Número de retorno de linhas alto. Aconselhado a busca direta dos dados sem a utilização de indices. É melhor percorrer toda a tabela ao invés de usar o índice para buscar muitas linhas. Baseado em sistemas de Data Warehouse ou Bussiness Inteligence. Pode se melhorar a leitura sequencial dos registros na tabela através do aumento no valor do paramêtro: DB_FILE_MULTIBLOCK_READ_COUNT e se aplica também:
- Tabelas pequenas
- Alto grau de paralelismo
- Nenhum indice atende ao filtro
- Nenhum filtro na consulta ou baixa seletividade
- Uso de hint – FULL (NOMETABELA)
Custo menor – Sempre que possivel analise o custo e veja as referencias para que se construa bases eficazes para a analise do otimizador.
Índices
Quando fazemos a consulta geralmente no plano, temos retornos do uso do índice e para interpreta-los:
Lembre-se INDICE BTREE significa indice balanceado.
INDEX UNIQUE SCAN – Os predicados são baseados em filtros onde existem indices de chaves unicas. É retornado um unico ROWID.
INDEX RANGE SCAN – São baseadas em consultas que necessitam varrer o índice para buscar os valores, geralmente são utilizadas quando combinamos predicados um filtro utilizando se do índice e outro em uma coluna que não está presente no índice.
INDEX FULL SCAN – Usado quando usamos no predicado o IS NOT NULL para filtrar a coluna indexada. Geralmente a consulta contém mais de dois predicados, sendo um baseado na coluna com o índice e outra não.
INDEX FAST FULL SCAN – Usado quando o otimizador irá buscar todos os dados da tabela, fazendo um full table scan isso desde que o predicado faça um filtro usando se a clausula IS NOT NULL em uma das colunas indexadas. É uma alternativa ao full table scan visto que forçamos a busca dos valores no índice na tentativa de fazer o retorno mais rapido das linhas. Para “forçar” o uso dessa técnica pode ser necessário adicionar o hint na consulta – INDEX_FFS.
INDEX SKIP SCAN – É a forma que o otimizador na leitura do indice, descarta aquele galho quando as informações do galho não satisfazem o filtro do predicado da consulta. Geralmente presente em consultas do tipo BETWEEN ou onde se encontram predicados usando colunas indexadas com os simbolos <, >, <=, >=.
INDEX JOIN SCAN – Faz um HASH JOIN, une vários indices (partes) para conseguir resolver a consulta, desde que estes indices apareçam nos predicados.
AND-EQUAL – Essá operação permite ao sistema fazer um merge entre indices combinando os diversos predicados que são usados. Em teoria poderia atingir até a cinco indices baseados em uma unica coluna e usar estes indices para fazer as buscas para os resultados. A ideia é encontrar os ROWIDs. Para forçar o uso dos vários indices, utilize o hint – AND_EQUAL.
Outras considerações
- O retorno de número de registros é facilitado quando a coluna a ser contabilizada é NOT NULL.
- Indice IOT é ideal quando em sistemas que necessitam buscar os valores do registro e fazem a busca pela chave primária geralmente. A IOT armazena no indice as informacoes das colunas restantes. A diferença é que em tabelas do tipo head as buscas precisam ir ao indices e depois no segmento da tabela. Na IOT isso é feito diretamente no segmento do indice.
- Indices do tipo BITMAP são adequados para acelerar o retorno das funções de agregação. como soma, contar, max etc. O predicado IS NOT NULL pode ser beneficiado pelo indice bitmap, ja que este indice, diferente dos demais armazena valores nulos. Também indicados para colunas com grande número de valores distintos: Cores, Sexo, Cidades, Estados, Países etc.
- Indices compostos – podem ter suas seletividades baixas quando sozinhos, mas combinados a seletividade aumenta, reduzindo o numero de registros lidos
Considerações ao analisar o plano de execução – BITMAP operations
As operações que no plano retornam a resposta como BITMAP nao diz, necessariamente que o indice percorrido é bitmap. O otimizador pode transformar em alguns casos o indice Btree em Bitmap.
•BITMAP CONVERSION FROM ROWID: Indice B-tree convertido em Bitmap e depois convertido de novo em rowid após o final da comparação.
•BITMAP MERGE: Pode ser feito o merge entre varios indices bitmaps para se fazer buscas em atributos de indices diferentes.
•BITMAP MINUS: è uma operação onde, se inverter as posições em outro mapa de bits pode se beneficiar a busca. Geralemente esta presentes em operações como : LAST_NAME=’Andre’ and LAST_NAME<>’LUIZ’.
•BITMAP KEY ITERATION: Percorre a tabela encontrando as linhas e encontrando a correspondencia no bitmap a partir de um indice bitmap.Essa operação é feita para criar a operação: BITMAP MERGE.
•BITMAP JOIN INDEX: Eficiente quando usado para facilitar as operações de JOIN entre os predicados.Faz com que a quantidade de dados retornados seja menor. Monta o mapa de bits com a chave primaria e no mesmo bloco todas as FKs facilitando o retorno das operações de JOIN.
