Para compreender verdadeiramente a importância de UPDATE STATISTICS, você precisa entender exatamente o que acontece quando uma consulta SQL é colocada pelo usuário para execução. Cada consulta SQL que é inserido deve ser analisado, otimizado e executado.
O otimizador é o componente que prepara um plano de consulta. Um plano de consulta ideal e aquele que consulta a execução da minha query - ou seja, ele determina a forma mais eficiente para buscar os dados. Para isso, ela faz uso de um conjunto de dados estatísticos, no entanto, estes dados estatísticos não pode ser assumida para ser exato. A precisão dos dados depende de um grande número de fatores, como o tipo de algoritmo de amostragem utilizado, o número de amostras colhidas, e a distorção dos dados.
O otimizador de consulta não recalcula automaticamente do perfil para as tabelas. Em alguns casos, reunindo as estatísticas pode levar mais tempo do que executar a consulta. Para garantir que o otimizador escolhe um plano de consulta que melhor reflete o estado atual de suas tabelas, execute UPDATE STATISTICS em intervalos regulares.
Como trabalha o UPDATE STATISTICS
O UPDATE STATISTICS deve ser executada após uma carga inicial dos dados e criação de índices. Ele também deve ser executado após quaisquer mudanças significativas nas tabelas do banco de dados, incluindo qualquer inserção em larga escala, ou operações de atualização ou excluir. Se você não implementar UPDATE STATISTICS, o otimizador tem dados imprecisos para determinar os melhores caminhos de acesso aos seus dados.
A base do otimizador de consulta estima o custos sobre o número de linhas a serem recuperadas de cada tabela. Por sua vez, a estimativa do número de linhas é baseado na seletividade de cada expressão condicional, que é utilizado na cláusula WHERE. Um filtro é uma expressão condicional, que é usado para selecionar as linhas. A seletividade é um valor entre 0 e 1 que indica a porcentagem de linhas dentro da tabela que o filtro pode passar. Um filtro seletivo, que passa algumas linhas, tem seletividade para 0, e um filtro que passa quase todas as linhas tem seletividade 1.
O otimizador pode usar as distribuições de dados para calcular a seletividade para os filtros em uma consulta. No entanto, na ausência de distribuição de dados, o servidor de banco de dados calcula a seletividade em relação aos filtros de diferentes tipos com base em índices de tabela. A correção de estimar a seletividade altamente influencia o custo de cada plano de execução. Assim, a precisão de obter um plano ideal é totalmente dependente da informação estatística atual dos objetos de banco de dados envolvidos na consulta.
Cada vez que a consulta UPDATE STATISTICS é executado, as seguintes tabelas de catálogo do sistema são atualizadas. Os itens listados em cada ponto devem colunas em tabelas.
SYSTABLES
+-------------+--------------------------------------------------------------------+
| nindexes | Número de índices na tabela; |
+-------------+--------------------------------------------------------------------+
| nrows | Número de linhas na tabela; |
+-------------+--------------------------------------------------------------------+
| npused | Número de páginas usadas para armazenar "nrows" na tabela; |
+-------------+--------------------------------------------------------------------+
SYSCOLUMNS
+-------------+--------------------------------------------------------------------+
| colmin | Segundo valor mínimo da coluna |
+-------------+--------------------------------------------------------------------+
| Colmax | Segundo valor máximo da coluna |
+-------------+--------------------------------------------------------------------+
SYSINDEXES e SYSFRAGMENTS
+-------------+--------------------------------------------------------------------+
| levels | Número de níveis na B-Tree |
+-------------+--------------------------------------------------------------------+
| leaves | Número de leaves contidas na B-tree |
+-------------+--------------------------------------------------------------------+
| nunique | Número de valores distintos |
+-------------+--------------------------------------------------------------------+
| clust | Clusterdness em relativo a tabela; |
+-------------+--------------------------------------------------------------------+
SYSDISTRIB
- Linhas que tenham informações sobre como os valores das tabelas e dados do usuário são distribuídos. (Então, todas as colunas são afetadas)
- Também mantém a resolução e nível de confiança da distribuição.
Modos do UPDATE STATISTICS
A três modos de atualização, os dados estatísticos são low (baixa), medium (média) e high (alta).
O modo de low preenche apenas os valores estatísticos escalares (ou seja, não existem indicações de distribuição) de uma tabela. Todas as informações, como os níveis de índice B-tree, número de páginas consumidas pela tabela, número de valores distintos de uma coluna são armazenados.
No modo medium, além de estatísticas modo de low, a declaração classifica um conjunto de amostras de valores de coluna e preenche os dados de distribuição na tabela do catálogo do sistema sysdistrib que representa esta amostra. De modo geral, tem uma margem de confiança no intervalo de 85% a 99%.
No modo de high, além de estatísticas modo de low, a declaração classifica todos os valores da coluna e preenche a tabela com informações sysdistrib distribuição exata em todos os valores da tabela no momento da execução. Embora possa parecer uma boa ideia usar sempre o UPDATE STATISTICS no modo de high, isso não é realmente o caso. O modo de high usa muitas box para alcançar maior precisão devido à sua baixa resolução de 0,5. Portanto, ele consome uma grande quantidade de espaço em disco, especialmente quando a tabela é muito grande. Além disso, é bastante pesado executar o UPDATE STATISTICS no sistema no modo high em produção que funciona continuamente. Além disso, o modo de high nem sempre garante uma estimativa perfeita uma vez que nenhum plano de execução é garantia de ser 100% melhor desde que o plano se baseia em uma estimativa.
Resolução e Confiança
Dois termos importantes para entender em relação ao update statistic que são resolução e confiança. A resolução é um número entre 0.005 e 10. Confiança é uma medida de equivalência entre as estimativas obtidas e os valores reais obtidos. É expressa como um valor entre 0,80 e 0,99. O ideal é que ele é necessário para a consulta para ter um valor alto confiança. A resolução padrão para o modo de alta é de 0,5 e, para o modo médio, é 2,5. A confiança padrão para o modo de high é de 0,99. Encontra-se entre 0,85 e 0,99 para o modo medium.
Histograma e o UPDATE STATISTICS
Informix utiliza histogramas para armazenar a informação de distribuição de dados. Os histogramas são usados para calcular a seletividade dos predicados. Eles são comprovados para produzir estimativa de taxa baixa de erros e incorrer em quase nenhum run-time overhead.
Um histograma é usado para sintetizar graficamente e exibir a distribuição de um conjunto de dados do processo. O Informix utiliza histogramas de altura equilibrad (isto é, largura/altura) como range de transbordo, em oposição aos histogramas largura equilibrada. Tendo a altura em relação significa o mesmo número de valores em cada range é mantido. Dependendo dos valores, a gama é determinada, e um range representa uma gama. Abaixo está um exemplo de um histograma:
^
Número |
de |
linhas | altura constante
+------------------------
|
|
|
|
---+------------------------
| 5 25 29 77 118
| range de valores
Suponha que há mil linhas em uma tabela. Se o número do range é fixada em 10, a proporção será de 1000-1010, assim existem 100 linhas em cada range. Esta 100 representa a altura. No caso de um histograma em relação altura, este valor (100) é fixo.
A distribuições de dados são gerados pelo servidor para colunas individuais de uma tabela, e são armazenados como histogramas encoded e coluna encdat do catálogo do sistema sysdistrib. O número padrão de caixas pode ser adequada se os dados são uniformemente distribuídas em todo o domínio de valores para essa coluna. No entanto, se os dados são altamente distorcidos, então são necessários um número maior de caixas (menor resolução) para garantir que os dados não é muito inclinada.
A mágica do UPDATE STATISTICS
Basicamente, sempre que o comando UPDATE STATISTICS é executado tanto no modo de alta ou média, dois tipos de caixas são criados, os distribution bin e o overflow bin. Cada entrada no bin é composto da seguinte maneira:
Distribuição bin
- Número de linhas no lixo;
- Número de valores distintos na bin;
- Maior valor de dados no lixo;
Overflow bin
- Valor dos dados
- Frequência de valor de dados
Ilustração
Agora, vamos considerar uma tabela de inventário que consiste em três colunas - item_num, customer_name e amount. Iremos realizar UPDATE STATISTICS na coluna item_num, em seguida, ver como as duas caixas são construídas.
Table 1. Inventory table
item_num customer_name amount
1 manoj 92.5
1 prasanna 43.75
1 bharath 90
1 ranjani 78.5
1 priya 23.5
1 radhika 126.75
1 vaibhav 75
1 harsha 300.50
1 vishwas 20
2 deepak 32.5
2 vinay 90
2 archit 65.20
2 vishnu 32.75
2 samir 48.3
2 ravi 49.5
3 srini 67.5
4 rahul 56.0
5 sudev 73.0
Para preencher a distribuição de dados, execute do UPDATE STATISTICS no modo de alta ou média. A informação no histograma pode ser recuperada a partir do utilitário Informix chamado dbschema invocando o seguinte comando:
$ Dbschema-d (dbname) -hd [(table)]
A listagem a seguir mostra a distribuição dos dados acima quando o update statistic é executado no modo médio com uma resolução de 10.
Listing 1. Distribution output
$ dbschema -d newdb -hd Inventory
DBSCHEMA Schema Utility INFORMIX-SQL Version 11.10.FC2
Copyright IBM Corporation 1996, 2006 All rights reserved
Software Serial Number AAA#B000000
{
Distribution for informix.Inventory.item_num
Constructed on 2007-11-09 04:47:00.00000
Medium Mode, 10.000000 Resolution, 0.950000 Confidence
--- DISTRIBUTION ---
( 1)
1: ( 2, 2, 4)
2: ( 1, 1, 5)
--- OVERFLOW ---
1: ( 9, 1)
2: ( 6, 2)
Definir o número de caixas
A fórmula para calcular o número de caixas é:
100/resolution = número de caixas.
Uma resolução de 1 significa que 1% dos dados entra em cada bin (100/1 = 100 caixas)
A resolução de 10 significa que 10% das os dados vão para cada bin (10 caixas são criados).
Compreender a distribuição bin
O bin sempre começa com um número bin ou um identificador que é incrementado sequencialmente para cada linha. Cada fila tem três colunas associados. Todas as três colunas estão representadas com os parênteses. A primeira coluna indica o tamanho da caixa. A segunda coluna indica o número de elementos diferentes dentro do intervalo, e a terceira coluna indica os valores mais elevados dentro do range.
Por exemplo, pegando acima a saída da Distribuição em consideração, a primeira linha pode ser lida da seguinte forma:
- O tamanho bin = 2
- Existem dois elementos distintos entre o intervalo de 1 e 4. Você pode ver isso quando você olha para a tabela de inventário. Os dois valores distintos são 3 e 4. Um observador realmente interessado deve ter notado que os valores 1 e 2 não são levados em consideração. A contagem para este está incluído na caixa de overflow e não é para ser tomada em consideração para este bem.
- O intervalo é entre os valores 1 e 4.
Compreender o overflow bin
Como o bin de distribuição, a overflow bin também está representada em linhas. Cada linha começa com um identificador que é incrementado para cada linha. Existem apenas duas colunas nesta bin, cada um representado dentro do parêntese. A primeira coluna indica o número de vezes que o valor da segunda coluna é repetida. A segunda coluna especifica o próprio valor da coluna.
Por exemplo, na tabela acima, pode ser visto que o C1=1 é repetido 9 vezes e C1=2 é repetido 6 vezes. Note que apenas os valores de coluna que cruzam 25% do tamanho da bin são colocados na overflow bin. As contagens obtidas aqui são valores absolutos tiro certo que podem ser utilizados para as estimativas.
NOTA : A saída do dbschema não pode ser comparado com o conceito regular de filas e colunas. Os termos de linhas e colunas aqui estão literalmente tomadas e não têm ligação com a concepção de linhas e colunas usadas em bancos de dados.
Ilustração de um plano de consulta
Considere duas tabelas, cliente e ordens . A tabela cliente tem a atributos customer_num , CEP e customer_name ; ordens tem o atributos customer_num , quantidade e item_num .
Abaixo está um exemplo de como um plano de consulta ficaria assim para a consulta:
select * from orders a, customer b where a.customer_num > 435 and b.zipcode > "65*" ;
Listing 2. SQEXPLAIN output
QUERY:
------
Completed pass 1 in 0 minutes 0 seconds
UPDATE STATISTICS:
==================
Table: informix.customer
Mode: HIGH
Number of Bins: 288 Bin size 11
Sort data 0.2 MB Sort memory granted 0.2 MB
Estimated number of table scans 1
PASS #1 zipcode
Light scans enabled
Scan 0 Sort 0 Build 0 Insert 0 Close 0 Total 0
Completed pass 1 in 0 minutes 0 seconds
QUERY:
------
select * from orders a, customer b where a.customer_num > 435 and b.zipcode > "65*"
Estimated Cost: 9805
Estimated # of Rows Returned: 244530
Maximum Threads: 1
1) informix.b: INDEX PATH
(1) Index Keys: zipcode (Parallel, fragments: ALL)
Lower Index Filter: informix.b.zipcode > '65*'
2) informix.a: INDEX PATH
(1) Index Keys: customer_num (Parallel, fragments: ALL)
Lower Index Filter: informix.a.customer_num > 435
NESTED LOOP JOIN
Como seria se não existisse o UPDATE STATISTICS
Considere um cenário típico em que a importância de UPDATE STATISTICS pode ser demonstrada. Considere três tabelas, T1, T2 e T3. Suponha que eles têm 10, 100 e 1000 linhas, respectivamente. Agora, suponha que você tem que realizar uma operação 'EQUI-JOIN' em todas as três tabelas. O otimizador seria checaria o plano de consulta antes de realizar a própria operação de junção real, fazendo uso dos dados estatísticos. Você assume que esses dados estatísticos estão atualizados e continua seguir em frente.
Agora, quantas maneiras você pode executar esta operação participar? Como você sabe que T1*T2 não é o mesmo que T2*T1, que é uma questão simples de descobrir. Você tem três tabelas e, portanto, você teria essas forma 3!*2!*1! executar uma operação de junção. Isso é de 12 maneiras diferentes de fazê-lo. Para este exemplo, apenas 5 casos do 12 são usados.
Case 1 -- (T1 * T2) * T3 results in 1,011,010 row accesses.
Case 2 -- (T2 * T1) * T3 results in 1,101,100 row accesses.
Case 3 -- (T1 * T3) * T2 results in 1,011,010 row accesses.
Case 4 -- T1 * (T2 * T3) results in 1,001,010 row accesses.
Case 5 -- T3 * (T2 * T1) results in 1,011,000 row accesses.
Agora, suponha que havia um grande número de operações realizadas, e essas tabelas foram manipulados. O cenário atual resultaria em T1, T2 e T3 com 1000, 100 e 10, linhas respectivamente. Desde o UPDATE STATISTICS não é executado automaticamente, as tabelas de catálogo do sistema não seria atualizada e ainda estaria mantendo os dados estatísticos de data. Agora, você deve executar novamente a operação de junção por igualdade.
O otimizador escolhe Caso 4, uma vez que determinou que ela tem o número mínimo de acessos linha e, portanto, deve ser eficiente. Mas, o plano não produz o melhor resultado. De acordo com a situação atual, um plano eficiente seria usar Caso 5 - (T3 * (T2 * T1) - ao passo que o otimizador escolhe Caso 4 -. T1 * (T2 * T3) Isso resulta em uma linha extra enorme '9990' acessos. Este número pode crescer para trilhões para bancos de dados volumosos, o que reduz drasticamente a eficiência da consulta. Se você tivesse executado a instrução UPDATE STATISTICS, teria sido muito mais eficiente. Este exemplo mostra porque UPDATE STATISTICS tem de ser executado periodicamente. Resumindo tudo isso, use a instrução UPDATE STATISTICS para executar qualquer uma das seguintes tarefas:
- Calcula-se a distribuição dos valores da coluna.
- Atualizar as tabelas de catálogo do sistema que o servidor de banco de dados usa para otimizar as consultas.
- Força re-otimização de rotinas SPL.
- Converta índices existentes quando você atualizar o servidor de banco de dados.
Sintaxe de UPDATE STATISTICS
Versão 1: Para atualizar as estatísticas para todo o banco de dados
UPDATE STATISTICS (LOW | MEDIUM | HIGH);
Versão 2: Para atualizar as estatísticas para uma tabela específica em um banco de dados. Todas as colunas são atualizados neste caso.
UPDATE STATISTICS (LOW | MEDIUM | HIGH) FOR TABLE (table_name) ;
Versão 3: Para atualizar as estatísticas para uma determinada coluna de uma tabela específica em um banco de dados.
UPDATE STATISTICS (LOW | MEDIUM | HIGH) FOR TABLE (table_name) (column_name);
Versão 4: Para atualizar as estatísticas para um procedimento armazenado em um banco de dados.
UPDATE STATISTICS (LOW | MEDIUM | HIGH) FOR PROCEDURE;
Versão 5: Para executar UPDATE STATISTICS, definindo a sua própria resolução.
UPDATE STATISTICS (LOW | MEDIUM | HIGH) FOR TABLE (table_name) RESOLUTION 10;
O comando UPDATE STATISTICS não é executada automaticamente. É de responsabilidade do usuário para executar esta declaração manualmente. Este comando deve ser executado periodicamente para que o otimizador tem os mais recentes dados com ele para chegar a um plano eficaz para buscar os dados.
Idealmente, é preferido para executar esta indicação quando um volume de dados é manipulado na base de dados. Por padrão, o UPDATE STATISTICS é executado no modo de baixo. Neste modo, as distribuições de dados não são gerados. Nunca use o modo de alta para um banco de dados inteiro ou todas as tabelas em um banco de dados ou até mesmo todas as colunas de uma tabela, a menos que o banco de dados ou as tabelas são de um tamanho consideravelmente menor.
Link
