1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle query using hash joins

Discussion in 'SQL PL/SQL' started by professional, Aug 13, 2009.

  1. professional

    professional Active Member

    Likes Received:
    Trophy Points:
    Hi Friends

    I have a procedure which I just migrated from Oracle 9i to 10g. The procedure was performing fine in 9i. The execution plan in 9i was not using hash joins. After migrating this procedure to 10g and the optimizer is using hash joins. The query it seems is paging to the TEMP tablespace and is taking a long long time. What i did was thatt I increased my hash_area_size :

    Code (SQL):
    ALTER system SET hash_area_size=1048576000 SCOPE=spfile;
    However the above did not solve the problem. The Explain Plan is pasted below. DO you guys have any suggestion?

    Code (Text):
    Operation      Object Name      Rows      Bytes      Cost      Object Node      In/Out      PStart      PStop

    SELECT STATEMENT Optimizer Mode=ALL_ROWS            11               5550                                              
        HASH GROUP BY            11        3 K      5550                                              
          HASH JOIN            1 M      567 M      5337                                              
            HASH JOIN            1 K      375 K      4166                                              
              TABLE ACCESS FULL      HWA.APPLICATION_USER      202        6 K      2                                              
              MERGE JOIN CARTESIAN            1 K      340 K      4164                                              
                NESTED LOOPS            276        73 K      3979                                              
                  NESTED LOOPS            276        69 K      3979                                              
                    NESTED LOOPS            276        63 K      3977                                              
                      NESTED LOOPS OUTER            276        43 K      3701                                              
                        HASH JOIN            276        39 K      3701                                              
                          HASH JOIN            276        28 K      3510                                              
                            HASH JOIN            279        23 K      1704                                              
                              NESTED LOOPS            328        23 K      1497                                              
                                TABLE ACCESS FULL      HWA.POLICY      324        17 K      1497                                              
                                INDEX UNIQUE SCAN      HWA.XPKPOLICY      1        17        0                                              
                              TABLE ACCESS FULL      HWA.OUT_HWA_SALESPERSON      152 K      2 M      205                                              
                            VIEW            151 K      2 M      1803                                              
                              HASH GROUP BY            151 K      2 M      1803                                              
                                  MERGE JOIN            153 K      2 M      1803                                              
                                    INDEX FULL SCAN      HWA.XPKPOLICY      180 K      882 K      384                                              
                                    SORT JOIN            152 K      1 M      1419                                              
                                      TABLE ACCESS FULL      HWA.OUT_HWA_SALESPERSON      152 K      1 M      205                                              
                          TABLE ACCESS FULL      HWA.SALES_AGENT      55 K      2 M      190                                              
                        INDEX UNIQUE SCAN      HWA.XPKSTATE      1        17        0                                              
                      TABLE ACCESS BY INDEX ROWID      HWA.SALES_AGENT_COMPANY      1        73        1                                              
                        INDEX UNIQUE SCAN      HWA.XPKSALES_AGENT_COMPANY      1               0                                              
                    TABLE ACCESS BY INDEX ROWID      HWA.STATE      1        20        1                                              
                      INDEX UNIQUE SCAN      HWA.XPKSTATE      1               0                                              
                  INDEX UNIQUE SCAN      HWA.XPKLOOKUP      1        18        0                                              
                BUFFER SORT            4        164        4164                                              
                  TABLE ACCESS FULL      HWA.HWA_SALESPERSON      4        164        1                                              
            TABLE ACCESS FULL      HWA.OUT_HWA_SALESPERSON      152 K      3 M      202
  2. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO
    You DID restart the database after the spfile change? It doesn't appear so. Simply changing the spfile does only that, nothing more. You could have tested this before editing the spfile by altering your current session:

    alter session set hash_area_size = 1048576000;

    and running the query again. The hash_area_size is set at the session level and the value in the pfile/spfile is your global setting that each session uses upon connecting to the database.

    You failed to post the 'acceptable' plan from the 9i database; did you not generate a plan from that releaase? There is too much information missing from this post to give you a reason why this query now performs so 'poorly'. Possibly you can correct that by posting the following details:

    * 9i query plan
    * Table definitions, including indexes
    * init.ora settings
    * Actual release numbers of the '9i' and '10g' databases ('9i' and '10g' are marketing blurbs, not useful release information)
  3. tyro

    tyro Forum Genius

    Likes Received:
    Trophy Points:
    I suggest generate statistics on your indexes and tables and run the query again and see if it makes a difference.