Colegas segue abaixo um manual bem explicado de todos os tipos de HINTS que podemos usar em Banco Oracle. Douglas Freire - MADMAX

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

Quebra-Linha

Colaborador..: Douglas Freire
Categoria(s).: Oracle; Banco de Dados;
Versão.......: 8x,9x, 10g
Data.........: 10/07/2007 16:13:17
Visualizado..: 19652 vezes
Fonte........: Douglas Freire - MADMAX