Tuning Informix SQL
O ajuste de consulta SQL é frequentemente considerada como uma grande responsabilidade para os programadores e desenvolvedores, mas os administradores de banco de dados também devem participar ativamente neste processo. Uma das grandes vantagens de ter os administradores de banco de dados SQL envolvidos no ajuste de consulta é que eles podem oferecer uma perspectiva diferente. Considerando que o programador aborda o problema do ponto de vista do desempenho do aplicativo, um DBA pode abordar o problema com uma compreensão do próprio banco de dados, e pode oferecer conselhos e sugestões para o layout do banco de dados, organização de tabelas e índices, e uso efetivo do Informix e recursos do sistema, incluindo a fragmentação dos dados, a prioridade PDQ, tempo de CPU, utilização de memória e armazenamento de dados. Às vezes, os programadores e desenvolvedores só quero ter uma visão diferente de suas em questões aos termos de desempenho, para que possam modificar a consulta para maior eficiência.
Este artigo irá cobrir uma série de abordagens para o tuning. Parte 1 aborda os critérios para o ajuste, ferramentas e uma metodologia geral. Além disso, discute o papel do otimizador, incluindo o parâmetro OPTCOMPIND, directivas consulta e estatísticas internas.
Fique atento para a parte 2, quando eu vou cobrir as considerações de desempenho para as consultas de si mesmos, incluindo os métodos de acesso, junte-se métodos, extensões para tabelas e índices, níveis de índice, a fragmentação e a prioridade PDQ. Parte 2 também irá incluir exemplos e análise.
Critérios para o Ajuste
Os critérios para o ajuste dependem em grande parte dos requisitos de negócios individuais. Em geral, o desempenho do seu sistema e de seu banco de dados é a maior preocupação. Há muitas considerações, mas a maioria criticas delas são do tempo de resposta e utilização de recursos . Tempo de resposta se refere ao tempo que os usuários espera os seus pedidos, ou seja, para as suas consultas SQL seja concluído. A utilização de recursos refere-se ao uso dos recursos do sistema, como CPU, memória e discos na realização de consultas SQL.
Em geral, se, depois de ter passado pelo processo de ajuste, o tempo de resposta das consultas SQL, após ajuste é menor que a utilização de recursos do sistema é menor, então você pode concluir com confiança que você tem ajustado a consulta SQL para maior eficiência. Como você mede o tempo de resposta da utilização dos recursos de uma consulta SQL? Unix e Informix nos forneceu algumas ferramentas e utilitários para nos ajudar a fazer medições científicas e quantitativa do tempo de resposta da utilização dos recursos.
Ferramentas para a otimização
Ferramentas Unix
Time and timexsys3523:omcadmin > time dbaccess airgen_cm_db sel.sql
Database selected.
(count(*))
5958
1 row(s) retrieved.
Database closed.
real 0m0.09s
user 0m0.01s
sys 0m0.06sO campo "real" mostra o tempo decorrido entre o início da consulta e do fim da consulta.
O campo "user" mostra a soma de tempo de CPU usado pelo processador de usuário para a operação.
O campo "sys" mostra a soma de tempo de CPU usado pelo sistema como um todo.
O campo que mais nos interessa é o campo "real", isto é o campo que indica o tempo de resposta da operação. Vamos dar uma olhada no exemplo acima. A partir do resultado, podemos determinar o tempo de resposta para a nossa consulta é de 0,09 segundos.
Timex é apenas uma outra variação do utilitário do tempo que vai apresentar mais tempo em formato legível. Aqui está a saída timex da mesma consulta:
sys3523:omcadmin > timex dbaccess airgen_cm_db sel.sql
Database selected.
(count(*))
5958
1 row(s) retrieved.
Database closed.
real 0.09
user 0.02
sys 0.04O utilitário vmstat se aprofunda no sistema de relatórios de estatísticas de uso de recursos sobre os processadores que estão sendo usados, memória virtual, I/O de disco, e CPU. Ele também exibe um resumo de uma linha de atividades de memória virtual já que o sistema foi reiniciado ou iniciado. Aqui está um exemplo do resultado:
sys3523:omcadmin > vmstat 1 10
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr m0 m1 m4 m5 in sy cs us sy id
0 0 0 1959208 1294824 141 824 1 1 1 0 0 0 0 0 0 906 946 700 2 3 95
0 0 0 1995568 1260288 0 46 0 0 0 0 0 0 0 0 0 834 386 213 0 0 100
0 0 0 1995568 1260288 0 40 0 0 0 0 0 0 0 0 0 884 265 199 0 1 99
0 0 0 1995568 1260288 0 40 0 0 0 0 0 0 0 0 0 834 325 186 0 0 100
0 0 0 1995568 1260288 43 286 0 0 0 0 0 0 0 0 0 869 1682 242 0 1 99
0 0 0 1995352 1260048 658 3503 0 0 0 0 0 0 0 0 0 827 21930 375 3 14 83
0 0 0 1995408 1260240 662 3495 0 0 0 0 0 0 0 0 0 825 22010 387 4 13 83
0 0 0 1995568 1260288 121 691 0 0 0 0 0 0 0 0 0 834 4310 261 1 3 96
0 0 0 1995568 1260288 0 40 0 0 0 0 0 0 0 0 0 824 250 188 0 0 100
0 0 0 1995568 1260288 0 40 0 0 0 0 0 0 0 0 0 824 365 214 0 0 100O que mais nos interessa na saída são os campos "r", "po" e "id".
O campo "R" nos diz quantos jobs estão no sistema da ready queue, aguardando recursos para ser executado.
O campo "PO" nos diz quantas páginas na memória estão atualmente paginada. Se esse número for muito grande e continua a aumentar, é geralmente uma indicação de memória física insuficiente ou de memória RAM, e pode ser necessário instalar mais memória.
O campo "ID" nos diz o quanto os recursos da CPU do sistema estão sendo utilizados.
Estes campos em conjunto lhe dará uma boa idéia de como os recursos do sistema são usados atualmente.
Ferramentas do Informix
A ferramenta mais abrangente que prevê a coleta detalhadas no Informix no plano de consulta SQL e estatísticas de execução é o utilitário SET EXPLAIN. Este utilitário irá gerar um arquivo chamado sqexplain.out, com registros em detalhes para cada passo da execução da consulta. Além disso, fornece uma estimativa dos custos da consulta e as estimativas de resultados da consulta. Ao examinar o arquivo de saída do SET EXPLAIN, você pode determinar se medidas podem ser tomadas para melhorar o desempenho da consulta. O exemplo a seguir mostra o conjunto explicar a saída para uma consulta bastante complexa:
QUERY:
------
SELECT --+AVOID_FULL(omchn)+AVOID_FULL(daphn)
omchn.omc_hn_uanc,
nvl(daphn.gtt_version,"0000000000000000000"),
nvl(idachn.egt4_version,"0000000000000000000"),
nvl(ihlrhn.hlr_timestamp,"00000000000000"),
vsgw_hn.hn_igw_uanc,
nvl(vsgw_hn.hn_igw_version, "00000000000000")
FROM omchn, daphn, idachn, ihlrhn, vsgw_hn
WHERE daphn.dap_hn_inst = omchn.omc_hn_inst
AND idachn.idac_hn_inst = omchn.omc_hn_inst
AND ihlrhn.hlr_hn_inst = omchn.omc_hn_inst
AND vsgw_hn.vsgw_hn_inst = omchn.omc_hn_inst
DIRECTIVES FOLLOWED:
AVOID_FULL ( omchn )
AVOID_FULL ( daphn )
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 8
Estimated # of Rows Returned: 1
1) root.idachn: SEQUENTIAL SCAN
2) root.daphn: INDEX PATH
(1) Index Keys: dap_hn_inst (Serial, fragments: ALL)
Lower Index Filter: root.daphn.dap_hn_inst = root.idachn.idac_hn_inst
NESTED LOOP JOIN
3) root.vsgw_hn: SEQUENTIAL SCAN
NESTED LOOP JOIN
4) root.omchn: INDEX PATH
Filters: root.vsgw_hn.vsgw_hn_inst = root.omchn.omc_hn_inst
(1) Index Keys: omc_hn_inst (Serial, fragments: ALL)
Lower Index Filter: root.idachn.idac_hn_inst = oot.omchn.omc_hn_inst
NESTED LOOP JOIN
5) root.ihlrhn: INDEX PATH
(1) Index Keys: hlr_hn_inst (Serial, fragments: ALL)
Lower Index Filter: root.ihlrhn.hlr_hn_inst = root.omchn.omc_hn_inst
NESTED LOOP JOIN- A primeira parte mostra a sintaxe da consulta.
- A segunda parte mostra os custos estimados da consulta.
- A terceira parte é a explicação detalhada de cada etapa da consulta executada.
O que nós estamos mais interessados é a segunda e terceira partes. Os custos estimados são unidades de custo que o otimizador usa para comparar planos de consulta. Estas unidades não se traduzem diretamente em tempos, pois eles representam o tempo relativo para um acesso ao disco.
O otimizador escolheu este plano de consulta, pois o custo estimado para a sua execução foi a mais baixa entre todos os planos avaliados. Uma consulta com maior estimativa de custos em geral, leva mais tempo para executar do que um com menor custo estimado. A terceira parte é de vital importância no ajuste de consulta, pois nos fornece uma grande quantidade de informações úteis, tais como o método de acesso a dados e o método de junção que a consulta usada. O exemplo acima mostra que a varredura seqüencial e os índices são usados para fazer a recuperação de dados, e o nested-loop método join é usado para juntar todas as tabelas. Vou discutir isso em detalhes mais adiante neste artigo.
Este utilitário é fácil de usar. Se você quiser saber o plano de execução detalhado da consulta para uma certa consulta SQL, basta adicionar SET EXPLAIN ON na declaração perante a sua consulta original da seguinte forma:
set explain on;
update statistics for stored procedure dap_int();set explain on avoid_execute;
select count(*) from act;set explain on avoid_execute;
select count(*) from act;Como então podemos aplicar essas ferramentas em consultas SQL ? Pessoas diferentes podem ter diferentes abordagens, mas, em geral, você deve seguir a metodologia e os passos descritos abaixo:
1 - Coletar dados estatísticos sobre a sua consulta SQL original. Nesta etapa, você precisa usar as ferramentas discutidas acima para obter estatísticas sobre a consulta: seu tempo de resposta, o sua query de execução detalhado, e os custos para posterior análise em profundidade.
2 - Analisar as estatísticas. Nesta etapa, você precisa vasculhas as estatísticas coletadas acima e dê uma boa olhada na guery de execução da consulta. Como dito acima, o desempenho é a maior preocupação no ajuste de consulta. Você precisa considerar todos os fatores que poderiam afetar o desempenho ao examinar o plano de consulta: o método de acesso, o método de junção, subqueries, indices e extensões de índice, indices e fragmentação do índice e assim por diante. Vou discutir cada um desses fatores em detalhe mais adiante na parte dois deste artigo.
3 - Configurar o ambiente de teste. Este é um passo muito importante. O ambiente de teste deve ser criado exatamente o mesmo ou muito semelhante ao ambiente de produção onde a consulta é executada em termos de configurações de hardware e software. Por exemplo, se a máquina de produção tem 6 CPUs 400HM, a máquina de teste também deve ter seis CPUs 400HM, caso contrário seus testes subseqüentes serão inválidas e não confiáveis. Tenha em mente, todas essas consultas vão finalmente ser executado em produção.
4 - Fazer alterações e testar a sua nova consulta. Este é um passo importante e também pode ser o passo mais entediante em tuning. Fazer alterações na consulta original um de cada vez, e testar para ver se o desempenho melhorou (tempo de resposta diminuída). Detalhes do registro de seu teste, tais como as alterações feitas, tempo de resposta, e plano de execução. Se depois de fazer a mudança, o desempenho de sua consulta não é melhor do que sua consulta original, reverta a alteração. Seus testes deve ser válido e confiável, em outras palavras, os testes devem ser repetidos. Por exemplo, se você fez dois testes idênticos na mesma consulta, e pela primeira vez, ele produz um tempo de resposta muito boa (digamos 10 segundos), mas na segunda vez, o tempo de resposta aumentou para 30 segundos, do que os testes não são repetível pois a diferença do tempo de resposta é muito grande. Você precisa re-examinar o procedimento do teste e identificar as diferenças entre os dois testes. Se os testes são repetíveis, as diferenças entre os resultados dos testes devem ser mínimos.
5 - Analisar os resultados dos testes. Ao analisar os resultados dos testes, é preciso examinar a validade e confiabilidade dos resultados do teste. Precisamos examinar o hardware, software, a carga e todos os outros factores para assegurar os resultados dos testes são válidos e confiáveis.
6 - Implementar suas melhorias no sistema de produção. Antes de implementar, é necessário realizar a última revisão detalhada e certifique-se a novas consultas não irá causar quaisquer problemas na produção.
Papel do Otimizador
Como outros sistemas de gerenciamento de banco de dados relacional como Oracle e SQL Server, o Informix tem o seu otimizador interno, que é responsável por selecionar o melhor plano de execução da consulta. Depois de consultas SQL são analisadas, o otimizador irá considerar todas as formas possíveis para executar a consulta, analisando fatores como custos de disco I/O e CPU. Ele irá então construir todos os planos viáveis simultaneamente usando uma de baixo para cima, a respiração de primeira estratégia de busca.
Em outras palavras, o otimizador irá construir todos os planos de junção possíveis primeiro, e depois eliminar o mais caro de qualquer pares redundantes, que são juntar pares que contêm as mesmas tabelas e produzir o mesmo conjunto de linhas como um outro par de junção. Se uma consulta utiliza tabelas adicionais, o otimizador une cada par restante para uma nova tabela para formar todos os join triplets possíveis, eliminando o mais caro de triplets redundante, e assim por diante para cada tabela adicional a serem unidas. Quando um conjunto não-redundante de combinações possíveis de join foi gerado, o otimizador escolhe o plano que parece ter os menores custos de execução. Por exemplo, o otimizador deve determinar se índices deveriam ser utilizados. Se a consulta inclui um join, o otimizador deve determinar o plano de join (hash, sort merge, or nested loop), e a ordem em que as tabelas são avaliados ou associados.
O otimizador estimativa o custo de consulta da query sobre o número de linhas a serem recuperados de cada tabela. Por sua vez, o número estimado de linhas é baseada na seletividade de cada expressão condicional que é usado na cláusula WHERE. O otimizador utiliza informações de distribuição de dados gerados por UPDATE STATISTICS para calcular a seletividade dos filtros na consulta. No entanto, na ausência de dados de informação de distribuição, o otimizador irá calcular a seletividade dos filtros de diferentes tipos com base em índices da tabela. Por exemplo, se a coluna tem valores indexados em valores literal e valores nulos, em seguida, a seletividade é igual ao número de chaves distintas no índice. Consulte o Chapter 10 of the Performance Guide para a tabela detalhada que o otimizador utiliza para calcular a seletividade na ausência de distribuição de dados. Mas a seletividade calculada desta forma não é tão preciso quanto seletividade calculados usando a distribuição de dados.
Por isso, é óbvio que a precisão da estimativa de seletividade depende de quantas vezes você executa UPDATE STATISTICS. Se você executar UPDATE STATISTICS com freqüência, o otimizador irá calcular a seletividade de forma mais precisa, uma vez que a distribuição dos dados serão atualizados a cada vez que você executa UPDATE STATISTICS, exceto quando você executar o UPDATE STATISTICS com opção de baixo.
O otimizador utiliza as informações do catálogo do sistema a seguir, pois cria um plano de consulta:
- O número de linhas em uma tabela, a partir da mais recente instrução UPDATE STATISTICS
- Se uma coluna constrained é obrigado a ser único
- A distribuição de valores de coluna, quando solicitado, com a palavra-chave MÉDIO ou ALTO na instrução UPDATE STATISTICS
- O número de páginas de disco que contêm dados da linha
- Os índices que existem em uma tabela, incluindo as colunas que índice, sejam eles ascendentes ou descendentes, e se eles são agrupados
- A profundidade da estrutura do índice (uma medida da quantidade de trabalho que é necessário para executar uma pesquisa de índice)
- O número de páginas de disco que ocupam as entradas do índice
- O número de entradas em um índice único, que pode ser usado para estimar o número de linhas que retorna um filtro de igualdade
- Valores segundo-maior e segundo-menor de chave em uma coluna indexada.
O comportamento do otimizador é influenciado por três fatores principais: o valor do parâmetro OPTCOMPIND no arquivo de configuração Informix, directivas de consulta e a precisão das estatísticas internas.
Parâmetro OPTCOMPIND
OPTCOMPIND é uma variável de ambiente ou um parâmetro no arquivo de configuração do Informix. O otimizador usa o seu valor para determinar a sua escolha do método de acesso a dados. Tem um dos três valores (0, 1 e 2), que indicam o seguinte:
Valor 0 - O otimizador escolhe a varredura de índices sobre a varredura de tabela se índices apropriados existir, mesmo sem considerar os custos estimados.
Valor 1 - O otimizador se comporta como ele faz para o valor 0 se o modo de isolamento da transação não é Leia Repetitivo. Se o modo de isolamento da transação é Repeatable Read, em seguida, o otimizador iria basear sua escolha puramente sobre os custos estimados.
Valor 2 - O otimizador se usa custos estimados para determinar um plano de execução, independentemente do modo de isolamento da transação.
É possível definir OPTCOMPIND tanto como uma variável de ambiente ou como um parâmetro no arquivo de configuração, mas defini-lo como um parâmetro terá prioridade na execução.
Directivas de consulta
Outra forma de influenciar o otimizador é a utilização de directivas de consulta. Directivas de consulta são dicas em consultas SQL que instruem o otimizador de como executar a query. Existem quatro tipos de directivas consulta da seguinte maneira:
- Acesso directivas plano que força o otimizador a usar o método de acesso designado para recuperação de dados, ou varredura seqüencial ou varredura de índice. - Junte-se diretrizes para que otimizador de força para unir as tabelas na ordem designada. - Junte-se directivas plano que otimizador de força para usar o designado método de junção para unir tabelas na consulta, ou loop aninhado, merge sort participar, ou hash dinâmica participar. - Directivas objetivo que forçar o otimizador a usar as regras designadas para retornar os resultados da consulta.
Estatísticas internas
Estatísticas internas que queremos dizer são as estatísticas nos catálogos do sistema que o otimizador usa para determinar o menor custo plano de execução da consulta. Para garantir que o otimizador seleciona o melhor plano de consulta, é importante manter as estatísticas internas atuais e precisas. O servidor de banco de dados inicializa um perfil estatístico dos objetos de banco de dados como tabelas, índices, procedimentos armazenados e triggers, e coloca distribuição de dados em catálogos do sistema quando as tabelas do banco de dados são criados, mas não atualiza as estatísticas automaticamente.
Para manter este perfil estatística atualizada, você precisa executar UPDATE STATISTICS regularmente, caso contrário, o perfil estatístico do seu sistema pode não refletir o estado atual de seu sistema, e o otimizador pode não ser capaz de fazer escolha certa entre os planos de execução de inúmeras consulta. Há três modos para a execução de STATISTICS UPADTE e, em geral você precisa para executar UPDATE STATISTICS após cada vez que você realiza trabalhos em lotes grandes de que a mudança de grandes quantidades de dados da tabela e cada vez que depois que você adicionar um índice para uma tabela. Para obter informações detalhadas sobre como executar UPDATE STATISTICS, consulte o IBM Informix Dynamic Server Administrator's Guide, Version 9.4. A regra de ouro é que com mais freqüência você execute o UPDATE STATISTICS, o mais atualizado e preciso perfil estatístico do seu sistema será, e maior a chance de que o otimizador fará a melhor escolha para um plano de execução da consulta.
Embora o comportamento do otimizador é influenciada por OPTCOMPIND e diretrizes como falamos acima, o otimizador geralmente escolhe uma base de plano de consulta sobre as directrizes a seguir:
- O otimizador não usará um índice se a consulta recupera uma grande quantidade de dados de uma tabela. Por exemplo, se os clientes de sua empresa são muito uniformemente distribuído entre todos os 50 Estados, e você deseja recuperar as informações do cliente para cada estado, exceto para New York, e você executar a seguinte consulta:
SELECT * FROM customer WHERE STATE <> "NEW YORK";- Se vários índices estão definidos na tabela, o otimizador utiliza o índice que pode descartar a maioria dos dados na tabela. Por exemplo, se sua empresa tem 200.000 clientes em New York, e apenas cerca de 1000 lugares clientes encomendas no mesmo dia, digamos 20 janeiro de 1997, e você emite a seguinte consulta para obter seus nomes e endereços:
SELECT name, address FROM customer
WHERE state = "NEW YORK" AND order_date = "01/20/97"- Se não houver directivas na consulta, o otimizador irá sempre recuperar dados de tabelas com o filer mais restritivo primeiro. Vejamos a seguinte consulta:
SELECT * FROM customer, orders
WHERE customer.customer_num = orders.customer_num
AND customer.state = "NEVADA";- O otimizador escolha de dynamic hash se juntar, se nenhuma das colunas unidas tem índice. No exemplo anterior, se customer.customer_num and orders.customer_num não são indexadas, de junção de hash dinâmica seria escolhido pelo otimizador como o melhor plano de execução.
- O otimizador escolherá junções de laço aninhado se:
-- O número de linhas recuperadas da tabela exterior depois que o servidor de banco de dados se aplica todos os filtros de tabela é pequena, e a tabela interna tem um índice que pode ser usado para executar a associação.
-- O índice sobre a tabela ultraperiféricas pode ser usado para retornar as linhas na ordem da cláusula ORDER BY, eliminando a necessidade de uma espécie.
Retirado - Clique Aqui
0 comentários:
Enviar um comentário