troubleshooting Question

Oracle 10g query using hash joins paging to TEMP tablespace runs forever

Avatar of scott_m_ruby
scott_m_ruby asked on
Oracle Database
12 Comments3 Solutions2851 ViewsLast Modified:
I had a proc that was performing well in oracle 9i with an execution plan that was not using hash joins. I upgraded to Oracle 10 and the optimizer is using hash joins for the same proc. It ends up paging to the TEMP tablesspace and takes forever. I increased my hash_area_size ( alter system set hash_area_size=1048576000 SCOPE=spfile; ) but this did not solve my problem. I appreciate any ideas on what is happening. The Explain Plan is shown below:

Operation      Object Name      Rows      Bytes      Cost      Object Node      In/Out      PStart      PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS            11               5550                                              
  FILTER                                                                        
    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                                              
                            FILTER                                                                        
                              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         
ASKER CERTIFIED SOLUTION
sathyagiri

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros