Test of GROUP BY operation in ORACLE 11.1.0.7.0 64-bit RAC on RHL 5
PGA_TARGET set to 20GB
96GB Physical RAM per Node
Test is done in Intra-node Parallel Mode (coordinator & all slaves are in the same node)

Aggregate a table with about 300M rows which is hash partitioned by (PK1, PK2) in 4-way, 8-way or 16-way.

create table test_temp1 nologging parallel 8 as

SELECT  PK1, PK2,
                   MAX (ATTR1) AS attr1,
                   MAX (ATTR2) AS attr2,

           ......

                    MAX (ATTRx) AS attrx,

                    SUM (METRIC1) AS metric1,

                    SUM (METRIC2) AS metric2,

           ......

                    SUM (METRICx) AS metricx

   FROM TEST_TABLE_300M

        GROUP BY PK1, PK2 

 Id  

 Operation                

 Name                        

 Rows  

 Bytes

TempSpc

 Cost  

 Pstart

 Pstop

    TQ  

IN-OUT

 PQ Distrib

0

 CREATE TABLE STATEMENT  

                             

71M

5110M

       

228K

       

       

       

     

           

1

  PX COORDINATOR          

                             

     

       

       

       

       

       

       

     

           

2

   PX SEND QC (RANDOM)    

 :TQ10001                    

71M

5110M

       

220K

       

       

  Q1,01

 P->S

 QC (RAND)  

3

        LOAD AS SELECT        

 TEST_TEMP1                  

       

       

       

       

       

       

  Q1,01

 PCWP

           

4

         HASH GROUP BY        

                             

71M

5110M

37G

220K

       

       

  Q1,01

 PCWP

           

5

          PX RECEIVE          

                             

288M

20G

       

7950

       

       

  Q1,01

 PCWP

           

6

           PX SEND HASH      

 :TQ10000                    

288M

20G

       

7950

       

       

  Q1,00

 P->P

 HASH      

7

            PX BLOCK ITERATOR

                             

288M

20G

       

7950

1

8

  Q1,00

 PCWC

           

*  8

             TABLE ACCESS FULL

 TEST_TABLE_300M

288M

20G

       

7950

113

120

  Q1,00

 PCWP

           

The result shows some difference of execution time, but no major performance advantage of hash group by. Screen shots from SQL*Plus with “set autotrace on”

Table DOP = 8

SORT GROUP BY

HASH GROUP BY

 4-way hash

Elapsed: 00:08:06.68

Statistics

---------------------------------------------------

           3544  recursive calls

           1219  db block gets

        1488205  consistent gets

        4031290  physical reads

            676  redo size

            339  bytes sent via SQL*Net to client

            598  bytes received via SQL*Net from client

              2  SQL*Net roundtrips to/from client

             11  sorts (memory)

              8  sorts (disk)

              1  rows processed

Elapsed: 00:08:38.33

Statistics

-----------------------------------------------------

           3614  recursive calls

              4  db block gets

        1488304  consistent gets

        3471727  physical reads

            632  redo size

            339  bytes sent via SQL*Net to client

            598  bytes received via SQL*Net from client

              2  SQL*Net roundtrips to/from client

             11  sorts (memory)

              0  sorts (disk)

              1  rows processed

8-way hash

Elapsed: 00:07:31.14

Statistics

----------------------------------------------------

           2845  recursive calls

           1275  db block gets

         327837  consistent gets

        3090843  physical reads

            632  redo size

            339  bytes sent via SQL*Net to client

            588  bytes received via SQL*Net from client

              2  SQL*Net roundtrips to/from client

              8  sorts (memory)

              8  sorts (disk)

              1  rows processed

Elapsed: 00:05:30.39

Statistics

----------------------------------------------------

           2319  recursive calls

              3  db block gets

         327837  consistent gets

        2542910  physical reads

            632  redo size

            339  bytes sent via SQL*Net to client

            588  bytes received via SQL*Net from client

              2  SQL*Net roundtrips to/from client

              8  sorts (memory)

              0  sorts (disk)

              1  rows processed

16-way hash

Elapsed: 00:09:11.07

Statistics

----------------------------------------------------

           2641  recursive calls

           1235  db block gets

         327252  consistent gets

        2862847  physical reads

            632  redo size

            339  bytes sent via SQL*Net to client

            606  bytes received via SQL*Net from client

              2  SQL*Net roundtrips to/from client

             16  sorts (memory)

              8  sorts (disk)

              1  rows processed

Elapsed: 00:08:31.53

Statistics

---------------------------------------------------

          13153  recursive calls

              3  db block gets

         329359  consistent gets

        2322689  physical reads

            632  redo size

            339  bytes sent via SQL*Net to client

            606  bytes received via SQL*Net from client

              2  SQL*Net roundtrips to/from client

             25  sorts (memory)

              0  sorts (disk)

              1  rows processed