CONHECENDO HINTS
Mesmo sabendo que o otimizador é incrivelmente preciso na escolha do caminho de otimização correto e no uso de índices para milhares de consultas no seu sistema, ele não é perfeito. O ORACLE possui hints (sugestões) que você poderá usar para determinadas consultas, de modo que o otimizador seja desconsiderado, na esperança de conseguir melhor desempenho para determinada consulta.
Os hints modificam o caminho de execução quando um otimizador processa uma instrução específica. O parâmetro OPTIMIZER_MODE de init.ora pode ser usado para modificar todas as instruções no banco de dados para que sigam um caminho de execução específico, mas um hint para um caminho de execução diferente substitui qualquer coisa que esteja especificada no init.ora. Contudo, a otimização baseada em custo não será usada se as tabelas não tiverem sido analisadas.
Hints disponíveis e agrupamentos
Os hints disponíveis variam de acordo com a versão do banco de dados instalado. Embora este trabalho focalize apenas os hints que são usados com maior freqüência, muitos dos hints que não são abordados com detalhes podem oferecer grandes ganhos de desempenho com um sistema específico. Todos os hints disponíveis para sua versão de banco de dados, podem ser encontrados no Database Administrator’s Guide.
Usando Hints de métodos de acesso
Os hints que são agrupados em métodos de acesso permitem que o codificador varie o modo como a consulta real é acessada. Esse grupo de hints é usado freqüentemente, especialmente o hint INDEX. Ele oferece orientação a respeito de se e como os índices são usados, e como os índices correspondentes serão mesclados para chegar à resposta final. Os hints de método de acesso são os seguintes:
AND_EQUAL CLUSTER FULL
HASH INDEX INDEX_ASC
INDEX_COMBINE INDEX_DESC INDEX_FFS
INDEX_JOIN NO_INDEX RPWID
Usando Hints de transformação de consulta
Este tipo de hint é útil especialmente em data warehouse em que você está acostumado com o uso de tabelas de fato e dimensão. O hint FACT pode forçar determinada tabela a ser tabela FACT ou principal para uma consulta. O hint NO_FACT realiza o oposto. O hint STAR é usado apenas para acessar de modo eficaz a tabela FACT na junção de várias tabelas. Os hints de transformação da consulta são os seguintes:
FACT MERGE NO_EXPAND
NO_FACT NO_MERGE NOREWRITE
REWRITE STAR USE_CONCAT
STAR_TRANSFORMATION
Usando Hints de operação e junção
O agrupamento das operações de junção mostra como as tabelas unidades mesclam dados. Uma operação de junção, como USE_MERGE ou USE_HASH, pode ser melhor para apanhar todas as linhas para uma consulta (vazão), enquanto USE_NL pode ser melhor para apanhar a primeira linha (tempo de resposta). Os hints de operação e junção são os seguintes:
DRIVING_SITE HASH_AJ HASH_SJ
LEADING MERGE_AJ MERGE_SL
NL_AJ NL_SJ ORDERED
PUSH_SUBQ USE_HASH USE_MERGE
USE_NL
Usando a execução paralela
O agrupamento de execução paralela aplica-se a bancos de dados usando a opção paralela. Estes hints redefinem a especificação da tabela para o grau de paralelismo. Os hints de execução paralela são os seguintes:
NOPARALLEL NOPARALLEL_INDEX PARALLEL
PARALLEL_INDEX PQ_DISTRIBUTE
Usando outros Hints
Os hints APPEND e NOAPPEND podem ser usados sem a opção paralela, mas constantemente são usados com ela. O agrupamento de cachê diz respeito aos hints que colocarão itens como usados mais recentemente (CACHE) e usados menos recentemente (NOCACHE). Os hints são os seguintes:
APPEND CACHE CURSOR_SHARING_EXACT
NOAPPEND NO_UNNEST NO_PUSH_PRED
NOCACHE PUSH_PRED ORDERED_PREDICATES
UNNEST
Especificando um hint
Se o hint por alguma razão for especificado de forma incorreta, ele se torna um simples comentário e será ignorado. Para evitar este problema, sempre que você especificar um hint, execute um plano de execução ou especifique AUTOTRACE = ON para ver se o hint foi mesmo utilizado.
Sintaxe
Select /*+ FULL(tabela) */ coluna1, . . .
Sendo que a tabela na sintaxe acima corresponde a tabela que será realizada um FULL TABLE SCAN.
Exemplo
Select /*+ FULL(emp) */ empno, ename, deptno
From emp
Where deptno = 1;
Sendo que se houvesse um índice na coluna deptno, um FULL TABLE SCAN seria realizado de forma a ignorar o índice e prevalecer o hint.
Veremos os hints mais utilizados por DBAs e desenvolvedores segundo pesquisa informal na TUSC. Detalhes de outros hints podem ser encontrados na documentação da ORACLE ou em livros específicos deste assunto. A seguir a lista dos dez mais da TUSC, em ordem de uso:
Especificando múltiplos Hints
Você pode usar mais de um hints de cada vez, embora isso possa fazer com que algum ou todos os hints sejam ignorados. A sintaxe básica é separar os hints com espaços.
Sintaxe
Select /*+ FULL(tabela) CACHE(tabela) */ coluna1, . . .
Sendo que a tabela na sintaxe acima corresponde a tabela que será realizada um FULL TABLE SCAN e o CACHE.
Exemplo
Select /*+ FULL(emp) CACHE (emp) */ empno, ename, deptno
From emp
Where deptno = 1;
A especificação de vários hints que entram em conflito entre si faz com que a consulta não use esses hints.
Usando uma alias
Quando você usa aliases sobre determinada tabela que deseja usar em um hint, precisa especificar o alias e não o nome da tabela no hint. Se você especificar o nome da tabela no hint quando um alias for usado, o hint será desconsiderado.
Sintaxe
Select /*+ FULL(A) */ coluna1, from tabela1 as A
Usando o hint INDEX
Este hint é utilizado para forçar que um ou mais índices sejam executados para determinada consulta. Você pode especificar um ou mais índices com este hint, e o ORACLE escolherá um ou mais índices especificados com base no melhor plano de execução. Caso você especifique apenas um, o otimizador considerará apenas um índice.
Sintaxe
Select /*+ INDEX (tabela indice1, indice2,...) */ coluna1, . . .
Exemplo
Select /*+ INDEX (emp deptno_idx) */ empno, ename, deptno
From emp
Where deptno = 1;
Neste exemplo o índice deptno_idx será utilizado.
Usando o hint ORDERED
Este hint faz com que as tabelas sejam acessadas em uma ordem específica, com base na ordem das tabelas na cláusula FROM da consulta. Ao contrário do que consta na documentação, ele pode ser usado para a otimização baseada em custo ou em regras.
Sintaxe
Select /*+ ORDERED (tabela indice1, indice2,...) */ coluna1, . . .
Exemplo
Select /*+ INDEX (emp deptno_idx) */ empno, ename, deptno
From emp
Where deptno = 1;
Neste exemplo o índice deptno_idx será utilizado.
Usando o hint PARALLEL
Este hint faz com que consultas FULL SCAN seja divididas em partes ( o grau de paralelismo) e procedam cada parte com um processo diferente do sistema operacional. A DML paralela só pode ser usada quando você instala a opção de particionamento (Partitioning Option) com o seu banco de dados. O Grau de paralelismo é aplicado a cada operação de uma instrução SQL.
É possível especificar o número desejado de servidores simultâneos que podem ser usados para uma operação paralela. Pode-se especificar este hint às partes INSERT, UPDATE e DELETE de uma instrução. É necessário que na criação das tabelas, você tenha utilizado a cláusula parallel.
Sintaxe
Select /*+ PARALLEL (tabela, DEGREE, INSTANCES) */ , . . .
O grau é o número de partes em que a consulta é dividida. A instância (o segundo número especificado após o grau) é o número de instâncias usadas.
Exemplo
Select /*+ INDEX (emp deptno_idx) */ empno, ename, deptnob
From emp
Where deptno = 1;
Neste exemplo o índice deptno_idx será utilizado.
Usando o hint FIRST_ROWS
Este índice direciona uma consulta para ser otimizada com base na recuperação mais rápida da primeira linha. Este tipo de índice é muito útil quando o desenvolvedor faz uma interface para o usuário que apanha um único registro no banco de dados. E seria uma péssima opção para sistemas de relatórios, onde um número muito grande de registros é selecionado.
Este índice é ignorado quando utilizamos as instruções UPDATE e DELETE, pois todas as linhas recuperadas serão atualizadas ou excluídas. Também será ignorado quando utilizamos qualquer função de agrupamento (GROUP BY, DISTINCT, INTERSECT, MINUS, UNION), pois todas as linhas para o agrupamento precisam ser recuperadas.
Sintaxe
Select /*+ FIRST_ROWS (n) */ coluna1, . . .
Exemplo
Select /*+ FIRST_ROWS (10) */ empno, ename, deptno
From emp
Where deptno = 1;
Usando o hint RULE
Cada hint que é emitido causa o uso uso do otimizador baseado em custom, exceto o hint ROLE que faz com que o otimizador use a otimização baseada em regra. Isso quer dizer que a distribuição dos dados na tabela e nos índices não é considerada. Ao invés disto, o otimizador executa a consulta com base em um conjunto de regras predefinidas pelo ORACLE. Com exceção aos hints DRIVING_SITE e ORDERED, todos os outros hints serão ignorados quando utilizado o hint RULE.
Sintaxe
Select /*+ RULE */ coluna1, . . .
Exemplo
Select /*+ RULE */ empno, ename, deptno
From emp
Where deptno = 1;
Usando o hint FULL
Este hint instrui a consulta a desconsiderar o otimizador e realizar uma varredura completa da tabela. O hint FULL possui uma funcionalidade diferente, com base na consulta que você esta ajustando. Você ´pode usa-lo para forçar uma verredura completa quando uma grande parte da tabela estiver sendo consultada. O custo da leitura do índice e das linhas pode ser maior do que simplesmente ler a tabela inteira. Este hint pode causar um resultado inesperado. Causar uma varredura de tabela completa pode fazer com que as tabelas sejam acessadas em uma ordem diferente, pois uma tabela principal diferente é usada. Isso pode gerar um desempenho melhor, fazendo-o pensar que a varredura de tabela completa foi o benefício principal, quando a mudança da ordem da tabela principal foi a causa real do melhor desempenho.
Sintaxe
Select /*+ FULL(tabela) */ coluna1, . . .
Exemplo
Select /*+ FULL(emp) */ empno, ename, deptno
From emp
Where deptno = 1;
Usando o hint LEADING
A medida que a complexidade das consultas aumenta, torna-se mais dificil descobrir a ordem de todas as tabela usando o hint ORDERED. Você normalmente pode descobrir qual tabela deve ser acessada primeiro (tabela principal), mas pode não saber qual tabela acessar depois dessa. A hint LEADING permite que você especifique uma tabela para controlar a consulta; o otimizador descobre qual tabela usar depois dela. Se você especificar mais de uma tabela com esse hint, ela será ignorada. O hint ORDERED cancela o hint LEADING.
Sintaxe
Select /*+ LEADING (tabela1) */ coluna1, . . .
Exemplo
Select /*+ LEADING (DEPT) */ emp.empno, ename, dept.deptno, itemno
From emp, dept, orders
Where emp.deptno = dept.deptno
and emp.empno = orders.empno
and dept.deptno = 1
and emp.empno = 7747
and orders.ordno = 45;
Usando o hint USE_NL
Este hint normalmente é o modo mais rápido de retornar uma única linha (tempo de resposta); assim, ela é conseqüentemente mais lenta no retorno de todas as linhas. Este hint faz com que uma instrução seja processada usando loops aninhados, que torna a primeira linha combinando de uma tabela, com base no resultado de outra tabela. Isso é o oposto de uma junção por mesclagem, que apanha linhas que correspondem às condições de cada tabela e depois mescla – isso normalmente leva mais tempo para obter a primeira linha.
Sintaxe
Select /*+ USE_NL(tabela indice1, índice2) */ coluna1, . . .
Exemplo
Select /*+ USE_NL(dept) */ empno, ename, dept.deptno
From emp, dept
Where emp.deptno = dept.deptno
and dept.deptno = 1
and emp.empno = 7747;
Usando o hint APPEND
Este hint é ótimo para ser utilizado se você tiver espaço para queimar. Ele não verifica se existe espaço dentro dos blocos atualmente usados para instruções, mas, em vez disso, anexa os dados aos novos blocos. Você potencialmente poderia desperdiçar espaço, mas ganhará velocidade em retorno. Se você nunca exclui linhas de uma tabela, definitivamente deve usar APPEND.
Se você utilizar o carregamento em paralelo com o SQL LOADER, você terá que usar a opção APPEND. Além disso, se um INSERT tiver paralelismo com o hint PARALLEL, APPEND será utilizado como padrão. Você poderá usar o hint NOAPPEND para cancelar este comportamento.
Sintaxe
insert /*+ APPEND*/ …
Exemplo
insert /*+ APPEND */
into emp (empno, deprno)
values (7747,10);
Usando o hint USE_HASH
Este hint normalmente é o modo mais rápido de unir muitas linhas de várias tabelas, se você tiver memória para esta operação. USE_HASH é semelhante aos loops aninhados, onde o resultado de uma tabela é percorrido através do resultado da tabela unida. A diferença aqui é que a segunda tabela (aquela sendo percorrida) é colocada na memória. Você precisa ter um HASH_AREA_SIZE e PGA_AGGREGATE_TARGET grande o bastante para que isto funcione corretamente, caso contrário, a operação ocorrera no disco e pode não apresentar uma boa performance.
Sintaxe
Select /*+ USE_HASH(tabela1) */ coluna1, . . .
Exemplo
Select /*+ USE_HASH(dept) */ empno, ename, dept.deptno
From emp, dept
Where emp.deptno = dept.deptno
and emp.empno = 7747;
Problemas com hints
Freqüentemente descobrimos que um hint não se comporta como gostaríamos. As vezes o otimizador cancela o hint, mas normalmente as pessoas tem um problema relacionado aos seguintes fatos:
Sintaxe incorreta
A tabela não esta analisada (analyze table)
Existe um conflito com outro hint
Você não esta no modo do otimizador baseado em custo
O hint exige que um parâmetro de inicialização seja definido para que funcione
Existe um alias para a tabela e você utilizou o nome da tabela no hint
O hint exige uma versão diferente da que você possui
Você não entende a aplicação correta do hint
Existe um bug no software
Resumos de todos os Hints
HINT USO
CHOOSE Força a otimização baseada em custo
RULE Força a otimização baseada em regra
FIRST_ROWS Geralmente, força o uso de índices
ALL_ROWS Geralmente, força uma varredura de tabela completa
FULL Força uma varredura de tabela completa
INDEX Força o uso de um índice
NO_INDEX Evita que um índice especificado seja usado
INDEX_JOIN Permite a mistura de índices de uma única tabela
INDEX_ASC Usa um índice classificando em ordem crescente
INDEX_DESC Usa um índice classificando em ordem decrescente
AND_EQUAL Acessa vários índices b-tree
INDEX_COMBINE Acessa vários índices bitmap
INDEX_FFS Força varreduras completas rapidas
ORDERED Especifica a ordem principal das tabelas
LEADING Especifica apenas a primeira tabela principal
ORDERED_PREDICATES Força a ordem do predicado
NO_EXPAND Ajuda a eliminar a expansão do OR
ROWID Vai para o local físico exato para pegar a informação
STAR Força um plano de consultas em estrela
DRIVING_SITE Processa dados apanhando-os de um bloco de dados em particular
USE_MERGE Alterar o modo como as tabelas são unidas internamente
PUSH_SUBQ Força a subconsulta a ser processada antecipadamente
PARALLEL Faz com que as consultas de varredura de tabela completa dividam a consulta em partes e processem cada parte com um processo diferente
NO_PARALLEL Desativa o uso de operações paralelas em qualquer consulta para uma tabela especificada para usar operações paralelas
APPEND Anexa dados em novos blocos
NOAPPEND Verifica o espaço livre dos blocos atuais antes de usar novos blocos
CACHE Faz com que uma varredura de tabela completa seja fixada na memória
NOCACHE Faz com que uma tabela especificada seja colocada em cachê no nível de banco de dados para não ser colocada em cachê quando você o acessa
CLUSTER Força o agrupamento de clusters
HASH Força o hashing do cluster
CURSOR_SHARING_EXACT Cancela a definição de CURSOR_SHARING
Douglas Freire - MADMAX
|