Re: How to force hash and merge joins

If you are having trouble getting the optimizer to use hash joins, investigate the values for the HASH_AREA_SIZE and HASH_JOIN_ENABLED parameters. You can apply the USE_HASH hint to advise the optimizer to use a hash join if all fail.

To advise the optimizer to use a sort merge join, apply the USE_MERGE hint.

HTH,
http://www.webyourcompany.com

ed.stevens_at_comcast.net (Ed Stevens) wrote in message news:<1512dfb8.0305311413.64fe12f1_at_posting.google.com>...
> ubject: How to force hash and merge joins
>
> Developers put a new system into production yesterday. (By new
> system, I mean additional application functions - new screens,
> reports, etc. , a few new tables - on an existing database). Last
> night I got a call from the lead developer that the reports were
> running so long the web front end was timing out. He had isolated the
> queries and run them in SQLPlus and found they were running 2 to 5
> minutes in production and 2 seconds or less in test.
>
> Platforms: Test is running 8.1.7 SE on Win2k. Production is running
> 8.0.5 SE on NT. Yes, I know we've got apples and oranges, but read
> on. I don't think that is the key.
>
> We loaded the production data into the test db and ran stats to ensure
> same data volumes and stats. Then we ran explain plans on both
> systems. On production (the poor performer) it yielded a full table
> scan within a 3-deep nested loop. On the test system, it yielded a
> full table scan within a hash join and a Cartesian merge join.
>
> At this point we check v$parameter for any differences that might
> impact the plan. We found that on the test (fast) system
> HASH_AREA_SIZE was 1638400, while on the prod system (slow) it was
> zero. AHAH! We said. Logged on to the prod system, altered session
> to set HASH_AREA_SIZE=1638400, and ran the explain plan. No joy . . .
> still yielding a 3-deep nested loop. I have also compared index
> structures between both systems and found them to be identical.
>
> I am not posting the SQL or explain plans because (they are pretty
> involved and tend to line wrap so badly (when posted) as to make them
> very difficult to read. Also, I really think I'm asking a broader
> question that goes beyond the specific query - how to influence a plan
> toward hash and merge joins instead of nested loops.
>
> While there may be things that could be done with the query itself,
> the fact that we are able to get a fast performing hash join on the
> one system leads me to think that there's something I can do as a DBA
> to force it on the other system.
>
> I'm going home to study my Kyte and Harrison books, but wanted to get
> this out in hopes of eliciting some response in time to start cracking
> on it first thing Monday morning.
Received on Sat May 31 2003 - 22:35:51 CDT