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

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         
scott_m_rubyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sathyagiriCommented:
Generate statistics on your indexes and tables and run the query again and see if it makes a difference.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rbrookerCommented:
Hi,

to change your hash_area_size, you need to "alter system set hash_area_size=nnn scope=both;"  scope=spfile changes the spfile, but does not make the setting take effect, you would need to bounce.  scope=both changes both the spfile and the running value.

you could also change optimizer modes to cbo, see if that helps.

good luck. :)
scott_m_rubyAuthor Commented:
i received an error when I run "alter system set hash_area_size=nnn scope=both;". But I did bounce the instance and I still get the same problem. I have 4gb RAM on this server and would like to give as much as possible to Oracle to avoid this paging. Does anyone know of another setting to get past this.  
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

rbrookerCommented:
Hi,

have you looked at your other question?  its said there that you should be setting PGA_AGGREGATE_TARGET.  try setting that to a larger number and see what happens.
scott_m_rubyAuthor Commented:
I have tried adjusting all relevant settings as follows. I did bounce the instance after issuing the Alters, with no luck:

alter system set workarea_size_policy=auto SCOPE=both;
alter system set SGA_MAX_SIZE='1G' SCOPE=spfile;
alter system set SGA_TARGET='1G' SCOPE=spfile;
alter system set PGA_AGGREGATE_TARGET='400M' SCOPE=spfile;
rbrookerCommented:
Hi,

after each scope=spfile, i hope you bounced the database else the changes would not have taken effect.
can you post the query sql? perhaps there are a few hints that can be applied to it to change the execution plan.
scott_m_rubyAuthor Commented:
I am able to get this particular query to run similarly to how it did in 9i by using the "no_cpu_costing" hint. But, this hash join problem will affect any query written and some are beyond by control. So, I am looking for a global solution to this issue. My database is small ( roughly 385mb) with no tables having more than 200,000 rows.
rbrookerCommented:
Hi,

have a look at this, see if this helps.
http://www.freelists.org/archives/oracle-l/07-2004/msg02383.html
rbrookerCommented:
also :
http://vsbabu.org/oracle/sect01.html

init.ora parameter, HASH_JOIN_ENABLED=FALSE would be a global fix
scott_m_rubyAuthor Commented:
rbrooker,

Thanks for all your comments. Unfortunately, the HASH_JOIN_ENABLED parameter is no longer valid in Oracle 10g. That is the type of global fix I was looking for. I have read all the links you posted, but nothing has helped with this problem.  
MohanKNairCommented:
Verify whether schema statistics are updated. Perhaps some more indexes may need to be created. NESTED LOOPS join uses indexes.

>>  MERGE JOIN CARTESIAN          1 K     340 K     4164    
The explain plan shows cartesian join. Check whether all tables appear in the where clause.
Computer101Commented:
Forced accept.

Computer101
EE Admin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.