Artigos Banco de Dados Tuning no Oracle

Finalmente vamos trocar algumas idéias sobre o assunto mais desafiador e interessante, na minha opinião, do Oracle: Tuning.

Tentarei redigir este artigo (o primeiro da série) de forma a ser útil às diversas áreas de interesse, desde o gerente de T.I., arquiteto de sistemas, Solution Provider, etc... Porém, especialmente útil para os desenvolvedores e DBAs.

Desde já adianto que não existe uma fórmula para um tuning no banco de dados, dificilmente um trabalho de tuning será igual ao outro. No entanto, todos serão fruto de pesquisa e análise por isso, neste artigo abordarei apenas exemplos didáticos de casos ocorridos, e nos que o sucederem, abordarei conceitos e o estudo de alguns casos práticos, onde o leitor poderá, inclusive, acompanhar de um servidor Oracle local.

O que é tuning?

Imaginemos uma viagem de lua-de-mel para o litoral, onde estava no programa levar a sogra, com seu cachorrinho de estimação, e a respectiva acompanhante (do cachorrinho), o sobrinho (que nunca viu o mar) e ainda bagagem para 30 dias (quando o passeio durará 7 dias).

Imaginemos também que o noivo fique com todo o CUSTO (guarde esta palavra) excessivo dessa lua de mel. Provavelmente, e por motivos óbvios, ele comece a cortar gastos. Ele percebe que a sogra, o sobrinho e tanta bagagem são totalmente desnecessários à viagem. Nesse momento ele fez um TUNING de gastos na viagem, ou seja, ele corta todos os CUSTOS desnecessários para que a meta, no caso a lua-de-mel, seja bem sucedida.

Tuning é isso, nas mais diversas áreas da vida. Focando em banco de dados, nada mais é que eliminar todo e qualquer CUSTO desnecessário na execução de uma ou mais transações.

Esse custo excessivo pode ser eliminado através de diversas soluções, tais como: criação e deleção de índices, alteração de parâmetros (inclusive de memória), remodelagem física de tabelas, geração de estatísticas, reescrita de códigos SQL entre outros. Estes assuntos serão abordados neste artigo ou nos próximos.

Planos de execução

Quando, por exemplo, realizamos uma transação de consulta no Oracle, ou mesmo transações de manipulação de dados, cria-se previamente um plano para a execução da transação propriamente dita, ou seja, o melhor caminho ou o jeito mais fácil que o otimizador ( Nota 1 ) do Oracle acredita ser para alcançar o resultado da consulta.

Otimizador : Não tentemos entendê-lo como uma entidade, mas sim como um conjunto de informações coletadas, na maioria das vezes, através das estatísticas do banco de dados, responsável pela tomada de decisão do melhor plano de acesso das transações no Oracle.

Claro que o Oracle não é perfeito e, às vezes (muito às vezes), não escolhe o melhor plano de acesso para uma determinada transação.

É neste momento que entra o DBA. Este ao perceber o engano do otimizador poderá, por sua própria conta e risco (e méritos também), forçar o Oracle a adotar um plano de acesso diferente através de uma alternativa chamada HINT, que será vista posteriormente.

Para se obter um plano de execução, deve-se usar o comando explain plan, cujo exemplo se encontra na Listagem 1.


 SQL> EXPLAIN PLAN FOR
   2  SELECT *
   3  FROM   emp e, dept d
   4  WHERE  e.deptno = d.deptno
   5  AND    e.ename  = 'SMITH';
  
 Explained.
 Plan Table
 --------------------------------------------------------------------------------
 | Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
 --------------------------------------------------------------------------------
 | SELECT STATEMENT          |          |       |      |        |       |       |
 |  NESTED LOOPS             |          |       |      |        |       |       |
 |   TABLE ACCESS FULL       |EMP       |       |      |        |       |       |
 |   TABLE ACCESS BY INDEX RO|DEPT      |       |      |        |       |       |
 |    INDEX UNIQUE SCAN      |PK_DEPT   |       |      |        |       |       |
 --------------------------------------------------------------------------------
  
 8 rows selected.
Listagem 1. Geração do plano de execução

Vamos entender o que a Listagem 1 nos mostra:

  • SELECT STATEMENT: inicialmente o Oracle identifica o tipo de transação;
  • NESTED LOOPS: informa, se houver, o ‘Join Method’ (será visto ainda nesse artigo);
  • TABLE ACCESS FULL: método de acesso à primeira tabela da consulta (após a cláusula FROM);
  • TABLE ACCESS BY INDEX ROWID: método de acesso à segunda tabela da consulta;
  • INDEX UNIQUE SCAN: detalhamento do método de acesso TABLE ACCESS BY INDEX ROWID (percebam a identação), contendo o nome do índice usado.

Na ‘tabela’ formatada pelo Explain Plan temos também:

  • Rows: número de linhas selecionadas;
  • Bytes: total de bytes (somatória dos blocos) selecionados;
  • Cost: custo gerado pela etapa da execução;
  • Pstart / Pstop: implementado na versão 9i e usado em tabelas particionadas, mostra as partições lidas, inicial e final.

Claro que temos planos de execução bem mais complexos, mas a idéia foi passada. Vejamos agora os principais métodos de junção, que são importantíssimos para criar uma boa consulta.

Nested Loop

O Nested Loop é o método de junção mais vantajoso para realizar a junção de tabelas as quais retornarãopoucos registros e ainda claramente relacionadas através de dependência entre as mesmas tabelas (chaves primária e estrangeira). Desta forma, o otimizador elege uma ‘tabela principal’, que servirá de base para buscar os registros na tabela dependente. Para cada registro existente na tabela principal, o Oracle buscará os registros na tabela dependente que atendam às condições de filtro (cláusula WHERE). Veja um exemplo na Listagem 2.


SELECT e.nome, e.sobrenome, d.departamento
FROM empregados e, departamentos d
WHERE e.dep_num = d.de_num
AND e.emp_num = 15;
Listagem 2. Uma consulta simples

O otimizador dividirá este comando em duas consultas menores, sendo a principal representada na Listagem 3.

A dependência entre as tabelas é notada claramente na consulta, visto que é desnecessário recuperar informações na tabela “departments” referente a empregados que não sejam aqueles retornados pela consulta da Listagem 3, ou seja, nada será retornado da tabela “departments” sobre funcionários que não possuam emp_num=15.


SELECT e.nome, e.sobrenome
FROM empregados e
WHERE e.emp_num = 15
Listagem 3. Consulta principal, após divisão do otimizador

Para cada registro retornado pela consulta principal, o otimizador aplicará a consulta dependente, definida na Listagem 4, para gerar o resultado final.

A consulta da Listagem 4 poderá ser chamada de ‘consulta dependente’ pois, como vimos, é diretamente dependente da consulta anterior.


SELECT d.departamento
FROM   departamentos d
WHERE  e.dep_num = d.dep_num
Listagem 4. Consulta dependente, após divisão do otimizador

Merge Join

Também chamado de SORT_MERGE_JOIN, um MERGE JOIN basicamente classifica todas as linhas relevantes na primeira tabela pela chave JOIN ( Nota 2 ), bem como as linhas relevantes na segunda tabela também pela chave JOIN, e funde então estas linhas classificadas.

Exemplo! Em uma venda de garagem você pode comprar 400 livros. O negócio é comprar tudo ou nada. Você resolve comprar tudo. Agora, você tem que encontrar os livros que você já tem em casa. Como você faria isso?

Provavelmente, você faria um MERGE JOIN, ou seja, primeiramente você classifica os livros que tem em casa pela chave primária (autor, título), então você classifica os 400 livros por sua chave primaria (autor, título).

Agora você começa pelo alto de ambas as pilhas. Se o valor da chave primaria da pilha esquerda for mais elevado, então retira um livro da pilha direita e vice-versa. Quando ambos os valores das pilhas são iguais, você encontrou as duplicidades.

É exatamente este o processo que o Oracle executa nos Merge Joins.

Nota : Chave JOINNada mais é do que a condição na cláusula WHERE da consulta onde duas tabelas são relacionadas através do sinal de = (igual), entre dois campos em comum

Hash Join

O otimizador do Oracle usa (ou pelo menos deveria usar) este método para realizar a junção entre tabelasquando um grande número de linhas será retornado para gerar o resultado.

Normalmente ele usa esse método de junção de tabelas quando são encontradas as estatísticas para as mesmas, no entanto, ele não usará o Hash Join quando o operador não for de igualdade.

O otimizador escolhe a menor tabela para construir uma tabela em memória utilizando os campos que compõem a chave da junção descrita na consulta SQL.

A chave de junção é composta pelos campos utilizados para relacionar as tabelas envolvidas na operação. Em seguida, o otimizador busca na outra tabela da consulta, relacionada pela chave da junção, recuperar os registros que atendem às condições e compara com os dados da tabela criada em memória, também conhecida como TABELA HASH.

Nota: Na verdade, o otimizador do Oracle 9i adora o Hash Join, já perdi a conta das vezes em que precisei adicionar HINT para forçar o Oracle a usar outro método de junção. Veremos mais à frente, mas só para adiantar, HINT é uma espécie de coelho tirado da cartola para forçar o otimizador do Oracle a executar um plano de execução diferente do que ele vem realizando.

Sort Merge

O Sort Merge costuma ser a última opção do otimizador. Quando não existe relação de dependência entre as tabelas o otimizador não usará o Nested Loop.

Se o otimizador perceber que as tabelas já estão ordenadas, então ele (provavelmente) também não usará o Hash Join.

Outra situação a qual o Sort Merge deve ser utilizado é quando os operadores são <, <=, > ou >= pois, utilizando este método, o otimizador organiza as duas tabelas pelos campos da junção da consulta (obviamente se as mesmas não estiverem organizadas) e posteriormente, simplesmente as une.

Analisando um estudo de caso

Inicialmente vamos criar a nossa VIEW de visualização dos planos de execução. Execute o script conforme Listagem 5.


SQL> @<>
SQL> View Criada
SQL> Select * from plan_view;
Listagem 5. Criação da Plan View e execução da mesma

Temos na Listagem 5 acriação da plan_view e consequente execução da mesma, atentando para os schemas de execução e criação. Quem preferir também pode usar o comando:


select * from table(DBMS_XPLAN.DISPLAY);

Mãos à obra

Vamos analisar uma consulta problemática (obviamente modificada por motivos de segurança)( Listagem 6 ).Nesta consulta três tabelas são consultadas e unidas através da chave JOIN.


SELECT object_status.object_status_id, reg1_sec_20051201.account_id,
   reg1_sec_20051201.create_dttm, reg1_sec_20051201.record_type,
   reg1_sec_20051201.recordaction, reg1_sec_20051201.billpublisherid,
   reg1_sec_20051201.proccenterid, reg1_sec_20051201.billerid,
   reg1_sec_20051201.alt_acct_num, reg1_sec_20051201.eb_activity_amt,
   reg1_sec_20051201.eb_amount_due, reg1_sec_20051201.amount_due,
   reg1_sec_20051201.amount_due_flag, reg1_sec_20051201.eb_bal_amt,
   reg1_sec_20051201.bal_amt, reg1_sec_20051201.bal_amt_flag,
   reg1_sec_20051201.eb_min_due, reg1_sec_20051201.eb_prev_bal,
   reg1_sec_20051201.eb_ebillid, reg1_sec_20051201.eb_duedate_tms,
   reg1_sec_20051201.eb_opening_tms, reg1_sec_20051201.ref_text,
   reg1_sec_20051201.eb_tms, reg1_sec_20051201.eb_closing_tms,
   reg1_sec_20051201.eb_url, reg1_sec_20051201.eb_tlnk_url,
   reg1_sec_20051201.eb_tad_url, reg1_sec_20051201.eb_ttext,
   reg1_sec_20051201.fi_tran_tms, reg1_sec_20051201.eb_mag_type,
   reg1_sec_20051201.errorcode, reg1_sec_20051201.errormessage,
   status_code.status_code_name, object_status.status_user_name,
   object_status.status_process_name
FROM reg1_sec_20051201, object_status, status_code
WHERE object_status.object_status_id = reg1_sec_20051201.object_status_id 
–---Olha a Chave JOIN!!
AND object_status.status_code_id = status_code.status_code_id;   
---E Outra aqui!!
Listagem 6. Consulta problemática

Vamos agora observar seu plano de execução ( Listagem 7 ).


SELECT *
  FROM PLAN_VIEW;
Plan Table
----------------------------------------------------------------------------------------------
| Operation                       |  Name              | Rows | Bytes| Cost  | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| SELECT STATEMENT                |                    |   16M|    9G|2993642|       |       |
|  HASH JOIN                      |                    |   16M|    9G|2993642|       |       |
|   TABLE ACCESS FULL             |STATUS_CODE         |    87|    1K|     2 |       |       |
|   MERGE JOIN                    |                    |   16M|    9G|2976102|       |       |
|    TABLE ACCESS BY INDEX R      | reg1_sec_20051201  |   16M|    9G|2429116|       |       |
|     INDEX FULL SCAN             | reg1_sec_20051201_ |   16M|      |  37772|       |       |
|    SORT JOIN                    |                    |   19M|  792M| 546986|       |       |
|     TABLE ACCESS FULL           |OBJECT_STATUS       |   19M|  792M|  34032|       |       |
----------------------------------------------------------------------------------------------
Listagem 7. Plano de execução da consulta da Listagem 6

Perceba que temos duas ocorrências de FULL TABLE SCAN (linha TABLE ACCESS FULL). Neste evento, o Oracle varre todos os blocos de uma tabela para selecionar apenas os blocos solicitados na consulta.

Será que um índice seria interessante nesta consulta?

Se o filtro nesta tabela não for muito seletivo ou se a tabela for considerada pequena, o Oracle acredita (e na maioria das vezes tem razão) que o TABLE ACCESS FULL será mais rápido, mesmo se a coluna da tabela pesquisada tiver um índice. No entanto, estatísticas falsas ( Nota 3 ) também podem contribuir para uma má escolha do otimizador no acesso à tabela.

Vamos então descobrir o porquê do TABLE ACCESS FULL. Inicialmente vejamos o número de linhas nas tabelas.

A primeira não tem nem graça.Como o próprio plano de execução mostra, ela possui apenas 87 linhas. Já a segunda possui quase 20 milhões de registros (19M). Este é o ponto a ser melhorado!

Analisando a seletividade, vamos verificar a quantidade de linhas retornadas nesta consulta ( Listagem 8 ).

Nota : Estatísticas Rapidamente, podemos dizer que as estatísticas do Oracle (e em outros bancos também) são um conjunto de informações colhidas do próprio banco de dados para ajudar o otimizador na tomada de decisão. As estatísticas no Oracle podem ser obtidas através dos comandos Analyze e do comando DBMS_STATS.Para conceituarmos estatísticas no Oracle teríamos que ser bem mais amplos, daria com certeza para escrevermos um artigo sobre o assunto. O que você acha? Caso positivo, sinta-se à vontade para me enviar um e-mail.

SELECT count(object_status.object_status_id)
FROM sis2070_rcd_obj, object_status, status_code
WHERE object_status.object_status_id = sis2070_rcd_obj.object_status_id
  AND object_status.status_code_id = status_code.status_code_id
 
count(object_status.object_status_id)
--------------------------------------------
16734839
Listagem 8. Verificando o número de registros na tabela

A seletividade está muito alta ( Nota 4 ), cerca de 17 milhões de linhas selecionadas de uma tabela de 20 milhões (usando fórmula: linhas da tabela retornadas na consulta/linhas da tabela * 100 teremos 80% da tabela). É por isso que o otimizador jamais usaria um índice mesmo se a coluna tivesse um (e tem!).

Nestes casos, perguntamos ao DA (Data Architect – Arquiteto de dados) ou ao desenvolvedor se ele realmente precisa deste número de linhas selecionadas. Se o mesmo disser que não precisa de todos os dados, então solicitamos a ele que construa um filtro melhor para a consulta, no entanto, se cada linha retornada for necessária teremos de verificar outras formas para torná-la mais rápida. Inicialmente usaremos uma ferramenta do Oracle chamada HINT.

Nota : Seletividade de uma consulta Podemos chamar seletividade como sendo a relação estabelecida entre a quantidade de linhas de uma tabela retornadas por uma consulta com a quantidade total de linhas da mesma tabela. É exatamente através da análise dessa seletividade que o Oracle decide entre usar um índice ou varrer todos os blocos de uma tabela. Por exemplo, imaginemos uma tabela com 1 milhão de linhas: se uma consulta na mesma retorna 900 mil linhas é muito mais rápido o Oracle varrer a tabela toda do que utilizar um índice. Apenas para termos um parâmetro, qualquer seletividade acima de 10% do valor total de linhas de uma tabela é considerada alta, e dificilmente o Oracle utilizará um índice nessa consulta.

Supondo que no campo status_code_id tenhamos o índice OBJECT_STATUS_NK1_X e no campo object_status_id tenhamos o índice OBJECT_STATUS_NK2_X. Com o HINT de índice, a nossa consulta ficaria conforme Listagem 9.

Atentando que, para consultas com alta seletividade, o uso de índices não é aconselhado, portanto estamos apenas ilustrando uma situação.


SELECT /*+ index(object_status OBJECT_STATUS_NK1_X OBJECT_STATUS_NK2_X) */
  object_status.object_status_id, reg1_sec_20051201.account_id,
  ...
  ...
FROM reg1_sec_20051201, object_status, status_code
WHERE object_status.object_status_id = reg1_sec_20051201.object_status_id
 AND object_status.status_code_id = status_code.status_code_id;E o plano de execução seria….
 
Plan Table
------------------------------------------------------------------------------------------------
| Operation                    |  Name                  | Rows | Bytes|  Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| SELECT STATEMENT             |                        |   16M|    9G|3266651 |       |       |
|  HASH JOIN                   |                        |   16M|    9G|3266651 |       |       |
|   TABLE ACCESS FULL          |STATUS_CODE             |    87|    1K|      2 |       |       |
|   MERGE JOIN                 |                        |   16M|    9G|3249111 |       |       |
|    TABLE ACCESS BY INDEX R   |reg1_sec_20051201       |   16M|    9G|2429116 |       |       |
|     INDEX FULL SCAN          |reg1_sec_20051201_NK1_X |   16M|      |  37772 |       |       |
|    SORT JOIN                 |                        |   19M|  792M| 819995 |       |       |
|     TABLE ACCESS BY INDEX    |OBJECT_STATUS           |   19M|  792M| 306989 |       |       |
|      INDEX FULL SCAN         |OBJECT_STATUS_NK2_X     |   19M|      |  68177 |       |       |
------------------------------------------------------------------------------------------------
Listagem 10. Aconsulta problemática com uma HINT forçando a utilização de um índice

Acabamos de comprovar a obediência do Oracle, ou seja, até mesmo quando solicitamos a ele uma manobra ‘absurda’, ele segue a risca o solicitado, ou seja, ele contrariou totalmente o otimizador (que estava coberto de razão) e passou a usar índice na tabela OBJECT_STATUS.

Além disso, podemos verificar se o tempo de resposta seria melhor se mudássemos todos os métodos de junção para Hash Join. Neste caso a nossa consulta ficaria conforme Listagem 10.


SELECT /*+ USE_HASH (object_status status_code reg1_rcd_obj) */
       object_status.object_status_id, reg1_sec_20051201.account_id,
       ...
       ...
  FROM reg1_sec_20051201, object_status, status_code
 WHERE object_status.object_status_id = reg1_sec_20051201.object_status_id
   AND object_status.status_code_id = status_code.status_code_id;
 
Plan Table
------------------------------------------------------------------------------------
| Operation            | Name              |  Rows | Bytes|  Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------
| SELECT STATEMENT     |                   |   161K|   10M| 560145 |       |       |
|  HASH JOIN           |                   |   161K|   10M| 560145 |       |       |
|   TABLE ACCESS FULL  | STATUS_CODE       |     87|    1K|      2 |       |       |
|   HASH JOIN          |                   |   161K|    8M| 560124 |       |       |
|    TABLE ACCESS FULL | reg1_sec_20051201 |   161K|    7M| 246024 |       |       |
|    TABLE ACCESS FULL | OBJECT_STATUS     |    19M|  151M|  34035 |       |       |
------------------------------------------------------------------------------------
Listagem 10. Aconsulta problemática com uma HINT forçando um HASH JOIN

Perfeito, conforme o HINT utilizado, o método de junção para todas as tabelas foi o HASH JOIN.

Para melhorar ainda mais, vamos paralelizar a consulta, ou seja, vamos mandar o Oracle utilizar um número maior de processadores na consulta ( Listagem 11 ).

Lembrando que esta mudança no plano de execução não irá diminuir o custo da consulta, ou seja, o esforço que o Oracle realizou para executar a consulta. A vantagem no paralelismo é que teremos mais de um processador lendo blocos de dados ao mesmo tempo. Só para exemplificar, é como ter dois carros tendo que percorrer 50 KM cada, ao invés de apenas um carro tendo que percorrer 100 Km.


  SELECT /*+ PARALLEL(object_status, 5) */
       object_status.object_status_id, reg1_sec_20051201.account_id,
       ...
       ...
  FROM reg1_sec_20051201, object_status, status_code
  WHERE object_status.object_status_id = reg1_sec_20051201.object_status_id
   AND object_status.status_code_id = status_code.status_code_id;
Listagem 11. Aconsulta problemática paralelizada

Neste caso, o Oracle usará cinco processadores na varredura ou FULL SCAN da tabela OBJECT_STATUS.

Pra finalizar, vamos juntar todas as HINTS e verificar o plano de execução, inclusive a não aconselhada na Listagem 9. Como dissemos, neste momento o que importa é conhecermos as possibilidades. ( Listagem 12 ).


SELECT /*+ index(object_status OBJECT_STATUS_NK1_X OBJECT_STATUS_NK2_X) 
USE_HASH(object_status status_code reg1_sec_20051201) PARALLEL(object_status, 5) */
object_status.object_status_id, reg1_sec_20051201.account_id,
       reg1_sec_20051201.create_dttm, reg1_sec_20051201.record_type,
       reg1_sec_20051201.recordaction, reg1_sec_20051201.billpublisherid,
       reg1_sec_20051201.proccenterid, reg1_sec_20051201.billerid,
       reg1_sec_20051201.alt_acct_num, reg1_sec_20051201.eb_activity_amt,
       reg1_sec_20051201.eb_amount_due, reg1_sec_20051201.amount_due,
       reg1_sec_20051201.amount_due_flag, reg1_sec_20051201.eb_bal_amt,
       reg1_sec_20051201.bal_amt, reg1_sec_20051201.bal_amt_flag,
       reg1_sec_20051201.eb_min_due, reg1_sec_20051201.eb_prev_bal,
       reg1_sec_20051201.eb_ebillid, reg1_sec_20051201.eb_duedate_tms,
       reg1_sec_20051201.eb_opening_tms, reg1_sec_20051201.ref_text,
       reg1_sec_20051201.eb_tms, reg1_sec_20051201.eb_closing_tms,
       reg1_sec_20051201.eb_url, reg1_sec_20051201.eb_tlnk_url,
       reg1_sec_20051201.eb_tad_url, reg1_sec_20051201.eb_ttext,
       reg1_sec_20051201.fi_tran_tms, reg1_sec_20051201.eb_mag_type,
       reg1_sec_20051201.errorcode, reg1_sec_20051201.errormessage,
       status_code.status_code_name, object_status.status_user_name,
       object_status.status_process_name
  FROM reg1_sec_20051201, object_status, status_code
 WHERE object_status.object_status_id = reg1_sec_20051201.object_status_id
   AND object_status.status_code_id = status_code.status_code_id;
 
 
Plan Table
-------------------------------------------------------------------------------------------
| Operation                  |  Name              |  Rows | Bytes|  Cost  | Pstart| Pstop |
-------------------------------------------------------------------------------------------
| SELECT STATEMENT           |                    |    16M|    9G|    714M|       |       |
|  HASH JOIN                 |                    |    16M|    9G|    714M|       |       |
|   TABLE ACCESS FULL        |STATUS_CODE         |     87|    1K|      2 |       |       |
|   HASH JOIN                |                    |    16M|    9G|    714M|       |       |
|    TABLE ACCESS BY INDEX R |OBJECT_STATUS       |    19M|  792M| 306989 |       |       |
|     INDEX FULL SCAN        |OBJECT_STATUS_NK2_X |    19M|      |  68177 |       |       |
|    TABLE ACCESS FULL       |reg1_sec_20051201   |    16M|    9G| 246024 |       |       |
-------------------------------------------------------------------------------------------
Listagem 12. Aconsulta problemática após o tuning

Notemos tudo conforme solicitado na consulta, ou seja, o método de junção HASH JOIN, bem como os acessos via índice.

É interessante e muito útil percebermos que podemos montar o nosso plano de acesso conforme as nossas necessidades. O domínio dessa técnica multiplica nossas alternativas de solução em problemas de tuning em banco de dados Oracle.

Para que todos possam visualizar se o impacto dessas mudanças foram positivas ou negativas, se o custo e, consequentemente, o tempo de execução aumentou ou diminuiu, seria necessária a reprodução do ambiente dessa consulta, mas ainda não é o momento (além de ser inviável, neste caso). A intenção foi realmente demonstrar as possibilidades e alternativas possíveis alterando o plano de execução. A seguir veremos os scripts necessários para a criação do ambiente, bem como as características de banco de dados, para que todos possamos acompanhar não somente os efeitos no plano de execução mas também os ganhos obtido em cada mudança da maneira mais próxima possível.

Estudo de caso

Nosso estudo será baseado em testes de métrica de tempo e custo. Executaremos a mesma consulta com diversas variantes na execução como: a utilização de métodos de junção diferentes, a utilização ou não de índices, chaves primárias e chaves de junção e, as alterações no plano de execução causadas pela geração ou não de estatísticas de banco de dados, bem como o parâmetro DB_FILE_MULTIBLOCK_READ_COUNT ( Nota 5 ).

Nota : Parâmetro DB_FILE_MULTIBLOCK_READ_COUNTO parâmetro DB_FILE_MILTIBLOCK_READ_COUNT informa para o otimizador quantos blocos o Oracle consegue ler de uma só vez.

Ambiente utilizado

Hardware

  • Um servidor com processador Pentium 4 3.0 ghz;
  • 1 Gb de memória ram;
  • Disco serial ATA.

Software

  • Windows XP;
  • Oracle 9.2.

Definições de banco de dados:

  • Criação das tablespaces EXAMPLE e INDX com gerenciamento local e extents com tamanho uniforme de 20 Mb e 1 Gb de tamanho cada uma;
  • Otimizador configurado como CHOOSE. Esta configuração, como o próprio nome diz, condiciona o método de otimização das transações no Oracle à existência ou não de estatísticas nas tabelas consultadas. Caso tenhamos as tabelas analisadas, então o método escolhido será o ALL_ROWS, caso contrário, o método de otimização aplicado será o RULES, ou mais comumente chamado de regra. Na Parte 3 do artigo nós falaremos um pouco mais sobre os métodos de otimização;
  • Parâmetro DB_FILE_MULTIBLOCK_READ_COUNT = 8 (ver Nota 5 ).

Toda a configuração deste ambiente se encontra no script da seção ‘Scripts para execução’.

Scripts para execução

Primeiramente criaremos a estrutura de armazenamento, ou seja, as tablespaces ( Listagem 13 ).

Após a criação das tablespaces, criaremos o esquema com todos os objetos. Perceba na Listagem 14 que adicionei alguns comentários no decorrer do script para facilitar o entendimento.


CREATE TABLESPACE EXAMPLE DATAFILE
  'C:\ORACLE\ORADATA\EXAMPLE01.DBF' SIZE 1024M;
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 20M SEGMENT SPACE MANAGEMENT  AUTO;

CREATE TABLESPACE INDX DATAFILE
  'C:\ORACLE\ORADATA\INDX01.DBF' SIZE 600M;
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 20M SEGMENT SPACE MANAGEMENT  AUTO;
Listagem 13. Scripts para criação das tablespaces

CREATE USER SQL_MAG_USER  PROFILE DEFAULT
    IDENTIFIED BY sqlmag
    DEFAULT TABLESPACE EXAMPLE
    TEMPORARY TABLESPACE TEMP
    ACCOUNT UNLOCK;
GRANT CREATE SESSION TO SQL_MAG_USER; -- Um pouco de segurança. (Ver Nota 6)

-- Concessão de permissão nas Tablespaces
ALTER USER SQL_MAG_USER
    QUOTA UNLIMITED
    ON EXAMPLE
    QUOTA UNLIMITED
    ON INDX;

ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=8; -- (Ver Nota 1)

CREATE TABLE SQL_MAG_USER.DEPARTAMENTO (DEP_ID NUMBER(2) NOT NULL,
                                        DEP_NOME   VARCHAR2(20) NOT NULL )
TABLESPACE EXAMPLE;

-- Inserção de dados na tabela departamento
insert into SQL_MAG_USER.DEPARTAMENTO values (1,'Presidencia');
insert into SQL_MAG_USER.DEPARTAMENTO values (2,'Diretoria');
insert into SQL_MAG_USER.DEPARTAMENTO values (3,'Gerencia');
insert into SQL_MAG_USER.DEPARTAMENTO values (4,'Vendas');
insert into SQL_MAG_USER.DEPARTAMENTO values (5,'Informatica');
insert into SQL_MAG_USER.DEPARTAMENTO values (6,'Apoio');
insert into SQL_MAG_USER.DEPARTAMENTO values (7,'Secretaria');
insert into SQL_MAG_USER.DEPARTAMENTO values (8,'Limpeza');

COMMIT;

CREATE TABLE SQL_MAG_USER.FUNCIONARIO (FUNC_ID NUMBER(10) NOT NULL,
         FUNC_NOME VARCHAR2(100) NOT NULL,
         FUNC_DAT_CONTRATACAO DATE NOT NULL,
         FUNC_DAT_NASCIMENTO
         DATE NOT NULL,
         FUNC_EST_CIVIL VARCHAR2(2) NOT NULL,
         FUNC_SEXO CHAR(1) NOT NULL,
         DEP_ID NUMBER(2) NOT NULL,
         CAR_ID NUMBER(2) NOT NULL) 
    TABLESPACE EXAMPLE;

-- Inserção de dados na tabela funcionario.
INSERT INTO SQL_MAG_USER.FUNCIONARIO
  VALUES (1 ,'Luis' ,TO_DATE('01/jan/2003', 'dd-Mon-yyyy HH:MI:SS AM') 
  ,TO_DATE('01/mar/1950', 'dd-Mon-yyyy HH:MI:SS AM'), 'Ca' ,'M',1,1  );

...

INSERT INTO SQL_MAG_USER.FUNCIONARIO
  VALUES (5 ,'Marcio' ,TO_DATE('01/jan/2003', 'dd-Mon-yyyy HH:MI:SS AM') 
  ,TO_DATE('01/mar/1950', 'dd-Mon-yyyy HH:MI:SS AM') , 'Ca' ,'M',2,3  );

commit;

-- Bloco PL/SQL para inserção de dados na tabela funcionario.
DECLARE
i PLS_INTEGER := 5;
BEGIN
  WHILE i < 2000
  LOOP
    i := i + 1;
    INSERT INTO SQL_MAG_USER.Funcionario
      VALUES (i,'Pedro' ,TO_DATE('01/jan/05', 'dd-Mon-yyyy HH:MI:SS AM') 
      ,TO_DATE('08/set/77', 'dd-Mon-yyyy HH:MI:SS AM'), 'Ca' ,'M',3,4  );

commit;
 END LOOP;
END;
/

...

/

-- Criação da tabela cargo.
CREATE TABLE SQL_MAG_USER.CARGO (CAR_ID NUMBER(2) NOT NULL,
                                 CAR_NOME VARCHAR2(50) NOT NULL) 
    TABLESPACE EXAMPLE ;

 

-- Inserção de dados na tabela cargo.

insert into SQL_MAG_USER.CARGO values (1,'Presidente');
insert into SQL_MAG_USER.CARGO values (2,'Vice-Presidente');
insert into SQL_MAG_USER.CARGO values (3,'Diretor');
insert into SQL_MAG_USER.CARGO values (4,'Gerente');
insert into SQL_MAG_USER.CARGO values (5,'Vendedor');
insert into SQL_MAG_USER.CARGO values (6,'Analista');
insert into SQL_MAG_USER.CARGO values (7,'Suporte');
insert into SQL_MAG_USER.CARGO values (8,'Secretario');
insert into SQL_MAG_USER.CARGO values (9,'Faxineiro');

COMMIT;
Listagem 14. Scripts para criação do esquema SQL_MAG_USER
Nota : Privilégio de sistema Create Session Na maioria das vezes o privilégio de sistema ‘create session’ é suficiente para um usuário de aplicação no banco de dados.

É comum criarmos usuários no Oracle concedendo a ele as roles connect e resource, mas precisamos analisar se realmente isso é preciso, veja por que:

A role connect consiste em :

  • CREATE VIEW (Nível de risco alto);
  • CREATE TABLE (Nível de risco altíssimo);
  • ALTER SESSION (Nível de risco altíssimo);
  • CREATE CLUSTER (Nível de risco alto);
  • CREATE SESSION;
  • CREATE SYNONYM;
  • CREATE SEQUENCE;
  • CREATE DATABASE LINK (Nível de risco NEM PENSAR!).

Já a role Resource consiste em:

  • CREATE TYPE;
  • CREATE TABLE;
  • CREATE CLUSTER;
  • CREATE TRIGGER (Nível de risco altíssimo);
  • CREATE OPERATOR;
  • CREATE SEQUENCE;
  • CREATE INDEXTYPE;
  • CREATE PROCEDURE (Nível de risco altíssimo);
  • UNLIMITED TABLESPACE (Nível de risco altíssimo).

Imaginemos uma empresa com 10 mil funcionários (uma empresa grande correto?), seguida da tabela de funcionários, cargos e departamentos, criadas acima.

Um gerente pediu a um desenvolvedor distraído que gerasse um relatório de nomes e cargos de todos os funcionários do sexo feminino. Então o desenvolvedor disparou uma consulta na base de dados Oracle. Para fins de análise, vamos executar alguns comandos antes da query para capturar informações que nos serão úteis no tuning ( Listagem 15 ).


SQL> SET TIMING ON -- Para verificarmos o tempo de execução
SQL> SET AUTOTRACE TRACEONLY -- Para verificarmos o plano de execução logo após a execução
SQL> select  FUNC_NOME,car_nome,dep_nome
from sql_mag_user.departamento d,sql_mag_user.funcionario f, sql_mag_user.cargo c
where func_sexo='F';

576000 linhas selecionadas.

Decorrido: 00:00:04.04

Plano de Execução:


----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'CARGO'
   4    2       TABLE ACCESS (FULL) OF 'FUNCIONARIO'
   5    1     TABLE ACCESS (FULL) OF 'DEPARTAMENTO'

576000  rows processed
Listagem 15. Consulta disparada pelo desenvolvedor distraído

Analisando o resultado da consulta temos uma pergunta: como 576000 linhas processadas se a maior tabela possui 8000 linhas?

A explicação é simples, como a chave de junção não existe, o Oracle processa cada linha por cada tabela envolvida na consulta em uma operação cartesiana, realizando assim, um CARTESIAN JOIN. Vejamos na prática:

  • Tabela funcionário = 8000 linhas;
  • Tabela cargo = 9 linhas;
  • Tabela departamento = 8 linhas;
  • 8000 x 9 x 8 = 576000 linhas processadas..

Interessante não?

Mas o desenvolvedor distraído, percebendo o engano, reescreveu a consulta e a passou para o DBA distraído que não gerou estatísticas, não criou índices e não sugeriu chaves estrangeiras. Vamos ver o que ocorreu na Listagem 16.


SQL> select  FUNC_NOME,car_nome,dep_nome
from sql_mag_user.departamento d,sql_mag_user.funcionario f, sql_mag_user.cargo c
where d.DEP_id=f.DEP_id
and c.CAR_id=f.CAR_id
and func_sexo='F';

8000 linhas selecionadas.

Decorrido: 00:00:00.01

Plano de Execução:


----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       MERGE JOIN
   4    3         SORT (JOIN)
   5    4           TABLE ACCESS (FULL) OF 'CARGO'
   6    3         SORT (JOIN)
   7    6           TABLE ACCESS (FULL) OF 'FUNCIONARIO'
   8    1     SORT (JOIN)
   9    8       TABLE ACCESS (FULL) OF 'DEPARTAMENTO'

8000  rows processed

Perceba que de 4 segundos, o tempo da consulta caiu para 0,01 segundos, que faz uma bela diferença, e que a número de linhas processadas também caiu, apenas 8000 linhas. Bem melhor, não?

Mas digamos que o DBA distraído resolveu gerar estatísticas. Vamos analisar agora as Listagens 17 e 18.

Na geração das estatísticas utilizaremos o comando ANALYZE. Ele não é tão completo quanto o pacote DBMS_STATS do Oracle, mas para o que iremos aplicar é suficiente (além de que planejo escrever um artigo só sobre DBMS_STATS no futuro).


ANALYZE TABLE SQL_MAG_USER.FUNCIONARIO COMPUTE STATISTICS;
ANALYZE TABLE SQL_MAG_USER.DEPARTAMENTO COMPUTE STATISTICS;
ANALYZE TABLE SQL_MAG_USER.CARGO COMPUTE STATISTICS;
ANALYZE INDEX SQL_MAG_USER.CARGO_IDX COMPUTE STATISTICS;
ANALYZE INDEX SQL_MAG_USER.DEPARTAMENTO_IDX COMPUTE STATISTICS;
ANALYZE INDEX SQL_MAG_USER.FUNCIONARIO_IDX COMPUTE STATISTICS;
Listagem 17. Geração de estatísticas

SQL> select  FUNC_NOME, car_nome, dep_nome
from sql_mag_user.departamento d, sql_mag_user.funcionario f, sql_mag_user.cargo c
where d.DEP_id=f.DEP_id
and c.CAR_id=f.CAR_id
and func_sexo='F';

8000 linhas selecionadas.


Decorrido: 00:00:00.01
Listagem 18. Nova execução da mesma consulta

Plano de Execução:


------------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=5000 Bytes=120000)
   1    0   HASH JOIN (Cost=36 Card=5000 Bytes=120000)
   2    1     TABLE ACCESS (FULL) OF 'CARGO' (Cost=4 Card=9 Bytes=99)
   3    1     HASH JOIN (Cost=31 Card=5000 Bytes=65000)
   4    3       TABLE ACCESS (FULL) OF 'DEPARTAMENTO' (Cost=4 Card=8 Bytes=16)
   5    3       TABLE ACCESS (FULL) OF 'FUNCIONARIO' (Cost=26 Card=5000 Bytes=55000)

8000 rows processed

Perceba a linha 1 do plano de execução: Hash Join? Mas não era Nested Loop?

A explicação para a mudança no método de junção é simples, como o otimizador esta configurado para o modo CHOOSE (escolha), caso a tabela já tenha estatística gerada ele se utiliza do modo ALL_ROWS, que tem como objetivo retornar todas as linhas da consulta com o menor tempo possível baseado nas informações das estatísticas.

No entanto, como no nosso primeiro caso ainda não tínhamos a estatística gerada, ele usou o modo RULES (um conjunto de regras - quinze ao todo) e estas regras definem o plano de execução.

No nosso exemplo, o modo RULES achou que o método de junção NESTED LOOP era melhor, já o modo ALL_ROWS achou que o método de junção HASH JOIN era melhor. Um comentário importante: a nossa consulta é muito singela, por isso o método de junção não fez diferença. Em consultas mais complexas, o método de junção pode ser a diferença entre o sucesso e o fracasso da consulta no tocante ao tempo de execução.

E se criássemos índices, chaves primárias e chaves estrangeiras, veríamos diferença na nossa consulta? Vejamos nas Listagens 19 e 20. Os scripts abaixo criam índices únicos, as respectivas chaves primárias e estrangeiras. 


CREATE UNIQUE INDEX SQL_MAG_USER.CARGO_IDX ON SQL_MAG_USER.CARGO (CAR_ID)
  LOGGING
  TABLESPACE INDX
  NOPARALLEL;

CREATE UNIQUE INDEX SQL_MAG_USER.DEPARTAMENTO_IDX ON SQL_MAG_USER.DEPARTAMENTO (DEP_ID)
  LOGGING
  TABLESPACE INDX
  NOPARALLEL;

CREATE UNIQUE INDEX SQL_MAG_USER.FUNCIONARIO_IDX ON SQL_MAG_USER.FUNCIONARIO (FUNC_ID)
  LOGGING
  TABLESPACE INDX
  NOPARALLEL;

ALTER TABLE SQL_MAG_USER.CARGO ADD (
  CONSTRAINT CAR_PK PRIMARY KEY (CAR_ID));

ALTER TABLE SQL_MAG_USER.DEPARTAMENTO ADD (
  CONSTRAINT DEP_PK PRIMARY KEY (DEP_ID));

ALTER TABLE SQL_MAG_USER.FUNCIONARIO ADD (
  CONSTRAINT FUNC_DEP_PK PRIMARY KEY (FUNC_ID));

ALTER TABLE SQL_MAG_USER.FUNCIONARIO
    ADD (CONSTRAINT CAR_FK FOREIGN KEY(CAR_ID)
    REFERENCES SQL_MAG_USER.CARGO(CAR_ID)) ;

ALTER TABLE SQL_MAG_USER.FUNCIONARIO
    ADD (CONSTRAINT DEP_FK FOREIGN KEY(DEP_ID)
    REFERENCES SQL_MAG_USER.DEPARTAMENTO(DEP_ID)) ;
Listagem 19. Criando índices e chaves

SQL> select  FUNC_NOME,car_nome,dep_nome
from sql_mag_user.departamento d,sql_mag_user.funcionario f, sql_mag_user.cargo c
where d.DEP_id=f.DEP_id
and c.CAR_id=f.CAR_id
and func_sexo='F';

8000 linhas selecionadas.

Decorrido: 00:00:00.01
Listagem 20. Executando novamente a consulta

Plano de Execução:


-----------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=5000 Bytes=120000)
   1    0   NESTED LOOPS (Cost=31 Card=5000 Bytes=120000)
   2    1     HASH JOIN (Cost=31 Card=5000 Bytes=110000)
   3    2       TABLE ACCESS (FULL) OF 'CARGO' (Cost=4 Card=9 Bytes=99)
   4    2       TABLE ACCESS (FULL) OF 'FUNCIONARIO' (Cost=26 Card=5000 Bytes=55000)
   5    1     INDEX (UNIQUE SCAN) OF 'DEPARTAMENTO_IDX' (UNIQUE)

8000 rows processed

Perceba na linha 1 do plano de execução que o otimizador voltou a utilizar o NESTED LOOP e que, na linha 5, o índice na tabela Departamento foi utilizado.

Neste caso, o otimizador preferiu utilizar o índice desconsiderando (ou não dando a devida importância) a seletividade da consulta. Porém, tendo a ciência de que se tratava de uma tabela pequena, pois a mesma (a tabela) possuía estatísticas.

Mais um teste: se quisermos forçar a não utilização do índice, como faríamos?

Uma das formas e talvez a mais simples será com um Hint (dica). Veja um exemplo na Listagem 21.


SQL> select  /*+ NO_INDEX(d DEPARTAMENTO_IDX ) */ FUNC_NOME,car_nome, dep_nome
from sql_mag_user.departamento d,sql_mag_user.funcionario f, sql_mag_user.cargo c
where d.DEP_id=f.DEP_id
and c.CAR_id=f.CAR_id
and func_sexo='F';

8000 linhas selecionadas.
 
Decorrido: 00:00:00.01
Listagem 21. Utilização de Hint. Executando novamente a consulta

Plano de Execução:


----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=5000 Bytes=120000)
   1    0   HASH JOIN (Cost=9 Card=5000 Bytes=120000)
   2    1     TABLE ACCESS (FULL) OF 'CARGO' (Cost=2 Card=9 Bytes=99)
   3    1     HASH JOIN (Cost=6 Card=5000 Bytes=65000)
   4    3       TABLE ACCESS (FULL) OF 'DEPARTAMENTO' (Cost=2 Card=8 Bytes=16)
   5    3       TABLE ACCESS (FULL) OF 'FUNCIONARIO' (Cost=3 Card=5000 Bytes=55000)
 
8000 rows processed

Conforme planejávamos. O hint NO_INDEX dá a dica ao otimizador de não utilizar determinado índice. Esta técnica pode trazer ótimos benefícios (quando temos total certeza do que estamos fazendo) ou grandes tragédias (como neste caso). Veja que o otimizador voltou a utilizar um Full Table Scan na tabela Departamento. É claro que, neste exemplo, o impacto não é tão grande, mas imagine em um modelo mais complexo. Tal modelo mais complexo, inclusive, demonstrando o impacto de uma modelagem mal arquitetada, será visto na parte 3 do artigo com direito a tabelas bem mais populadas, cerca de 10 milhões de registros e melhorias bem significantes no tocante ao tempo de execução das consultas.

Vamos para o nosso segundo exemplo.

Primeiramente, vamos excluir toda a estrutura ( Listagem 22 ) para que não existam índices, estatísticas ou qualquer outro fator que possa “mascarar” nossa analise (o comando DROP USER irá excluir o esquema, incluindo todos os objetos, estatísticas, etc). Após esta etapa, basta recriar o ambiente.


DROP USER SQL_MAG_USER CASCADE;
Listagem 22. Excluindo o esquema SQL_MAG_USER

Testaremos uma consulta que retorna apenas uma linha, inicialmente sem índices, chaves ou estatísticas nas tabelas e analisaremos o comportamento da mesma nos diversos estados ( Listagem 23 ).


SQL> select FUNC_NOME, car_nome, dep_nome
from sql_mag_user.departamento d, sql_mag_user.funcionario f, sql_mag_user.cargo c
where  func_nome='Luis';
 
72 linhas selecionadas.
 
Decorrido: 00:00:00.01
Listagem 23. Consulta que deveria retornar apenas uma linha

Plano de Execução:


----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'CARGO'
   4    2       TABLE ACCESS (FULL) OF 'FUNCIONARIO'
   5    1     TABLE ACCESS (FULL) OF 'DEPARTAMENTO'
 
72 rows processed

Rodamos a consulta sem as chaves de junção, e o que temos? 72 linhas retornadas. Isso por que o Oracle realizou um CARTESIAN JOIN com o único campo que interessava na tabela funcionário. Analisemos:

  • Tabela funcionário = 1 linha processada;
  • Tabela cargo = 9 linhas processadas;
  • Tabela departamento = 8 linhas processadas;
  • Ou seja, 1 x 9 x 8 = 72.

Vamos analisar agora o comportamento do otimizador executando a mesma consulta com as chaves de junção ( Listagem 24 ).


SQL> select FUNC_NOME,car_nome,dep_nome
from sql_mag_user.departamento d,sql_mag_user.funcionario f, sql_mag_user.cargo c
where d.dep_id=f.dep_id
and c.car_id=f.car_id
AND func_nome='Luis';
 
Decorrido: 00:00:00.00
Listagem 24. Utilizando as chaves de junção

Plano de Execução:


----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       MERGE JOIN
   4    3         SORT (JOIN)
   5    4           TABLE ACCESS (FULL) OF 'CARGO'
   6    3         SORT (JOIN)
   7    6           TABLE ACCESS (FULL) OF 'FUNCIONARIO'
   8    1     SORT (JOIN)
   9    8       TABLE ACCESS (FULL) OF 'DEPARTAMENTO'
 
   1  rows processed

Bem diferente... O ponto chave foi a utilização do Merge Join como método de junção. Esses exemplos são interessantes para percebermos que poderemos alterar o método de junção, bem como o plano de execução de consultas fazendo alterações no código da consulta sem lançar mão de Hints ou outro artifício.

Vamos gerar as estatísticas nas tabelas, executar a consulta e analisar seu comportamento.

Primeiramente gere as estatísticas e, após isso, vá para a Listagem 25.


SQL> select  FUNC_NOME,car_nome,dep_nome
from sql_mag_user.departamento d,sql_mag_user.funcionario f, sql_mag_user.cargo c
where d.dep_id=f.dep_id
and c.car_id=f.car_id
AND func_nome='Luis';
 
Decorrido: 00:00:00.01
Listagem 25. Utilizando as chaves de junção após geração das estatísticas

Plano de Execução:


---------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1111 Bytes=35552)
 
   1    0   HASH JOIN (Cost=9 Card=1111 Bytes=35552)
   2    1     TABLE ACCESS (FULL) OF 'CARGO' (Cost=2 Card=9 Bytes=99)
   3    1     HASH JOIN (Cost=6 Card=1111 Bytes=23331)
   4    3       TABLE ACCESS (FULL) OF 'DEPARTAMENTO' (Cost=2 Card=8 Bytes=88)
 
   5    3       TABLE ACCESS (FULL) OF 'FUNCIONARIO' (Cost=3 Card=1111 Bytes=11110)

Com as informações do otimizador, o plano de execução alterou o método de junção para HASH JOIN, ou seja, como o otimizador não achou o índice, e tinha a informação de que se tratava de um número relativamente grande de linhas para tratar ele achou que o Hash Join seria melhor do que o Merge Join.

Vamos criar os índices e chaves e executar a consulta novamente.

Crie os índices e chaves e, após isso, o script da Listagem 26.


SQL> select FUNC_NOME,car_nome,dep_nome
from sql_mag_user.departamento d,sql_mag_user.funcionario f, sql_mag_user.cargo c
where d.dep_id=f.dep_id
and c.car_id=f.car_id
AND func_nome='Luis';
 
Decorrido: 00:00:00.01
Listagem 26. Execução da consulta após a criação dos índices

Plano de Execução:


---------------------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=88 Card=1111 Bytes=35552)
   1    0   HASH JOIN (Cost=88 Card=1111 Bytes=35552)
   2    1     TABLE ACCESS (FULL) OF 'CARGO' (Cost=3 Card=9 Bytes=99)
   3    1     HASH JOIN (Cost=84 Card=1111 Bytes=23331)
   4    3       TABLE ACCESS (FULL) OF 'DEPARTAMENTO' (Cost=3 Card=8 Bytes=88)
   5    3       TABLE ACCESS (BY INDEX ROWID) OF 'FUNCIONARIO' (Cost=80 Card=1111 Bytes=11110)
   6    5         INDEX (FULL SCAN) OF 'FUNCIONARIO_IDX' (UNIQUE) (Cost=21 Card=10000)

Conforme esperado, o otimizador notou a presença do índice e percebeu que pela seletividade da tabela funcionário, a qual seleciona apenas uma linha, era mais interessante utilizá-lo.

Mas ainda resta mais um teste, o parâmetro DB_FILE_MULTIBLOCK_READ_COUNT. Vamos ver se ele é capaz de alterar um plano de execução. Esse parâmetro encontra-se no valor de 8, vamos alterá-lo para 32 e ver o que acontece ( Listagem 27 ).


ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=32;
 
SQL> select FUNC_NOME,car_nome,dep_nome
from sql_mag_user.departamento d, sql_mag_user.funcionario f, sql_mag_user.cargo c
where d.dep_id=f.dep_id
and c.car_id=f.car_id
AND func_nome='Luis';
 
Decorrido: 00:00:00.00
Listagem 27. Alterando o parâmetro DB_FILE_MULTIBLOCK_READ_COUNT e executando a consulta novamente

Plano de Execução:


-----------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=25 Card=1111 Bytes=35552)
   1    0   HASH JOIN (Cost=25 Card=1111 Bytes=35552)
   2    1     TABLE ACCESS (FULL) OF 'CARGO' (Cost=3 Card=9 Bytes=99)
   3    1     HASH JOIN (Cost=21 Card=1111 Bytes=23331)
   4    3       TABLE ACCESS (FULL) OF 'DEPARTAMENTO' (Cost=3 Card=8 Bytes=88)
   5    3       TABLE ACCESS (FULL) OF 'FUNCIONARIO' (Cost=17 Card=1111 Bytes=11110)
 
1 row processed

Mais uma forma de alterarmos um plano de execução através de parâmetros do Oracle. Ora, se o parâmetro DB_FILE_MULTIBLOCK_READ_COUNT diz para o otimizador a quantidade de blocos que o Oracle consegue ler de uma só vez e esse parâmetro esta relativamente alto (32), o otimizador poderá perfeitamente pensar “se eu posso ler uma quantidade alta de blocos de uma só vez, pra que eu quero um índice? É muito mais rápido ler estes blocos...”.Faz sentido não acha?