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&gt; 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&gt; @&lt;&gt;
SQL&gt; View Criada
SQL&gt; 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 &lt; 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&gt; SET TIMING ON -- Para verificarmos o tempo de execu��o
SQL&gt; SET AUTOTRACE TRACEONLY -- Para verificarmos o plano de execu��o logo ap�s a execu��o
SQL&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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?

Tecnologias:

Confira outros conte�dos:

SQL SUM: somando os valores de uma...

SQL: INNER JOIN

SQL: Introdu��o ao Where

Assista grátis a nossa aula inaugural

Perguntas frequentes

Quem somos?

Por que a programação se tornou a profissão mais promissora da atualidade?

Como faço para começar a estudar?

Em quanto tempo de estudo vou me tornar um programador?

Sim, você pode se tornar um programador e não precisa ter diploma de curso superior!

O que eu irei aprender estudando pela DevMedia?

Principais diferenciais da DevMedia

Qual o investimento financeiro que preciso fazer para me tornar um programador?

Como funciona a forma de pagamento da DevMedia?

Por Devmedia Em 2008

Nossos casos de sucesso

Leonardo Carlos

Eu sabia pouqu�ssimas coisas de programa��o antes de come�ar a estudar com voc�s, fui me especializando em v�rias �reas e ferramentas que tinham na plataforma, e com essa bagagem consegui um est�gio logo no in�cio do meu primeiro per�odo na faculdade.

Lucas Rodrigues

Estudo aqui na Dev desde o meio do ano passado! Nesse per�odo a Dev me ajudou a crescer muito aqui no trampo.
Fui o primeiro desenvolvedor contratado pela minha empresa. Hoje eu lidero um time de desenvolvimento!
Minha meta � continuar estudando e praticando para ser um Full-Stack Dev!

Her�clito J�nior

Economizei 3 meses para assinar a plataforma e sendo sincero valeu muito a pena, pois a plataforma � bem intuitiva e muuuuito did�tica a metodologia de ensino. Sinto que estou EVOLUINDO a cada dia. Muito obrigado!

Julio Cablen

Nossa! Plataforma maravilhosa. To amando o curso de desenvolvimento front-end, tinha coisas que eu ainda n�o tinha visto. A did�tica � do jeito que qualquer pessoa consegue aprender. S�rio, to apaixonado, adorando demais.

Joelberth Sena

Adquiri o curso de voc�s e logo percebi que s�o os melhores do Brasil. � um passo a passo incr�vel. S� n�o aprende quem n�o quer. Foi o melhor investimento da minha vida!

Felipe Nunes

Foi um dos melhores investimentos que j� fiz na vida e tenho aprendido bastante com a plataforma. Voc�s est�o fazendo parte da minha jornada nesse mundo da programa��o, irei assinar meu contrato como programador gra�as a plataforma.

Wanderson Oliveira

Comprei a assinatura tem uma semana, aprendi mais do que 4 meses estudando outros cursos. Exerc�cios pr�ticos que n�o tem como n�o aprender, est�o de parab�ns!

Jos� Lucas

Obrigado DevMedia, nunca presenciei uma plataforma de ensino t�o presente na vida acad�mica de seus alunos, parab�ns!

Eduardo Dorneles

Aprendi React na plataforma da DevMedia h� cerca de 1 ano e meio... Hoje estou h� 1 ano empregado trabalhando 100% com React!

Adauto Junior

J� fiz alguns cursos na �rea e nenhum � t�o bom quanto o de voc�s. Estou aprendendo muito, muito obrigado por existirem. Est�o de parab�ns... Espero um dia conseguir um emprego na �rea.

Ver todos os casos de sucesso

Tuning no Oracle

Veja neste artigo alguns aspectos e t�cnicas importantes na otimiza��o de desempenho do Oracle (tunning).

O que � tuning?

Planos de execu��o

Nested Loop

Merge Join

Hash Join

Sort Merge

Analisando um estudo de caso

M�os � obra

Estudo de caso

Ambiente utilizado

Hardware

Software

Defini��es de banco de dados:

Scripts para execu��o

Confira outros conte�dos:

SQL SUM: somando os valores de uma...

SQL: INNER JOIN

SQL: Introdu��o ao Where

Perguntas frequentes

Nossos casos de sucesso