Docstoc

Oracle related stuff_ Hash Aggregation

Document Sample
Oracle related stuff_ Hash Aggregation Powered By Docstoc
					8/10/12                                                Oracle related stuff: Hash Aggregation


                                                  Share      Report Abuse           Next Blog»         Create Blog   Sign In




          Oracle related stuff

          Sunday, January 16, 2011                                                               Blog Archive
          Hash Aggregation
                                                                                                 ► 2012 (24)
          Oracle introduced in Oracle 10g the hash aggregation as a new feature.
          It can be used for both GROUP BY and UNIQUE operations (HASH GROUP                     ▼ 2011 (34)
          BY and HASH UNIQUE respectively) and is by default the preferred
                                                                                                   ► December (2)
          aggregation method if there is no particular reason that lets the cost
          based optimizer prefer the sort based aggregation (SORT GROUP BY and                     ► November (4)
          SORT UNIQUE), for example if the GROUP BY is followed by an ORDER BY
          on the same expression (using the SORT GROUP BY in such cases is not                     ► October (3)
          always beneficial by the way, see Guy Harrison's blog for an example).
                                                                                                   ► August (3)
          Ever since its introduction from time to time I've heard complaints about
          performance degradations of aggregation operations that are based on                     ► July (3)
          the new hash aggregation algorithm compared to the previously used
                                                                                                   ► June (3)
          sort based aggregations.
                                                                                                   ► May (3)
          Now there may be many potential reasons for such performance
          degradations (and possibly many of them might not have anything to do                    ► April (3)
          with the hash aggregation) but here is a surprising revelation that might
          explain why some of them were indeed caused by the switch to the new                     ► March (3)
          algorithm: The hash aggregation operation does not work very well
          together with the automatic PGA management                                               ► February (3)
          (WORKAREA_SIZE_POLICY = AUTO, default since 9i). The fundamental
                                                                                                   ▼ January (4)
          defect is that it is not able to dynamically resize to a larger workarea
          size when using automatic PGA management and therefore remains more                        Quiz Night
          or less at its initial expected size based on the estimates at optimization
          time.                                                                                      Pending
                                                                                                       Statistics
          This effectively means that the efficiency of the hash aggregation
          operation when using automatic PGA management is heavily dependant                         Hash
          on the cardinality estimates at optimization time - in case of estimates                     Aggregation
          in the right ballpark, the memory used at execution time will correspond
          to the actual requirements at runtime, but in case of bad estimates, the                   Adaptive Cursor
          operation potentially uses far less memory than available and                                Sharing
          unnecessarily spills to disk.

          Let's start with a simple script to demonstrate the issue:                             ► 2010 (14)

                                                                                                 ► 2009 (32)
           e co n iig n
          steh o tmn o                                                                           ► 2008 (13)
          so prmtrpa
           hw aaee g                                                                             ► 2007 (5)
           hw aaee rcse
          so prmtrpoess

           - le eso e okrasz_oiy aul otae_ie
          - atrssinstwrae_ieplc =mna sr_rasz =
                                                                                                 Subscribe To
            0000
           4000;

oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                                                   1/46
8/10/12                                                Oracle related stuff: Hash Aggregation

                                                                                                    Posts
           rp al 1 ug;
          do tbet pre
                                                                                                    Comments
           rp al 2 ug;
          do tbet pre

           rae al 1
          cet tbet                                                                              Contact Details
          as
          slc mdrwu,2)a ue_d rwu a ojc_dfo da c
           eet o(onm 7 s sri, onm s beti rm ul o                                                Randolf Geist,
          netb lvl< 300
           nc y ee = 00;                                                                        Independent
                                                                                                consultant
           rae al 2
          cet tbet                                                                              Oracle performance
          as                                                                                    troubleshooting
           eet itnt sri rm 1
          slc dsic ue_dfo t;
                                                                                                Database design for
                                                                                                good performance
           xc bssasgte_al_tt(ul t' ehdot > fr
          ee dm_tt.ahrtbesasnl,'1,mto_p = 'o a
                                                                                                In-house seminars
           l oun ie '
          l clmssz 1)
                                                                                                and workshops for
          ee dm_tt.ahrtbesasnl,'2,mto_p = 'o a
           xc bssasgte_al_tt(ul t' ehdot > fr                                                   developers -
          l clmssz 1)
           l oun ie '                                                                           application design for
                                                                                                performance and
          clm lwvlnwvlelwvle
           oun o_a e_au o_au                                                                    scalability
           oun ihvl e_au ihvle
          clm hg_a nwvlehg_au
                                                                                                Contact me:
          slc
           eet                                                                                  info@sqltools-
                     mxue_d +1a lwvl
                      a(sri)   s o_a                                                            plusplus.org
                      a(sri)  a(sri)  i(sri)   s ihvl
                    ,mxue_d +mxue_d -mnue_d +1a hg_a
           rm
          fo                                                                                    If you're looking for
                         2
                        t;                                                                      SQLTools++, the
                                                                                                open source
           - naiae n usr sn 1
          - Ivldt aycrosuigT
                                                                                                Windows(tm) Oracle
           omn n al 1 s '
          cmeto tbet i ';
                                                                                                frontend GUI that I
          atrssinstsaitc_ee =al
           le eso e ttsislvl  l;                                                                maintain, you can
                                                                                                find more information
          - atrssinsttaeieietfe ='m_rc_orc_ad
           - le eso e rcfl_dniir smtaecretcr                                                    on its homepage and
          iaiy;
           nlt'                                                                                 sourceforge.net.

           - le eso e _m_rc"  53;
          - atrssinst"smtae =655
                                                                                                Upcoming Public
          slc /+fl(inrt)
           eet * ul@ne 1                                                                        Appearances
                   ul@ne 2
                  fl(inrt)                                                                      12th October: Free
                   ul@ne 3
                  fl(inrt)
                                                                                                Webinar at DOAG:
                   edn(inr 2 3 1
                  laig@ne t t t)
                                                                                                Cost-Based Optimizer
                   s_l@ne 3
                  uen(inrt)
                                                                                                Grundlagen
                   s_ah@ne 1
                  uehs(inrt)
                  n_wpji_nus@ne t)
                   osa_onipt(inr 1                                                              December: CBO Days
                  uehs_grgto(inr
                   s_ahageain@ne)                                                               at Trivadis, Zürich
               */                                                                               2013: DOAG
          mxct
           a(n)                                                                                 Expertenseminar,
          fo
           rm                                                                                   Berlin
          (
           eet * bnm(ne) / on() s n, 1ojc_d 3ue
          slc /+q_aeinr * cut* a ct t.beti,t.sr
          _d
           i                                                                                    OakTable Book
          fo t,t,t t
           rm 1 2 2 3                                                                           "Expert Oracle
          weet.sri =t.sri
           hr 1ue_d    2ue_d                                                                    Practices"
           ru y 1ojc_d 3ue_d
          gopb t.beti,t.sri
          )
          ;

           - le eso e _m_rc"
          - atrssinst"smtae =0;

oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                                                 2/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           e aeie  ieie 0 rmpo n a f
          stpgsz 0lnsz 20tisolo tbof

           eet   rm al(bsxlndslycro(ul ul ALTT
          slc *fo tbedm_pa.ipa_usrnl,nl,'LSAS
            AT);
           LS')

           e aeie 4
          stpgsz 1

           - ug h ttsis o ht h on adnlt ewe 1 n
          - Fdetesaitc s ta teji criaiybtent ad
            2 il e  rudd p o )
           t wl b 0(one u t 1
           elr
          dcae
            rc bssassarc
           se dm_tt.tte;
            oas bssasnmra;
           nvl dm_tt.uary
           dsctnme;
            itn ubr                                                                             Co-author of the
           aglnnme;
            vce ubr                                                                             OakTable Book
           nlctnme;
            uln ubr                                                                             "Expert Oracle
           dniynme;
            est ubr                                                                             Practices" by APress.
           se2dm_tt.tte;
            rc bssassarc                                                                        Available now
           ei
          bgn
             bssasgtclm_tt(ul t' ue_d, itn > i
            dm_tt.e_ounsasnl,'1,'sri' dsct= ds
                                                                                                Blogroll
           ct vce > vce, uln > uln, est > est,
          tn,agln= agln nlct= nlct dniy= dniy s
           e > rc;
          rc= se)
                                                                                                   Christian
             rc.p = ;
            se2ec: 2
                                                                                                   Antognini
             oas = bssasnmra(
            nvl : dm_tt.uary
            &o_au,
             lwvle                                                                                 Event 10046 – Full
            &ihvle
             hg_au                                                                                 List of Levels
            );                                                                                     9 hours ago
             rc.kas = ul
            se2bvl : nl;
            dm_tt.rpr_ounvle(rc,oas;
             bssaspeaeclm_ausse2nvl)                                                               Jonathan Lewis
            dm_tt.e_ounsas
             bssasstclm_tt(                                                                        Debugging
            onae>ul
             wnm=nl,                                                                               23 hours ago
             anm='1,
            tbae>t'
            clae>ue_d,
             onm='sri'                                                                             Tanel Poder
            dsct>itn,
             itn=dsct                                                                              Future
            nlct>uln,
             uln=nlct                                                                              Appearances 2012
            se=se2
             rc>rc,                                                                                1 week ago
             vce=agln
            agln>vce,
            dniy>est
             est=dniy                                                                              Martin Bach
            );                                                                                     Lights-out
          ed
           n;                                                                                      management
          /                                                                                        console on
                                                                                                   Supermicro boards
           - naiae n usr sn 1
          - Ivldt aycrosuigT                                                                       1 week ago
           omn n al 1 s '
          cmeto tbet i ';
                                                                                                   Timur
           le eso e ttsislvl  l;
          atrssinstsaitc_ee =al                                                                    Akhmadeev
                                                                                                   Adaptive STAT
           - le eso e rcfl_dniir smtaewogcri
          - atrssinsttaeieietfe ='m_rc_rn_adn
                                                                                                   lines in SQL trace
           lt'
          aiy;
                                                                                                   1 week ago
           - le eso e _m_rc"  53;
          - atrssinst"smtae =655
                                                                                                   Charles Hooper
                                                                                                   Parallel Parking –
           eet * ul@ne 1
          slc /+fl(inrt)
                 fl(inrt)
                  ul@ne 2                                                                          What is causing
                 fl(inrt)
                  ul@ne 3                                                                          the Query to be
                 laig@ne t t t)
                  edn(inr 2 3 1                                                                    Executed in
                 uen(inrt)
                  s_l@ne 3                                                                         Parallel?
                 uehs(inrt)
                  s_ah@ne 1                                                                        1 week ago
                  osa_onipt(inr 1
                 n_wpji_nus@ne t)
                 uehs_grgto(inr
                  s_ahageain@ne)                                                                   Alex Fatkulin
              */                                                                                   Exporting DBFS via
          mxct
           a(n)                                                                                    NFS
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                                                3/46
8/10/12                                                Oracle related stuff: Hash Aggregation

          fo
           rm                                                                                   2 weeks ago
          (
          slc /+q_aeinr * cut* a ct t.beti,t.sr
           eet * bnm(ne) / on() s n, 1ojc_d 3ue                                                 Kerry Osborne
          _d
           i                                                                                    Exadoop
          fo t,t,t t
           rm 1 2 2 3                                                                           2 weeks ago
           hr 1ue_d   2ue_d
          weet.sri =t.sri
          gopb t.beti,t.sri
           ru y 1ojc_d 3ue_d                                                                    Hemant K
          )                                                                                     Chitale
          ;                                                                                     How to use Oracle
                                                                                                Virtual Box
           - le eso e _m_rc"
          - atrssinst"smtae =0;
                                                                                                templates
                                                                                                2 weeks ago
           e aeie  ieie 0 rmpo n a f
          stpgsz 0lnsz 20tisolo tbof
                                                                                                Oracle
           eet   rm al(bsxlndslycro(ul ul ALTT
          slc *fo tbedm_pa.ipa_usrnl,nl,'LSAS
                                                                                                Optimizer
            AT);
           LS')
                                                                                                Development
                                                                                                Group
                                                                                                Upcoming events:
                                                                                                INOUG Oracle
                                                                                                Training Day!
          I first start with showing the current PGA_AGGREGATE_TARGET (P_A_T)
          and PROCESSES setting.                                                                3 weeks ago

                                                                                                Richard Foote
          I create then two tables as sample data that will be joined and already
                                                                                                InSync12
          collect information about the minimum and maximum value of the join
                                                                                                Conference:
          column.
                                                                                                Melbourne 21-22
                                                                                                August 2012
          Any cursors that are dependent on table T1 will then be invalidated to
                                                                                                4 weeks ago
          make sure that a reoptimization of the next query will take place (of
          course running the complete script invalidates such cursors anyway due                Greg Rahn
          to the drop and re-create of the tables).                                             Linux 6
                                                                                                Transparent Huge
          I then run a simple join followed by a group by operation. I've added a               Pages and Hadoop
          cartesian join by the way, but its only purpose is to ensure that the                 Workloads
          generated row source is sufficiently large to give the group by                       1 month ago
          something to do.
                                                                                                Arup Nanda
          I have used hints to ensure that I always get the same execution plan                 Quiz: Mystery of
          even if I later on manipulate the statistics to see the effect of incorrect           Create Table
          cardinality estimates.                                                                Statement
                                                                                                2 months ago
          After getting the actual runtime execution plan along with execution
          statistics I modify the column statistics of one of the tables' join column           Alberto Dell'Era
          in such a way that the optimizer thinks that there is no overlap between              Third International
          the join column values and therefore computes a very low join                         NoCOUG SQL &
          cardinality.                                                                          NoSQL Challenge!
                                                                                                2 months ago
          After making sure again that a reoptimization will take place I run the
          same statement again with the same data volume and the same hints in                  Rob van Wijk
          place.                                                                                Much Ado About
                                                                                                Nothing?
          And this is what I get for all major versions that are currently out there            2 months ago
          (10.2.0.4, 10.2.0.5, 11.1.0.7, 11.2.0.1 and 11.2.0.2):
                                                                                                Martin Berger
                                                                                                who cares if a
                                                                                                listener is dying
           once o
          Cnetdt:
                                                                                                3 months ago
           rce aaae 0 nepie dto ees 02050  4i
          Oal Dtbs 1gEtrrs EiinRlae1.... -6btP
           outo
          rdcin
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                                              4/46
8/10/12                                                Oracle related stuff: Hash Aggregation

          Wt tePriinn,OA,Dt Mnn adRa ApiainTsi
           ih h attoig LP aa iig n el plcto et                                                  Joze Senegacnik
           g pin
          n otos                                                                                Oracle XML
                                                                                                Training with
           Q>
          SL
                                                                                                Marco Gralike in
           Q> hw aaee g
          SL so prmtrpa
                                                                                                Ljubljana
                                                                                                5 months ago
           AE
          NM                  YE
                             TP      AU
                                    VLE
           ------------------ ----- ---------
          ------------------ ---------------
                                                                                                  Adrian Billington
           ------
          ------
                                                                                                  Profiling trace files
           g_grgt_agt
          paageaetre          i nee 0M
                             bgitgr20
                                                                                                  with preprocessor
           Q> hw aaee rcse
          SL so prmtrpoess
                                                                                                  external tables in
          NM
           AE                  TP
                                YE  VLE
                                     AU                                                           11g
          ------------------ ---------------
           ------------------ ----- ---------                                                     8 months ago
           ------
          ------
          a_mpoess
           qt_rcse             itgr
                                nee 0                                                             Julian Dyke
          d_rtrpoess
           bwie_rcse           itgr
                                nee 1                                                             DBFS
          gssre_rcse
           c_evrpoess          itgr
                                nee 0                                                             9 months ago
           o_uu_rcse
          jbqeepoess            nee
                               itgr 10
          lgaciemxpoess
           o_rhv_a_rcse        itgr
                                nee 2                                                             Dion Cho
          poess
           rcse                itgr
                                nee 10
                                     5                                                            Interesting case of
          SL
           Q>                                                                                     the restriction of
          SL - atrssinstwrae_ieplc =mna sr_ras
           Q> - le eso e okrasz_oiy  aul otae_                                                    global hints
          ie=4000;
           z   0000                                                                               1 year ago
           Q>
          SL
           Q> rp al 1 ug;
          SL do tbet pre
           rp al 1 ug
          do tbet pre
                 *
           RO t ie :
          ERRa ln 1
           R-04: al r iw os o xs
          OA092 tbeo ve de nteit


           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> rp al 2 ug;
          SL do tbet pre
           rp al 2 ug
          do tbet pre
                 *
           RO t ie :
          ERRa ln 1
           R-04: al r iw os o xs
          OA092 tbeo ve de nteit


           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> rae al 1
          SL cet tbet
            2 as
               eet o(onm 7 s sri, onm s beti rm u
            3 slc mdrwu,2)a ue_d rwu a ojc_dfo d
           l onc y ee = 00;
          a cnetb lvl< 300

           al rae.
          Tbecetd

           lpe: 00:00
          Easd 0:00.7
           Q>
          SL
           Q> rae al 2
          SL cet tbet
            2 as
               eet itnt sri rm 1
            3 slc dsic ue_dfo t;

           al rae.
          Tbecetd

           lpe: 00:00
          Easd 0:00.6
           Q>
          SL
           Q> xc bssasgte_al_tt(ul t' ehdot >
          SL ee dm_tt.ahrtbesasnl,'1,mto_p = '
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                                                  5/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           o l oun ie '
          fralclmssz 1)

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.9
           Q>
          SL
           Q> xc bssasgte_al_tt(ul t' ehdot >
          SL ee dm_tt.ahrtbesasnl,'2,mto_p = '
           o l oun ie '
          fralclmssz 1)

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL
           Q> oun o_a e_au o_au
          SL clm lwvlnwvlelwvle
           Q> oun ihvl e_au ihvle
          SL clm hg_a nwvlehg_au
           Q>
          SL
           Q> eet
          SL slc
            2       a(sri)   s o_a
                   mxue_d +1a lwvl
            3       a(sri)  a(sri)  i(sri)   s ih
                  ,mxue_d +mxue_d -mnue_d +1a hg
           vl
          _a
               rm
            4 fo
            5       2
                   t;

             O_A  IHVL
            LWVL HG_A
           ----- -----
          ----- -----
               27    53

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> - naiae n usr sn 1
          SL - Ivldt aycrosuigT
           Q> omn n al 1 s '
          SL cmeto tbet i ';

           omn rae.
          Cmetcetd

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL
           Q> le eso e ttsislvl  l;
          SL atrssinstsaitc_ee =al

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> - le eso e rcfl_dniir     smtaecret
          SL - atrssinsttaeieietfe ='m_rc_orc
           criaiy;
          _adnlt'
           Q>
          SL
           Q> - le eso e _m_rc"
          SL - atrssinst"smtae =655 53;
           Q>
          SL
           Q> eet * ul@ne 1
          SL slc /+fl(inrt)
            2        ul@ne 2
                     fl(inrt)
            3        ul@ne 3
                     fl(inrt)
            4        edn(inr 2 3 1
                     laig@ne t t t)
            5        s_l@ne 3
                     uen(inrt)
            6        s_ah@ne 1
                     uehs(inrt)
            7        osa_onipt(inr 1
                     n_wpji_nus@ne t)
            8        s_ahageain@ne)
                     uehs_grgto(inr
            9     */
            0 a(n)
           1 mxct
            1 rm
           1 fo
            2
           1 (
            3 eet * bnm(ne) / on() s n, 1ojc_d 3
           1 slc /+q_aeinr * cut* a ct t.beti,t
           ue_d
          .sri
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        6/46
8/10/12                                                Oracle related stuff: Hash Aggregation

            14      rm 1 2 2 3
                   fo t,t,t t
            15      hr 1ue_d   2ue_d
                   weet.sri =t.sri
            16      ru y 1ojc_d 3ue_d
                   gopb t.beti,t.sri
            17     )
            18     ;

             A(N)
            MXCT
           -----
          -----
                1

           lpe: 00:07
          Easd 0:00.1
           Q>
          SL
           Q> - le eso e _m_rc"
          SL - atrssinst"smtae =0   ;
           Q>
          SL
           Q> e aeie   ieie 0 rmpo n a f
          SL stpgsz 0lnsz 20tisolo tbof
           Q>
          SL
           Q> eet  rm al(bsxlndslycro(ul ul AL
          SL slc *fo tbedm_pa.ipa_usrnl,nl,'L
           TT AT);
          SASLS')
           Q_D ssfvav, hl ubr
          SLI 21dhhj3 cidnme 0
           ------------------
          -------------------
           eet * ul@ne 1
          slc /+fl(inrt)          ul@ne 2
                                 fl(inrt)         fu
           l@ne 3
          l(inrt)          edn(inr
                          laig@ne
           2 3 1
          t t t)        s_l@ne 3
                       uen(inrt)         s_ah@ne
                                        uehs(inrt
          1)       osa_onipt(inr
                   n_wpji_nus@ne
           1
          t)        s_ahageain@ne)
                   uehs_grgto(inr         / a(n) rm
                                         * mxct fo
             eet * bnm(ne) / on()
           (slc /+q_aeinr * cut*
           s n, 1ojc_d 3ue_d rm 1 2 2 3 hr 1ue_
          a ct t.beti,t.sri fo t,t,t t weet.sri
              2ue_d ru y 1ojc_d
          d=t.sri gopb t.beti,
           3ue_d
          t.sri )

           ln ah au: 14404
          Pa hs vle 33829

           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------
             d  prto
          |I |Oeain                 ae    trs    -os
                                 |Nm |Sat |ERw |ARw |   -os
             -ie |Bfes| Oe | 1e |Ue-e |
            ATm      ufr    Mm     Mm    sdMm
           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------
                EET TTMN
          | 0|SLC SAEET          |     |     1|       |    1|0
           :00.2
          00:07 |       3
                       19|     |      |        |
                 OT GRGT
          | 1| SR AGEAE          |     |     1|     1|     1|0
           :00.2
          00:07 |       3
                       19|     |      |        |
          | 2| VE IW             |     |     1|    7K
                                                  50|     1K0
                                                          80|
           :00.3
          00:04 |       3
                       19|     |      |        |
          | 3|     AH RU Y
                  HS GOPB        |     |     1|    7K
                                                  50|     1K0
                                                          80|
           :00.3
          00:04 |       3
                       19|    5| 51| 3M()
                             3M 52K        8 0|
           *
          | 4|      AH ON
                   HS JI         |     |     1|    0K
                                                  87|     1K0
                                                          80|
           :00.2
          00:00 |       3   38| 38| 03 0|
                       19| 14K 14K 19K()
          | 5|       ETD OP
                    NSE LOS      |     |     1|    2
                                                  79|     2 0
                                                          79|
           :00.1
          00:00 |        4
                        8 |    |      |        |
          | 6|       AL CES UL 2 |
                     TBEACS FL|T             1|     7
                                                   2 |     7 0
                                                          2 |
           :00.1
          00:00 |        3|    |      |        |
          | 7|       AL CES UL 2 |
                     TBEACS FL|T            2 |
                                             7      7
                                                   2 |    2 0
                                                          79|
           :00.1
          00:00 |        1
                        8 |    |      |        |
          | 8|       AL CES UL
                    TBEACS FL |T    1 |           90
                                             1| 297| 300|00 0
           :00.1
          00:00 |        5
                        5 |    |      |        |
           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------

           rdct nomto ietfe y prto d:
          PeiaeIfrain(dniidb oeaini)
           -------------------------
          --------------------------
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        7/46
8/10/12                                                Oracle related stuff: Hash Aggregation



                 ces"1.UE_D=T""SRI"
               4-acs(T""SRI""2.UE_D)


           9 os eetd
          2 rw slce.

           lpe: 00:01
          Easd 0:00.2
           Q>
          SL
           Q> e aeie 4
          SL stpgsz 1
           Q>
          SL
           Q> - ug h ttsis o ht h on adnlt ewe
          SL - Fdetesaitc s ta teji criaiybtent
             n 2 il e    rudd p o )
          1adt wl b 0(one u t 1
           Q> elr
          SL dcae
                rc bssassarc
            2 se dm_tt.tte;
                oas bssasnmra;
            3 nvl dm_tt.uary
                itn ubr
            4 dsctnme;
                vce ubr
            5 aglnnme;
                uln ubr
            6 nlctnme;
                est ubr
            7 dniynme;
                rc bssassarc
            8 se2dm_tt.tte;
               ei
            9 bgn
           10    bssasgtclm_tt(ul t' ue_d, itn
                dm_tt.e_ounsasnl,'1,'sri' dsct=
             itn, vce > vce, uln > uln, est > es
          >dsct agln= agln nlct= nlct dniy= dni
           y rc > rc;
          t,se = se)
           11    rc.p = ;
                se2ec: 2
           12    oas = bssasnmra(
                nvl : dm_tt.uary
           13    lwvle
                &o_au,
           14    hg_au
                &ihvle
           15   );
           16    rc.kas = ul
                se2bvl : nl;
           17    bssaspeaeclm_ausse2nvl)
                dm_tt.rpr_ounvle(rc,oas;
           18    bssasstclm_tt(
                dm_tt.e_ounsas
           19    wnm=nl,
                onae>ul
           20    anm='1,
                tbae>t'
           21    onm='sri'
                clae>ue_d,
           22    itn=dsct
                dsct>itn,
           23    uln=nlct
                nlct>uln,
           24    rc>rc,
                se=se2
           25    vce=agln
                agln>vce,
           26    est=dniy
                dniy>est
           27   );
            8 n;
           2 ed
            9
           2 /
           l   3   lwvle
          od 1: &o_au,
           e
          nw 1:3         7
                        2,
           l   4   hg_au
          od 1: &ihvle
           e
          nw 1:4        53

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL
           Q> - naiae n usr sn 1
          SL - Ivldt aycrosuigT
           Q> omn n al 1 s '
          SL cmeto tbet i ';

           omn rae.
          Cmetcetd

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL
           Q> le eso e ttsislvl  l;
          SL atrssinstsaitc_ee =al

oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        8/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> - le eso e rcfl_dniir     smtaewogc
          SL - atrssinsttaeieietfe ='m_rc_rn_
           riaiy;
          adnlt'
           Q>
          SL
           Q> - le eso e _m_rc"
          SL - atrssinst"smtae =655 53;
           Q>
          SL
           Q> eet * ul@ne 1
          SL slc /+fl(inrt)
            2        ul@ne 2
                     fl(inrt)
            3        ul@ne 3
                     fl(inrt)
            4        edn(inr 2 3 1
                     laig@ne t t t)
            5        s_l@ne 3
                     uen(inrt)
            6        s_ah@ne 1
                     uehs(inrt)
            7        osa_onipt(inr 1
                     n_wpji_nus@ne t)
            8        s_ahageain@ne)
                     uehs_grgto(inr
            9     */
            0 a(n)
           1 mxct
            1 rm
           1 fo
            2
           1 (
            3 eet * bnm(ne) / on() s n, 1ojc_d 3
           1 slc /+q_aeinr * cut* a ct t.beti,t
           ue_d
          .sri
            4 rm 1 2 2 3
           1 fo t,t,t t
            5 hr 1ue_d    2ue_d
           1 weet.sri =t.sri
            6 ru y 1ojc_d 3ue_d
           1 gopb t.beti,t.sri
            7
           1 )
            8
           1 ;

             A(N)
            MXCT
           -----
          -----
                1

           lpe: 00:13
          Easd 0:00.7
           Q>
          SL
           Q> - le eso e _m_rc"
          SL - atrssinst"smtae =0      ;
           Q>
          SL
           Q> e aeie    ieie 0 rmpo n a f
          SL stpgsz 0lnsz 20tisolo tbof
           Q>
          SL
           Q> eet   rm al(bsxlndslycro(ul ul AL
          SL slc *fo tbedm_pa.ipa_usrnl,nl,'L
           TT AT);
          SASLS')
           Q_D ssfvav, hl ubr
          SLI 21dhhj3 cidnme 0
           ------------------
          -------------------
           eet * ul@ne 1
          slc /+fl(inrt)             ul@ne 2
                                    fl(inrt)      fu
           l@ne 3
          l(inrt)           edn(inr 2 3 1
                           laig@ne t t t)
           s_l@ne 3
          uen(inrt)            s_ah@ne 1
                              uehs(inrt)        osa
                                               n_wp
           ji_nus@ne 1
          _onipt(inrt)             s_ahageain@ne)
                                  uehs_grgto(inr
             / a(n) rm   eet * bnm(ne) / on() s n,
            * mxct fo (slc /+q_aeinr * cut* a ct
           1ojc_d 3ue_d rm 1 2 2 3 hr 1ue_d
          t.beti,t.sri fo t,t,t t weet.sri =
           2ue_d ru y 1ojc_d 3ue_d
          t.sri gopb t.beti,t.sri )

           ln ah au: 14404
          Pa hs vle 33829

           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----
          -----
             d  prto
          |I |Oeain           ae  trs  -os  -os
                            |Nm |Sat |ERw |ARw |
             -ie |Bfes|Ras |Wie | Oe | 1e |Ue-e |
            ATm    ufr  ed   rts   Mm   Mm  sdMm
            sdTp
           Ue-m|
           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        9/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           -----
          -----
                 EET TTMN
          | 0|SLC SAEET         |    |    1|      |   1|0
           :00.7
          00:13 |        3
                        19| 21 | 21 |
                             75    75       |    |      |
                 |
                  OT GRGT
          | 1| SR AGEAE         |    |    1|    1|    1|0
           :00.7
          00:13 |        3
                        19| 21 | 21 |
                             75    75       |    |      |
                 |
          | 2| VE  IW           |    |    1|    1|   1K0
                                                     80|
           :00.2
          00:06 |        3
                        19| 21 | 21 |
                             75    75       |    |      |
                 |
          | 3|      AH RU Y
                   HS GOPB      |    |    1|    1|   1K0
                                                     80|
           :00.2
          00:06 |        3   75    75
                        19| 21 | 21 |     5| 51| 00 1|
                                         3M 52K 71K()
              35
             252|
           *
          | 4|       AH ON
                    HS JI       |    |    1|    1|   1K0
                                                     80|
           :00.1
          00:00 |        3
                        19|   0|        38| 38| 17 0|
                                    0| 14K 14K 16K()
                 |
          | 5|        ETD OP
                     NSE LOS    |    |    1|   2
                                              79|    2 0
                                                     79|
           :00.1
          00:00 |         4
                         8 |  0|    0|      |    |      |
                 |
          | 6|        AL CES UL 2 |
                      TBEACS FL|T         1|    7
                                               2 |    7 0
                                                     2 |
           :00.1
          00:00 |         3|  0|    0|      |    |      |
                 |
          | 7|        AL CES UL 2 |
                      TBEACS FL|T        2 |
                                          7     7
                                               2 |   2 0
                                                     79|
           :00.1
          00:00 |         1
                         8 |  0|    0|      |    |      |
                 |
          | 8|        AL CES UL
                     TBEACS FL |T 1 |         90    00 0
                                          1| 297| 300|
           :00.1
          00:00 |         5
                         5 |  0|    0|      |    |      |
                 |
           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----
          -----

           rdct nomto ietfe y prto d:
          PeiaeIfrain(dniidb oeaini)
           -------------------------
          --------------------------

                 ces"1.UE_D=T""SRI"
               4-acs(T""SRI""2.UE_D)


           8 os eetd
          2 rw slce.

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL




          As it can be seen with an estimate in the right ballpark the HASH GROUP
          BY operation completes in memory (very close to the 20%
          PGA_AGGREGATE_TARGET maximum size of a single workarea from 10.2
          on with automatic PGA management for PGA_AGGREGATE_TARGET <
          500M, for more information see Joze Senegacnik's paper on the internals
          of automatic PGA management).

          However repeating exactly the same operation with the fudged
          statistics it spills to disk and uses only 7M, although it could have used
          up to 40M (given that there is no concurrent workload).

          The same does not happen when repeating this experiment with other
          operations that use a workarea - the most obvious one being a SORT
          GROUP BY, as can be seen from this output:

oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        10/46
8/10/12                                                Oracle related stuff: Hash Aggregation



           once o
          Cnetdt:
           rce aaae 0 nepie dto ees 02050  4i
          Oal Dtbs 1gEtrrs EiinRlae1.... -6btP
           outo
          rdcin
           ih h attoig LP aa iig n el plcto et
          Wt tePriinn,OA,Dt Mnn adRa ApiainTsi
           g pin
          n otos

           Q>
          SL
           Q> hw aaee g
          SL so prmtrpa

           AE
          NM                  YE
                             TP      AU
                                    VLE
           ------------------ ----- ---------
          ------------------ ---------------
           ------
          ------
           g_grgt_agt
          paageaetre          i nee 0M
                             bgitgr20
           Q> hw aaee rcse
          SL so prmtrpoess

           AE
          NM                  YE
                             TP      AU
                                    VLE
           ------------------ ----- ---------
          ------------------ ---------------
           ------
          ------
           qt_rcse
          a_mpoess            nee
                             itgr   0
           bwie_rcse
          d_rtrpoess          nee
                             itgr   1
           c_evrpoess
          gssre_rcse          nee
                             itgr   0
           o_uu_rcse
          jbqeepoess          nee
                             itgr   10
           o_rhv_a_rcse
          lgaciemxpoess       nee
                             itgr   2
           rcse
          poess               nee
                             itgr    5
                                    10
           Q>
          SL
           Q> rp al 1 ug;
          SL do tbet pre

           al rpe.
          Tbedopd

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL
           Q> rp al 2 ug;
          SL do tbet pre

           al rpe.
          Tbedopd

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL
           Q> rae al 1
          SL cet tbet
            2 as
               eet o(onm 5 s sri, onm s beti rm u
            3 slc mdrwu,1)a ue_d rwu a ojc_dfo d
           l onc y ee = 00;
          a cnetb lvl< 300

           al rae.
          Tbecetd

           lpe: 00:00
          Easd 0:00.6
           Q>
          SL
           Q> rae al 2
          SL cet tbet
            2 as
               eet itnt sri rm 1
            3 slc dsic ue_dfo t;

           al rae.
          Tbecetd

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL
           Q> xc bssasgte_al_tt(ul t' ehdot >
          SL ee dm_tt.ahrtbesasnl,'1,mto_p = '
           o l oun ie '
          fralclmssz 1)

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd


oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        11/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           lpe: 00:00
          Easd 0:00.9
           Q>
          SL
           Q> xc bssasgte_al_tt(ul t' ehdot >
          SL ee dm_tt.ahrtbesasnl,'2,mto_p = '
           o l oun ie '
          fralclmssz 1)

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL
           Q> oun o_a e_au o_au
          SL clm lwvlnwvlelwvle
           Q> oun ihvl e_au ihvle
          SL clm hg_a nwvlehg_au
           Q>
          SL
           Q> eet
          SL slc
            2       a(sri)   s o_a
                   mxue_d +1a lwvl
            3       a(sri)  a(sri)  i(sri)   s ih
                  ,mxue_d +mxue_d -mnue_d +1a hg
           vl
          _a
               rm
            4 fo
            5       2
                   t;

             O_A  IHVL
            LWVL HG_A
           ----- -----
          ----- -----
               15    29

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> - naiae n usr sn 1
          SL - Ivldt aycrosuigT
           Q> omn n al 1 s '
          SL cmeto tbet i ';

           omn rae.
          Cmetcetd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e ttsislvl  l;
          SL atrssinstsaitc_ee =al

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> - le eso e rcfl_dniir     smtaecret
          SL - atrssinsttaeieietfe ='m_rc_orc
           criaiy;
          _adnlt'
           Q>
          SL
           Q> - le eso e _m_rc"
          SL - atrssinst"smtae =655 53;
           Q>
          SL
           Q> eet * ul@ne 1
          SL slc /+fl(inrt)
            2        ul@ne 2
                     fl(inrt)
            3        ul@ne 3
                     fl(inrt)
            4        edn(inr 2 3 1
                     laig@ne t t t)
            5        s_l@ne 3
                     uen(inrt)
            6        s_ah@ne 1
                     uehs(inrt)
            7        osa_onipt(inr 1
                     n_wpji_nus@ne t)
            8        ouehs_grgto(inr
                     n_s_ahageain@ne)
            9     */
            0 a(n)
           1 mxct
            1 rm
           1 fo
            2
           1 (
            3 eet * bnm(ne) / on() s n, 1ojc_d 3
           1 slc /+q_aeinr * cut* a ct t.beti,t
           ue_d
          .sri
            4 rm 1 2 2 3
           1 fo t,t,t t
            5 hr 1ue_d    2ue_d
           1 weet.sri =t.sri
            6 ru y 1ojc_d 3ue_d
           1 gopb t.beti,t.sri
            7
           1 )
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        12/46
8/10/12                                                Oracle related stuff: Hash Aggregation

             8
            1 ;

             A(N)
            MXCT
           -----
          -----
                1

           lpe: 00:05
          Easd 0:00.4
           Q>
          SL
           Q> - le eso e _m_rc"
          SL - atrssinst"smtae =0    ;
           Q>
          SL
           Q> e aeie    ieie 0 rmpo n a f
          SL stpgsz 0lnsz 20tisolo tbof
           Q>
          SL
           Q> eet   rm al(bsxlndslycro(ul ul AL
          SL slc *fo tbedm_pa.ipa_usrnl,nl,'L
           TT AT);
          SASLS')
           Q_D 4qsjz8, hl ubr
          SLI 9tnpukx cidnme 0
           ------------------
          -------------------
           eet * ul@ne 1
          slc /+fl(inrt)           ul@ne 2
                                  fl(inrt)         fu
           l@ne 3
          l(inrt)           edn(inr
                           laig@ne
           2 3 1
          t t t)         s_l@ne 3
                        uen(inrt)         s_ah@ne
                                         uehs(inrt
          1)        osa_onipt(inr
                   n_wpji_nus@ne
           1
          t)         ouehs_grgto(inr
                    n_s_ahageain@ne)         / a(n)
                                            * mxct f
           o   eet * bnm(ne) /
          rm(slc /+q_aeinr *
           on() s n, 1ojc_d 3ue_d rm 1 2 2 3 hr
          cut* a ct t.beti,t.sri fo t,t,t t wee
           1ue_d   2ue_d ru y
          t.sri =t.sri gopb
           1ojc_d 3ue_d
          t.beti,t.sri )

           ln ah au: 08425
          Pa hs vle 26919

           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------
             d  prto
          |I |Oeain                 ae    trs    -os
                                 |Nm |Sat |ERw |ARw |   -os
             -ie |Bfes| Oe | 1e |Ue-e |
            ATm      ufr    Mm     Mm    sdMm
           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------
                EET TTMN
          | 0|SLC SAEET          |     |     1|       |    1|0
           :00.3
          00:05 |       0
                       13|     |      |        |
                 OT GRGT
          | 1| SR AGEAE          |     |     1|     1|     1|0
           :00.3
          00:05 |       0
                       13|     |      |        |
          | 2| VE IW             |     |     1|    1K
                                                  35|     5K0
                                                          40|
           :00.7
          00:03 |       0
                       13|     |      |        |
          | 3|     OT RU Y
                  SR GOPB        |     |     1|    1K
                                                  35|     5K0
                                                          40|
           :00.7
          00:03 |       0
                       13|    8| 93| 2M()
                             2M 11K        5 0|
           *
          | 4|      AH ON
                   HS JI         |     |     1|    4K
                                                  46|     5K0
                                                          40|
           :00.1
          00:00 |       0   38| 38| 07 0|
                       13| 14K 14K 19K()
          | 5|       ETD OP
                    NSE LOS      |     |     1|    2
                                                  25|     2 0
                                                          25|
           :00.1
          00:00 |        8
                        4 |    |      |        |
          | 6|       AL CES UL 2 |
                     TBEACS FL|T             1|     5
                                                   1 |     5 0
                                                          1 |
           :00.1
          00:00 |        3|    |      |        |
          | 7|       AL CES UL 2 |
                     TBEACS FL|T            1 |
                                             5      5
                                                   1 |    2 0
                                                          25|
           :00.1
          00:00 |        5
                        4 |    |      |        |
          | 8|       AL CES UL
                    TBEACS FL |T    1 |           98
                                             1| 277| 300|00 0
           :00.1
          00:00 |        5
                        5 |    |      |        |
           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------

           rdct nomto ietfe y prto d:
          PeiaeIfrain(dniidb oeaini)
           -------------------------
          --------------------------

                 ces"1.UE_D=T""SRI"
               4-acs(T""SRI""2.UE_D)



oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        13/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           9 os eetd
          2 rw slce.

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL
           Q> e aeie 4
          SL stpgsz 1
           Q>
          SL
           Q> - ug h ttsis o ht h on adnlt ewe
          SL - Fdetesaitc s ta teji criaiybtent
             n 2 il e    rudd p o )
          1adt wl b 0(one u t 1
           Q> elr
          SL dcae
                rc bssassarc
            2 se dm_tt.tte;
                oas bssasnmra;
            3 nvl dm_tt.uary
                itn ubr
            4 dsctnme;
                vce ubr
            5 aglnnme;
                uln ubr
            6 nlctnme;
                est ubr
            7 dniynme;
                rc bssassarc
            8 se2dm_tt.tte;
               ei
            9 bgn
           10    bssasgtclm_tt(ul t' ue_d, itn
                dm_tt.e_ounsasnl,'1,'sri' dsct=
             itn, vce > vce, uln > uln, est > es
          >dsct agln= agln nlct= nlct dniy= dni
           y rc > rc;
          t,se = se)
           11    rc.p = ;
                se2ec: 2
           12    oas = bssasnmra(
                nvl : dm_tt.uary
           13    lwvle
                &o_au,
           14    hg_au
                &ihvle
           15   );
           16    rc.kas = ul
                se2bvl : nl;
           17    bssaspeaeclm_ausse2nvl)
                dm_tt.rpr_ounvle(rc,oas;
           18    bssasstclm_tt(
                dm_tt.e_ounsas
           19    wnm=nl,
                onae>ul
           20    anm='1,
                tbae>t'
           21    onm='sri'
                clae>ue_d,
           22    itn=dsct
                dsct>itn,
           23    uln=nlct
                nlct>uln,
           24    rc>rc,
                se=se2
           25    vce=agln
                agln>vce,
           26    est=dniy
                dniy>est
           27   );
            8 n;
           2 ed
            9
           2 /
           l   3   lwvle
          od 1: &o_au,
           e
          nw 1:3         5
                        1,
           l   4   hg_au
          od 1: &ihvle
           e
          nw 1:4        29

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> - naiae n usr sn 1
          SL - Ivldt aycrosuigT
           Q> omn n al 1 s '
          SL cmeto tbet i ';

           omn rae.
          Cmetcetd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e ttsislvl  l;
          SL atrssinstsaitc_ee =al

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        14/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           Q> - le eso e rcfl_dniir     smtaewogc
          SL - atrssinsttaeieietfe ='m_rc_rn_
           riaiy;
          adnlt'
           Q>
          SL
           Q> - le eso e _m_rc"
          SL - atrssinst"smtae =655 53;
           Q>
          SL
           Q> eet * ul@ne 1
          SL slc /+fl(inrt)
            2        ul@ne 2
                     fl(inrt)
            3        ul@ne 3
                     fl(inrt)
            4        edn(inr 2 3 1
                     laig@ne t t t)
            5        s_l@ne 3
                     uen(inrt)
            6        s_ah@ne 1
                     uehs(inrt)
            7        osa_onipt(inr 1
                     n_wpji_nus@ne t)
            8        ouehs_grgto(inr
                     n_s_ahageain@ne)
            9     */
            0 a(n)
           1 mxct
            1 rm
           1 fo
            2
           1 (
            3 eet * bnm(ne) / on() s n, 1ojc_d 3
           1 slc /+q_aeinr * cut* a ct t.beti,t
           ue_d
          .sri
            4 rm 1 2 2 3
           1 fo t,t,t t
            5 hr 1ue_d    2ue_d
           1 weet.sri =t.sri
            6 ru y 1ojc_d 3ue_d
           1 gopb t.beti,t.sri
            7
           1 )
            8
           1 ;

             A(N)
            MXCT
           -----
          -----
                1

           lpe: 00:05
          Easd 0:00.6
           Q>
          SL
           Q> - le eso e _m_rc"
          SL - atrssinst"smtae =0    ;
           Q>
          SL
           Q> e aeie    ieie 0 rmpo n a f
          SL stpgsz 0lnsz 20tisolo tbof
           Q>
          SL
           Q> eet   rm al(bsxlndslycro(ul ul AL
          SL slc *fo tbedm_pa.ipa_usrnl,nl,'L
           TT AT);
          SASLS')
           Q_D 4qsjz8, hl ubr
          SLI 9tnpukx cidnme 0
           ------------------
          -------------------
           eet * ul@ne 1
          slc /+fl(inrt)           ul@ne 2
                                  fl(inrt)         fu
           l@ne 3
          l(inrt)           edn(inr
                           laig@ne
           2 3 1
          t t t)         s_l@ne 3
                        uen(inrt)         s_ah@ne
                                         uehs(inrt
          1)        osa_onipt(inr
                   n_wpji_nus@ne
           1
          t)         ouehs_grgto(inr
                    n_s_ahageain@ne)         / a(n)
                                            * mxct f
           o   eet * bnm(ne) /
          rm(slc /+q_aeinr *
           on() s n, 1ojc_d 3ue_d rm 1 2 2 3 hr
          cut* a ct t.beti,t.sri fo t,t,t t wee
           1ue_d   2ue_d ru y
          t.sri =t.sri gopb
           1ojc_d 3ue_d
          t.beti,t.sri )

           ln ah au: 08425
          Pa hs vle 26919

           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------
             d  prto
          |I |Oeain              ae    trs   -os
                              |Nm |Sat |ERw |ARw | -os
             -ie |Bfes| Oe | 1e |Ue-e |
            ATm    ufr   Mm     Mm    sdMm
           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------
                EET TTMN
          | 0|SLC SAEET       |     |    1|      |   1|0
           :00.5
          00:05 |     0
                     13|    |      |       |
                 OT GRGT
          | 1| SR AGEAE       |     |    1|    1|    1|0
           :00.5
          00:05 |     0
                     13|    |      |       |
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        15/46
8/10/12                                                Oracle related stuff: Hash Aggregation

          | 2| VEIW             |    |   1|    1|  5K0
                                                   40|
           :00.8
          00:03 |      0
                      13|     |     |      |
          | 3|    OT RU Y
                 SR GOPB        |    |   1|    1|  5K0
                                                   40|
           :00.8
          00:03 |      0
                      13|    8| 93| 2M()
                            2M 11K     5 0|
           *
          | 4|     AH ON
                  HS JI         |    |   1|    1|  5K0
                                                   40|
           :00.1
          00:00 |      0   38| 38| 03 0|
                      13| 14K 14K 15K()
          | 5|      ETD OP
                   NSE LOS      |    |   1|   2
                                             25|   2 0
                                                   25|
           :00.1
          00:00 |       8
                       4 |    |     |      |
          | 6|      AL CES UL 2 |
                    TBEACS FL|T          1|    5
                                              1 |   5 0
                                                   1 |
           :00.1
          00:00 |       3|    |     |      |
          | 7|      AL CES UL 2 |
                    TBEACS FL|T         1 |
                                         5     5
                                              1 |  2 0
                                                   25|
           :00.1
          00:00 |       5
                       4 |    |     |      |
          | 8|      AL CES UL
                   TBEACS FL |T   1 |        98   00 0
                                         1| 277| 300|
           :00.1
          00:00 |       5
                       5 |    |     |      |
           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------

           rdct nomto ietfe y prto d:
          PeiaeIfrain(dniidb oeaini)
           -------------------------
          --------------------------

                 ces"1.UE_D=T""SRI"
               4-acs(T""SRI""2.UE_D)


           9 os eetd
          2 rw slce.

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL




          All I've done is to reduce the data set, because the SORT GROUP BY in
          this case required more memory for the same amount of data than the
          HASH GROUP BY, in order to prevent the operation to spill to disk with a
          200M PGA_AGGREGATE_TARGET setting and change the
          USE_HASH_AGGREGATION hint to NO_USE_HASH_AGGREGATION.

          As you can see, the operation completes both times in memory and uses
          the same amount of memory no matter what the estimates look like.

          I've tested the serial execution of the most common workarea based
          operations like HASH JOIN, SORT ORDER BY, WINDOW SORT, SORT
          UNIQUE and all of them were able to dynamically resize the workarea in
          cases where the initial estimated size was too small.

          If you carefully check then you'll notice that I haven't mentioned the
          HASH UNIQUE operation yet, and later on you'll see why.

          A cunning feature of the automatic PGA management comes to help,
          however, which is a kind of "feedback loop" for the workareas based on
          statistics maintained by the automatic PGA memory management, and
          indeed when the HASH GROUP BY cursor based on the incorrect
          cardinality estimate gets shared (not invalidated) and re-executed, the
          next execution will look like this:



           once o
          Cnetdt:
           rce aaae 0 nepie dto ees 02050  4i
          Oal Dtbs 1gEtrrs EiinRlae1.... -6btP
           outo
          rdcin
           ih h attoig LP aa iig n el plcto et
          Wt tePriinn,OA,Dt Mnn adRa ApiainTsi
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        16/46
8/10/12                                                Oracle related stuff: Hash Aggregation
           ih h attoig LP aa iig n el plcto et
          Wt tePriinn,OA,Dt Mnn adRa ApiainTsi
           g pin
          n otos

           Q>
          SL
           Q> le eso e ttsislvl  l;
          SL atrssinstsaitc_ee =al

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e rcfl_dniir  smtaewogcr
          SL atrssinsttaeieietfe ='m_rc_rn_ad
           nlt_eetdeeuin;
          iaiyrpae_xcto'

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e _m_rc"  53;
          SL atrssinst"smtae =655

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> eet * ul@ne 1
          SL slc /+fl(inrt)
            2        ul@ne 2
                     fl(inrt)
            3        ul@ne 3
                     fl(inrt)
            4        edn(inr 2 3 1
                     laig@ne t t t)
            5        s_l@ne 3
                     uen(inrt)
            6        s_ah@ne 1
                     uehs(inrt)
            7        osa_onipt(inr 1
                     n_wpji_nus@ne t)
            8        s_ahageain@ne)
                     uehs_grgto(inr
            9     */
            0 a(n)
           1 mxct
            1 rm
           1 fo
            2
           1 (
            3 eet * bnm(ne) / on() s n, 1ojc_d 3
           1 slc /+q_aeinr * cut* a ct t.beti,t
           ue_d
          .sri
            4 rm 1 2 2 3
           1 fo t,t,t t
            5 hr 1ue_d    2ue_d
           1 weet.sri =t.sri
            6 ru y 1ojc_d 3ue_d
           1 gopb t.beti,t.sri
            7
           1 )
            8
           1 ;

             A(N)
            MXCT
           -----
          -----
                1

           lpe: 00:07
          Easd 0:00.6
           Q>
          SL
           Q> le eso e _m_rc"
          SL atrssinst"smtae =0;

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> e aeie   ieie 0 rmpo n a f
          SL stpgsz 0lnsz 20tisolo tbof
           Q>
          SL
           Q> eet  rm al(bsxlndslycro(ul ul AL
          SL slc *fo tbedm_pa.ipa_usrnl,nl,'L
           TT AT);
          SASLS')
           Q_D ssfvav, hl ubr
          SLI 21dhhj3 cidnme 0
           ------------------
          -------------------
           eet * ul@ne 1
          slc /+fl(inrt)         ul@ne 2
                                fl(inrt)     fu
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        17/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           l@ne 3
          l(inrt)        edn(inr 2 3 1
                        laig@ne t t t)
                 s_l@ne 3
                uen(inrt)         s_ah@ne 1
                                 uehs(inrt)
             osa_onipt(inr 1
            n_wpji_nus@ne t)
           s_ahageain@ne)
          uehs_grgto(inr        / a(n) rm  eet *
                               * mxct fo (slc /+q
           _aeinr / on() s n, 1ojc_d
          bnm(ne)* cut* a ct t.beti,
           3ue_d rm 1 2 2 3 hr 1ue_d    2ue_d ru
          t.sri fo t,t,t t weet.sri =t.sri gopb
            1ojc_d 3ue_d
          yt.beti,t.sri )

           ln ah au: 14404
          Pa hs vle 33829

           ---------------------------------
          ----------------------------------
           -----------------------------
          ------------------------------
             d  prto
          |I |Oeain                 ae    trs    -os
                                 |Nm |Sat |ERw |ARw |    -os
             -ie |Bfes| Oe | 1e |Ue-e |Ue-m|
            ATm      ufr    Mm     Mm    sdMm     sdTp
           ---------------------------------
          ----------------------------------
           -----------------------------
          ------------------------------
                EET TTMN
          | 0|SLC SAEET          |     |     1|       |     1|0
           :00.5
          00:07 |       3
                       19|     |      |        |        |
                 OT GRGT
          | 1| SR AGEAE          |     |     1|     1|      1|0
           :00.5
          00:07 |       3
                       19|     |      |        |        |
          | 2| VE IW             |     |     1|     1|     1K0
                                                           80|
           :00.4
          00:04 |       3
                       19|     |      |        |        |
          | 3|     AH RU Y
                  HS GOPB        |     |     1|     1|     1K0
                                                           80|
           :00.4
          00:04 |       3
                       19|    5| 51| 3M()
                             3M 52K        8 0|         |
           *
          | 4|      AH ON
                   HS JI         |     |     1|     1|     1K0
                                                           80|
           :00.1
          00:00 |       3   38| 38| 42 0|
                       19| 14K 14K 11K()                |
          | 5|       ETD OP
                    NSE LOS      |     |     1|    2
                                                  79|      2 0
                                                           79|
           :00.1
          00:00 |        4
                        8 |    |      |        |        |
          | 6|       AL CES UL 2 |
                     TBEACS FL|T             1|     7
                                                   2 |      7 0
                                                           2 |
           :00.1
          00:00 |        3|    |      |        |        |
          | 7|       AL CES UL 2 |
                     TBEACS FL|T            2 |
                                             7      7
                                                   2 |     2 0
                                                           79|
           :00.1
          00:00 |        1
                        8 |    |      |        |        |
          | 8|       AL CES UL
                    TBEACS FL |T    1 |           93
                                             1| 286| 300| 00 0
           :00.1
          00:00 |        5
                        5 |    |      |        |        |
           ---------------------------------
          ----------------------------------
           -----------------------------
          ------------------------------

           rdct nomto ietfe y prto d:
          PeiaeIfrain(dniidb oeaini)
           -------------------------
          --------------------------

                 ces"1.UE_D=T""SRI"
               4-acs(T""SRI""2.UE_D)


           8 os eetd
          2 rw slce.

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL




          So Oracle this time based the workarea memory requirements on the
          feedback from the previous execution and therefore allocated sufficient
          memory to complete the operation without spilling to disk.

          Notice however that the feedback loop unfortunately does not work as
          desired when the workarea execution fails due to insufficient TEMP
          space. Ideally the feedback loop should allow the subsequent
          executions to grab more memory specifically in such cases, but at
          present it doesn't - the failure obviously prevents an update of the
          statistics and therefore subsequent executions continue to fail since
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        18/46
8/10/12                                                Oracle related stuff: Hash Aggregation

          they still use the same amount of memory.

          You can test this by simply assigned a very small TEMP tablespace to
          the user executing the query so that the second execution fails due to
          insufficient TEMP space. If you repeat the execution in this case, it will
          fail again and keeps doing so.

          What happens if I invalidate the cursor and repeat the execution?



           Q> omn n al 1 s '
          SL cmeto tbet i ';

           omn rae.
          Cmetcetd

           lpe: 00:00
          Easd 0:00.1
           Q> e co n iig n
          SL steh o tmn o
           Q>
          SL
           Q> le eso e ttsislvl  l;
          SL atrssinstsaitc_ee =al

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e rcfl_dniir  smtaewogcr
          SL atrssinsttaeieietfe ='m_rc_rn_ad
           nlt_eetdee_nai'
          iaiyrpae_xcivld;

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> le eso e _m_rc"  53;
          SL atrssinst"smtae =655

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> eet * ul@ne 1
          SL slc /+fl(inrt)
            2        ul@ne 2
                     fl(inrt)
            3        ul@ne 3
                     fl(inrt)
            4        edn(inr 2 3 1
                     laig@ne t t t)
            5        s_l@ne 3
                     uen(inrt)
            6        s_ah@ne 1
                     uehs(inrt)
            7        osa_onipt(inr 1
                     n_wpji_nus@ne t)
            8        s_ahageain@ne)
                     uehs_grgto(inr
            9     */
            0 a(n)
           1 mxct
            1 rm
           1 fo
            2
           1 (
            3 eet * bnm(ne) / on() s n, 1ojc_d 3
           1 slc /+q_aeinr * cut* a ct t.beti,t
           ue_d
          .sri
            4 rm 1 2 2 3
           1 fo t,t,t t
            5 hr 1ue_d    2ue_d
           1 weet.sri =t.sri
            6 ru y 1ojc_d 3ue_d
           1 gopb t.beti,t.sri
            7
           1 )
            8
           1 ;
                1

           lpe: 00:12
          Easd 0:00.9
           Q>
          SL
           Q> le eso e _m_rc"
          SL atrssinst"smtae =0;
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        19/46
8/10/12                                                Oracle related stuff: Hash Aggregation



           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> e aeie    ieie 0 rmpo n a f
          SL stpgsz 0lnsz 20tisolo tbof
           Q>
          SL
           Q> eet   rm al(bsxlndslycro(ul ul AL
          SL slc *fo tbedm_pa.ipa_usrnl,nl,'L
           TT AT);
          SASLS')
           Q_D ssfvav, hl ubr
          SLI 21dhhj3 cidnme 0
           ------------------
          -------------------
           eet * ul@ne 1
          slc /+fl(inrt)             ul@ne 2
                                    fl(inrt)      fu
           l@ne 3
          l(inrt)           edn(inr 2 3 1
                           laig@ne t t t)
           s_l@ne 3
          uen(inrt)            s_ah@ne 1
                              uehs(inrt)        osa
                                               n_wp
           ji_nus@ne 1
          _onipt(inrt)             s_ahageain@ne)
                                  uehs_grgto(inr
             / a(n) rm   eet * bnm(ne) / on() s n,
            * mxct fo (slc /+q_aeinr * cut* a ct
           1ojc_d 3ue_d rm 1 2 2 3 hr 1ue_d
          t.beti,t.sri fo t,t,t t weet.sri =
           2ue_d ru y 1ojc_d 3ue_d
          t.sri gopb t.beti,t.sri )

           ln ah au: 14404
          Pa hs vle 33829

           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----
          -----
             d   prto
          |I |Oeain                  ae   trs    -os
                                  |Nm |Sat |ERw |ARw |  -os
             -ie |Bfes|Ras |Wie | Oe | 1e |Ue-e |
            ATm       ufr    ed     rts    Mm     Mm    sdMm
            sdTp
           Ue-m|
           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----
          -----
                 EET TTMN
          | 0|SLC SAEET           |     |    1|       |    1|0
           :00.8
          00:12 |        3
                        19| 21 | 21 |
                              75      75       |     |       |
                 |
                  OT GRGT
          | 1| SR AGEAE           |     |    1|     1|     1|0
           :00.8
          00:12 |        3
                        19| 21 | 21 |
                              75      75       |     |       |
                 |
          | 2| VE  IW             |     |    1|     1|    1K0
                                                          80|
           :00.1
          00:06 |        3
                        19| 21 | 21 |
                              75      75       |     |       |
                 |
          | 3|      AH RU Y
                   HS GOPB        |     |    1|     1|    1K0
                                                          80|
           :00.1
          00:06 |        3    75
                        19| 21 | 21 | 75     5| 51| 07 1|
                                            3M 52K 76K()
              35
             252|
           *
          | 4|       AH ON
                    HS JI         |     |    1|     1|    1K0
                                                          80|
           :00.1
          00:00 |        3
                        19|     0|         38| 38| 19 0|
                                       0| 14K 14K 12K()
                 |
          | 5|        ETD OP
                     NSE LOS      |     |    1|    2
                                                  79|     2 0
                                                          79|
           :00.1
          00:00 |         4
                         8 |    0|     0|      |     |       |
                 |
          | 6|        AL CES UL 2 |
                      TBEACS FL|T            1|     7
                                                   2 |     7 0
                                                          2 |
           :00.1
          00:00 |         3|    0|     0|      |     |       |
                 |
          | 7|        AL CES UL 2 |
                      TBEACS FL|T           2 |
                                             7      7
                                                   2 |    2 0
                                                          79|
           :00.1
          00:00 |         1
                         8 |    0|     0|      |     |       |
                 |
          | 8|        AL CES UL
                     TBEACS FL |T    1 |          93
                                             1| 286| 300|00 0
           :00.1
          00:00 |         5
                         5 |    0|     0|      |     |       |
                 |
           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----
          -----

oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        20/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           rdct nomto ietfe y prto d:
          PeiaeIfrain(dniidb oeaini)
           -------------------------
          --------------------------

                 ces"1.UE_D=T""SRI"
               4-acs(T""SRI""2.UE_D)


           8 os eetd
          2 rw slce.

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL




          Back to square one - with the invalidation the statistics are gone, too,
          so the bad cardinality estimate again lead to the suboptimal execution
          of the HASH GROUP BY.

          So you might think, why bother? Only the first execution of a cursor
          without workarea execution statistics will be affected by the problem,
          subsequent execution of the same cursor will benefit from the statistics
          from the previous executions.

          The problem however is, that this is fine for applications that share
          cursors. Unfortunately applications that peform heavy duty aggregations
          like data warehouses typically do not share cursors, since they do not
          care about the optimization overhead and deliberately use literals to
          provide as much information to the optimizer as possible.

          Also these heavy duty aggregations usually use Parallel Execution
          features, and as you'll see from the output of the same test case, if I
          run the HASH GROUP BY in parallel by simply setting table T1 to parallel
          degree 2, a similar problem occurs - so Parallel Execution is affected as
          well.



           once o
          Cnetdt:
           rce aaae 0 nepie dto ees 02050  4i
          Oal Dtbs 1gEtrrs EiinRlae1.... -6btP
           outo
          rdcin
           ih h attoig LP aa iig n el plcto et
          Wt tePriinn,OA,Dt Mnn adRa ApiainTsi
           g pin
          n otos

           Q>
          SL
           Q> hw aaee g
          SL so prmtrpa

           AE
          NM                  YE
                             TP      AU
                                    VLE
           ------------------ ----- ---------
          ------------------ ---------------
           ------
          ------
           g_grgt_agt
          paageaetre          i nee 0M
                             bgitgr20
           Q> hw aaee rcse
          SL so prmtrpoess

           AE
          NM                  YE
                             TP      AU
                                    VLE
           ------------------ ----- ---------
          ------------------ ---------------
           ------
          ------
           qt_rcse
          a_mpoess            nee
                             itgr   0
           bwie_rcse
          d_rtrpoess          nee
                             itgr   1
           c_evrpoess
          gssre_rcse          nee
                             itgr   0
           o_uu_rcse
          jbqeepoess          nee
                             itgr   10
           o_rhv_a_rcse
          lgaciemxpoess       nee
                             itgr   2
           rcse
          poess               nee
                             itgr    5
                                    10
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        21/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           Q>
          SL
           Q> - le eso e okrasz_oiy  aul otae_
          SL - atrssinstwrae_ieplc =mna sr_ras
           z   0000
          ie=4000;
           Q>
          SL
           Q> rp al 1 ug;
          SL do tbet pre

           al rpe.
          Tbedopd

           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> rp al 2 ug;
          SL do tbet pre

           al rpe.
          Tbedopd

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL
           Q> rae al 1
          SL cet tbet
            2 as
               eet o(onm 7 s sri, onm s beti rm u
            3 slc mdrwu,2)a ue_d rwu a ojc_dfo d
           l onc y ee = 00;
          a cnetb lvl< 300

           al rae.
          Tbecetd

           lpe: 00:00
          Easd 0:00.6
           Q>
          SL
           Q> le al 1 aall ;
          SL atrtbet prle 2

           al lee.
          Tbeatrd

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL
           Q> rae al 2
          SL cet tbet
            2 as
               eet itnt sri rm 1
            3 slc dsic ue_dfo t;

           al rae.
          Tbecetd

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL
           Q> xc bssasgte_al_tt(ul t' ehdot >
          SL ee dm_tt.ahrtbesasnl,'1,mto_p = '
           o l oun ie '
          fralclmssz 1)

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL
           Q> xc bssasgte_al_tt(ul t' ehdot >
          SL ee dm_tt.ahrtbesasnl,'2,mto_p = '
           o l oun ie '
          fralclmssz 1)

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL
           Q> oun o_a e_au o_au
          SL clm lwvlnwvlelwvle
           Q> oun ihvl e_au ihvle
          SL clm hg_a nwvlehg_au
           Q>
          SL
           Q> eet
          SL slc
            2       a(sri)   s o_a
                   mxue_d +1a lwvl
            3       a(sri)  a(sri)  i(sri)   s ih
                  ,mxue_d +mxue_d -mnue_d +1a hg
           vl
          _a
               rm
            4 fo
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        22/46
8/10/12                                                Oracle related stuff: Hash Aggregation

              5                  2
                                t;

             O_A  IHVL
            LWVL HG_A
           ----- -----
          ----- -----
               27    53

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> - naiae n usr sn 1
          SL - Ivldt aycrosuigT
           Q> omn n al 1 s '
          SL cmeto tbet i ';

           omn rae.
          Cmetcetd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e ttsislvl  l;
          SL atrssinstsaitc_ee =al

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e rcfl_dniir  smtaecretc
          SL atrssinsttaeieietfe ='m_rc_orc_a
           dnlt'
          riaiy;

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> le eso e _m_rc"  53;
          SL atrssinst"smtae =655

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> eet * ul@ne 1
          SL slc /+fl(inrt)
            2        ul@ne 2
                     fl(inrt)
            3        ul@ne 3
                     fl(inrt)
            4        edn(inr 2 3 1
                     laig@ne t t t)
            5        s_l@ne 3
                     uen(inrt)
            6        s_ah@ne 1
                     uehs(inrt)
            7        osa_onipt(inr 1
                     n_wpji_nus@ne t)
            8        s_ahageain@ne)
                     uehs_grgto(inr
            9     */
            0 a(n)
           1 mxct
            1 rm
           1 fo
            2
           1 (
            3 eet * bnm(ne) / on() s n, 1ojc_d 3
           1 slc /+q_aeinr * cut* a ct t.beti,t
           ue_d
          .sri
            4 rm 1 2 2 3
           1 fo t,t,t t
            5 hr 1ue_d    2ue_d
           1 weet.sri =t.sri
            6 ru y 1ojc_d 3ue_d
           1 gopb t.beti,t.sri
            7
           1 )
            8
           1 ;

             A(N)
            MXCT
           -----
          -----
                1

           lpe: 00:04
          Easd 0:00.2
           Q>
          SL
           Q> le eso e _m_rc"
          SL atrssinst"smtae =0;
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        23/46
8/10/12                                                Oracle related stuff: Hash Aggregation



           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> e aeie   ieie 0 rmpo n a f
          SL stpgsz 0lnsz 20tisolo tbof
           Q>
          SL
           Q> eet  rm al(bsxlndslycro(ul ul AL
          SL slc *fo tbedm_pa.ipa_usrnl,nl,'L
           TT')
          SAS);
           Q_D ssfvav, hl ubr
          SLI 21dhhj3 cidnme 0
           ------------------
          -------------------
           eet * ul@ne 1
          slc /+fl(inrt)          ul@ne 2
                                 fl(inrt)        fu
           l@ne 3
          l(inrt)         edn(inr 2 3 1
                         laig@ne t t t)
                s_l@ne 3
               uen(inrt)         s_ah@ne 1
                                uehs(inrt)
            osa_onipt(inr 1
           n_wpji_nus@ne t)
           s_ahageain@ne)
          uehs_grgto(inr        / a(n) rm  eet *
                               * mxct fo (slc /+q
           _aeinr / on() s n, 1ojc_d
          bnm(ne)* cut* a ct t.beti,
           3ue_d rm 1 2 2 3 hr 1ue_d    2ue_d ru
          t.sri fo t,t,t t weet.sri =t.sri gopb
             1ojc_d 3ue_d
          yt.beti,t.sri )

           ln ah au: 6889
          Pa hs vle 44991

           ---------------------------------
          ----------------------------------
           ------------------------------
          -------------------------------
             d
          |I |Oeainprto                    |Nmae      trs
                                                    |Sat |ERw-os
              -os
           |ARw | ATm             ufr    Mm     Mm
                        -ie |Bfes| Oe | 1e | O1M |    //
           ---------------------------------
          ----------------------------------
           ------------------------------
          -------------------------------
                   EET TTMN
          | 0|SLC SAEET                    |        |    1|
           |         0:00.3
                  1|00:04 |          7
                                    8 |      |     |       |
                    OT GRGT
          | 1| SR AGEAE                    |        |    1|      1
           |         0:00.3
                  1|00:04 |          7
                                    8 |      |     |       |
                     X ORIAO
          | 2| P CODNTR                    |        |    1|
           |         0:00.3
                  2|00:04 |          7
                                    8 |      |     |       |
          | 3|        X ED C RNO)
                     P SN Q (ADM              T102
                                           |:Q00 |       0|      1
           |         0:00.1
                  0|00:00 |          0|      |     |       |
          | 4|         OT GRGT
                      SR AGEAE             |        |    2|      1
           |         0:00.1
                  2|00:08 |          0|      |     |       |
          | 5|          IW
                       VE                  |        |    2|     7
                                                               55
          K|     1K0:00.8
                80|00:04 |           0|      |     |       |
          | 6|          AH RU Y
                        HS GOPB            |        |    2|     7
                                                               55
          K|     1K0:00.8
                80|00:04 |           0|    5| 51|
                                          3M 52K        //|
                                                       200
          | 7|           X EEV
                        P RCIE             |        |    2|     1
                                                               84
          K|     1K0:00.1
                80|00:00 |           0|      |     |       |
          | 8|            X ED AH
                         P SN HS              T101
                                           |:Q00 |       0|     1
                                                               84
          K|         0:00.1
                  0|00:00 |          0|      |     |       |
           *
          | 9|             AH ON
                          HS JI            |        |    2|     1
                                                               84
          K|     1K0:00.1
                80|00:00 |          8 | 14K 14K
                                     9   38| 38|        //|
                                                       200
              0
          | 1 |             UFR OT
                           BFE SR          |        |    2|
                48 0:00.1
           | 15 |00:00 |                99
                                     0|266|266|99       //|
                                                       200
              1
          | 1 |              X EEV
                            P RCIE         |        |    2|     2
                                                               79
                48 0:00.1
           | 15 |00:00 |             0|      |     |       |
              2
          | 1 |               X ED RACS       T100
                             P SN BODAT |:Q00 |          0|     2
                                                               79
           |         0:00.1
                  0|00:00 |          0|      |     |       |
              3
          | 1 |                ETD OP
                              NSE LOS      |        |    1|     2
                                                               79
           |     2 0:00.1
                79|00:00 |           4
                                    8 |      |     |       |
              4
          | 1 |                 AL CES UL 2
                               TBEACS FL|T          |    1|     27
           |      7 0:00.1
                 2 |00:00 |          3|      |     |       |
              5
          | 1 |                 AL CES UL 2
                               TBEACS FL|T          |    7
                                                        2 |     27
           |     2 0:00.1
                79|00:00 |           1
                                    8 |      |     |       |
              6
          | 1 |             X LC TRTR
                           P BOKIEAO       |        |         06
                                                         2| 314
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        24/46
8/10/12                                                Oracle related stuff: Hash Aggregation

              00 0:00.1
           | 300|00:00 |    9
                           8 |   |  |     |
           * 7
          | 1 |       AL CES UL |T
                     TBEACS FL    1  |  8   06
                                       1 | 314
              00 0:00.1
           | 300|00:00 |    9
                           8 |   |  |     |
           ---------------------------------
          ----------------------------------
           ------------------------------
          -------------------------------

           rdct nomto ietfe y prto d:
          PeiaeIfrain(dniidb oeaini)
           -------------------------
          --------------------------

                 ces"1.UE_D=T""SRI"
               9-acs(T""SRI""2.UE_D)
               7 ces:>: N Z=Z
              1 -acs(Z=ZAD:<:)


           8 os eetd
          3 rw slce.

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL
           Q> e aeie 4
          SL stpgsz 1
           Q>
          SL
           Q> - ug h ttsis o ht h on adnlt ewe
          SL - Fdetesaitc s ta teji criaiybtent
             n 2 il e    rudd p o )
          1adt wl b 0(one u t 1
           Q> elr
          SL dcae
                rc bssassarc
            2 se dm_tt.tte;
                oas bssasnmra;
            3 nvl dm_tt.uary
                itn ubr
            4 dsctnme;
                vce ubr
            5 aglnnme;
                uln ubr
            6 nlctnme;
                est ubr
            7 dniynme;
                rc bssassarc
            8 se2dm_tt.tte;
               ei
            9 bgn
           10    bssasgtclm_tt(ul t' ue_d, itn
                dm_tt.e_ounsasnl,'1,'sri' dsct=
             itn, vce > vce, uln > uln, est > es
          >dsct agln= agln nlct= nlct dniy= dni
           y rc > rc;
          t,se = se)
           11    rc.p = ;
                se2ec: 2
           12    oas = bssasnmra(
                nvl : dm_tt.uary
           13    lwvle
                &o_au,
           14    hg_au
                &ihvle
           15   );
           16    rc.kas = ul
                se2bvl : nl;
           17    bssaspeaeclm_ausse2nvl)
                dm_tt.rpr_ounvle(rc,oas;
           18    bssasstclm_tt(
                dm_tt.e_ounsas
           19    wnm=nl,
                onae>ul
           20    anm='1,
                tbae>t'
           21    onm='sri'
                clae>ue_d,
           22    itn=dsct
                dsct>itn,
           23    uln=nlct
                nlct>uln,
           24    rc>rc,
                se=se2
           25    vce=agln
                agln>vce,
           26    est=dniy
                dniy>est
           27   );
            8 n;
           2 ed
            9
           2 /
           l   3   lwvle
          od 1: &o_au,
           e
          nw 1:3         7
                        2,
           l   4   hg_au
          od 1: &ihvle
           e
          nw 1:4        53

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        25/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           Q> - naiae n usr sn 1
          SL - Ivldt aycrosuigT
           Q> omn n al 1 s '
          SL cmeto tbet i ';

           omn rae.
          Cmetcetd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e ttsislvl  l;
          SL atrssinstsaitc_ee =al

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e rcfl_dniir  smtaewogcr
          SL atrssinsttaeieietfe ='m_rc_rn_ad
           nlt'
          iaiy;

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e _m_rc"  53;
          SL atrssinst"smtae =655

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> eet * ul@ne 1
          SL slc /+fl(inrt)
            2        ul@ne 2
                     fl(inrt)
            3        ul@ne 3
                     fl(inrt)
            4        edn(inr 2 3 1
                     laig@ne t t t)
            5        s_l@ne 3
                     uen(inrt)
            6        s_ah@ne 1
                     uehs(inrt)
            7        osa_onipt(inr 1
                     n_wpji_nus@ne t)
            8        s_ahageain@ne)
                     uehs_grgto(inr
            9     */
            0 a(n)
           1 mxct
            1 rm
           1 fo
            2
           1 (
            3 eet * bnm(ne) / on() s n, 1ojc_d 3
           1 slc /+q_aeinr * cut* a ct t.beti,t
           ue_d
          .sri
            4 rm 1 2 2 3
           1 fo t,t,t t
            5 hr 1ue_d    2ue_d
           1 weet.sri =t.sri
            6 ru y 1ojc_d 3ue_d
           1 gopb t.beti,t.sri
            7
           1 )
            8
           1 ;

             A(N)
            MXCT
           -----
          -----
                1

           lpe: 00:11
          Easd 0:00.7
           Q>
          SL
           Q> le eso e _m_rc"
          SL atrssinst"smtae =0;

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> e aeie  ieie 0 rmpo n a f
          SL stpgsz 0lnsz 20tisolo tbof
           Q>
          SL
           Q> eet rm al(bsxlndslycro(ul ul AL
          SL slc *fo tbedm_pa.ipa_usrnl,nl,'L
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        26/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           TT')
          SAS);
           Q_D ssfvav, hl ubr
          SLI 21dhhj3 cidnme 0
           ------------------
          -------------------
           eet * ul@ne 1
          slc /+fl(inrt)          ul@ne 2
                                 fl(inrt)         fu
           l@ne 3
          l(inrt)          edn(inr 2 3 1
                          laig@ne t t t)        s_l
                                               uen
           @ne 3
          (inrt)
                  s_ah@ne 1
                 uehs(inrt)         osa_onipt(inr
                                   n_wpji_nus@ne
           1
          t)         s_ahageain@ne)
                    uehs_grgto(inr        / a(n) rm
                                         * mxct fo
              eet *
           (slc /+
           bnm(ne) / on() s n, 1ojc_d 3ue_d rm 1
          q_aeinr * cut* a ct t.beti,t.sri fo t
             2 2 3 hr 1ue_d   2ue_d ru y 1ojc_d 3
          ,t,t t weet.sri =t.sri gopb t.beti,t
           ue_d
          .sri )

           ln ah au: 6889
          Pa hs vle 44991

           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----------
          -----------
             d    prto
          |I |Oeain                        ae
                                         |Nm      trs   -os
                                                |Sat |ERw
              -os
           |ARw | ATm           ufr    ed    rts
                       -ie |Bfes|Ras |Wie | Oe | 1eMm    Mm
              //
          | O1M |MxTp| a-m
           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----------
          -----------
                  EET TTMN
          | 0|SLC SAEET                  |      |    1|
           |        0:00.6
                 1|00:11 |          7
                                   8 |    0|   0|     |
          |         |        |
                   OT GRGT
          | 1| SR AGEAE                  |      |    1|     1
           |        0:00.6
                 1|00:11 |          7
                                   8 |    0|   0|     |
          |         |        |
                    X ORIAO
          | 2| P CODNTR                  |      |    1|
           |        0:00.6
                 2|00:11 |          7
                                   8 |    0|   0|     |
          |         |        |
          | 3|       X ED C RNO)
                    P SN Q (ADM            T102
                                         |:Q00 |     0|     1
           |        0:00.1
                 0|00:00 |          0|    0|   0|     |
          |         |        |
          | 4|        OT GRGT
                     SR AGEAE            |      |    2|     1
           |        0:00.5
                 2|00:22 |              40    40
                                    0| 26 | 26 |      |
          |         |        |
          | 5|         IW
                      VE                 |      |    2|     1
           |     1K0:00.3
                80|00:15 |              40    40
                                    0| 26 | 26 |      |
          |         |        |
          | 6|         AH RU Y
                       HS GOPB           |      |    2|     1
           |     1K0:00.3
                80|00:15 |              40    40
                                    0| 26 | 26 |     5| 51
                                                    3M 52K
          |             16
                    | 124|
          | 7|          X EEV
                       P RCIE            |      |    2|     1
           |     1K0:00.1
                80|00:00 |          0|    0|   0|     |
          |         |        |
          | 8|           X ED AH
                        P SN HS            T101
                                         |:Q00 |     0|     1
           |        0:00.1
                 0|00:00 |          0|    0|   0|     |
          |         |        |
           *
          | 9|            AH ON
                         HS JI           |      |    2|     1
           |     1K0:00.1
                80|00:00 |          9
                                   8 |    0|       38| 38
                                               0| 14K 14K
          |      //|
                200          |
              0
          | 1 |            UFR OT
                          BFE SR         |      |    2|
                48 0:00.1
           | 15 |00:00 |            0|    0|       99
                                               0|266|26699
          |      //|
                200          |
              1
          | 1 |             X EEV
                           P RCIE        |      |    2|    2
                                                          79
                48 0:00.1
           | 15 |00:00 |            0|    0|   0|     |
          |         |        |
              2
          | 1 |              X ED RACS     T100
                            P SN BODAT |:Q00 |       0|    2
                                                          79
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        27/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           |       0:00.1
                 0|00:00 |       0|   0| 0|    |
          |        |      |
             3
          | 1 |            ETD OP
                          NSE LOS    |    |  1|    2
                                                  79
           |    2 0:00.1
               79|00:00 |        4
                                8 |   0| 0|    |
          |        |      |
             4
          | 1 |             AL CES UL 2
                           TBEACS FL|T    |  1|    27
           |     7 0:00.1
                2 |00:00 |       3|   0| 0|    |
          |        |      |
             5
          | 1 |             AL CES UL 2
                           TBEACS FL|T    |  7
                                            2 |    27
           |    2 0:00.1
               79|00:00 |        1
                                8 |   0| 0|    |
          |        |      |
             6
          | 1 |         X LC TRTR
                       P BOKIEAO     |    |      06
                                             2| 314
              00 0:00.1
           | 300|00:00 |         9
                                8 |   0| 0|    |
          |        |      |
           * 7
          | 1 |          AL CES UL |T
                        TBEACS FL      1  |  8   06
                                            1 | 314
              00 0:00.1
           | 300|00:00 |         9
                                8 |   0| 0|    |
          |        |      |
           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----------
          -----------

           rdct nomto ietfe y prto d:
          PeiaeIfrain(dniidb oeaini)
           -------------------------
          --------------------------

                 ces"1.UE_D=T""SRI"
               9-acs(T""SRI""2.UE_D)
               7 ces:>: N Z=Z
              1 -acs(Z=ZAD:<:)


           7 os eetd
          3 rw slce.

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL




          So, if your database uses automatic PGA management and

          - uses the hash aggregation HASH GROUP BY (or HASH UNIQUE, more on
          that in a moment)
          - and does not share cursors

          every execution that is based on bad cardinality estimates potentially
          has a problem with the hash aggregation because it might not make
          efficient use of the available memory.

          The same applies to applications that share cursors, however in that
          case only the first execution after re-optimization / invalidation is
          affected.

          So you might want to carefully check the runtime execution statistics of
          your critial hash aggregations.

          Mind you, things could be worse, and that is where the HASH UNIQUE
          operation comes into the picture.

          When I realised the issue with the HASH GROUP BY operation I was
          quite certain that the HASH UNIQUE operation will be affected in a
          similar way, since internally Oracle seems to use the same mechanism
          for both operations (In the SMM trace files both are called HASH GROUP
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        28/46
8/10/12                                                Oracle related stuff: Hash Aggregation

          BY).

          To my surprise I noticed that in 10g versions below 10.2.0.5 and 11g
          versions below 11.2.0.1 (which means in this case 10.2.0.4 and 11.1.0.7
          respectively, I didn't test other versions) the HASH UNIQUE operation
          suffers from an even more dramatic problem: The cardinality estimate is
          not considered and the initial workarea size is always based on minimum
          assumptions.

          In passing, according to the SMM trace files that can be activated using
          the undocumented "_smm_trace" parameter it looks like that many of
          the sort-based workareas like SORT ORDER BY or WINDOW SORT seem
          to suffer from the same defect, since they however are able to
          dynamically resize and make use of the workarea statistics feedback
          they effectively work as expected, they just start with a too low
          workarea size estimate every time they are executed for the first time
          after re-optimization / invalidation.

          The combination of the two issues - inability to dynamically resize and
          ignoring the optimizer estimates - leads to a dire result: Every first
          execution of a HASH UNIQUE operation in those versions will only use
          the minimum amount of memory. The following test case shows the
          problem:


           e co n iig n
          steh o tmn o

           hw aaee g
          so prmtrpa
           hw aaee rcse
          so prmtrpoess

           - le eso e okrasz_oiy aul otae_ie
          - atrssinstwrae_ieplc =mna sr_rasz =
            0000
           4000;

           rp al 1 ug;
          do tbet pre

           rp al 2 ug;
          do tbet pre

           rae al 1
          cet tbet
          as
           eet o(onm 7 s sri, onm s beti rm ul o
          slc mdrwu,2)a ue_d rwu a ojc_dfo da c
           nc y ee = 00;
          netb lvl< 300

           rae al 2
          cet tbet
          as
           eet itnt sri rm 1
          slc dsic ue_dfo t;

           xc bssasgte_al_tt(ul t' ehdot > fr
          ee dm_tt.ahrtbesasnl,'1,mto_p = 'o a
           l oun ie '
          l clmssz 1)

           xc bssasgte_al_tt(ul t' ehdot > fr
          ee dm_tt.ahrtbesasnl,'2,mto_p = 'o a
           l oun ie '
          l clmssz 1)

           oun o_a e_au o_au
          clm lwvlnwvlelwvle
           oun ihvl e_au ihvle
          clm hg_a nwvlehg_au

           eet
          slc
                      a(sri)   s o_a
                     mxue_d +1a lwvl
                      a(sri)  a(sri)  i(sri)   s ihvl
                    ,mxue_d +mxue_d -mnue_d +1a hg_a
           rm
          fo
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        29/46
8/10/12                                                Oracle related stuff: Hash Aggregation

                         2
                        t;

           - naiae n usr sn 1
          - Ivldt aycrosuigT
           omn n al 1 s '
          cmeto tbet i ';

           le eso e ttsislvl  l;
          atrssinstsaitc_ee =al

           le eso e rcfl_dniir  smtaecretcria
          atrssinsttaeieietfe ='m_rc_orc_adn
           iy;
          lt'

           le eso e _m_rc"  53;
          atrssinst"smtae =655

           eet * ul@ne 1
          slc /+fl(inrt)
                   ul@ne 2
                  fl(inrt)
                   ul@ne 3
                  fl(inrt)
                   edn(inr 2 3 1
                  laig@ne t t t)
                   s_l@ne 3
                  uen(inrt)
                   s_l@ne 1
                  uen(inrt)
                   s_ahageain@ne)
                  uehs_grgto(inr
               */
           a(sri)
          mxue_d
           rm
          fo
          (
           eet * bnm(ne) omre / itnt 1ojc_d 3ue
          slc /+q_aeinr n_eg * dsic t.beti,t.s
           _d
          ri
           rm 1 2 2 3
          fo t,t,t t
           hr 1ue_d    2ue_d
          weet.sri =t.sri
          )
          ;

           le eso e _m_rc"
          atrssinst"smtae =0;

           e aeie  ieie 0 rmpo n a f
          stpgsz 0lnsz 20tisolo tbof

           eet   rm al(bsxlndslycro(ul ul ALTT
          slc *fo tbedm_pa.ipa_usrnl,nl,'LSAS
            AT);
           LS')

           e aeie 4
          stpgsz 1

           - ug h ttsis o ht h on adnlt ewe 1 n
          - Fdetesaitc s ta teji criaiybtent ad
            2 il e  rudd p o )
           t wl b 0(one u t 1
           elr
          dcae
            rc bssassarc
           se dm_tt.tte;
            oas bssasnmra;
           nvl dm_tt.uary
            itn ubr
           dsctnme;
            vce ubr
           aglnnme;
            uln ubr
           nlctnme;
            est ubr
           dniynme;
            rc bssassarc
           se2dm_tt.tte;
           ei
          bgn
             bssasgtclm_tt(ul t' ue_d, itn > i
            dm_tt.e_ounsasnl,'1,'sri' dsct= ds
           ct vce > vce, uln > uln, est > est,
          tn,agln= agln nlct= nlct dniy= dniy s
           e > rc;
          rc= se)
             rc.p = ;
            se2ec: 2
             oas = bssasnmra(
            nvl : dm_tt.uary
             lwvle
            &o_au,
             hg_au
            &ihvle
            );
             rc.kas = ul
            se2bvl : nl;
             bssaspeaeclm_ausse2nvl)
            dm_tt.rpr_ounvle(rc,oas;
             bssasstclm_tt(
            dm_tt.e_ounsas
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        30/46
8/10/12                                                Oracle related stuff: Hash Aggregation

             wnm=nl,
            onae>ul
             anm='1,
            tbae>t'
             onm='sri'
            clae>ue_d,
             itn=dsct
            dsct>itn,
             uln=nlct
            nlct>uln,
             rc>rc,
            se=se2
             vce=agln
            agln>vce,
             est=dniy
            dniy>est
            );
           n;
          ed
          /

           - naiae n usr sn 1
          - Ivldt aycrosuigT
           omn n al 1 s '
          cmeto tbet i ';

           le eso e ttsislvl  l;
          atrssinstsaitc_ee =al

           le eso e rcfl_dniir  smtaewogcriai
          atrssinsttaeieietfe ='m_rc_rn_adnl
           y;
          t'

           le eso e _m_rc"  53;
          atrssinst"smtae =655

           eet * ul@ne 1
          slc /+fl(inrt)
                   ul@ne 2
                  fl(inrt)
                   ul@ne 3
                  fl(inrt)
                   edn(inr 2 3 1
                  laig@ne t t t)
                   s_l@ne 3
                  uen(inrt)
                   s_l@ne 1
                  uen(inrt)
                   s_ahageain@ne)
                  uehs_grgto(inr
               */
           a(sri)
          mxue_d
           rm
          fo
          (
           eet * bnm(ne) omre / itnt 1ojc_d 3ue
          slc /+q_aeinr n_eg * dsic t.beti,t.s
           _d
          ri
           rm 1 2 2 3
          fo t,t,t t
           hr 1ue_d    2ue_d
          weet.sri =t.sri
          )
          ;

           le eso e _m_rc"
          atrssinst"smtae =0;

           e aeie  ieie 0 rmpo n a f
          stpgsz 0lnsz 20tisolo tbof

           eet   rm al(bsxlndslycro(ul ul ALTT
          slc *fo tbedm_pa.ipa_usrnl,nl,'LSAS
            AT);
           LS')




          Obviously it is very similar to the HASH GROUP BY test case, this time
          however I've used a DISTINCT clause and replaced the HASH JOIN with
          a NESTED LOOP which makes the generated trace files easier to read,
          since there is exactly one workarea involved in this execution.

          And this is what I get from 11.1.0.7, this time using AMM, and therefore
          the PGA_AGGREGAT_TARGET has been set to 0 (You'll get the same
          result from 10.2.0.4 with a corresponding P_A_T setting):


           once o
          Cnetdt:
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        31/46
8/10/12                                                Oracle related stuff: Hash Aggregation
           once o
          Cnetdt:
           rce aaae 1 nepie dto ees 11070  4i
          Oal Dtbs 1gEtrrs EiinRlae1.... -6btP
           outo
          rdcin
           ih h attoig LP aa iig n el plcto et
          Wt tePriinn,OA,Dt Mnn adRa ApiainTsi
           g pin
          n otos

           Q>
          SL
           Q> hw aaee g
          SL so prmtrpa

           AE
          NM                  YE
                             TP      AU
                                    VLE
           ------------------ ----- ---------
          ------------------ ---------------
           ------
          ------
           g_grgt_agt
          paageaetre          i nee
                             bgitgr0
           Q> hw aaee rcse
          SL so prmtrpoess

           AE
          NM                   YE
                              TP     AU
                                    VLE
           ------------------ ----- ---------
          ------------------ ---------------
           ------
          ------
           qt_rcse
          a_mpoess             nee
                              itgr  0
           bwie_rcse
          d_rtrpoess           nee
                              itgr  1
           c_evrpoess
          gssre_rcse           nee
                              itgr  0
           lbltnpoess
          goa_x_rcse           nee
                              itgr  1
           o_uu_rcse
          jbqeepoess           nee
                              itgr   00
                                    10
           o_rhv_a_rcse
          lgaciemxpoess        nee
                              itgr  4
           rcse
          poess                nee
                              itgr   5
                                    10
           Q>
          SL
           Q> - le eso e okrasz_oiy  aul otae_
          SL - atrssinstwrae_ieplc =mna sr_ras
           z   0000
          ie=4000;
           Q>
          SL
           Q> rp al 1 ug;
          SL do tbet pre

           al rpe.
          Tbedopd

           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> rp al 2 ug;
          SL do tbet pre

           al rpe.
          Tbedopd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> rae al 1
          SL cet tbet
            2 as
               eet o(onm 7 s sri, onm s beti rm u
            3 slc mdrwu,2)a ue_d rwu a ojc_dfo d
           l onc y ee = 00;
          a cnetb lvl< 300

           al rae.
          Tbecetd

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL
           Q> rae al 2
          SL cet tbet
            2 as
               eet itnt sri rm 1
            3 slc dsic ue_dfo t;

           al rae.
          Tbecetd

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL
           Q> xc bssasgte_al_tt(ul t' ehdot >
          SL ee dm_tt.ahrtbesasnl,'1,mto_p = '
           o l oun ie '
          fralclmssz 1)

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        32/46
8/10/12                                                Oracle related stuff: Hash Aggregation



           lpe: 00:00
          Easd 0:00.6
           Q>
          SL
           Q> xc bssasgte_al_tt(ul t' ehdot >
          SL ee dm_tt.ahrtbesasnl,'2,mto_p = '
           o l oun ie '
          fralclmssz 1)

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> oun o_a e_au o_au
          SL clm lwvlnwvlelwvle
           Q> oun ihvl e_au ihvle
          SL clm hg_a nwvlehg_au
           Q>
          SL
           Q> eet
          SL slc
            2       a(sri)   s o_a
                   mxue_d +1a lwvl
            3       a(sri)  a(sri)  i(sri)   s ih
                  ,mxue_d +mxue_d -mnue_d +1a hg
           vl
          _a
               rm
            4 fo
            5       2
                   t;

             O_A  IHVL
            LWVL HG_A
           ----- -----
          ----- -----
               27    53

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> - naiae n usr sn 1
          SL - Ivldt aycrosuigT
           Q> omn n al 1 s '
          SL cmeto tbet i ';

           omn rae.
          Cmetcetd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e ttsislvl  l;
          SL atrssinstsaitc_ee =al

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e rcfl_dniir  smtaecretc
          SL atrssinsttaeieietfe ='m_rc_orc_a
           dnlt'
          riaiy;

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e _m_rc"  53;
          SL atrssinst"smtae =655

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> eet * ul@ne 1
          SL slc /+fl(inrt)
            2        ul@ne 2
                     fl(inrt)
            3        ul@ne 3
                     fl(inrt)
            4        edn(inr 2 3 1
                     laig@ne t t t)
            5        s_l@ne 3
                     uen(inrt)
            6        s_l@ne 1
                     uen(inrt)
            7        s_ahageain@ne)
                     uehs_grgto(inr
            8     */
               a(sri)
            9 mxue_d
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        33/46
8/10/12                                                Oracle related stuff: Hash Aggregation

            0 rm
           1 fo
            1
           1 (
            2 eet * bnm(ne) omre / itnt 1ojc_d
           1 slc /+q_aeinr n_eg * dsic t.beti,t
           .sri
          3ue_d
            3 rm 1 2 2 3
           1 fo t,t,t t
            4 hr 1ue_d   2ue_d
           1 weet.sri =t.sri
            5
           1 )
            6
           1 ;

           A(SRI)
          MXUE_D
           ------
          ------
                26

           lpe: 00:17
          Easd 0:00.9
           Q>
          SL
           Q> le eso e _m_rc"
          SL atrssinst"smtae =0;

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> e aeie   ieie 0 rmpo n a f
          SL stpgsz 0lnsz 20tisolo tbof
           Q>
          SL
           Q> eet  rm al(bsxlndslycro(ul ul AL
          SL slc *fo tbedm_pa.ipa_usrnl,nl,'L
           TT AT);
          SASLS')
           Q_D cq0uc1, hl ubr
          SLI 7nz2wsa cidnme 0
           ------------------
          -------------------
           eet * ul@ne 1
          slc /+fl(inrt)           ul@ne 2
                                  fl(inrt)
           ul@ne 3
          fl(inrt)         edn(inr 2 3 1
                          laig@ne t t t)
           s_l@ne 3
          uen(inrt)          s_l@ne 1
                            uen(inrt)
           s_ahageain@ne)
          uehs_grgto(inr         / a(sri) rm  eet
                                * mxue_d fo (slc /
          *+
           bnm(ne) omre / itnt 1ojc_d 3ue_d rm
          q_aeinr n_eg * dsic t.beti,t.sri fo t
          1,
           2 2 3 hr 1ue_d    2ue_d
          t,t t weet.sri =t.sri )

           ln ah au: 51466
          Pa hs vle 14868

           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----
          -----
             d   prto
          |I |Oeain                 ae   trs   -os
                                 |Nm |Sat |ERw |ARw | -os
             -ie |Bfes|Ras |Wie | Oe | 1e |Ue-e |
            ATm       ufr   ed     rts    Mm    Mm    sdMm
            sdTp
           Ue-m|
           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----
          -----
                 EET TTMN
          | 0|SLC SAEET          |     |   1|       |    1|0
           :00.8       07    40
          00:17 | 419| 17 | 17 |     40      |     |       |
                 |
                  OT GRGT
          | 1| SR AGEAE          |     |   1|     1|     1|0
           :00.8       07    40
          00:17 | 419| 17 | 17 |     40      |     |       |
                 |
          | 2| VE  IW            |     |   1|    7K
                                                52|     1K0
                                                        80|
           :00.7       07    40
          00:09 | 419| 17 | 17 |     40      |     |       |
                 |
          | 3|      AH NQE
                   HS UIU        |     |   1|    7K
                                                52|     1K0
                                                        80|
           :00.7       07    40
          00:09 | 419| 17 | 17 |     40    5| 26| 09 1|
                                           2M 39K 62K()
              31
             132|
          | 4|       ETD OP
                    NSE LOS      |     |   1|    1K
                                                80|     1K0
                                                        80|
           :00.1
          00:00 | 419| 07      0|     0|     |     |       |
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        34/46
8/10/12                                                Oracle related stuff: Hash Aggregation

               |
          | 5|     ETD OP
                  NSE LOS    |    |   1|   2
                                          79|  2 0
                                               79|
           :00.1
          00:00 |     4
                     8 |   0|    0|     |    |    |
               |
          | 6|     AL CES UL 2 |
                   TBEACS FL|T        1|    7
                                           2 |  7 0
                                               2 |
           :00.1
          00:00 |     3|   0|    0|     |    |    |
               |
          | 7|     AL CES UL 2 |
                   TBEACS FL|T       2 |
                                      7     7
                                           2 | 2 0
                                               79|
           :00.1
          00:00 |     1
                     8 |   0|    0|     |    |    |
               |
           *
          | 8|     AL CES UL
                  TBEACS FL |T 1 |  79| 11 |
                                     2    11   1K0
                                               80|
           :00.3    09
          00:00 | 405|     0|    0|     |    |    |
               |
           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----
          -----

           rdct nomto ietfe y prto d:
          PeiaeIfrain(dniidb oeaini)
           -------------------------
          --------------------------

                 itr"1.UE_D=T""SRI"
               8-fle(T""SRI""2.UE_D)


           0 os eetd
          3 rw slce.

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL
           Q> e aeie 4
          SL stpgsz 1
           Q>
          SL
           Q> - ug h ttsis o ht h on adnlt ewe
          SL - Fdetesaitc s ta teji criaiybtent
             n 2 il e   rudd p o )
          1adt wl b 0(one u t 1
           Q> elr
          SL dcae
                rc bssassarc
            2 se dm_tt.tte;
                oas bssasnmra;
            3 nvl dm_tt.uary
                itn ubr
            4 dsctnme;
                vce ubr
            5 aglnnme;
                uln ubr
            6 nlctnme;
                est ubr
            7 dniynme;
                rc bssassarc
            8 se2dm_tt.tte;
               ei
            9 bgn
           10    bssasgtclm_tt(ul t' ue_d, itn
                dm_tt.e_ounsasnl,'1,'sri' dsct=
             itn, vce > vce, uln > uln, est > es
          >dsct agln= agln nlct= nlct dniy= dni
           y rc > rc;
          t,se = se)
           11    rc.p = ;
                se2ec: 2
           12    oas = bssasnmra(
                nvl : dm_tt.uary
           13    lwvle
                &o_au,
           14    hg_au
                &ihvle
           15   );
           16    rc.kas = ul
                se2bvl : nl;
           17    bssaspeaeclm_ausse2nvl)
                dm_tt.rpr_ounvle(rc,oas;
           18    bssasstclm_tt(
                dm_tt.e_ounsas
           19    wnm=nl,
                onae>ul
           20    anm='1,
                tbae>t'
           21    onm='sri'
                clae>ue_d,
           22    itn=dsct
                dsct>itn,
           23    uln=nlct
                nlct>uln,
           24    rc>rc,
                se=se2
           25    vce=agln
                agln>vce,
           26    est=dniy
                dniy>est
           27   );
            8 n;
           2 ed
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        35/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           29      /
           l
          od        3
                   1:         lwvle
                             &o_au,
           e
          nw        3
                   1:              7
                                  2,
           l
          od        4
                   1:         hg_au
                             &ihvle
           e
          nw        4
                   1:             53

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL
           Q> - naiae n usr sn 1
          SL - Ivldt aycrosuigT
           Q> omn n al 1 s '
          SL cmeto tbet i ';

           omn rae.
          Cmetcetd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e ttsislvl  l;
          SL atrssinstsaitc_ee =al

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e rcfl_dniir  smtaewogcr
          SL atrssinsttaeieietfe ='m_rc_rn_ad
           nlt'
          iaiy;

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> le eso e _m_rc"  53;
          SL atrssinst"smtae =655

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> eet * ul@ne 1
          SL slc /+fl(inrt)
            2        ul@ne 2
                     fl(inrt)
            3        ul@ne 3
                     fl(inrt)
            4        edn(inr 2 3 1
                     laig@ne t t t)
            5        s_l@ne 3
                     uen(inrt)
            6        s_l@ne 1
                     uen(inrt)
            7        s_ahageain@ne)
                     uehs_grgto(inr
            8     */
               a(sri)
            9 mxue_d
            0 rm
           1 fo
            1
           1 (
            2 eet * bnm(ne) omre / itnt 1ojc_d
           1 slc /+q_aeinr n_eg * dsic t.beti,t
           .sri
          3ue_d
            3 rm 1 2 2 3
           1 fo t,t,t t
            4 hr 1ue_d    2ue_d
           1 weet.sri =t.sri
            5
           1 )
            6
           1 ;

           A(SRI)
          MXUE_D
           ------
          ------
                26

           lpe: 00:12
          Easd 0:00.6
           Q>
          SL
           Q> le eso e _m_rc"
          SL atrssinst"smtae =0;
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        36/46
8/10/12                                                Oracle related stuff: Hash Aggregation



           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> e aeie   ieie 0 rmpo n a f
          SL stpgsz 0lnsz 20tisolo tbof
           Q>
          SL
           Q> eet  rm al(bsxlndslycro(ul ul AL
          SL slc *fo tbedm_pa.ipa_usrnl,nl,'L
           TT AT);
          SASLS')
           Q_D cq0uc1, hl ubr
          SLI 7nz2wsa cidnme 0
           ------------------
          -------------------
           eet * ul@ne 1
          slc /+fl(inrt)           ul@ne 2
                                  fl(inrt)
           ul@ne 3
          fl(inrt)         edn(inr 2 3 1
                          laig@ne t t t)
           s_l@ne 3
          uen(inrt)          s_l@ne 1
                            uen(inrt)
           s_ahageain@ne)
          uehs_grgto(inr         / a(sri) rm  eet
                                * mxue_d fo (slc /
          *+
           bnm(ne) omre / itnt 1ojc_d 3ue_d rm
          q_aeinr n_eg * dsic t.beti,t.sri fo t
          1,
           2 2 3 hr 1ue_d    2ue_d
          t,t t weet.sri =t.sri )

           ln ah au: 51466
          Pa hs vle 14868

           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----
          -----
             d   prto
          |I |Oeain                 ae   trs    -os
                                 |Nm |Sat |ERw |ARw |  -os
             -ie |Bfes|Ras |Wie | Oe | 1e |Ue-e |
            ATm       ufr   ed     rts    Mm     Mm    sdMm
            sdTp
           Ue-m|
           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----
          -----
                 EET TTMN
          | 0|SLC SAEET          |     |    1|       |    1|0
           :00.6       07    40
          00:12 | 419| 17 | 17 |     40       |     |       |
                 |
                  OT GRGT
          | 1| SR AGEAE          |     |    1|     1|     1|0
           :00.6       07    40
          00:12 | 419| 17 | 17 |     40       |     |       |
                 |
          | 2| VE  IW            |     |    1|     1|    1K0
                                                         80|
           :00.8       07    40
          00:09 | 419| 17 | 17 |     40       |     |       |
                 |
          | 3|      AH NQE
                   HS UIU        |     |    1|     1|    1K0
                                                         80|
           :00.8       07    40
          00:09 | 419| 17 | 17 |     40     5| 26| 03 1|
                                           2M 39K 69K()
              31
             132|
          | 4|       ETD OP
                    NSE LOS      |     |    1|     1|    1K0
                                                         80|
           :00.1
          00:00 | 419| 07      0|     0|      |     |       |
                 |
          | 5|        ETD OP
                     NSE LOS     |     |    1|    2
                                                 79|     2 0
                                                         79|
           :00.1
          00:00 |        4
                        8 |    0|     0|      |     |       |
                 |
          | 6|        AL CES UL 2 |
                      TBEACS FL|T           1|     7
                                                  2 |     7 0
                                                         2 |
           :00.1
          00:00 |        3|    0|     0|      |     |       |
                 |
          | 7|        AL CES UL 2 |
                      TBEACS FL|T          2 |
                                            7      7
                                                  2 |    2 0
                                                         79|
           :00.1
          00:00 |        1
                        8 |    0|     0|      |     |       |
                 |
           *
          | 8|        AL CES UL
                     TBEACS FL |T   1 |   79|
                                           2       1|    1K0
                                                         80|
           :00.1
          00:00 | 405| 09      0|     0|      |     |       |
                 |
           ---------------------------------
          ----------------------------------
           ---------------------------------
          ----------------------------------
           -----
          -----
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        37/46
8/10/12                                                Oracle related stuff: Hash Aggregation



           rdct nomto ietfe y prto d:
          PeiaeIfrain(dniidb oeaini)
           -------------------------
          --------------------------

                 itr"1.UE_D=T""SRI"
               8-fle(T""SRI""2.UE_D)


           0 os eetd
          3 rw slce.

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL




          As you can see no matter what the estimates looks like, on this system
          the first execution of a HASH UNIQUE will not get more than 6M -
          subsequent executions of the same cursor benefit from the statistics
          from the previous run as seen before.

          Again, switching to a SORT UNIQUE and reducing the data set
          accordingly, the problem can not be reproduced:


           once o
          Cnetdt:
           rce aaae 1 nepie dto ees 11070  4i
          Oal Dtbs 1gEtrrs EiinRlae1.... -6btP
           outo
          rdcin
           ih h attoig LP aa iig n el plcto et
          Wt tePriinn,OA,Dt Mnn adRa ApiainTsi
           g pin
          n otos

           Q>
          SL
           Q> hw aaee g
          SL so prmtrpa

           AE
          NM                  YE
                             TP      AU
                                    VLE
           ------------------ ----- ---------
          ------------------ ---------------
           ------
          ------
           g_grgt_agt
          paageaetre          i nee
                             bgitgr0
           Q> hw aaee rcse
          SL so prmtrpoess

           AE
          NM                  YE
                             TP      AU
                                    VLE
           ------------------ ----- ---------
          ------------------ ---------------
           ------
          ------
           qt_rcse
          a_mpoess            nee
                             itgr   0
           bwie_rcse
          d_rtrpoess          nee
                             itgr   1
           c_evrpoess
          gssre_rcse          nee
                             itgr   0
           lbltnpoess
          goa_x_rcse          nee
                             itgr   1
           o_uu_rcse
          jbqeepoess          nee
                             itgr    00
                                    10
           o_rhv_a_rcse
          lgaciemxpoess       nee
                             itgr   4
           rcse
          poess               nee
                             itgr    5
                                    10
           Q>
          SL
           Q> rp al 1 ug;
          SL do tbet pre

           al rpe.
          Tbedopd

           lpe: 00:01
          Easd 0:00.5
           Q>
          SL
           Q> rp al 2 ug;
          SL do tbet pre

           al rpe.
          Tbedopd

           lpe: 00:00
          Easd 0:00.7
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        38/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           Q>
          SL
           Q> rae al 1
          SL cet tbet
            2 as
               eet o(onm 5 s sri, onm s beti rm u
            3 slc mdrwu,1)a ue_d rwu a ojc_dfo d
           l onc y ee = 00;
          a cnetb lvl< 300

           al rae.
          Tbecetd

           lpe: 00:00
          Easd 0:00.6
           Q>
          SL
           Q> rae al 2
          SL cet tbet
            2 as
               eet itnt sri rm 1
            3 slc dsic ue_dfo t;

           al rae.
          Tbecetd

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL
           Q> xc bssasgte_al_tt(ul t' ehdot >
          SL ee dm_tt.ahrtbesasnl,'1,mto_p = '
           o l oun ie '
          fralclmssz 1)

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.4
           Q>
          SL
           Q> xc bssasgte_al_tt(ul t' ehdot >
          SL ee dm_tt.ahrtbesasnl,'2,mto_p = '
           o l oun ie '
          fralclmssz 1)

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.3
           Q>
          SL
           Q> oun o_a e_au o_au
          SL clm lwvlnwvlelwvle
           Q> oun ihvl e_au ihvle
          SL clm hg_a nwvlehg_au
           Q>
          SL
           Q> eet
          SL slc
            2       a(sri)   s o_a
                   mxue_d +1a lwvl
            3       a(sri)  a(sri)  i(sri)   s ih
                  ,mxue_d +mxue_d -mnue_d +1a hg
           vl
          _a
               rm
            4 fo
            5       2
                   t;

             O_A  IHVL
            LWVL HG_A
           ----- -----
          ----- -----
               15    29

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> - naiae n usr sn 1
          SL - Ivldt aycrosuigT
           Q> omn n al 1 s '
          SL cmeto tbet i ';

           omn rae.
          Cmetcetd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e ttsislvl  l;
          SL atrssinstsaitc_ee =al

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        39/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           Q> le eso e rcfl_dniir  smtaecretc
          SL atrssinsttaeieietfe ='m_rc_orc_a
           dnlt'
          riaiy;

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e _m_rc"  53;
          SL atrssinst"smtae =655

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> eet * ul@ne 1
          SL slc /+fl(inrt)
            2        ul@ne 2
                     fl(inrt)
            3        ul@ne 3
                     fl(inrt)
            4        edn(inr 2 3 1
                     laig@ne t t t)
            5        s_l@ne 3
                     uen(inrt)
            6        s_l@ne 1
                     uen(inrt)
            7        osa_onipt(inr 1
                     n_wpji_nus@ne t)
            8        ouehs_grgto(inr
                     n_s_ahageain@ne)
            9     */
            0 a(sri)
           1 mxue_d
            1 rm
           1 fo
            2
           1 (
            3 eet * bnm(ne) omre / itnt 1ojc_d
           1 slc /+q_aeinr n_eg * dsic t.beti,t
           .sri
          3ue_d
            4 rm 1 2 2 3
           1 fo t,t,t t
            5 hr 1ue_d    2ue_d
           1 weet.sri =t.sri
            6
           1 )
            7
           1 ;

           A(SRI)
          MXUE_D
           ------
          ------
                14

           lpe: 00:07
          Easd 0:00.3
           Q>
          SL
           Q> le eso e _m_rc"
          SL atrssinst"smtae =0;

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> e aeie   ieie 0 rmpo n a f
          SL stpgsz 0lnsz 20tisolo tbof
           Q>
          SL
           Q> eet  rm al(bsxlndslycro(ul ul AL
          SL slc *fo tbedm_pa.ipa_usrnl,nl,'L
           TT AT);
          SASLS')
           Q_D k2b321, hl ubr
          SLI cbszyz4 cidnme 0
           ------------------
          -------------------
           eet * ul@ne 1
          slc /+fl(inrt)          ul@ne 2
                                 fl(inrt)
           ul@ne 3
          fl(inrt)         edn(inr 2 3 1
                          laig@ne t t t)
           s_l@ne 3
          uen(inrt)          s_l@ne 1
                            uen(inrt)
           osa_onipt(inr 1
          n_wpji_nus@ne t)
           ouehs_grgto(inr
          n_s_ahageain@ne)         / a(sri) rm  ee
                                  * mxue_d fo (slc
          t
           * bnm(ne) omre / itnt 1ojc_d 3ue_d r
          /+q_aeinr n_eg * dsic t.beti,t.sri f
          om
           1 2 2 3 hr 1ue_d    2ue_d
          t,t,t t weet.sri =t.sri )

           ln ah au: 88002
          Pa hs vle 32330
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        40/46
8/10/12                                                Oracle related stuff: Hash Aggregation



           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------
             d  prto
          |I |Oeain                 ae    trs     -os
                                 |Nm |Sat |ERw |ARw |    -os
             -ie |Bfes| Oe | 1e |Ue-e |
            ATm      ufr    Mm     Mm    sdMm
           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------
                EET TTMN
          | 0|SLC SAEET          |     |      1|       |   1|0
           :00.1
          00:07 | 143|22       |      |         |
                 OT GRGT
          | 1| SR AGEAE          |     |      1|     1|    1|0
           :00.1
          00:07 | 143|22       |      |         |
          | 2| VE IW             |     |      1|    1K
                                                   38|    5K0
                                                          40|
           :00.2
          00:06 | 143|22       |      |         |
          | 3|     OT NQE
                  SR UIU         |     |      1|    1K
                                                   38|    5K0
                                                          40|
           :00.2
          00:06 | 143|22      2| 74| 2M()
                             2M 14K        0 0|
          | 4|      ETD OP
                   NSE LOS       |     |      1|    5K
                                                   40|    5K0
                                                          40|
           :00.1
          00:00 | 143|22       |      |         |
          | 5|       ETD OP
                    NSE LOS      |     |      1|    2
                                                   25|    2 0
                                                          25|
           :00.1
          00:00 |       8
                       4 |     |      |         |
          | 6|       AL CES UL 2 |
                     TBEACS FL|T              1|     5
                                                    1 |    5 0
                                                          1 |
           :00.1
          00:00 |       3|     |      |         |
          | 7|       AL CES UL 2 |
                     TBEACS FL|T             1 |
                                              5      5
                                                    1 |   2 0
                                                          25|
           :00.1
          00:00 |       5
                       4 |     |      |         |
           *
          | 8|       AL CES UL
                    TBEACS FL |T    1 |     25| 20 |
                                             2     00     5K0
                                                          40|
           :00.1
          00:00 | 135|27       |      |         |
           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------

           rdct nomto ietfe y prto d:
          PeiaeIfrain(dniidb oeaini)
           -------------------------
          --------------------------

                 itr"1.UE_D=T""SRI"
               8-fle(T""SRI""2.UE_D)


           1 os eetd
          3 rw slce.

           lpe: 00:00
          Easd 0:00.6
           Q>
          SL
           Q> e aeie 4
          SL stpgsz 1
           Q>
          SL
           Q> - ug h ttsis o ht h on adnlt ewe
          SL - Fdetesaitc s ta teji criaiybtent
             n 2 il e   rudd p o )
          1adt wl b 0(one u t 1
           Q> elr
          SL dcae
                rc bssassarc
            2 se dm_tt.tte;
                oas bssasnmra;
            3 nvl dm_tt.uary
                itn ubr
            4 dsctnme;
                vce ubr
            5 aglnnme;
                uln ubr
            6 nlctnme;
                est ubr
            7 dniynme;
                rc bssassarc
            8 se2dm_tt.tte;
               ei
            9 bgn
           10    bssasgtclm_tt(ul t' ue_d, itn
                dm_tt.e_ounsasnl,'1,'sri' dsct=
             itn, vce > vce, uln > uln, est > es
          >dsct agln= agln nlct= nlct dniy= dni
           y rc > rc;
          t,se = se)
           11    rc.p = ;
                se2ec: 2
           12    oas = bssasnmra(
                nvl : dm_tt.uary
           13    lwvle
                &o_au,
           14    hg_au
                &ihvle
           15   );
           16    rc.kas = ul
                se2bvl : nl;
           17    bssaspeaeclm_ausse2nvl)
                dm_tt.rpr_ounvle(rc,oas;
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        41/46
8/10/12                                                Oracle related stuff: Hash Aggregation

           18         bssasstclm_tt(
                     dm_tt.e_ounsas
           19         wnm=nl,
                     onae>ul
           20         anm='1,
                     tbae>t'
           21         onm='sri'
                     clae>ue_d,
           22         itn=dsct
                     dsct>itn,
           23         uln=nlct
                     nlct>uln,
           24         rc>rc,
                     se=se2
           25         vce=agln
                     agln>vce,
           26         est=dniy
                     dniy>est
           27        );
           28       n;
                   ed
           29      /
           l
          od        3   lwvle
                   1: &o_au,
           e
          nw        3
                   1:        5
                            1,
           l
          od        4   hg_au
                   1: &ihvle
           e
          nw        4
                   1:       29

           LSL rcdr ucsfly opee.
          P/Q poeuescesul cmltd

           lpe: 00:00
          Easd 0:00.6
           Q>
          SL
           Q> - naiae n usr sn 1
          SL - Ivldt aycrosuigT
           Q> omn n al 1 s '
          SL cmeto tbet i ';

           omn rae.
          Cmetcetd

           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> le eso e ttsislvl  l;
          SL atrssinstsaitc_ee =al

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> le eso e rcfl_dniir  smtaewogcr
          SL atrssinsttaeieietfe ='m_rc_rn_ad
           nlt'
          iaiy;

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> le eso e _m_rc"  53;
          SL atrssinst"smtae =655

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> eet * ul@ne 1
          SL slc /+fl(inrt)
            2       ul@ne 2
                    fl(inrt)
            3       ul@ne 3
                    fl(inrt)
            4       edn(inr 2 3 1
                    laig@ne t t t)
            5       s_l@ne 3
                    uen(inrt)
            6       s_l@ne 1
                    uen(inrt)
            7       osa_onipt(inr 1
                    n_wpji_nus@ne t)
            8       ouehs_grgto(inr
                    n_s_ahageain@ne)
            9    */
            0 a(sri)
           1 mxue_d
            1 rm
           1 fo
            2
           1 (
            3 eet * bnm(ne) omre / itnt 1ojc_d
           1 slc /+q_aeinr n_eg * dsic t.beti,t
           .sri
          3ue_d
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        42/46
8/10/12                                                Oracle related stuff: Hash Aggregation

            14      rm 1 2 2 3
                   fo t,t,t t
            15      hr 1ue_d   2ue_d
                   weet.sri =t.sri
            16     )
            17     ;

           A(SRI)
          MXUE_D
           ------
          ------
                14

           lpe: 00:07
          Easd 0:00.4
           Q>
          SL
           Q> le eso e _m_rc"
          SL atrssinst"smtae =0;

           eso lee.
          Ssinatrd

           lpe: 00:00
          Easd 0:00.0
           Q>
          SL
           Q> e aeie   ieie 0 rmpo n a f
          SL stpgsz 0lnsz 20tisolo tbof
           Q>
          SL
           Q> eet  rm al(bsxlndslycro(ul ul AL
          SL slc *fo tbedm_pa.ipa_usrnl,nl,'L
           TT AT);
          SASLS')
           Q_D k2b321, hl ubr
          SLI cbszyz4 cidnme 0
           ------------------
          -------------------
           eet * ul@ne 1
          slc /+fl(inrt)          ul@ne 2
                                 fl(inrt)
           ul@ne 3
          fl(inrt)         edn(inr 2 3 1
                          laig@ne t t t)
           s_l@ne 3
          uen(inrt)          s_l@ne 1
                            uen(inrt)
           osa_onipt(inr 1
          n_wpji_nus@ne t)
           ouehs_grgto(inr
          n_s_ahageain@ne)         / a(sri) rm  ee
                                  * mxue_d fo (slc
          t
           * bnm(ne) omre / itnt 1ojc_d 3ue_d r
          /+q_aeinr n_eg * dsic t.beti,t.sri f
          om
           1 2 2 3 hr 1ue_d    2ue_d
          t,t,t t weet.sri =t.sri )

           ln ah au: 88002
          Pa hs vle 32330

           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------
             d  prto
          |I |Oeain                 ae    trs     -os
                                 |Nm |Sat |ERw |ARw |    -os
             -ie |Bfes| Oe | 1e |Ue-e |
            ATm      ufr    Mm     Mm    sdMm
           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------
                EET TTMN
          | 0|SLC SAEET          |     |      1|       |   1|0
           :00.3
          00:07 | 143|22       |      |         |
                 OT GRGT
          | 1| SR AGEAE          |     |      1|     1|    1|0
           :00.3
          00:07 | 143|22       |      |         |
          | 2| VE IW             |     |      1|     1|   5K0
                                                          40|
           :00.1
          00:06 | 143|22       |      |         |
          | 3|     OT NQE
                  SR UIU         |     |      1|     1|   5K0
                                                          40|
           :00.1
          00:06 | 143|22      2| 74| 2M()
                             2M 14K        0 0|
          | 4|      ETD OP
                   NSE LOS       |     |      1|     1|   5K0
                                                          40|
           :00.1
          00:00 | 143|22       |      |         |
          | 5|       ETD OP
                    NSE LOS      |     |      1|    2
                                                   25|    2 0
                                                          25|
           :00.1
          00:00 |       8
                       4 |     |      |         |
          | 6|       AL CES UL 2 |
                     TBEACS FL|T              1|     5
                                                    1 |    5 0
                                                          1 |
           :00.1
          00:00 |       3|     |      |         |
          | 7|       AL CES UL 2 |
                     TBEACS FL|T             1 |
                                              5      5
                                                    1 |   2 0
                                                          25|
           :00.1
          00:00 |       5
                       4 |     |      |         |
           *
          | 8|       AL CES UL
                    TBEACS FL |T    1 |     25|
                                             2       1|   5K0
                                                          40|
           :00.1
          00:00 | 135|27       |      |         |
           ---------------------------------
          ----------------------------------
           ------------------------
          -------------------------
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        43/46
8/10/12                                                Oracle related stuff: Hash Aggregation



           rdct nomto ietfe y prto d:
          PeiaeIfrain(dniidb oeaini)
           -------------------------
          --------------------------

                 itr"1.UE_D=T""SRI"
               8-fle(T""SRI""2.UE_D)


           1 os eetd
          3 rw slce.

           lpe: 00:00
          Easd 0:00.1
           Q>
          SL
           Q> e o f
          SL stdcof
           Q> o
          SL dc
           Q>
          SL




          In 10.2.0.5 and 11.2.0.1/11.2.0.2 this issue is apparently fixed and the
          cardinality estimates are used for an initial workarea size calculation -
          however this doesn't mean that the dynamic resize problem is fixed - it
          simply means that the HASH UNIQUE in those versions behaves exactly
          as the HASH GROUP BY and the memory usage for the first execution
          depends on the cardinality estimates.

          Summary

          If your database uses automatic PGA management then for the hash
          aggregation HASH GROUP BY / HASH UNIQUE operations every initial
          execution that is based on bad cardinality estimates potentially has a
          problem because it might not make efficient use of the available
          memory.

          The same applies to applications that share cursors, however in that
          case only the initial execution after re-optimization / invalidation is
          affected, subsequent executions benefit from the workarea statistics
          feedback mechanism.

          Furthermore in 10g versions below 10.2.0.5 and 11g versions below
          11.2.0.1 the initial execution of a HASH UNIQUE operation ignores the
          cardinality estimates and will always be based on minimum assumptions.

          So you might want to carefully check the runtime execution statistics of
          your critical hash aggregations.

          Possible Workarounds

          Different strategies are available as workarounds, depending on the
          situation:

          - Upgrading to versions where the HASH UNIQUE operation at least
          considers the optimizer estimates might be beneficial

          - Obviously good cardinality estimates are crucial, in that case and
          when using the correct Oracle versions you should be fine. Using the
          undocumented OPT_ESTIMATE hint (or the deprecated undocumented
          CARDINALITY hint) might help in cases where other options like manually
          crafted statistics are not able to help the optimizer to come up with
          reasonable cardinality estimates.
oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                        44/46
8/10/12                                                Oracle related stuff: Hash Aggregation



          - Applications that are able to share cursors might not be too much
          affected due to the ability to use the workarea executions for
          subsequent executions of the same cursor

          - The described problems disappear when switching to manual workarea
          size policy and allowing for sufficient memory of workarea. Interestingly
          the less obvious SORT_AREA_SIZE is used for the manual control of the
          HASH GROUP BY operation, and not the HASH_AREA_SIZE. Of course
          using manual PGA memory management system-wide is highly
          unrecommended, so this might only be a workaround in certain cases of
          large batch jobs where manual workarea sizes are used anyway. Also
          note, when switching to manual workareas be aware of a nasty bug
          that was introduced in the 10.2.0.3 patchset. For more information see
          MOS note "6053134.8: ALTER SESSION to set SORT_AREA_SIZE no
          honoured" and for example mine and Jonathen Lewis' post about the
          issue. According to the MOS note the bug has been fixed in 10.2.0.4.3,
          10.2.0.5 and 11.1.0.7 / 11.2

          - Obviously the hash aggregation can be avoided on statement level
          using the NO_USE_HASH_AGGREGATION hint or on session / system
          level using the _GBY_HASH_AGGREGATION_ENABLED parameter. Since
          the sort based aggregation can be less efficient (but note that it
          doesn't have to be, it depends on the individual grouping and data
          pattern) again this doesn't necessarily solve the problem since
          aggregate workareas might spill to disk which wouldn't be necessary
          when hash aggregation was used and worked as expected.

          Final Note

          Of course there are a lot of things that I haven't touched yet or only
          briefly, like Parallel Execution of the hash aggregations, workareas used
          for index sorts, and all the other details when the workarea spills to
          disk.

          If you want to get an idea what kind of nasty things can happen in that
          case you might want to read Jonathan Lewis' post on analytic functions.

          I also haven't tried yet to fiddle around with the undocumented _smm*
          related parameters if setting them to non-default values allows to work
          around the issue.

          And of course then there are bugs like this one: Bug 6817844 - Multi
          pass sort with auto memory management even with plenty of PGA [ID
          6817844.8], which is also mentioned in Jonathan's post.

          As a final note, the odd TEMP tablespace I/O pattern issue that
          Jonathan describes in his post (Bug 9041800) is marked as fixed in the
          11.2.0.2 patch set, but I haven't tested this yet.

          Eingestellt von Randolf um 12:56 PM

                                     Recommend this on Google



          Labels: 10.2.0.4, 10.2.0.5, 10gR2, 11.1.0.7, 11.2.0.1, 11.2.0.2, 11g, bug, C BO, Hash
          Aggregation, Performance, PGA, PGA_AGGREGATE_TARGET,
          WORKAREA_SIZE_POLIC Y

oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                          45/46
8/10/12                                                Oracle related stuff: Hash Aggregation




          No comments:

          Post a Comment

          Newer Post                                           Home                             Older Post

          Subscribe to: Post Comments (Atom)




oracle-randolf.blogspot.ca/2011/01/hash-aggregation.html                                                     46/46

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:8/10/2012
language:English
pages:46