INFORMIX: Otimizando Queries com Diretivas

Para quem não está acostumado com o termo "diretiva do otimizador" irei dar um breve descrição:

- Um componente que faz a diferença em um RDBMS é otimizador de plano acesso de queries, este utiliza-se de cálculos baseados em custo ou regra.

No caso do Informix desde a versão 7.X (1994?) o otimizador é baseada em custo, no Informix Online (1990?) lembro-me que não tinha histograma mas creio que já era baseado em custo, porque estou contando esta histórinha?

Bom o Oracle passou a utilizar otimizador baseado em custo a partir da versão 10.X (2004?) , na versão 9.i o otimizador era hibrido o DBA escolhia: custo ou regra. (É mole!!?)

Voltando a nossas diretivas, temos os seguintes tipos:

Diretivas de Métodos de Acesso

São diretivas que influenciam no caminho usado no plano de acesso, tais como acesso full, indexado etc.

Segue abaixo lista de diretivas:

FULL - Força um acesso sequencial na tabela

AVOID_FULL - Evita que seja executado um acesso sequencial na tabela

INDEX - Força a pesquisa no indice informado

AVOID_INDEX - Desconsidera o indice informado

INDEX_SJ - Força a auto-junção pelo indice informado

AVOID_INDEX_SJ - Evita a auto-junção através do índice informado.

Diretivas de Métodos de Join

São diretivas que influenciam a forma como a junção entre as tabelas será feita, por exemplo ler tabela a e buscar ocorrência na tabela B (Nested Loop)

Segue abaixo lista de diretivas:

USE_NL

AVOID_NL

USE_HASH

USE_HASH (tabname)

USE_HASH (tabname/BUILD)

USE_HASH (tabname/PROBE AVOID_HASH

Diretivas de Ordem

Existe somente uma, indica que a ordem de acesso das tabelas na junção será de acordo com a ordem das tabelas na cláusula from do SQL.

ORDERED

Diretivas de Metas

Estas definem o comportamento do otimizador em relação ao retorno do resultado da query.

FIRST_ROWS - O retorno da query ocorre logo após a leitura das primeiras linhas, com isto é mais fácil interromper uma query quando vemos que a mesma não está rápida.

ALL_ROWS - O retorno das primeiras linhas da query ocorre após a leitura de todas as linhas envolvidas na consulta.

Diretivas de Explain

No exemplo abaixo usamo uma diretiva para que o otimizador exiba ao output do plano de acesso no arquivo de explain.

SELECT {+ EXPLAIN } *
FROM Tabela1 a, Tabela2 b
WHERE b.customer_num = a.customer_num

No exemplo abaixo usamo uma diretiva para que o otimizador exiba ao output do plano de acesso no arquivo de explain, porém a query não será executada, somente será criado o plano fe acesso.

SELECT {+ EXPLAIN AVOID_EXECUTE} ......

Definindo Diretivas Externas

Uma diretiva externa é aquela que pode ser armazenada dentro do banco de dados, imagine um ERP onde você não tenha acesso ao código fonte, e foi detectada uma query onde o plano de acesso não esta legal.

Bom neste caso é possível armazenar esta query juntamente com as diretivas dentro de uma tabela de catálogo do Informix, sempre que este comando sql for parseado o engine irá aplicar as diretivas.

Veja exemplo abaixo:

save external directives {+ ORDERED USE_NL(u)} active for SELECT * FROM Tabela1 a, Tabela2 b WHERE b.cod_emp = a.cod_emp

Para consultar as diretivas externas em catálogo:

select * from sysmaster:sysdirectives

Para desativar uma diretiva externa:

UPDATE sysdirectives set active = 0 where id = ?

Para excluir uma diretiva externa:

DELETE from sysdirectives where id = 2

Normalmente quando falamos do otimizador temos que falar de várias tópicos, pois o cálculo do custo está relacionado ao custo de I/O, Network, CPU e seletividade.

Não quero entrar em update statistics mas vale lembrar que além de atualizar as estatisticas do catálogo e gerar os bins do histograma o mesmo tem mais funções, dentre elas varrer as páginas de índices que foram marcadas como excluídas e reenviar para a fila do B-Tree cleaner para que as mesmas sejam removidas.

Eu já tinha ouvido algumas discussões sobre o assunto: Update Statistics reorganiza os índices? Não é bem assim, ele apenas remove entradas marcadas como excluídas dos índices.

Por isto não esqueça de atualizar a estatisticas de suas tabelas e procedures, se você ainda não usa a versão 11.50 começa a pensar em usá-la, pois nesta versão a atualização de estatisticas é automática.

Dúvidas? Pregutas? Questions? Askpuentes!!