Avatar of scott_m_ruby
scott_m_ruby
 asked on

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         
Oracle Database

Avatar of undefined
Last Comment
Computer101

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
sathyagiri

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
rbrooker

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
scott_m_ruby

ASKER
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.  
rbrooker

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_ruby

ASKER
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;
Your help has saved me hundreds of hours of internet surfing.
fblack61
rbrooker

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_ruby

ASKER
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.
rbrooker

Hi,

have a look at this, see if this helps.
http://www.freelists.org/archives/oracle-l/07-2004/msg02383.html
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rbrooker

also :
http://vsbabu.org/oracle/sect01.html

init.ora parameter, HASH_JOIN_ENABLED=FALSE would be a global fix
scott_m_ruby

ASKER
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.  
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Computer101

Forced accept.

Computer101
EE Admin
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy