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
aleques
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Sex, 06 Fev 2009 5:34 pm
Localização: São Paulo - SP

pessoal, sou novato no oracle (e sql tb) 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:

Selecionar tudo

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
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

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 ?
aleques
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Sex, 06 Fev 2009 5:34 pm
Localização: São Paulo - SP

Certo... estou 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:

Selecionar tudo

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)

Selecionar tudo

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Sex, 06 Fev 2009 5:34 pm
Localização: São Paulo - SP

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

Selecionar tudo

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...
Avatar do usuário
Toad
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 253
Registrado em: Sex, 18 Nov 2005 2:14 pm
Localização: Seattle, WA
Contato:
Matheus Gonçalves
matheus.dev
twitter.com/developer__c

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

Selecionar tudo

where tb_a.data_execucao in 
por

Selecionar tudo

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
aleques
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Sex, 06 Fev 2009 5:34 pm
Localização: São Paulo - SP

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:

Selecionar tudo

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Sex, 06 Fev 2009 5:34 pm
Localização: São Paulo - SP

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.
ruevers
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 324
Registrado em: Sex, 02 Jun 2006 1:48 pm
Localização: sp
Contato:

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
RodrigoValentim
Moderador
Moderador
Mensagens: 367
Registrado em: Ter, 25 Mar 2008 3:41 pm
Localização: Salvador - BA
Rodrigo Valentim
Analista de Sistemas
Oracle Developer

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

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.

Selecionar tudo

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..

Selecionar tudo

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.
aleques
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Sex, 06 Fev 2009 5:34 pm
Localização: São Paulo - SP

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)

Selecionar tudo

 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
RodrigoValentim
Moderador
Moderador
Mensagens: 367
Registrado em: Ter, 25 Mar 2008 3:41 pm
Localização: Salvador - BA
Rodrigo Valentim
Analista de Sistemas
Oracle Developer

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

Sugeria as seguintes alterações

tirava essas consultas desnecessárias e colocaria parametros

Selecionar tudo

SELECT TO_DATE('26/03/2009','DD-MM-YYYY') FROM DUAL
analisava melhor a necessidade de ordernar dessa forma

Selecionar tudo

  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.

Selecionar tudo

 ORDER BY 
    TB05.VTC_TECNOLOGIA, 
   TB01.HCM_ARQ_ORIGEM
Acredito que o grande problema está aqui...

Selecionar tudo

  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.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 6 visitantes