Treinamentos Oracle - Nerv Informática

Group by MUITO lento - Oracle 10g

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Mensagemem Sex, 06 Fev 2009 6:56 pm

pessoal, sou novato no oracle (e sql também) e me deparei com o seguinte problema:

tenho uma tabela com milhoes de registros.
quando faço um group by de um campo específico, demora demais...
ex:
select campo from tabela group by campo

a maioria de registros desse campo, são diferentes, mas por causa de alguns (10%), tenho sempre q fazer esse group by...

pelo que eu lí e entendi (não sei se estou certo), nesse caso não seria viável o uso de índices...

o que eu posso fazer para melhorar isso? ou não tem jeito?

[]
Alex
aleques
Localização: São Paulo - SP

Mensagemem Sáb, 07 Fev 2009 2:24 pm

Bom, sem ver o seu SQL fica difícil de dar uma dica.
Mas aí vão algumas considerações:
* Select em MILHÕES de linhas... Realmente é necessário ? Não pode filtrar um pouco antes de fazer o group by ?
* Você está fazendo uma SOMA, algum calculo com essas linhas ?
dr_gori
Localização: Porto Alegre - RS

Thomas F. G
http://about.me/plsql

Mensagemem Sáb, 07 Fev 2009 10:33 pm

Certo... toh sem o sql aqui... mas sei explicar a situação.
na vdd ele é um sql com 1 monte de coisa, mas esse trecho específico demora pacas... explicando melhor:

é uma tabela de chamados
imagine duas colunas: código do chamado e data de entrada...
imagine então assim:

codigo_chamado | data_entrada
1 | 01/01/2009
2 | 01/01/2009
3 | 01/01/2009
4 | 01/01/2009
4 | 02/01/2009
5 | 02/01/2009

Veja que o campo codigo_chamado não é chave única... logo, nesse group by q mostrarei abaixo ocorre um full table scan (acho q é isso)

select MAX(data_entrada), outros_campos from tabela GROUP BY codigo_chamado

então quando eu tenho mais de uma linha com codigo_chamado igual, eu pego só a linha com a última data_entrada...

só a partir dai, que eu faço outros agrupamentos.. aí usando os índices de outras colunas... não faço tudo duma vez porque tem uns counts q só dão certo depois q faço esse agrupamento inicial.. aí depois dele, é tudo blza.

eu tirei o max pra testar lá no serviço, e continuou lento, então constatei que o miolo do problema está nesse group by especificamente

Testes que fiz:
tirei esse group by... a consulta com 5 left joins com outras tabelas, foi muito rápida.. é só colocar esse agrupamento (mesmo sem o max) que ferra tudo MESMO...

Se vocês souberem uma dica aew pra mi ajudar...

[]
Alex
aleques
Localização: São Paulo - SP

Mensagemem Sáb, 07 Fev 2009 10:55 pm

Cara.... só agora q ví q esse meu raciocínio está errado né..

a ideia é pegar todos os registros e quando tiverem códigos iguais, pegar o com a última data_execucao

o que está lá no serviço é assim

select
tb_a.data_execucao,
tb_a.codigo_chamado,
tb_a.outros_campos

from tabelaA tb_a

where tb_a.data_execucao in
(select max(data_execucao) from tabelaA tb_temp where tb_temp.codigo_chamado = tb_a.codigo_chamado)


isso é o que está funcionando hoje....
mas está absurdamente lento...
aleques
Localização: São Paulo - SP

Mensagemem Seg, 09 Fev 2009 12:26 pm

Pelo que você falou você está buscando a maior data de execução para cada código chamado. Certo?

A consulta vai ficar lenta mesmo.
Em meus testes ele retornou 28200 registros em 20segundos.

Quanto está demorando sua consulta?

Talvez possa ajudar um pouco trocar o código

Código: Selecionar todos
where tb_a.data_execucao in


por

Código: Selecionar todos
where tb_a.data_execucao =


Uma vez que quando você usa max(data_execucao) , ele só vai retornar uma data, não há necessidade do uso de IN
Toad
Localização: Richmond, VA - USA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Mensagemem Seg, 09 Fev 2009 5:27 pm

Amigos, obrigado pela ajuda até agora...

Então... descobrí algumas coisas...
a troca do in por = não resolveu infelizmente...

Então... antes de fazer esse agrupamento, eu posso limitar o range da data...
porém surge outro problema: quando o range chega em uma tamanho X q me retorna Y linhas, aí ele não utiliza índices pra esse between (full access table).. ou seja, meu problema voltou... fica lento pra caramba... mas se eu fizer intervalo de 1 ou 2 dias, vai BEM rapido...

então vejam como remodelei a query:

Código: Selecionar todos
Select camposQueEuPreciso
from
  (SELECT MAX(data) data, codigo
   FROM tabela
   WHERE data BETWEEN to_date('01/01/2009' , 'dd/mm/yyyy')
   AND to_date('05/01/2009' , 'dd/mm/yyyy')
   GROUP BY codigo
   ) tabela_temp
INNER JOIN tabela
ON tabela_temp.codigo = tabela.codigo
AND tabela_temp.data = tabela.data


e assim os outros joins em diante....

esse INNER JOIN vai pelos índices.. então sem problema (acho)...

então antes de fazer o group by, limitei o range da data...


saí do problema do group, mas me deparei com esse novo problema...
estou usando o toad pra verificar se está usando o índice ou não.

estou travado nessa query....

valeu
Alex
aleques
Localização: São Paulo - SP

Mensagemem Seg, 09 Fev 2009 5:34 pm

isso toad, maior data de execuçao para o código, isso mesmo
então mano... demora ao ponto deu ter que parar a consulta.. tipo, se eu pego um intervalo de 5 dias e a query usa o indice pro between, são poucos segundos (menos de 5)... agora se eu pego 6 dias e não usa o índice, pula para minutos...

Eu sei que dependendo do intervalo de tempo que seja a consulta, vai ser lento mesmo.. mas 6 dias?!!? tudo bem que é registro pra caramba... mas...

Amanhã no trampo, voh pegar alguns números para a situação ser melhor vizualizada... porque assim toh ligado q fica dificil

aa tentei usar o hint.. mas num sei se usei certo... acho q não porque eu coloquei depois do select algo como /* index */ e continuou não usando o índice.
aleques
Localização: São Paulo - SP

Mensagemem Seg, 09 Mar 2009 2:17 pm

Então, nem vi direito...toda a discussão, mas pelo que vi no começo..ia demorar mesmo por causa do MAX(DATA) ele vai fazer um full na tabela e a restrição de data ajudou a diminuir o range....

[]´s
ruevers
Localização: sp


Mensagemem Qua, 08 Abr 2009 10:00 am

Essa á a parte que mais gosto...

bom, a criação de um indice não ajudaria no seu problema não? mostra pra gente o plano de execução dessa query pra tentarmos ajudar nessa questão dos indices... as vezes, por ter uma modelagem muito antiga, os indices antigos não estão mais ajudando e precisa de um reforço ou coisa do genero. Falo isso porque fiz um teste em minha base, onde tenho 27,5 milhões de linhas e a query demorou 24 secundos.

Código: Selecionar todos
Select max(i.dt_movimentacao),
       cod_prod
  from it_movimentacao
group by cod_prod


achei pouco tentei seguir um pouco sua lógica... usar o retorno da query como filtro..

Código: Selecionar todos
SELECT *
FROM it_movimentacao e
WHERE e.dt_movimentacao IN (Select max(i.dt_movimentacao),
                                   i.cod_prod
                              from it_movimentacao i
                          group by i.cod_prod)

e demorou 13 segundos

Geralmente, quando vou trabalhar com essa tabela, faço o filtro de algumas informações, tipo o range de data... pego o parametro que o usuário passou e faço a fatia com o uso de um between e add_months...

acho que pra te ajudar mesmo só vendo o plano de execução para ver o que pode ser sugerido como ideia.
RodrigoValentim
Localização: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Mensagemem Qui, 30 Abr 2009 9:47 am

Obrigado pela ajuda amigos. Muita coisa já mudou... como os usuarios só precisavam apenas 1 dia para geração de relatórios, o range é de apenas 1 dia.

Muita coisa mudou e, mesmo assim, ficou lento. Depois que o DBA aqui passou a fazer o analyze no banco com mais frequência, aí sim fluiu bem melhor a query...

a quey em sí, está péssima, mas como está funcionando e acabou meu prazo, vou deixar assim mesmo, por enquanto...

tipo, de uma data q tem no registro, tenho q agrupar e pegar: a data min, uma depois da data min, e uma depois dessa última...

segue a query final que está em produção hj (está nojenta, masss está funcionando e bem)

Código: Selecionar todos
SELECT

  TB05.VTC_TECNOLOGIA TECNOLOGIA,
  SUBSTR(TB01.HCM_ARQ_ORIGEM,0,3) LOTE,
  COUNT(SUBSTR(TB01.HCM_ARQ_ORIGEM,0,3)) VOLUME_LOTE,
  MIN(TBX.HORA_COLETA) HORA_COLETA,
  MIN ( decode(tby.hora_inicio, null, tbx.hora_coleta, tby.hora_inicio) ) hora_inicio,
  MAX ( decode(tbz.hora_fim, null, decode(tby.hora_inicio, null, tbx.hora_coleta, tby.hora_inicio), tbz.hora_fim) ) hora_fim,
  COUNT(*) QUANTIDADE

  FROM ms_historico_comando TB01

  RIGHT JOIN
  (
    SELECT

          HCM_ID_COMANDO,
          MIN(HCM_DATA_EXEC) HORA_COLETA
          FROM ms_historico_comando

           WHERE (   HCM_DATA_EXEC >= (SELECT TO_DATE('26/03/2009','DD-MM-YYYY') FROM DUAL)  AND  HCM_DATA_EXEC < (SELECT TO_DATE('27/03/2009','DD-MM-YYYY') FROM DUAL)  )

          AND HCM_TIPO_ENTRADA = 1

          GROUP BY HCM_ID_COMANDO
  ) TBX
      ON TB01.HCM_ID_COMANDO = TBX.HCM_ID_COMANDO
      AND TB01.HCM_DATA_EXEC = TBX.HORA_COLETA

  LEFT OUTER JOIN
  (
    SELECT

          TB_TEMP.HCM_ID_COMANDO,
          MIN(TB_TEMP.HCM_DATA_EXEC) HORA_INICIO
          FROM ms_historico_comando TB_TEMP

          WHERE (   HCM_DATA_EXEC >= (SELECT TO_DATE('26/03/2009','DD-MM-YYYY') FROM DUAL)  AND  HCM_DATA_EXEC < (SELECT TO_DATE('27/03/2009','DD-MM-YYYY') FROM DUAL)  )

            AND TB_TEMP.HCM_DATA_EXEC >
            (
             select MIN(HCM_DATA_EXEC) from ms_historico_comando

              WHERE (   HCM_DATA_EXEC >= (SELECT TO_DATE('26/03/2009','DD-MM-YYYY') FROM DUAL)  AND  HCM_DATA_EXEC < (SELECT TO_DATE('27/03/2009','DD-MM-YYYY') FROM DUAL)  )

                AND HCM_TIPO_ENTRADA = 1
                AND TB_TEMP.hcm_id_comando = hcm_id_comando

            )

          GROUP BY HCM_ID_COMANDO
  ) TBY
      ON TB01.HCM_ID_COMANDO = TBY.HCM_ID_COMANDO

  LEFT OUTER JOIN
  (
    SELECT

          TB_AUX_1.HCM_ID_COMANDO,
          MIN(TB_AUX_1.HCM_DATA_EXEC) HORA_FIM
          FROM ms_historico_comando TB_AUX_1

          WHERE (   HCM_DATA_EXEC >= (SELECT TO_DATE('26/03/2009','DD-MM-YYYY') FROM DUAL)  AND  HCM_DATA_EXEC < (SELECT TO_DATE('27/03/2009','DD-MM-YYYY') FROM DUAL)  )

            AND TB_AUX_1.HCM_DATA_EXEC >
              (
                select MIN(HCM_DATA_EXEC) from ms_historico_comando TB_AUX_2

              WHERE (   HCM_DATA_EXEC >= (SELECT TO_DATE('26/03/2009','DD-MM-YYYY') FROM DUAL)  AND  HCM_DATA_EXEC < (SELECT TO_DATE('27/03/2009','DD-MM-YYYY') FROM DUAL)  )

                AND TB_AUX_2.HCM_TIPO_ENTRADA = 1
                AND TB_AUX_2.hcm_id_comando = TB_AUX_1.hcm_id_comando
                AND TB_AUX_2.HCM_DATA_EXEC >
                (
                   select MIN(HCM_DATA_EXEC) from ms_historico_comando

                WHERE (   HCM_DATA_EXEC >= (SELECT TO_DATE('26/03/2009','DD-MM-YYYY') FROM DUAL)  AND  HCM_DATA_EXEC < (SELECT TO_DATE('27/03/2009','DD-MM-YYYY') FROM DUAL)  )

                   AND HCM_TIPO_ENTRADA = 1
                   AND TB_AUX_2.hcm_id_comando = hcm_id_comando
                )

              )

          GROUP BY HCM_ID_COMANDO
  ) TBZ
      ON TB01.HCM_ID_COMANDO = TBZ.HCM_ID_COMANDO


  LEFT OUTER JOIN MS_DEFIN_EXEC TB02
       ON TB01.HCM_DEX_ID = TB02.DEX_ID
  LEFT OUTER JOIN MS_VERSAO_TECNOLOGIA TB05
       ON TB02.DEX_VTC_ID = TB05.VTC_ID

  GROUP BY
    TB05.VTC_TECNOLOGIA,
    SUBSTR(TB01.HCM_ARQ_ORIGEM,0,3)

  ORDER BY
    TB05.VTC_TECNOLOGIA,
    SUBSTR(TB01.HCM_ARQ_ORIGEM,0,3)


a idéia do rodrigo é legal... tem muita coisa aqui nesse formato...
pretendo fazer isso depois...
alguém tem mais alguma sugestão para a query?

[]´s
Alex
aleques
Localização: São Paulo - SP

Mensagemem Qui, 30 Abr 2009 10:13 am

Sugeria as seguintes alterações

tirava essas consultas desnecessárias e colocaria parametros
Código: Selecionar todos
SELECT TO_DATE('26/03/2009','DD-MM-YYYY') FROM DUAL


analisava melhor a necessidade de ordernar dessa forma

Código: Selecionar todos
  ORDER BY
    TB05.VTC_TECNOLOGIA,
    SUBSTR(TB01.HCM_ARQ_ORIGEM,0,3)


sugeria fazer assim, pois, não existe diferença já que ele ordena tudo pelo primeiro digito, depois pelo segundo e assim por diante... você só estaria tirando uma função a mais da query.
Código: Selecionar todos
ORDER BY
    TB05.VTC_TECNOLOGIA,
   TB01.HCM_ARQ_ORIGEM


Acredito que o grande problema está aqui...

Código: Selecionar todos
  COUNT(SUBSTR(TB01.HCM_ARQ_ORIGEM,0,3)) VOLUME_LOTE,
  MIN(TBX.HORA_COLETA) HORA_COLETA,
  MIN(decode(tby.hora_inicio, null, tbx.hora_coleta, tby.hora_inicio) ) hora_inicio,
  MAX(decode(tbz.hora_fim, null, decode(tby.hora_inicio, null, tbx.hora_coleta, tby.hora_inicio), tbz.hora_fim) ) hora_fim,
  COUNT(*) QUANTIDADE


espero ter ajudado.
RodrigoValentim
Localização: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!



Voltar para SQL

Quem está online

Usuários navegando neste fórum: Bing [Bot] e 0 visitantes