Oracle – Tunning baseado em custo ? » Blog do Cauduro
Oracle – Tunning baseado em custo ?
Muitas pessoas fazem tunning de consultas baseado em custo.
Já lhe disseram que uma consulta ficou melhor porque o custo ficou menor ?
Ou mesmo você faz tunning baseado em custo ?
Mas o que é custo ?
O custo é um valor interno gerado pelo Oracle para estimar o peso (relacionado a recursos computacionais) de um determinado plano de execução.
O Oracle tem um poderoso otimizador de consultas.
Na verdade é um dos componentes mais fascinantes do Oracle.
Explicando de maneira bem sucinta, esse otimizador pega a consulta que queremos executar, gera centenas de planos de execução dando um custo a cada um desses planos, daí ele escolhe o com custo menor.
Com a explicação acima talvez faça ainda mais sentido fazer tunning baseado em custo, afinal , se o otimizador do Oracle escolhe baseado em custo, por que a gente faria diferente ?
O custo é uma estimativa, e estimativas nem sempre estão certas.
Já lhe aconteceu alguma vez de o GPS te mandar para um determinado caminho porque ele "teoricamente" era mais rápido quando na verdade ele não era mais rápido nem nada ?
O Gps talvez achou que uma rua era mão dupla quando não era e por aí vai.
Com o otimizador do Oracle pode acontecer a mesma coisa, ele achou que um plano era bom baseado nas estatísticas e parâmetros do banco, mas na realidade não era bem assim. E isso acontece muitas, muitas e muitas vezes.
Dessa forma, na hora de fazer o tunning de uma consulta, nós não focamos em custo, mas focamos nas leituras em memória que o Oracle teve que fazer para executar aquele comando.
Vou dar um exemplo para ficar mais facil de entender o que estou dizendo e porque isso faz sentido.
Vamos supor que tem uma consulta consumindo muito do ambiente, e você precise urgentemente melhorar ela.
Vamos supor que a consulta seja a seguinte:
select * from PRODUCTS where PROD_TYPE=1;
Ela é relativamente simples, mas esta demorando 8 segundos para trazer apenas 8 registros.
E ela é chamada centenas de vezes…
Seu chefe esta em cima de você e quer que você resolva o mais rápido possível.
Daí você decide ir em busca de otimizar o custo da consulta.
Você olha o custo dela:
SQL> select * from PRODUCTS where PROD_TYPE=1;
| Id  | Operation	  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |	    |	100K|	878K|	 54   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PRODUCTS |	100K|	878K|	 54   (2)| 00:00:01 |
Ele é muito baixo , apenas 54.
Talvez nesse momento você até se surpreenda, como uma consulta com um custo tão baixo pode ser tão lenta.
Mas você precisa melhorar ela e decide tentar o acesso por indice, para ver se melhora o custo, afinal, o custo é o seu guia.
SQL> select /*+index(PRODUCTS IDX_PRODUCTS_TYPE)*/ * from PRODUCTS where PROD_TYPE=1;
| Id  | Operation	 | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT |	       |   100K|   878K|   280	 (1)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_PRODUCTS_TYPE |   100K|   878K|   280	 (1)| 00:00:01 |
O custo continua baixo, mas é 4x maior que na consulta original, então você conclui que essa otimização não é boa.
Será que a vaga desse Dba estaria em jogo ? Provavelmente.
Mas alguns talvez se perguntem o que esse Dba fez de errado e o que poderia ter sido feito…
Vamos lá, será que ele focou no que realmente importa ?
Ao invéz de focar apenas no custo, vamos ver todas as estatísticas da execução dessa consulta.
Primeiramente a consulta sem modificações:
Elapsed: 00:00:08.45
Plan hash value: 1224321393
1 - filter("PROD_TYPE"=1)
454867  consistent gets
437238  physical reads
686  bytes sent via SQL*Net to client
A linha com "consistent gets" contém as leituras lógicas executadas pelo procedimento.
Vemos aqui que ela fez mais de 450 mil leituras lógicas (454867) para retornar apenas 8 linhas, da pra ver que temos um acesso ruim aqui.
Além disso, ela levou 8 segundos para retornar apenas 8 linhas, sendo que a consulta é muito simples.
Se olharmos a estrutura da tabela veremos que o campo filtro PROD_TYPE é indexado.
Vamos ver então o acesso usando tal indíce:
Plan hash value: 3577670305
1 - access("PROD_TYPE"=1)
Agora sim, 6 leituras lógicas para retornar 8 linhas.
A consulta levou menos que 1 milisegundo para ser executada.
Se tivessemos levado em conta apenas o custo não teriamos chegado nessa conclusão.
Por isso, devemos focar nas leituras lógicas…
Mas isso quer dizer que custo nao serve pra nada ? De maneira alguma, ele é fundamental para o otimizador. Agora será que ele não serve para o Dba ? Ele serve sim, no exemplo que dei aqui, eu poderia fazer um trace para tentar entender o porque o custo estava errado, para tentar realizar as correções e deixar que o otimizador escolhesse o plano ideal.