Hash Group By X Sort Group By

Para evitarmos problemas já conhecidos de usuários reclamando de lentidão é uma boa prática testarmos as funcionalidades mais importantes da aplicação e com isso, evitamos futuras dores de cabeça, pois afinal nunca ouvimos a frase abaixo:

 a)  Após a migração estamos com lentidão nas querys: X, Y e Z ;
Ou ainda e nesse caso:
 b)  A ordem das linhas retornadas não são mais as mesmas ???? !!!!

Então vamos as vias de fato, falando sobre um alteração ocorrida após a versão 10gR2.

Na versão Oracle9i e Oracle10gR1 temos como comportamento default para SQLs com a clausula GROUP BY a utilização do algoritmo SORT GROUP BY que envolve realizar classificação de dados para as colunas envolvidas e acumular os resultados agregados. O HASH GROUP BY calcula todas as linhas em uma hash table e a joga em memória e só devolve a primeira linha quando todas já foram agregadas. Pode ser necessário a criação de tabelas temporárias (work tables) para a resolução e o acesso a esses dados podem ser paralelizados.

Vamos estudar o caso do simples comando abaixo:

SQL>  SELECT OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS GROUP BY OBJECT_TYPE ;

Oracle: 10.1.0.3 (Sort Group By)

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
——-  —————————————————
     44  SORT GROUP BY (cr=1727 pr=7 pw=0 time=0 us cost=253 size=4059 card=369) 
  72009   VIEW  DBA_OBJECTS (cr=1727 pr=7 pw=0 time=1271203 us cost=252 size=4059 card=369)
  72009    UNION-ALL  (cr=1727 pr=7 pw=0 time=1040215 us)
      0     TABLE ACCESS BY INDEX ROWID SUM$ (cr=2 pr=2 pw=0 time=0 us cost=1 size=11 card=1)
      1      INDEX UNIQUE SCAN I_SUM$_1 (cr=1 pr=1 pw=0 time=0 us cost=0 size=0 card=1)(object id 956)
  72008     FILTER  (cr=1723 pr=4 pw=0 time=548046 us)
  72893      HASH JOIN  (cr=907 pr=0 pw=0 time=1238908 us cost=250 size=79860 card=726)
     92       INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=182 us cost=1 size=344 card=86)(object id 47)
  72893       HASH JOIN  (cr=906 pr=0 pw=0 time=690104 us cost=248 size=76956 card=726)
     92        INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=182 us cost=1 size=1892 card=86)(object id 47)
  72893        TABLE ACCESS FULL OBJ$ (cr=905 pr=0 pw=0 time=197794 us cost=247 size=60984 card=726)
   3775      TABLE ACCESS BY INDEX ROWID IND$ (cr=816 pr=4 pw=0 time=0 us cost=2 size=8 card=1)
   4648       INDEX UNIQUE SCAN I_IND1 (cr=85 pr=4 pw=0 time=0 us cost=1 size=0 card=1)(object id 41)
      0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
      0       INDEX FULL SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
      0       INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)
      1     NESTED LOOPS  (cr=2 pr=1 pw=0 time=0 us cost=2 size=7 card=1)
      1      INDEX FULL SCAN I_LINK1 (cr=1 pr=1 pw=0 time=0 us cost=1 size=3 card=1)(object id 137)
      1      INDEX RANGE SCAN I_USER2 (cr=1 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 47)

 Oracle: 11.2.0.1 (Hash Group By)

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
——-  —————————————————
     44  HASH GROUP BY (cr=1727 pr=316 pw=0 time=0 us cost=259 size=745756 card=67796)
  72009   VIEW  DBA_OBJECTS (cr=1727 pr=316 pw=0 time=6712987 us cost=254 size=745756 card=67796)
  72009    UNION-ALL  (cr=1727 pr=316 pw=0 time=6448745 us)
      0     TABLE ACCESS BY INDEX ROWID SUM$ (cr=2 pr=0 pw=0 time=0 us cost=1 size=11 card=1)
      1      INDEX UNIQUE SCAN I_SUM$_1 (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 956)
  72008     FILTER  (cr=1723 pr=315 pw=0 time=5933607 us)
  72893      HASH JOIN  (cr=907 pr=0 pw=0 time=1286352 us cost=252 size=7989850 card=72635)
     92       INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=182 us cost=1 size=344 card=86)(object id 47)
  72893       HASH JOIN  (cr=906 pr=0 pw=0 time=731097 us cost=250 size=7699310 card=72635)
     92        INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=273 us cost=1 size=1892 card=86)(object id 47)
  72893        TABLE ACCESS FULL OBJ$ (cr=905 pr=0 pw=0 time=227259 us cost=248 size=6101340 card=72635)
   3775      TABLE ACCESS BY INDEX ROWID IND$ (cr=816 pr=315 pw=0 time=0 us cost=2 size=8 card=1)
   4648       INDEX UNIQUE SCAN I_IND1 (cr=85 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 41)
      0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
      0       INDEX FULL SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
      0       INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)
      1     NESTED LOOPS  (cr=2 pr=1 pw=0 time=0 us cost=2 size=7 card=1)
      1      INDEX FULL SCAN I_LINK1 (cr=1 pr=1 pw=0 time=0 us cost=1 size=3 card=1)(object id 137)
      1      INDEX RANGE SCAN I_USER2 (cr=1 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 47)

Há alguns bugs documentados no metalink com a utilização desse novo algoritmo, alguns notes (6471770, 6818207 e 7716219). Esses erros estão relacionados desde trazer resultados errados até altíssima utilização de tablespace temporária, entre outros.

Normalmente a solução de contorno está em alterar o parâmetro _gby_hash_aggregation_enabled = false, no entanto acredito que essa seja uma solução muito ofensiva já que desabilita essa nova funcionalidade para toda base de dados e uma outra possibilidade seria fazer o mesmo  apenas para um comando via hint /*+ OPT_PARAM(‘_gby_hash_aggregation_enabled’ ‘false’).

Não se deve confiar em uma ordenação de resultados sem utilizar a clausula ORDER BY, como tratado por Tomas Kyte no artigo http://tkyte.blogspot.com/2005/08/order-in-court.html, no entanto antes da versão 10.2 você pode não ter se preocupado com essa ordenação já que os resultados eram ordenados pelo algoritmo de SORT ORDER BY. No entanto a partir dessa versão você pode ter grandes surpresas na ordem de retorno das linhas.

Normalmente, o algoritmo HASH GROUP BY é mais eficiente do que o algoritmo de SORT GROUP BY. No caso do SQL que fizemos acima temos:

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.03       0.27          0          4          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        4      0.65       0.81          7       1727          0          44
——- ——  ——– ———- ———- ———- ———-  ———-
total        6      0.68       1.09          7       1727          0          44

Oracle: 10.1.0.3 (Sort Group By)

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.14       0.08          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      0.53       0.61          7       1727          0          44
——- ——  ——– ———- ———- ———- ———-  ———-
total        6      0.67       0.69          7       1727          0          44

Oracle: 11.2.0.1 (Hash Group By)

Os testes foram executadas nas mesmas condições de temperatura e pressão e tivemos cerca de 35 %  a menos no tempo de resposta.

Vamos a mais um caso:

Oracle: 10.1.0.3 (Sort Group By)

SELECT OBJECT_TYPE, STATUS, COUNT(*) FROM DBA_OBJECTS
GROUP BY OBJECT_TYPE, STATUS
ORDER BY OBJECT_TYPE, STATUS

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.06       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        5      0.51       0.59          7       1727          0          49
——- ——  ——– ———- ———- ———- ———-  ———-
total        7      0.57       0.64          7       1727          0          49

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
——-  —————————————————
     49  SORT ORDER BY (cr=1727 pr=7 pw=0 time=0 us cost=254 size=5904 card=369)
     49   SORT GROUP BY (cr=1727 pr=7 pw=0 time=144 us cost=254 size=5904 card=369)
  72048    VIEW  DBA_OBJECTS (cr=1727 pr=7 pw=0 time=1296206 us cost=252 size=5904 card=369)
  72048     UNION-ALL  (cr=1727 pr=7 pw=0 time=1065476 us)
      0      TABLE ACCESS BY INDEX ROWID SUM$ (cr=2 pr=2 pw=0 time=0 us cost=1 size=11 card=1)
      1       INDEX UNIQUE SCAN I_SUM$_1 (cr=1 pr=1 pw=0 time=0 us cost=0 size=0 card=1)(object id 956)
  72047      FILTER  (cr=1723 pr=4 pw=0 time=553717 us)
  72925       HASH JOIN  (cr=907 pr=0 pw=0 time=1217958 us cost=250 size=79860 card=726)
     92        INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=182 us cost=1 size=368 card=92)(object id 47)
  72925        HASH JOIN  (cr=906 pr=0 pw=0 time=681051 us cost=248 size=76956 card=726)
     92         INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=182 us cost=1 size=2024 card=92)(object id 47)     72925         TABLE ACCESS FULL OBJ$ (cr=905 pr=0 pw=0 time=199675 us cost=247 size=60984 card=726)
   3775       TABLE ACCESS BY INDEX ROWID IND$ (cr=816 pr=4 pw=0 time=0 us cost=2 size=8 card=1)
   4648        INDEX UNIQUE SCAN I_IND1 (cr=85 pr=4 pw=0 time=0 us cost=1 size=0 card=1)(object id 41)
      0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
      0        INDEX FULL SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
      0        INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)
      1      NESTED LOOPS  (cr=2 pr=1 pw=0 time=0 us cost=2 size=7 card=1)
      1       INDEX FULL SCAN I_LINK1 (cr=1 pr=1 pw=0 time=0 us cost=1 size=3 card=1)(object id 137)
      1       INDEX RANGE SCAN I_USER2 (cr=1 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 47)

 Oracle: 11.2.0.1 (Hash Group By)

SELECT OBJECT_TYPE, STATUS, COUNT(*) FROM DBA_OBJECTS
GROUP BY OBJECT_TYPE, STATUS
ORDER BY OBJECT_TYPE, STATUS

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.07       0.09          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        5      0.56       0.60          7       1727          0          49
——- ——  ——– ———- ———- ———- ———-  ———-
total        7      0.63       0.70          7       1727          0          49

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
——-  —————————————————
     49  SORT ORDER BY (cr=1727 pr=7 pw=0 time=0 us cost=263 size=1084736 card=67796)
     49   HASH GROUP BY (cr=1727 pr=7 pw=0 time=96 us cost=263 size=1084736 card=67796)
  72048    VIEW  DBA_OBJECTS (cr=1727 pr=7 pw=0 time=1286480 us cost=254 size=1084736 card=67796)
  72048     UNION-ALL  (cr=1727 pr=7 pw=0 time=1052935 us)
      0      TABLE ACCESS BY INDEX ROWID SUM$ (cr=2 pr=2 pw=0 time=0 us cost=1 size=11 card=1)
      1       INDEX UNIQUE SCAN I_SUM$_1 (cr=1 pr=1 pw=0 time=0 us cost=0 size=0 card=1)(object id 956)
  72047      FILTER  (cr=1723 pr=4 pw=0 time=547063 us)
  72925       HASH JOIN  (cr=907 pr=0 pw=0 time=1221029 us cost=252 size=7989850 card=72635)
     92        INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=182 us cost=1 size=368 card=92)(object id 47)
  72925        HASH JOIN  (cr=906 pr=0 pw=0 time=682076 us cost=250 size=7699310 card=72635)
     92         INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=273 us cost=1 size=2024 card=92)(object id 47)  72925         TABLE ACCESS FULL OBJ$ (cr=905 pr=0 pw=0 time=194549 us cost=248 size=6101340 card=72635)
   3775       TABLE ACCESS BY INDEX ROWID IND$ (cr=816 pr=4 pw=0 time=0 us cost=2 size=8 card=1)
   4648        INDEX UNIQUE SCAN I_IND1 (cr=85 pr=4 pw=0 time=0 us cost=1 size=0 card=1)(object id 41)
      0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
      0        INDEX FULL SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
      0        INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)
      1      NESTED LOOPS  (cr=2 pr=1 pw=0 time=0 us cost=2 size=7 card=1)
      1       INDEX FULL SCAN I_LINK1 (cr=1 pr=1 pw=0 time=0 us cost=1 size=3 card=1)(object id 137)
      1       INDEX RANGE SCAN I_USER2 (cr=1 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 47)

Conclusão:

Nesse último caso quando as colunas da clausula GROUP BY e ORDER BY são as mesmas o algoritmo SORT GROUP BY mostrou-se um pouco mais eficiente, lembrando que em nossos testes poucas linhas foram ordenadas e que essa diferença aumentaria no caso de uma ordenação maior.

Antes de qualquer migração: Muitos Testes são necessários!!!

Abraços

By José Eduardo Fiamengui Júnior

Sobre oradeep

José Eduardo Fiamengui Júnior Graduação: Tecnologia em Informática pela Universidade Estadual de Campinas (Unicamp) Pós-Graduação: Administração em Banco de Dados Oracle pelo Instituto Brasileiro de Tecnologia Avançada (IBTA) Mba em Gestão Estratégica em TI pela FGV OCE Certified ITIL Certified Empresa Atual: Dba Oracle e Performance Specialist na Ccee Empresa Atual: Instrutor Oracle IBTA
Esta entrada foi publicada em Oracle. ligação permanente.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s